Running mysql inside a while loop

Question

I have a while loop of a mysql call but I also am trying to run another mysql query inside of the while loop but it is only doing it once. I can not figure it out.

Here is my code:

$sql = "SELECT * FROM widget_layout WHERE module_id=".mysql_real_escape_string($id)." AND state='".mysql_real_escape_string($page)."' AND position=".mysql_real_escape_string($position);

$query = mysql_query($sql);
while ($row = mysql_fetch_assoc($query)) {
    $layout .= $row['widget_id'].'<br/>'; //test if it is looping through all rows
    $sql2 = "SELECT title FROM widgets WHERE id=".$row['widget_id'];
    $query2 = mysql_query($sql2);
    $result2 = mysql_fetch_array($query2);
    $layout .= $result2[0]; // test the title output
}

It is looping through the first query no problem but the second query is only load the title of the first widget, return null for the rest. Any idea of why this is doing this?

Answer 1

You don't have to use a WHILE loop -- this can be done in a single SQL statement:

SELECT wl.widget_id,
       w.title
  FROM WIDGET_LAYOUT wl
  JOIN WIDGETS w ON w.id = wl.widget_id
 WHERE wl.module_id = mysql_real_escape_string($id)
   AND wl.state = mysql_real_escape_string($page) 
   AND wl.position = mysql_real_escape_string($position);

The issue with NULL title values depends on if the WIDGET.title column is NULLable, or there isn't a record in the WIDGETS table for the id value. You need to check the values coming back from the first query, confirm they have supporting records in the WIDGETS table first, then look at the title value...

Answer 2

Directly from the mysql_query() docs: multiple queries are not supported. Your innery query is killing the outer one.