CODEWITHSUNDEEP
phpmysqlloops
Deniz Zoeteman
Deniz Zoeteman

Reputation: 10111

Query goes in endless loop

The following goes into an endless loop, meaning it just constantly shows the same record over and over and over again.

<?php
while ($rowr = mysql_fetch_assoc(mysql_query("SELECT * FROM table1")) {
    while($rowu = mysql_fetch_assoc(mysql_query("SELECT * FROM table2 WHERE id = '".$rowr['uid']."'"))){
        while($rowc = mysql_fetch_assoc(mysql_query("SELECT * FROM table3 WHERE id = '".$rowr['cid']."'"))){
    ?>
    <tr><td><?php echo $rowc['post']; ?></td><td><a href="other.php?id=<?php echo $rowu['id']; ?>"><?php echo $rowu['username']; ?></a></td><td><a href="this.php?p=d&i=<?php echo $rowr['id']; ?>"><font color="#FF0000">X</font></a></td></tr>
    <?php
        };
    };
};
?>

Why does that happen and how can I fix it?

Upvotes: 2

Views: 227

Answers (4)

Czechnology
Czechnology

Reputation: 14992

Simply because the query gets executed again every time the loop condition is checked. That means you'll always get the same result unless something changes in the database in the meantime.

You need something like this:

<?php
$res1 = mysql_query("SELECT * FROM table1");
while ($rowr = mysql_fetch_assoc($res1)) {

    $res2 = mysql_query("SELECT * FROM table2 WHERE id = '".$rowr['uid']."'");
    while($rowu = mysql_fetch_assoc($res2)){

        $res3 = mysql_query("SELECT * FROM table3 WHERE id = '".$rowr['cid']."'");
        while($rowc = mysql_fetch_assoc($res3)){
?>
    <tr><td><?php echo $rowc['post']; ?></td><td><a href="other.php?id=<?php echo $rowu['id']; ?>"><?php echo $rowu['username']; ?></a></td><td><a href="this.php?p=d&i=<?php echo $rowr['id']; ?>"><font color="#FF0000">X</font></a></td></tr>
<?php
        }
    }
}
?>

That's just to illustrate why it doesn't work. As others already noted, combining your queries into one using JOIN would be advisable.

Btw, you don't need a ; after a {} block.

Upvotes: 2

Marc B
Marc B

Reputation: 360572

You're re-executing the queries on each loop iteration, restarting the results from scratch.

You want something like this instead:

$resultR = mysql_query("... table1");
while($rowR = mysql_fetch_assoc($resultR)) {
    $resultU = mysql_query("... table2");
    while($rowU = mysql_fetch_assoc($resultU)) {
        etc...
    }
}

Of course, this is a highly inefficient construct. You end up running count($rowR) * count($rowU) * count($rowC) queries. Why not reformulate it as a join?

Upvotes: 0

Headshota
Headshota

Reputation: 21449

Use joins and you can do it in one query.

Mysql Join

Join Tutorial

Upvotes: 0

jeroen
jeroen

Reputation: 91734

You are putting the mysql query in the while statement so every time it gets there it does the same query and shows the same first record, you are never advancing to the next record in the result set.

Personally I would combine all queries into one, but to show how you can solve your problem (the same applies to all loops):

$results = mysql_query("SELECT * FROM table3 WHERE id = '".$rowr['cid']."'");
while ($rowc = mysql_fetch_assoc($results))
{
  // do stuff
}

Upvotes: 7

Related Questions