对从数据库检索的信息进行排序

发布于 2024-11-26 05:49:16 字数 577 浏览 1 评论 0原文

我正在通过使用嵌套 while 循环从数据库检索信息来创建提要（有更好的方法吗？）。

我有一张名为 users 的表，其中包含所有名称。另一个表称为消息，其中包含消息、发布消息的用户和时间戳。

$userQuery = mysql_query("SELECT name FROM users");
while ($user = mysql_fetch_array($userQuery, MYSQL_NUM)) {
    $messageQuery = mysql_query("SELECT message FROM messages WHERE user = $user ORDER BY timestamp DESC");
    while ($message = mysql_fetch_array($messageQuery, MYSQL_NUM)) {
        echo "$user[0]: $message[0]";
    }
}

问题是它不按时间戳排序，我不知道它是如何排序的。我尝试过使用 UNIX 时间戳的时间戳、日期时间和 int 类型。

编辑：我应该补充一点，用户和消息匹配得很好，只是顺序不起作用。

原文

I'm creating a feed by retrieving information from my database using nested while loops (is there a better way to do this?).

I have one table called users with all the names amongst other things. The other table is called messages which has messages, the user who posted it, and a timestamp.

$userQuery = mysql_query("SELECT name FROM users");
while ($user = mysql_fetch_array($userQuery, MYSQL_NUM)) {
    $messageQuery = mysql_query("SELECT message FROM messages WHERE user = $user ORDER BY timestamp DESC");
    while ($message = mysql_fetch_array($messageQuery, MYSQL_NUM)) {
        echo "$user[0]: $message[0]";
    }
}

The problem is that it doesn't order by the timestamp and I can't tell how it's ordered. I've tried timestamp, datetime, and int types with UNIX timestamps.

EDIT: I should add that the user and message matches up fine, it's just the ordering that doesn't work.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦醒灬来后我 2024-12-03 05:49:16

我猜你的用户或多或少是随机顺序的，并且“在”一个用户内排序可以吗？！

use：

$result = mysql_query('select users.name,messages.message from messages join users on (users.name=messages.user) order by messages.timestamp');
while($row = mysql_fetch_row($result))
    echo "$row[0]: $row[1]";

这应该会给你一个有序的结果（至少如果你有一个名为 messages.timestamp 的列。检查名称;-)）。全部在一个查询中......

I guess you get your users in more or less random order and "within" one user the sorting is ok?!

use:

$result = mysql_query('select users.name,messages.message from messages join users on (users.name=messages.user) order by messages.timestamp');
while($row = mysql_fetch_row($result))
    echo "$row[0]: $row[1]";

That should give you an ordered result (at least if you have a column called messages.timestamp. Check the name ;-)). And all in one query...

回复收藏 0 原文

假扮的天使 2024-12-03 05:49:16

对于查询，您可以创建一个联接

SELECT u.name as name, m.message as message
FROM users u inner join messages m
on u.user = m.user
order by 
m.timestamp DESC

至于第二部分，我认为您的能力没有任何问题。也许您可以发布一些数据样本，看看这是否有任何影响。

For the query, you could create a join

SELECT u.name as name, m.message as message
FROM users u inner join messages m
on u.user = m.user
order by 
m.timestamp DESC

As for the second part, I don't see anything wrong with your could. May be you could post some samples of your data to see if that is making any difference.

回复收藏 0 原文

~没有更多了~