mysql最接近的时间,显示最接近当前日期的结果时间（MySQL和PHP）

I'm trying to make a query but after hours of trying I can't seem to get it right. What I'm trying to do is showing 1 thing from a database that is closest to the currect date & time and after the current date & time.

my columns look like this:

date time

1364399654 15:00

1364684400 16:00

1367272800 12:00

my PHP looks like this:

$timestamp_now = strtotime(date('d-m-Y')); //Creates a timestamp of the currect date

$time_now = date('H:i');

$sql = mysql_query('SELECT * FROM table_name ORDER BY date ASC, time ASC (WHERE date > '.$timestamp_now.') AND (time > "'.$time_now.'") LIMIT 1') or die(mysql_error());

$data = mysql_fetch_array($sql);

However, this doesn't work. Any words of advice?

解决方案

I would ditch using the PHP date/time methods and rely on MySQL giving a query that looks like

SELECT * FROM table_name

WHERE date > CURRENT_DATE

OR (

date = CURRENT_DATE

AND

time > CURRENT_TIME

)

ORDER BY date ASC, time ASC LIMIT 1

The OR makes sure that it gets the correct records else the TIME portion would block i.e. a result at 03:00 from the next day from appearing if the current time was at 06:00

I see you are using timestamp values there so you can always still pass in the PHP date numeric in place of CURRENT_DATE. This would give a final script of

$timestamp_now = strtotime(date('d-m-Y')); //Creates a timestamp of the currect date

$sql = mysql_query('SELECT * FROM table_name

WHERE date > '.$timestamp_now.'

OR (

date = '.$timestamp_now.'

AND

time > CURRENT_TIME

)

ORDER BY date ASC, time ASC LIMIT 1') or die(mysql_error());

$data = mysql_fetch_array($sql);

I would advise considering changing the database if possible to store just as a MySQL DATETIME field, as then you can change this query to simply WHERE datetime > NOW(), but that's entirely up to you. Just have always found MySQL date handling more logical than PHPs.