I have DB of posts with:
我有以下数据库:
post_id(int(7)) user_id(int(7)) post_txt(text) post_time(varchar(30)) likes_count(int(10))
(the post_time is look like this "5-4-2016 17:41")
(post_time看起来像这样“5-4-2016 17:41”)
I want to order it by "hot posts" e.g. "likes_count/post_time".
我想通过“热帖”订购,例如“likes_count / post_time”。
How can I do it?
我该怎么做?
This is how I do it now:
这就是我现在的做法:
$que_post=mysql_query("select * from user_post order by post_id desc");
PS. I know that this is not how you save likes usually, but I do it for purpose
PS。我知道这不是你通常喜欢的方式,但我是出于此目的
Edit-Example data:
97||25||Hello world||8-4-2016 14:19||19
The algorithm I want to use is time/likes. I know its bad algorithm, but I just want to understand the basics. I would like to know if you know good algorithm for that
我想要使用的算法是时间/喜欢。我知道它的算法不好,但我只想了解基础知识。我想知道你是否知道好的算法
2 个解决方案
#1
1
You should change type of post_time field to DATETIME (read manual). All your lines have to be converted by some script to format YYYY-MM-DD HH:MM:SS. When you do it, everything will work.
您应该将post_time字段的类型更改为DATETIME(阅读手册)。所有行都必须通过某些脚本转换为YYYY-MM-DD HH:MM:SS格式。当你这样做时,一切都会奏效。
Your problem appears cause of sorting by alphabet as text. For example: 01.01.2016 as text is less than 31.01.1999.
您的问题出现的原因是按字母顺序排序为文本。例如:01.01.2016,因为文本小于31.01.1999。
#2
0
Assuming you can convert the post_time column to use a date type, you want to find the number of seconds between it and now, e.g.
假设您可以将post_time列转换为使用日期类型,您希望找到它与现在之间的秒数,例如
Order By like_count / (unix_timestamp(now()) - unix_timestamp(post_time)) Desc;
If changing the column type isn't an option, you can convert it on-the-fly using the str_to_date function, but that will potentially be quite a slow operation.
如果不能更改列类型,则可以使用str_to_date函数即时转换它,但这可能是一个非常慢的操作。
#1
1
You should change type of post_time field to DATETIME (read manual). All your lines have to be converted by some script to format YYYY-MM-DD HH:MM:SS. When you do it, everything will work.
您应该将post_time字段的类型更改为DATETIME(阅读手册)。所有行都必须通过某些脚本转换为YYYY-MM-DD HH:MM:SS格式。当你这样做时,一切都会奏效。
Your problem appears cause of sorting by alphabet as text. For example: 01.01.2016 as text is less than 31.01.1999.
您的问题出现的原因是按字母顺序排序为文本。例如:01.01.2016,因为文本小于31.01.1999。
#2
0
Assuming you can convert the post_time column to use a date type, you want to find the number of seconds between it and now, e.g.
假设您可以将post_time列转换为使用日期类型,您希望找到它与现在之间的秒数,例如
Order By like_count / (unix_timestamp(now()) - unix_timestamp(post_time)) Desc;
If changing the column type isn't an option, you can convert it on-the-fly using the str_to_date function, but that will potentially be quite a slow operation.
如果不能更改列类型,则可以使用str_to_date函数即时转换它,但这可能是一个非常慢的操作。