Select data based on date and time columns

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
finny
Forum Newbie
Posts: 15
Joined: Mon Apr 24, 2006 7:43 am

Select data based on date and time columns

Post by finny »

Hi,

I have a mysql db with a date column (yyyy-mm-dd) and a time column (hh:mm:ss) from which I extract data from that falls after todays date...

Code: Select all

$query="SELECT *
FROM listings
WHERE show_date > CURDATE()
ORDER BY show_date";
$result=mysql_query($query);

$num=mysql_numrows($result);
This works fine however I would to stop like to stop displaying the data based on the time also. i.e. I dont want the rows to drop off at midnight I would like them to drop off at a certain time. Like 3 hrs before the time listed in 'show_time' column.

I tried a workaround but even that is not ideal... and it doesnt work anyway...

Code: Select all

$query="SELECT *
FROM listings
WHERE show_date > DATE_SUB(CURDATE(), INTERVAL 1 HOUR)
ORDER BY show_date";
$result=mysql_query($query);

$num=mysql_numrows($result);


I thought this might be 1 hr after midnight but it takes 1 hr after the curent time so that doesnt work either.

Any ideas...
litebearer
Forum Contributor
Posts: 194
Joined: Sat Mar 27, 2004 5:54 am

Re: Select data based on date and time columns

Post by litebearer »

Rather than two fields, one datetime field would suffice, then...

Code: Select all

$start_dt = "2011-02-10 13:29";
$end_dt = "2011-02-10 16:29";
$query = "SELECT * FROM TABLE_NAME_HERE WHERE DATETIME_FIELD HERE BETWEEN '$start_dt' AND '$end_dt'";
Post Reply