MySQL not pulling all rows.

Question

Hey guys, first time using stackoverflow.

can you guys help me debug?

Heres the problem, this query is selecting all of the rows from my database, its only outputting the first one twice for some reason.

  $top10_query = "SELECT * FROM kicks";
  $result = mysqli_query($cxn, $top10_query) or die("Couldn't execute query.");
  $row = mysqli_fetch_assoc($result);

  $rating = $row['rating'];
  $description = $row['description'];
  $completed = $row['completed'];
  $userid = $row['userid'];
  $posted = $row['posted'];

  while($row = mysqli_fetch_assoc($result)) {
   echo "<tr>";
    echo "<td class='rating'>" . $rating . "</td>";
    echo "<td class='description'>" . $description . " </td>";
    echo "<td class='completed_" . $completed . "'>" . $completed  . "</td>";
    echo "<td class='author'>";
     echo "Posted by: <a href='profile?userid=" . $userid . "'>" . $userid . "</a><br />";
     echo "on "; echo $posted;
    echo "</td>";
   echo "</tr>";
  }

Answer 1

You are looping over the rowset, but never retrieving its value more than once. You pulled all of the values out of the first row, and cached them here:

$rating = $row['rating'];
$description = $row['description'];
$completed = $row['completed'];
$userid = $row['userid'];
$posted = $row['posted'];

Move this code into the loop, and remove the first fetch.

Answer 2

The variables $rating etc are not "binded" to the expressions $row['rating'] etc. Once set, They will forever take these values unless you modify them again.

See PHP: Assignment Operators for detail.

Try to rewrite them as:

  $top10_query = "SELECT * FROM kicks";
  $result = mysqli_query($cxn, $top10_query) or die("Couldn't execute query.");

  while($row = mysqli_fetch_assoc($result)) {
    $rating = $row['rating'];    // <-- use the new value every time a row is fetched.
    $description = $row['description'];
    $completed = $row['completed'];
    $userid = $row['userid'];
    $posted = $row['posted'];

   echo "<tr>";
    echo "<td class='rating'>" . $rating . "</td>";
    echo "<td class='description'>" . $description . " </td>";
    echo "<td class='completed_" . $completed . "'>" . $completed  . "</td>";
    echo "<td class='author'>";
     echo "Posted by: <a href='profile?userid=" . $userid . "'>" . $userid . "</a><br />";
     echo "on "; echo $posted;
    echo "</td>";
   echo "</tr>";
  }

Answer 3

You need to update $rating, $description, etc. within the while loop:

<?php

  $top10_query = "SELECT * FROM kicks";
  $result = mysqli_query($cxn, $top10_query) or die("Couldn't execute query.");

  while($row = mysqli_fetch_assoc($result)) {
   $rating = $row['rating'];
   $description = $row['description'];
   $completed = $row['completed'];
   $userid = $row['userid'];
   $posted = $row['posted'];

   echo "<tr>";
    echo "<td class='rating'>" . $rating . "</td>";
    echo "<td class='description'>" . $description . " </td>";
    echo "<td class='completed_" . $completed . "'>" . $completed  . "</td>";
    echo "<td class='author'>";
     echo "Posted by: <a href='profile?userid=" . $userid . "'>" . $userid . "</a><br />";
     echo "on "; echo $posted;
    echo "</td>";
   echo "</tr>";
  }

?>

Or, of course, you can inline $rating, etc., writing $row['rating'] instead.

Note: you probably want to run your variables through htmlspecialchars before inserting them into HTML. Otherwise, a description like <script>alert('hacked');</script> could execute a script, opening yourself up to XSS attacks.

You can also use extract. I do not recommend you do this, however, as it may cause problems and confusion for other developers:

<?php

  $top10_query = "SELECT * FROM kicks";
  $result = mysqli_query($cxn, $top10_query) or die("Couldn't execute query.");

  while($row = mysqli_fetch_assoc($result)) {
   extract($row);

   echo "<tr>";
    echo "<td class='rating'>" . $rating . "</td>";
    echo "<td class='description'>" . $description . " </td>";
    echo "<td class='completed_" . $completed . "'>" . $completed  . "</td>";
    echo "<td class='author'>";
     echo "Posted by: <a href='profile?userid=" . $userid . "'>" . $userid . "</a><br />";
     echo "on "; echo $posted;
    echo "</td>";
   echo "</tr>";
  }

?>