显示最接近当前日期和时间的结果（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?

我正在尝试进行查询，但经过数小时的尝试后，我似乎无法做到正确。我想要做的是从最接近当前日期和数据库的数据库中显示一件事。时间和当前日期之后＆amp; 时间。 p>

我的列如下所示： p>

 日期时间
1364399654 15:00 
1364684400 16:00 
1367272800 12:00  
  code>  pre> 
 
 我的PHP看起来像这样： p> 
 
 
  $ timestamp_now = strtotime（date（'dm-Y'）  ）;  //创建当前日期的时间戳
 $ time_now = date（'H：i'）; 
 $ sql = mysql_query（'SELECT * FROM table_name ORDER BY date ASC，time ASC（WHERE date＆gt;'。$。  timestamp_now。'）AND（time＆gt;“'。$ time_now。'”）LIMIT 1'）或die（mysql_error（））; 
 $ data = mysql_fetch_array（$ sql）; 
  code>  pre  > 
 
 但是，这不起作用。 有任何建议吗？ p> 
  div>

答

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.

答

I beleive you need to get the SQL structured with WHERE before ORDER BY something like

SELECT * FROM table WHERE (date>$now) and (time>$now) ORDER BY date,time LIMIT 1

答

You should use one more test :

DATE(date) > CURDATE() : if it's tomorrow or later
DATE(date) = CURDATE() AND time > TIME(NOW()) : it it's today, but later

The full code :

$sql = mysql_query('SELECT * 
                    FROM table_name 
                    WHERE DATE(date) > CURDATE()
                    OR (DATE(date) = CURDATE() AND time > TIME(NOW()))
                    ORDER BY date ASC, time ASC 
                    LIMIT 1'
                  ) or die(mysql_error());
$data = mysql_fetch_array($sql);

Please note you're using timestamp in your date field. Timestamp include date and time, so maybe you'll be able to use this request :

SELECT * 
FROM table_name 
WHERE date > NOW()
ORDER BY date ASC
LIMIT 1

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

相关推荐