CODEWITHSUNDEEP
phpmysql
CodeOverload
CodeOverload

Reputation: 48565

PHP While() Stop Looping

i have a php loop which displays only one record even if there is hundreds.

here is the code:

<?php

$result1 = mysql_query("SELECT * FROM posts") or die(mysql_error());  
$numexem = mysql_num_rows($result1);
$s="0";

while($s<$numexem){
$postid=mysql_result($result1,$s,"id");
echo "Post id:".$postid;

$result2 = mysql_query("SELECT * FROM pics WHERE postid='$postid'") or die(mysql_error());  
$rows = mysql_fetch_array($result2) or die(mysql_error());
$pnum = mysql_num_rows($result2);

echo " There is ".$pnum." Attached Pictures";

$s++;
}

?>

I'm wondering if the loop stop because there is other SQL query inside it or what? and i don't think so.

Thanks

Upvotes: 0

Views: 1422

Answers (2)

cletus
cletus

Reputation: 625465

I would suggest a more traditional approach:

$result1 = mysql_query("SELECT * FROM posts") or die(mysql_error());  
while ($row = mysql_fetch_assoc($result1)) {
  ...
}

Also, don't do all these subordinate queries. Instead:

$sql = <<<END
SELECT *, (SELECT COUNT(1) FROM pics WHERE postid = p.postid) pic_count
FROM posts p
END;
$res = mysql_query($sql) or die($sql . ': ' . mysql_error());
while ($row = mysql_fetch_assoc($res)) {
  echo "$row[postid] has $row[pic_count] pictures\n";
}

Upvotes: 2

zneak
zneak

Reputation: 138261

No, it won't. By default, the MySQL PHP module prefetches all rows that match your query, so there's nothing to worry about in your code.

However, executing a hundred query can be time-consuming. You'd rather collect all the IDs from your first loop, then perform one single query using the SQL IN operator and get these results after. Avoid "nesting" queries as much as you can.

Upvotes: 0

Related Questions