Stop continuous table generation

Question

Hello, I am trying to stop the data in the following table from continuing to repeat itself, as in looping on the same data, crashing the browser:

Here's the query:

$posts = mysqli_fetch_assoc(mysqli_query($db, "SELECT * from tbl_bugresponse WHERE bugid = $id ORDER BY time"));

And here's the table code:

<table width="100%" class="tborder"><tr>
<th class="tcat">Name</th>
<th class="tcat">Reply</th>
</tr>
<?php
$colour="1";
while($posts){
if($colour=="1"){
     echo ("<tr class='alt1'>");
     $f1=$posts['by'];
     $f2=$posts['content'];
     $colour="2";
} else {
     echo "<tr class='alt2'>";
     $f1=$posts['by'];
     $f2=$posts['content'];
     $colour="1";
}
?>
<td><?php echo $f1; ?></td>
<td><?php echo $f2; ?></td>
</tr>
<?}?>
</table>

Note that I am using mysqli not mysql.

Answer 1

The problem is the while($posts){ line.

You are writing code which says "Keep going until someone sets the posts variable to null or false", but then you never touch it... so it will keep going forever.

Update

Had another look at your query, it looks like you've got confused about how it's supposed to work. Here's what it should look like:

$result = mysqli_query($db, "SELECT * from tbl_bugresponse WHERE bugid = $id ORDER BY time");
...
$post = mysql_fetch_assoc($result); // fetch first row
while( $post ) { // will keep going until $post gets set to null or false
  ...
  $post = mysql_fetch_assoc($result); // fetch next row and re-run the loop
}

You'll note that the mysqli_query call has been separated out. What mysqli_query does is run the database query, then give you a "handle" to the list of rows that come back from the query. This is common point of confusion as mostly people expect it to give them an array of all the rows, not a "handle".

You then have to call mysql_fetch_assoc to get the first row out of the list, do something with it, then call mysql_fetch_assoc again repeatedly to get the next row.

When it runs out of rows, mysql_fetch_assoc will return null instead of a valid row, so that's how you can check to see when you've finished, and to stop the loop.

Answer 2

You aren't progressing through the result set. mysqli_query gets your resultset, and mysqli_fetch_assoc progresses through it. You need something like this:

$results = mysqli_query($db, "SELECT * from tbl_bugresponse WHERE bugid = $id ORDER BY time");
while ($posts = mysqli_fetch_assoc($results)) {
    // table construction code goes here
}