PHP - Query inside query

Question

I have a very simple query but I need to make another query inside it. I have very little knowledge of SQL and PHP so I want to ask your help.

$result = mysql_query("SELECT * FROM img");
while ($row = mysql_fetch_assoc($result))
{
   $imgName = $row['name'];
   $catID = $row['catid'];
   // Problem
   // Need to get category NAME from category ID
   $result2 = mysql_query("SELECT name FROM cat WHERE id = $catID");
   while ($row2 = mysql_fetch_assoc($result2))
   {
      $catName = $row2['name'];
   }

   echo "Image: $imgName <br />";
   echo "Category: $catName";
}

Michael Berkowski · Accepted Answer · 2011-10-07 14:44:00Z

4

This looks to be a simple JOIN to get the category name. You can use a single query:

SELECT
  img.id AS imgId,
  img.name AS imgName,
  cat.name AS catName
FROM img JOIN cat ON img.catid = cat.id

Replace your initial query with the above, and it will eliminate the need for the inner query.

edited Oct 7, 2011 at 14:44

answered Oct 7, 2011 at 14:21

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

2 Comments

Michael Berkowski Over a year ago

@Qmal See edit above -- Be more explicit than img.* then. Reference image id as imgId, image name as imgName, and category name as catName in $row[]

Stan Over a year ago

Yea I got it that's why deleted the comment :) Thanks again

Robin Castlin · Accepted Answer · 2011-10-07 14:22:10Z

0

You could do a simple subquery on this one:

$result = mysql_query("
SELECT name, catid, (SELECT name FROM cat WHERE id = img.catid) AS cat_name
FROM img
");

answered Oct 7, 2011 at 14:22

Robin Castlin

11k1 gold badge30 silver badges44 bronze badges

1 Comment

BMBM Over a year ago

Uh, not sure about that. If there are many rows in the table that could be quite slow. Doing an inner join seems like the much better solution.

Matt Williamson · Accepted Answer · 2011-10-07 14:28:42Z

0

This should work. You'll see all the img properties of each table in each row and in addition the cat.name value.

SELECT *, cat.name as catname FROM img
INNER JOIN cat
ON cat.id = img.catid

edited Oct 7, 2011 at 14:28

answered Oct 7, 2011 at 14:21

Matt Williamson

40.3k10 gold badges67 silver badges73 bronze badges

1 Comment

Arda Over a year ago

You're missing selecting cat.name at your select statement.

Collectives™ on Stack Overflow

PHP - Query inside query

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related