mankee mankee - 4 months ago 10
PHP Question

Combining Two Select Queries MySQL

I have two tables :

items
and
comments
.

I want to select all items which a user commented on.

For simplicity, lets assume the
items
table has two columns :
item_id
and
item_content
. Let the
comments
table have 3 columns
user_id
,
item_id
and
comment_content
.

I am given the
user_id
of the commenting user, I need to first select all the
item_id
from the
comments
table, where
user_id = myUserId
.

This is a basic query
SELECT item_id FROM comments WHERE user_id = '$myUserId'
.

Then I need to select the
item_content
for each
item_id
returned by the previous query.

I was thinking of doing a
while($row = $my_first_query->fetch_array())
loop, and inside of it doing something like
SELECT item_content FROM item WHERE item_id = $row["item_id"]

however this is a bit messy and I was wondering if there was a simpler way of doing this, by combining the two queries into one.

Answer

Use an INNER JOIN:

SELECT t1.*
FROM items t1
INNER JOIN comments t2
    ON t1.item_id = t2.item_id
WHERE t2.user_id = myUserId

The approach you suggested of first querying the comments table and then looping over the result set is inefficient. In a join, MySQL can handle this algebra much faster than your PHP code.