MYSQL - How to return array from first table and select from another table using that array in one query?

Question

I have 2 tables:

1) game_follower (stores which user is following which game):

___________________________
| ID  | game_id | user_id |
| 1   |  1      |  1      |
| 2   |  2      |  1      |

2) videos:

__________________________________
| ID  | game_id | content | data |
| 1   |  2      |  blah   | blah |
| 2   |  1      |  blah   | blah |

I'm trying to implement a twitter-style following system, but I'm having trouble writing a query that does the following:

select multiple game_ids WHERE user_id = "1"
from that list of game_ids, select from videos where game_id = (multiple game ids that have been selected from the first query)

I can do it with separate queries but I've been told that using multiple queries is bad for performance, so how do I do this in with one query?

"I've been told that using multiple queries is bad for performance". That's a generalization. Using (for example) two or three queries isn't necessarily bad for performance (though here it's not necessary). The serious problems are if you are using "n+1" queries. That means running one query to fetch the IDs, then running the second query in a loop - once for each ID. That will really kill your performance if n could get large. — Mark Byers
– Mark Byers, Commented Jul 21, 2012 at 14:13

Moyed Ansari · Accepted Answer · 2012-07-21 14:12:51Z

3

try this

SELECT v.game_id
FROM videos v
INNER JOIN game_follower gf ON gf.game_id = v.game_id
WHERE gf.user_id = 1

answered Jul 21, 2012 at 14:12

Moyed Ansari

8,4612 gold badges38 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kush Over a year ago

Brilliant, this query works perfectly! Didn't know about the INNER JOIN function!

Moyed Ansari Over a year ago

I'm glad if this works for you. for more detail please visit dev.mysql.com/doc/refman/5.0/en/join.html

avgvstvs Over a year ago

Dope Monk, as you're a fresh developer, you should pay attention to Ryven's answer as it will prevent a hacker from altering the meaning of your query. Logically, it's identical to this one.

Kush Over a year ago

Yeah the framework I'm using has prepared queries so its all good, thanks for the lookout Moyed!

florian h · Accepted Answer · 2012-07-21 14:13:56Z

1

You should look into JOINs to do this with one query (which massivly increases speed compared to fetching the data seperately).

answered Jul 21, 2012 at 14:13

florian h

1,1711 gold badge10 silver badges25 bronze badges

Comments

Rhys · Accepted Answer · 2012-07-21 14:49:43Z

1

$conn = new PDO(CONNECTION_DETAILS, $username, $pass);
$variable = 1;
$sql = "SELECT game_follower.game_id FROM game_follower ";
$sql .= "INNER JOIN videos ";
$sql .= "ON game_follower.game_id = videos.game_id ";
$sql .= "WHERE game_follower.user_id = :id";
$query = $conn->prepare($sql);
$query->bindParam(':id', $variable, PDO::PARAM_INT);
$query->execute();

Something like that?

edited Jul 21, 2012 at 14:49

answered Jul 21, 2012 at 14:14

Rhys

1,4911 gold badge13 silver badges24 bronze badges

1 Comment

avgvstvs Over a year ago

This query is exactly correct, but you should modify it slightly to a prepared statement syntax.

Collectives™ on Stack Overflow

MYSQL - How to return array from first table and select from another table using that array in one query?

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related