PHP MySQL查询在过去24小时内最受欢迎

Say I want to get ten records with the MOST likes in the last 24 hours. Here's what I have so far:

假设我想在过去24小时内获得MOST喜欢的十条记录。这是我到目前为止所拥有的：

$date = date("o-m-d");
$query = "SELECT date_created,COUNT(to),from,to FROM likes WHERE date_created LIKE '$date%' GROUP BY to ORDER BY COUNT(to) DESC LIMIT 10";

The problem with that is that it only gets the most liked from THAT DAY, no matter how far into that day it is. It doesn't get the most liked from the last 24 hours.

问题在于它只能从那一天获得最喜欢的，无论它在那一天有多远。在过去的24小时里，它并没有得到最受欢迎。

structure for likes: from | to | date_created | id

喜欢的结构：来自|到| date_created | ID

dates are in standard ISO time - example 2010-07-14T00:35:31-04:00. Come straight from the PHP reference: date("c");

日期符合标准ISO时间 - 例如2010-07-14T00：35：31-04：00。直接来自PHP参考：date（“c”）;

4 个解决方案

#1

WHERE date_created > DATE_SUB( NOW(), INTERVAL 24 HOUR)

#2

If your date_created field is a datetime or timestamp field type, you can use DATE_SUB in your where clause as follows;

如果date_created字段是datetime或timestamp字段类型，则可以在where子句中使用DATE_SUB，如下所示;


WHERE date_created > DATE_SUB(NOW(), INTERVAL 24 HOUR)

#3

So first off date_created should be defined as a timestamp with on default current timestamp. If you have a date_modified in the table as well then date_modified would have on update current timestamp and you can defined with date created as a timestamp and this trigger to update it

因此，首先应将date_created定义为具有默认当前时间戳的时间戳。如果表中也有date_modified，则date_modified将具有更新当前时间戳，您可以使用创建的日期定义为时间戳，并使用此触发器更新它

CREATE TRIGGER likes_date_entered
BEFORE INSERT ON likes
FOR EACH ROW SET NEW.date_created = NOW()

Now that we have a timestamp you can easily apply some of the mysql date functions to the column.

现在我们有了一个时间戳，您可以轻松地将一些mysql日期函数应用于该列。

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

I'll leave what to do as an exercise for the reader, unless you say pretty please and want me to give the exact syntax.

我会留下为读者做的练习，除非你说得好，并希望我给出确切的语法。

#4

You should be using date/time functions, instead of LIKE.

您应该使用日期/时间函数，而不是LIKE。

WHERE date_created >= (NOW() - INTERVAL 24 HOUR)

#1

WHERE date_created > DATE_SUB( NOW(), INTERVAL 24 HOUR)

#2