mysql double-query

[notset]

Hello,

I have a problem with my private messages system.

In mysql table messages i'm storing from_user_id, but i need to get a username from users table in the same query.

The simple PHP way would be:

$sql = "SELECT * FROM messages";
$result = mysql_query($sql) OR exit(mysql_error());
while ($m = mysql_fetch_assoc($result)) {
$sql = "SELECT username FROM users WHERE id = '".$m['from_user_id']."'";
$result = mysql_query($sql) OR exit(mysql_error());
$m['username'] = mysql_result($result, 'username');
echo 'From: '.$m['username']. '(ID: '.$m['from_user_id'].')';
}

But this way is very bad for resource usage.
I just want to get a username from the same query and that it would be $m['username'] without any aditional queries.

Thank's

[mjax]

You need to look into SQL JOINs. For instance:

SELECT * FROM messages INNER JOIN users ON users.id = messages.from_user_id

This will fetch all messages and with each messages the details of the user that is linked to that message by the from_user_id field.

[8ta8ta]

SELECT * FROM messages, users WHERE users.id = messages.from_user_id