Forums

I have created a table in Joomla called event_schedules that has the below structure:

id (auto increment)
event_id
event_date
event_time

I have created a chronoform and created a table for the results to be stored in, that has the below structure:

name
other_names
address
city
state
zip_code
email_address
date (yyyy-mm-dd)
time (HH:MM:SS)

On my form, I have a date/time dropdown box that I want to display dates/times from the event_schedules table where they have not already been submitted to the chronoform table. It's kind of a reservation form, so 2 people can't reserve the same date/time. Below is the sql I'm using, but it's returning 0 rows, and I'm not sure why. I currently don't have any records in my chronoform table. Could someone give me a hand? Below is the sql I have:

SELECT es.id, DATE_FORMAT(es.event_date, '%M %d, %Y') AS date, TIME_FORMAT(es.event_time, '%h:%i %p') AS time FROM jos_event_schedules es, jos_chronoforms_photo_directory cpd where es.event_date != cpd.date AND es.event_time != cpd.time AND es.event_id = 1 order by date asc, time asc

Thanks

Hi jrthor2,

I'm no MySQL expert :-( I think you may need to specify a LEFT JOIN here to get a result when one of the tables is empty. The implicit INNER JOIN will find no matching records for this clause

where es.event_date != cpd.date AND es.event_time != cpd.time

You could also try:

WHERE (cpd.`date` = '' OR es.`event_date` != cpd.date) AND (cpd.`time` = '' OR es.`event_time` != cpd.`time`)

I generally find it helps to debug this kind of code with PHPMyAdmin.

Bob

The below also returned and empty result set:

SELECT es.id, DATE_FORMAT(es.event_date, '%M %d, %Y') AS date, TIME_FORMAT(es.event_time, '%h:%i %p') AS time FROM jos_event_schedules es, jos_chronoforms_photo_directory cpd WHERE (cpd.`date` = '' OR es.`event_date` != cpd.date) AND (cpd.`time` = '' OR es.`event_time` != cpd.`time`)