# php while loop inside while loop



## twmprys

I wonder if there are any kind php/mysql gurus out there? I've put a "while" loop inside another "while" loop, each querying different tables. The idea is that a heading is pulled from table1 and used only once, whilst the list of items relevant to each heading is pulled out of table2. It works fine for the first heading - the heading appears and the list follows. The second heading appears, but the inner loop doesn't run - nothing follows.

This is the basic script:


PHP:


$query_heading = mysql_query("SELECT * FROM table1 ORDER BY heading") 
or die(mysql_error()); 
while($row = mysql_fetch_array( $query_heading )) {
echo $row['heading'];
$query_list = mysql_query("SELECT * FROM table2 WHERE heading='{$row['heading']}' ORDER BY day, period, time") 
or die(mysql_error()); 
while ($list = mysql_fetch_array( $query_list )) {
echo $list['day'] . $list['period'] . $list['time'];
echo $list['details'];
}  
}


----------



## Big-K

My guess is that you've got an error in your second query, one that doesn't make mysql fail but causes it to return an empty set. My recomendation is to print out the query string during each loop to make sure its parsing right.

You might also want to look into mysql's LEFT JOIN and RIGHT JOIN syntax, if you can figure it out it'll simplify your code alot.


----------



## dudeking

Take a look at this article here
http://code.google.com/speed/articles/optimizing-php.html

*Avoid doing SQL queries within a loop*

A common mistake is placing a SQL query inside of a loop. This results in multiple round trips to the database, and significantly slower scripts. In the example below, you can change the loop to build a single SQL query and insert all of your users at once.



PHP:


foreach ($userList as $user) {
  $query = 'INSERT INTO users (first_name,last_name) VALUES("' . $user['first_name'] . '", "' . $user['last_name'] . '")';
  mysql_query($query);
}

Produces:


Code:


INSERT INTO users (first_name,last_name) VALUES("John", "Doe")

Instead of using a loop, you can combine the data into a single database query.


PHP:


$userData = array();
foreach ($userList as $user) {
  $userData[] = '("' . $user['first_name'] . '", "' . $user['last_name'] . '")';
}
$query = 'INSERT INTO users (first_name,last_name) VALUES' . implode(',', $userData);
mysql_query($query);

Produces:



Code:


INSERT INTO users (first_name,last_name) VALUES("John", "Doe"),("Jane", "Doe")...


----------



## twmprys

Thanks for your replies. Dudeking, do you mean something like this:



PHP:


$query_heading = mysql_query("SELECT * FROM table1 ORDER BY heading") ; 
$row = mysql_fetch_array( $query_heading );
$query_list = mysql_query("SELECT * FROM table2 WHERE heading='{$row['heading']}' ORDER BY day, period, time");
$list = mysql_fetch_array($query_list);
foreach ($row['heading'] as $list['heading']) {
$result[] = '("' . $row['heading'] . '", "' . $list['day'] .  '", "' .$list['period'] . '", "' . $list['time'] . '")';
echo $result[1] . '
' . $result[2] . '
' . $result[3] , '
' . $result[4] . '<br /.>' ;
}  
?>

That doesn't throw up any errors, but the variables in the echo are empty.


----------



## twmprys

What I have just written is, of course, rubbish. Thank you, Dudeking, for encouraging me to look into the foreach statement. For the record, the solution I came up with involves a foreach statement within a while loop, as follows:



PHP:


$query_heading = mysql_query("SELECT * FROM table1, table2 WHERE table1.heading-table2.heading ORDER BY table1.heading")  
or die(mysql_error());  
while($row = mysql_fetch_array( $query_heading )) { 
echo $row['heading'];
$newarray = array("$row[4]", "$row[5]", "$row[6]");
foreach ($newarray as $item) {
echo $item;
}
}


----------



## twmprys

No, that's wrong again! Forget all that rubbish about _foreach_. If anybody's still reading this, the solution is a lot simpler: you simply need to create a variable equal to nothing that can then be used inside the _while loop_ to check whether the heading has been used via an i_f statement_:



PHP:


$query_heading = mysql_query("SELECT * FROM table1, table2 WHERE table1.heading-table2.heading ORDER BY table1.heading")   
or die(mysql_error());
$usedheading = '';   
while($row = mysql_fetch_array( $query_heading )) {
$newheading = $row['heading'];
if ($newheading !== $usedheading) {
echo $row['heading']; 
$usedheading = $newheading;
}
echo $row['etc'] 
}


----------

