What I need to do is output a table of matches. But at the same time, I need the username of each user_id found in the table.
So I have:
SELECT * FROM $table_match
That fetches every row from the matches table. The matches table has the columns 'user_id1' and 'user_id2'. I want the username for each of these user_ids.
This can be done by calling the following two queries for each row fetched with the above query.
SELECT name FROM $table_user WHERE user_id=$user_id1
SELECT name FROM $table_user WHERE user_id=$user_id2
This works, but the problem with that is it will keep querying for usernames, even if it's already fetched it before. Despite MySQL's cache, I feel that this is slower than it needs to be. Maybe MySQL will optimize the query if I combine the two. Or even better, can I link the user_id column to the name column from the user table?
Thanks in advance.