Filtering & sorting by time & date

[Guest]

I have an events list that is generated in a dynamic table. As it is now, the SQL query includes:

WHERE `date` >= curdate()

so every night at midnight, that day's events disappear. There is also a shorter events list on the main page of the site that is LIMIT 5, so each day at midnight, it displays more new events as the old ones disappear.

I wanted to try to have it remove an item as soon as it's "end_time" in the DB has passed.  I tried using:

WHERE `date` >= curdate() AND end_time >= curtime()

but, that treats each separately (curdate/curtime), so that it is currently 9:00pm, and an event is tomorrow from 6pm-8pm, it does not show up (because of the end_time >= curtime() string. That didn't really surprise me once I saw it, though I didn't expect it at first (obviously.)

Does anyone know how I need to tweak this code to  do what I'm trying to do?  I also tried these two on a whim:

WHERE `date` >= curdate(), curtime()

WHERE `date` >= curdate() AND end_time >= NOW()

But both of those returned errors.

[bregent]

You were close. NOW() returns both date and time.

WHERE `date` >=  NOW()

And a bit of advice, it's generally a bad idea to use reserved works like 'date' as column names. It leads to confusion and special handling problems.

EDIT: OK, it looks like date is not a reserved word, still it's  not a good idea as it doesn't fully represent the data. If it's an expiriation date, then call it expDate or something else that conforms to your naming convention.

EDIT2: Now I see you have a seperate field for time. What datatype is 'date' ? Why not just use a datetime field rather than two fields?

[David_Powers]

bregent wrote:

EDIT: OK, it looks like date is not a reserved word, still it's  not a good idea as it doesn't fully represent the data. If it's an expiriation date, then call it expDate or something else that conforms to your naming convention.

I agree wholeheartedly. Unfortunately, MySQL decided to allow people to use "date", "time", and "timestamp" as column names, rather than enforcing good practice.

[Guest]

Thank you, guys... I like your advice and I will heed it moving forward.  I am completely self-taught (as I suppose most of us are) and I learn more and more as with each site, page, and mistake!

To answer your question about why I didn't use datetime, it's because there are actually three time factors for each event... date (which is a date field), start_time (which is a time field), and end_time (also a time field.)  I created a CMS for my friend (it's a site for her non-profit group) so that she can add/edit/delete events herself. She enters the date, start time, and end time (along with other info) into a form and submits (inserts) the record.

Using separate fields/columns just made more sense to me, as it's easy to use different form fields to submit the data, and also so that the date and times can be modified independently.  Lastly, I needed two time fields anyway, so either way there would have been an additional time field.  I'm not 100% sure if this all makes sense, but it's what occurred to me at the time I was coding it.

In this case, for example, even if I'd used datetime for the event, I would still have a separate field for end_time, which is what I would need to be checking in this query.  So, date is a date type, and end_time is a time type... any thoughts on how to go about this query?

(On a totally unrelated note, I just created my first Cron Job to delete old events once a month, and successfully tested it... learning more and more every day!)

Message was edited by: Mike_Watt because typos are in his nature.

Message was edited by: Mike_Watt because typos are STILL in his nature.

[David_Powers]

I would have two columns:start_time and end_time. I would make both of them DATETIME types. You can use the date and time functions to extract the value that you need from either column. By using DATETIME columns for both, you can have an event that lasts more than one day, or that finishes after midnight.

[Guest]

That makes sense... but what would be the most logical way to set those columns using a form? It seems like I would have to have a date field and a time field for the user, and then somehow combine those two fields on insert - which seems a bit out of my league.

[David_Powers]

This is the problem with Dreamweaver server behaviors. They let you run before you can walk. Combining two strings is one of the most basic tasks in a server-side language like PHP.

Assuming your columns are called start_time and end_time, you will have four fields in your form. Let's call them start_date, start_time, end_date, and end_time. When you use your Insert Record server behavior, the columns will be automatically linked to the start_time and end_time fields. All you need to do is to prepend the date elements to the times.

Put this at the top of the page:

<?php
if (isset($_POST['start_time'])) {
  $_POST['start_time'] = $_POST['start_date'] . ' ' . $_POST['start_time'];
  $_POST['end_time'] = $_POST['end_date'] . ' ' . $_POST['end_time'];
}
?>