-
1. Re: Filtering & sorting by time & date
bregent Mar 18, 2010 7:23 PM (in response to Mike_Watt)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?
-
2. Re: Filtering & sorting by time & date
David_Powers Mar 19, 2010 9:08 AM (in response to bregent)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.
-
3. Re: Filtering & sorting by time & date
Mike_Watt Mar 19, 2010 6:09 PM (in response to bregent)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.
-
4. Re: Filtering & sorting by time & date
David_Powers Mar 21, 2010 4:52 AM (in response to Mike_Watt)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.
-
5. Re: Filtering & sorting by time & date
Mike_Watt Mar 26, 2010 7:13 PM (in response to David_Powers)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.
-
6. Re: Filtering & sorting by time & date
David_Powers Mar 29, 2010 2:00 AM (in response to Mike_Watt)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']; } ?>




