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.

Seif · Mar 23rd, 2008, 8:28 PM

not sure I 100% follow what you mean but why not just use an OR statement ?

(Toggle Plain Text)

SELECT name FROM $table_user WHERE user_id=$user_id1 OR user_id=$user_id2

Ideally, if you are constructing a set of results from more than one table that reference each other using foreign keys, you can perform a series of joins to obtain what you need. There are multiple ways of performing a join:

(Toggle Plain Text)

SELECT table_user.name
FROM table_user,table_match
WHERE table_user.userid1 = table_match.userid1 OR
 table_user.userid2 = table_match.userid2

or

(Toggle Plain Text)

SELECT table_user.name
FROM table_user
INNER JOIN table_match
ON 
 table_user.userid1 = table_match.userid1 OR
 table_user.userid2 = table_match.userid2

Both commands should construct a list of names for each userid. Have a look at JOIN SQL statements. They should provide all that you need.

Sane · Mar 23rd, 2008, 8:52 PM

I was saying I need the username of every user_id I fetch, on top of the row I originally obtained. I can already do that with the MySQL queries I posted above. I'm now looking for a way to combine the queries so that I don't have to make 1+2*N queries. Instead just 1 query.

Modifying what you posted at the bottom might work. I will try some of that out. Thanks.

Edit: I can do it with one user id. Here's an example of where I need to output a table of ranks, and at the same time get the user name for every user_id.

 MySQL Syntax (Toggle Plain Text) 
SELECT $table_ranking.*, $table_user.name 
FROM $table_ranking INNER JOIN $table_user 
ON $table_ranking.user_id=$table_user.user_id AND $table_ranking.game_id=$game_id
SELECT $table_ranking.*, $table_user.name 
FROM $table_ranking INNER JOIN $table_user 
ON $table_ranking.user_id=$table_user.user_id AND $table_ranking.game_id=$game_id

The problem comes from extending this to get the usernames of both user_id1 and user_id2 for each row. It seems like I would need multiple joins to accomplish that. Maybe I can try screwing around with nested joins.

Arla · Mar 23rd, 2008, 11:42 PM

How about

(Toggle Plain Text)

Select a.name as user1, b.name as user2
  from $table_user a
         ,$table_user b
         ,$table_match c
  where a.user_id = c.user_id1
  and     b.user_id = c.user_id2

I think that should do it (bit tired right now, so might have missed something)

Jimbo · Mar 24th, 2008, 12:58 AM

Not the most elegant, but perhaps fits your needs?

 mysql Syntax (Toggle Plain Text) 
SELECT $table_ranking.*, $table_user.name
FROM $table_ranking INNER JOIN $table_user ON $table_ranking.user_id=$table_user.user_id
WHERE $table_user.user_id IN ($gameIds)
SELECT $table_ranking.*, $table_user.name
FROM $table_ranking INNER JOIN $table_user ON $table_ranking.user_id=$table_user.user_id
WHERE $table_user.user_id IN ($gameIds)

$gameIds should be a CSV list of user IDs put together in your PHP script somewhere (assumption: you're putting a list together in PHP, since this is in the PHP forum...)

Seif · Mar 24th, 2008, 7:46 AM

Theres no reason why you need multiple queries. if you want every name for user_id1 and user_id2, just add that condition clause to the join. If you are concerned of duplicate name entries in the view from the query, then add a distinct to the statement.

I'm still having a bit of a job visualizing what your problem actually is. are user_id1 and user_id2 unique values? do they both make up a composite key ? it's even more confusing in your second post with differing table and field names.
If you cannot create a view from one query between two tables, then maybe you should reconsider the design of your database for performance gain.

The following single query "should" select a distinct set of names in table user for each user_id and game_id entry in table_ranking.

 MySQL Syntax (Toggle Plain Text) 
SELECT DISTINCT $table_ranking.*, $table_user.name 
FROM $table_ranking INNER JOIN $table_user 
ON $table_ranking.user_id=$table_user.user_id OR $table_ranking.user_id=$table_ranking.game_id
SELECT DISTINCT $table_ranking.*, $table_user.name 
FROM $table_ranking INNER JOIN $table_user 
ON $table_ranking.user_id=$table_user.user_id OR $table_ranking.user_id=$table_ranking.game_id

Sane · Mar 24th, 2008, 9:23 AM

@Arla: You got it in one shot. Thanks. Now I'll have to do some testing to see if it is indeed any faster.

@Seif: I didn't think it would be this hard to visualize. I am reporting a table of information. In the table there is a user_id column, but I don't want the user_id of each row. I want the user name for each row.

For example, here: http://codersblock.net/arena/ranking.php. The table "ranking" contains several rows, and a column called "user_id". I report the user name for each row by getting it from a second table. That is shown by the MySQL in my second post.

But now I need to do it where there's a user_id1 AND user_id2. Now, let's go back to my very first post.

Quote:

I want the username for each of these user_ids.

For example, here: http://codersblock.net/arena/lobby.php. The matches table has two user_ids, and I report the user name for "each of these user_ids". This is accomplished by the MySQL from my first post, which I now want to combine into one whole query.

Read my question over again and maybe it will all fall together now. But since I've already got it working with Arla's MySQL, now I only need to optimize it if possible.

Arla · Mar 24th, 2008, 5:28 PM

Sane,

Glad that was the right query, was a bit hard trying to read between the lines of what you actually wanted, especially when you added the table_ranking into the mix which wasn't even in your first post (and I ignored it, since it wasn't something you mentioned in the original problem).

If possible, post an example of what's in the tables and what you would want as output, since I wasn't sure if you wanted only distinct names as Seif had a query for, or you wanted what I posted, or something else entirely.

Mar 23rd, 2008, 7:13 PM	#1
Sane Programming Guru Join Date: Apr 2005 Location: Waterloo, Ontario Posts: 1,869 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

Mar 23rd, 2008, 8:28 PM	#2
Seif Hobbyist Programmer Join Date: Jan 2006 Location: UK Posts: 215 Rep Power: 3	Re: MySQL - Basic Combining Of Queries not sure I 100% follow what you mean but why not just use an OR statement ? (Toggle Plain Text) SELECT name FROM $table_user WHERE user_id=$user_id1 OR user_id=$user_id2 SELECT name FROM $table_user WHERE user_id=$user_id1 OR user_id=$user_id2 Ideally, if you are constructing a set of results from more than one table that reference each other using foreign keys, you can perform a series of joins to obtain what you need. There are multiple ways of performing a join: (Toggle Plain Text) SELECT table_user.name FROM table_user,table_match WHERE table_user.userid1 = table_match.userid1 OR table_user.userid2 = table_match.userid2 SELECT table_user.name FROM table_user,table_match WHERE table_user.userid1 = table_match.userid1 OR table_user.userid2 = table_match.userid2 or (Toggle Plain Text) SELECT table_user.name FROM table_user INNER JOIN table_match ON table_user.userid1 = table_match.userid1 OR table_user.userid2 = table_match.userid2 SELECT table_user.name FROM table_user INNER JOIN table_match ON table_user.userid1 = table_match.userid1 OR table_user.userid2 = table_match.userid2 Both commands should construct a list of names for each userid. Have a look at JOIN SQL statements. They should provide all that you need.

Mar 23rd, 2008, 8:52 PM	#3
Sane Programming Guru Join Date: Apr 2005 Location: Waterloo, Ontario Posts: 1,869 Rep Power: 5	Re: MySQL - Basic Combining Of Queries I was saying I need the username of every user_id I fetch, on top of the row I originally obtained. I can already do that with the MySQL queries I posted above. I'm now looking for a way to combine the queries so that I don't have to make 1+2N queries. Instead just 1 query. Modifying what you posted at the bottom might work. I will try some of that out. Thanks. Edit: I can do it with one user id. Here's an example of where I need to output a table of ranks, and at the same time get the user name for every user_id. MySQL Syntax* (Toggle Plain Text) SELECT $table_ranking., $table_user.name FROM $table_ranking INNER JOIN $table_user ON $table_ranking.user_id=$table_user.user_id AND $table_ranking.game_id=$game_id SELECT $table_ranking., $table_user.name FROM $table_ranking INNER JOIN $table_user ON $table_ranking.user_id=$table_user.user_id AND $table_ranking.game_id=$game_id The problem comes from extending this to get the usernames of both user_id1 and user_id2 for each row. It seems like I would need multiple joins to accomplish that. Maybe I can try screwing around with nested joins. __________________ Waterloo's Canadian Computing Competition (CCC) - Stage 2 Problems, Solutions, and Test Data Last edited by Sane; Mar 23rd, 2008 at 9:14 PM.

Mar 23rd, 2008, 11:42 PM	#4
Arla Hobbyist Programmer Join Date: Mar 2005 Posts: 208 Rep Power: 4	Re: MySQL - Basic Combining Of Queries How about (Toggle Plain Text) Select a.name as user1, b.name as user2 from $table_user a ,$table_user b ,$table_match c where a.user_id = c.user_id1 and b.user_id = c.user_id2 Select a.name as user1, b.name as user2 from $table_user a ,$table_user b ,$table_match c where a.user_id = c.user_id1 and b.user_id = c.user_id2 I think that should do it (bit tired right now, so might have missed something)

Mar 24th, 2008, 12:58 AM	#5
Jimbo Battle Programmer Join Date: Feb 2006 Location: Bellevue, WA, USA Posts: 763 Rep Power: 3	Re: MySQL - Basic Combining Of Queries Not the most elegant, but perhaps fits your needs? mysql Syntax (Toggle Plain Text) SELECT $table_ranking., $table_user.name FROM $table_ranking INNER JOIN $table_user ON $table_ranking.user_id=$table_user.user_id WHERE $table_user.user_id IN ($gameIds) SELECT $table_ranking., $table_user.name FROM $table_ranking INNER JOIN $table_user ON $table_ranking.user_id=$table_user.user_id WHERE $table_user.user_id IN ($gameIds) $gameIds should be a CSV list of user IDs put together in your PHP script somewhere (assumption: you're putting a list together in PHP, since this is in the PHP forum...) __________________ <insert disclaimer here> <insert shameless plug for Visual Studio here>

Mar 24th, 2008, 7:46 AM	#6
Seif Hobbyist Programmer Join Date: Jan 2006 Location: UK Posts: 215 Rep Power: 3	Re: MySQL - Basic Combining Of Queries Theres no reason why you need multiple queries. if you want every name for user_id1 and user_id2, just add that condition clause to the join. If you are concerned of duplicate name entries in the view from the query, then add a distinct to the statement. I'm still having a bit of a job visualizing what your problem actually is. are user_id1 and user_id2 unique values? do they both make up a composite key ? it's even more confusing in your second post with differing table and field names. If you cannot create a view from one query between two tables, then maybe you should reconsider the design of your database for performance gain. The following single query "should" select a distinct set of names in table user for each user_id and game_id entry in table_ranking. MySQL Syntax (Toggle Plain Text) SELECT DISTINCT $table_ranking., $table_user.name FROM $table_ranking INNER JOIN $table_user ON $table_ranking.user_id=$table_user.user_id OR $table_ranking.user_id=$table_ranking.game_id SELECT DISTINCT $table_ranking., $table_user.name FROM $table_ranking INNER JOIN $table_user ON $table_ranking.user_id=$table_user.user_id OR $table_ranking.user_id=$table_ranking.game_id

Mar 24th, 2008, 5:28 PM	#8
Arla Hobbyist Programmer Join Date: Mar 2005 Posts: 208 Rep Power: 4	Re: MySQL - Basic Combining Of Queries Sane, Glad that was the right query, was a bit hard trying to read between the lines of what you actually wanted, especially when you added the table_ranking into the mix which wasn't even in your first post (and I ignored it, since it wasn't something you mentioned in the original problem). If possible, post an example of what's in the tables and what you would want as output, since I wasn't sure if you wanted only distinct names as Seif had a query for, or you wanted what I posted, or something else entirely.

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to 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

Programming Forums > Web Development and Design > PHP

MySQL - Basic Combining Of Queries