Sql query only printing first row

Question

I am coding in php and the code takes data from an array to fetch additional data from a mysql db. Because I need data from two different tables, I use nested while loops. But the code below always only prints out (echo "a: " . $data3[2]; or echo "b: " . $data3[2];) one time:

foreach($stuff as $key)
{
    $query3 = "SELECT * FROM foobar WHERE id='$key'";
    $result3 = MySQL_query($query3, $link_id);

    while ($data3 = mysql_fetch_array($result3))
    {
     $query4 = "SELECT * FROM foobar_img WHERE id='$data3[0]'";
     $result4 = MySQL_query($query4, $link_id);

     while ($data4 = mysql_fetch_array($result4))
     { 
      $x += 1;
      if ($x % 3 == 0)
      {
       echo "a: " . $data3[2];
      }
      else
      {
       echo "b: " . $data3[2];
      }
     }
    }
}

Answer 1

First and foremost, improve your SQL:

SELECT
    img.*
FROM
    foobar foo
    INNER JOIN foobar_img img ON
        foo.id = img.id
WHERE
    foo.id = $key

You will only have to iterate through one array.

Also, it appears that you're actually only selecting one row, so spitting out one row is expected behavior.

Additionally, please prevent yourself from SQL injection by using mysql_real_escape_string():

$query3 = "SELECT * FROM foobar WHERE id='" . 
     mysql_real_escape_string($key) . "'";

Update: As Dan as intimated, please run this query in your MySQL console to get the result set back, so you know what you're playing with. When you limit the query to one ID, you're probably only pulling back one row. That being said, I have no idea how many $keys are in $stuff, but if it spins over once, then it will be one.

You may be better off iterating through $stuff and building out an IN clause for your SQL:

$key_array = "";
foreach($stuff as $key)
{
    $key_array .= ",'" . mysql_real_escape_string($key) . "'";
}
$key_array = substr($key_array, 1);

...

WHERE foo.id IN ($key_array)

This will give you a result set with your complete list back, instead of sending a bunch of SELECT queries to the DB. Be kind to your DB and please use set-based operations when possible. MySQL will appreciate it.

I will also point out that it appears as if you're using text primary keys. Integer, incremental keys work best as PK's, and I highly suggest you use them!

Answer 2

You should use a JOIN between these two tables. It the correct way to use SQL, and it will work much faster. Doing an extra query inside the loop is bad practice, like putting loop-invariant code inside a loop.