Programming Forums > Web Development and Design > PHP

Sane · Mar 23rd, 2008, 7:13 PM

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:

MySQL Syntax (Toggle Plain Text)

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.

 MySQL Syntax (Toggle Plain Text) 
SELECT name FROM $table_user WHERE user_id=$user_id1
SELECT name FROM $table_user WHERE user_id=$user_id2
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.

Mar 23rd, 2008, 7:13 PM	#1
Sane Programming Guru Join Date: Apr 2005 Posts: 1,799 Rep Power: 5	MySQL - Basic Combining Of Queries 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: MySQL Syntax (Toggle Plain Text) SELECT * FROM $table_match 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. MySQL Syntax (Toggle Plain Text) SELECT name FROM $table_user WHERE user_id=$user_id1 SELECT name FROM $table_user WHERE user_id=$user_id2 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. __________________ Waterloo's Canadian Computing Competition (CCC) - Stage 2 Problems, Solutions, and Test Data

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Connecting MySQL and PHP	titaniumdecoy	PHP	10	Feb 25th, 2008 7:47 PM
BASIC - the best sort...	HippyVanMan	Other Programming Languages	6	Sep 23rd, 2007 7:03 PM
The basic of basic in programming...	Simongcc	Software Design and Algorithms	4	Aug 16th, 2007 3:34 AM
Tutorial - Using MySQL in C#	Darkhack	C#	12	Jan 17th, 2006 9:28 AM
[ANN] New script engine (Basic sintax)	MKTMK	C++	3	Sep 1st, 2005 5:51 PM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Web Development and Design > PHP

MySQL - Basic Combining Of Queries