从数据库中排序检索到的信息

Question

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.

Answer 1

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...