[SOLVED] Empty result sets killing the whole loop

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
sethpackham
Forum Newbie
Posts: 11
Joined: Fri Nov 07, 2003 3:28 pm
Location: Apex, NC

[SOLVED] Empty result sets killing the whole loop

Post by sethpackham »

I think an empty result set from an SQL statement inside my do...while loop kills the do...while loop from continuing.

Basically my do...while outputs a table of rows, but for a couple of the cells in that row I want to do other SQL SELECTs. However, as soon as any of those SELECT statements returns an empty set (at least this is my theory), the master do...while loop just stops running. So, for example, in a table that should have 341 rows, I only get the first two rows.

What can I do to keep the outer do...while loop running even if a nested SELECT statement gets no results? Or is this not my problem? Any ideas will be greatly appreciated.

Code: Select all

$sql="SELECT * FROM $table_hymns ORDER BY $order_by"; 
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result) or die(mysql_error());

do {
	
	?>
	<tr>
	<td class=mainTable><a href="hymns.php?id=<?php echo $myrow&#1111;"id"] ?>"><?php echo "#" . $myrow&#1111;"id"] . " - " . $myrow&#1111;"title"] ?></a></td>
	<td class=mainTable><?php echo $myrow&#1111;"category"] ?></td>
	<td class=mainTable>
	<?php
		
	$sql_a="SELECT * FROM $table_hymns_sundays WHERE openingsong='$myrow&#1111;id]' ORDER BY sunday DESC LIMIT 1"; 
	$result_a = mysql_query($sql_a) or die(mysql_error());
	$numrows_a = mysql_num_rows($result_a);
	$myrow_a = mysql_fetch_array($result_a) or die(mysql_error());
	echo $myrow_a&#1111;"sunday"];
	
	?></td><td class=mainTable><?php
	
	$sql_b="SELECT COUNT(*) FROM $table_hymns_sundays WHERE openingsong='$myrow&#1111;id]' OR sacramentsong='$myrow&#1111;id]' OR restsong='$myrow&#1111;id]' OR closingsong='$myrow&#1111;id]'"; 
	$result_b = mysql_query($sql_b);
	$myrow_b = mysql_fetch_array($result_b) or die(mysql_error());
	
	if ($myrow_b&#1111;"COUNT(*)"]) &#123;
		echo $myrow_b&#1111;"COUNT(*)"]; 
	&#125;
	
	?>
	</td></tr><?php

	&#125; while ($myrow = mysql_fetch_array($result)); // end do...while loop
Last edited by sethpackham on Mon Nov 10, 2003 10:23 am, edited 1 time in total.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Hmm... seems like the result doesn't gets bufferred...
Try to fetch entire result set before you issue any other queries on that connection:

Code: Select all

// php side result caching:
 while($myrow=mysql_fetch_array($result)) $res_arr[]=$myrow;
 $myrow=$res_arr[0];
 reset($res_arr);
do{
//................
} while(list($key,$myrow)=each($res_arr)); //end
sethpackham
Forum Newbie
Posts: 11
Joined: Fri Nov 07, 2003 3:28 pm
Location: Apex, NC

Post by sethpackham »

I wasn't really sure what you were suggesting, but it sort of made sense after I read the manual about the reset() and list() functions. I put the caching code before my do statement, like this:

Code: Select all

<?php
$sql="SELECT * FROM $table_hymns ORDER BY $order_by"; 
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result) or die(mysql_error());

// php side result caching: 
while($myrow = mysql_fetch_array($result)) $res_arr[]=$myrow; 
$myrow=$res_arr[0]; 
reset($res_arr); 

do {
//...
and changed my while statement to the one you recommended, like this:

Code: Select all

<?php
//...
} while(list($key,$myrow)=each($res_arr)); //end 
?>
But now I only get one row from the do...while. Again, I am running SQL statements inside my do...while loop. These SQL statements sometimes return no result set. As soon as that happens, the outer do...while loop kills.

I wasn't sure what to do with the $key part. Am I supposed to be sending some value as $key?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

$key is just a placeholder, because each() always returns two-item array.

Weird... did you tried var_dump'ing some variables inside a loop, especially $myrow ? This may help you to understand what's going on.
sethpackham
Forum Newbie
Posts: 11
Joined: Fri Nov 07, 2003 3:28 pm
Location: Apex, NC

SOLVED

Post by sethpackham »

I figured out my problem. Any time my query returned an empty result set, no matter where it was on my page, it basically was killing everything.

The culprit was this little bit of PHP that said to die:

Code: Select all

$myrow = mysql_fetch_array($result) or die(mysql_error());
I just commented off the end part on queries that were returning empty result sets:

Code: Select all

$myrow = mysql_fetch_array($result); // or die(mysql_error());
Dying seems to be a bad thing, I guess!
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

Try using the die() statement within the mysql_query() function instead. That should work.

The manual only shows examples of die() being used within the mysql_connect(), and mysql_query(), and similar functions. Since the mysql_fetch_array() isn't doing any database querying there should not be any error to report in that sence.

Reference material: [php_man]mysql[/php_man]
Post Reply