Avoiding query in a loop

[Icheb]

If I have one MySQL database table where two rows contain a userid and i want to get the usernames for both userids, how would I do this without doing a query in the while-loop?

For an example, I have a log where the id of the user who made a change and the id of the user who has been changed is saved, so I have a table that looks something like this:

entryid userid changeduser etc...

Now I have another table where the userids and the usernames are stored, something like

userid username etc...

I know how to join both tables to get one userid, but I can't figure out how to get both usernames within one query.

Can someone help me out?

[daemon]

I think I understand what you're trying to do, and this is how I do it:

Select all the users from the user table and then you should stick them in an array like this:

$array[ $userid ] = $username

Then, you can simply access the username by having the user ID and calling the element of the array.

An example in vB would be:

// Select all of the users from the database
$get_users = $DB_site->query("SELECT * FROM user");

// Load the users into an array where the key is the user ID
while ($user = $DB_site->fetch_array($get_users))
{
    $userarray["$user[userid]"] = $user['username'];
}

// Match user ID's to usernames
$your_query = $DB_site->query( /* PUT YOUR QUERY HERE */ );
while ($query = $DB_site->fetch_array($your_query))
{
    $username = $userarray["$query[userid]"];
} 

[Chroder]

You can do a table join..

$query = $DB_site->query("SELECT u.username, d.* FROM users u, logdata d WHERE u.userid = d.userid"); 

[Icheb]

daemon: Is that the only solution or is there also a way to do it with one query?

Chroder: That would only give me the username of one user. I have two userids in the table and I would like to get both usernames without having to do one query per user.

[Chroder]

Oh sorry, I didn't read that part. You can do two joins,

SELECT
    uone.username, utwo.username, d.* 
FROM 
    users uone,
    users utwo,
    logdata d
WHERE
    uone.userid = d.useridone
    AND utwo.userid = d.useridtwo

I'm pretty sure that works.