显示最接近当前日期的结果时间(MySQL和PHP) [英] Show closest result to current date & time (MySQL & PHP)

问题描述

我正在尝试进行查询，但是经过数小时的尝试，我似乎无法正确地进行查询.我正在尝试从数据库中显示最接近当前日期&的1件事.时间和当前日期之后时间.

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

我的PHP看起来像这样:

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?

推荐答案

我会放弃使用PHP日期/时间方法，而依靠MySQL给出类似以下的查询

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

OR会确保它获得了正确的记录，否则TIME部分会阻止，即如果当前时间是06:00，则从第二天的03:00开始显示结果

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

我看到您在那儿使用时间戳记值，因此您仍然可以始终传递PHP日期数字来代替CURRENT_DATE.这将给出最终的脚本

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);

如果可能的话，我建议考虑更改数据库以将其存储为MySQL DATETIME字段，因为这样您就可以将查询更改为简单的WHERE datetime > NOW()，但这完全取决于您.只是一直发现MySQL日期处理比PHP更合乎逻辑.

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.

这篇关于显示最接近当前日期的结果时间(MySQL和PHP)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！