PHP query not showing all results

Question

I have a database with 3 tables. One table contains people, the other table contains categories and the third table is the many to many relationship table. My php file is a report showing all the people in each category. For some reason, my query is not displaying the first record in each category and I can't figure out how to fix it.

Code:

<?php require_once('Connections/meritBadges.php');
$result = mysql_query('SELECT id, badge_name FROM meritbadgestable ORDER BY badge_name');

while ($row = mysql_fetch_array($result)) 
{
    $badgenumber = $row['id'];   
    $badgename = mysql_query("SELECT id, badge_name FROM meritbadgestable WHERE id=$badgenumber");
    while($badge = mysql_fetch_array($badgename))
    {
        $badge_name = $badge['badge_name'];
    } 
    $counselors = mysql_query("SELECT id, firstname, lastname, counselorid, meritbadgeid FROM counselorstable, meritbadgetocounselortable WHERE id=counselorid AND meritbadgeid='$badgenumber' ORDER BY lastname");

    $counselor = mysql_fetch_array($counselors);
    if(empty($counselor)) 
    {
        echo '<h2>Merit Badge: ' . $badge_name . '</h2><p class="counselortable">There are no counselors for this merit badge.</p>';
    }
    else 
    {
        echo '<h2>Merit Badge: ' . $badge_name . '</h2>
              <table width="950" border=1 cellpadding="2" cellspacing="0" class="counselorstable">
              <tr id="tabletop">
              <td width="162" bgcolor="#000000">ID</td>
              <td width="160" bgcolor="#000000">Name</td>
              </tr>';
    }

    while ($counselor = mysql_fetch_array($counselors)) 
    {
        echo "<tr valign='top'>\n";
        $id = htmlspecialchars($counselor['id']);
        $firstname = htmlspecialchars($counselor['firstname']);
        $lastname = htmlspecialchars($counselor['lastname']);

        echo "<td>$id</td>\n";
        echo "<td>$firstname $lastname</td>\n";
        echo "</tr>\n";
    }
    echo '</table><br />';
}
?>

Which query are you talking about? There are three shown (which by the way could likely be combined into one single query). I don't see any people or category tables shown in these queries. Please better explain. — Mike Brant, Dec 20 '13 at 23:54

score 1 · Answer 1 · answered Dec 20 '13 at 23:44

Here's why:

$counselor = mysql_fetch_array($counselors);
if(empty($counselor)) {
}

....    

while ($counselor = mysql_fetch_array($counselors)) {

That first call is checking to see how many rows you've got, but you're loading the first fow to do it; when you start the loop, you're loading the second row and onwards. What you can do instead is use mysql_num_rows to see if you've got any rows:

if (mysql_num_rows($counselor)) {
     echo 'There are rows';
}

But note that you're using mysql_* functions - those are deprecated as of version 5.5 of PHP, which means they're going to stop working. You should look at using mysqli_ or PDO instead.

score 1 · Answer 2 · edited May 23 '17 at 12:19

You're creating a numbered array here and then attempting to reference the 'id' key of $counselor. You need an associative array instead.

Change:

while ($counselor = mysql_fetch_array($counselors)) 
    {
        echo "<tr valign='top'>\n";
        $id = htmlspecialchars($counselor['id'])
        ...

To:

while ($counselor = mysql_fetch_assoc($counselors)) 
    {
        echo "<tr valign='top'>\n";
        $id = htmlspecialchars($counselor['id'])
        ...

You've also got a handful of other oddities in here, like the fact that you're fetching the array initially (doing an if/else statement) and then fetching it again and doing a while loop, all inside of another while loop. This is a lot of processing overhead and will make your script exceptionally slower than it needs to be.

Some suggestions:

Figure out how you can use SQL Joins in your database in order to make one single query and return all the data in one go. SQL is infinitely faster than a php loop like this.
The mysql_xxxx extension is deprecated and for a plethora of reasons you should not use it. Look at PDO, or at the very least mysqli instead.
If you can't do either, instead of fetching the array of results to determine whether it's empty, use mysql_num_rows to simply count the number of rows in the result and do the while loop if the result is > 0.

I entirely missed that. I thought that by default, the PHP mysql_fetch_array returned the result set BOTH by numeric index AND by association. I didn't know there was a way to change that default behavior without supplying an argument on the `mysql_fetch_array`. I totally missed that. — spencer7593, Dec 21 '13 at 00:08

score 0 · Answer 3 · answered Dec 21 '13 at 00:03

To check if the query is not empty:

 $counselors = mysql_query("SELECT id, firstname, lastname, counselorid, meritbadgeid FROM counselorstable, meritbadgetocounselortable WHERE id=counselorid AND meritbadgeid='$badgenumber' ORDER BY lastname");

    if(mysql_num_rows($counselors)) 
    {
...

and to fetch the results

while ($counselor = mysql_fetch_assoc($counselors)) 
    {
        echo "<tr valign='top'>\n";
        $id = htmlspecialchars($counselor['id'])
...

You can visit these sites:

spencer7593 · Answer 4 · 2013-12-21T00:09:55.323

That block of code inside your while loop is not getting executed for the first row you retrieved, way back before the loop.

The "fetch" operation on the loop retrieves the next row, overwriting the values for the first row, before you have output the first row.

That's the problem.

One way to fox that, without any major restructuring of your code, would be to use a do { } while loop in place of the while loop, and move that loop up into the previous else block, so that it is only executed if you have already fetched a row. (Just move the closing bracket for that else below the loop.)

A do { } while loop ensures that the action within the loop is executed at least once, which means that the values from the first row would get processed (echoed to the page), before the second row is fetched.

For example:

else 
{
    echo '<h2>Merit Badge: ' . $badge_name . '</h2>
          <table width="950" border=1 cellpadding="2" cellspacing="0" class="counselorstable">
          <tr id="tabletop">
          <td width="162" bgcolor="#000000">ID</td>
          <td width="160" bgcolor="#000000">Name</td>
          </tr>';
    do
    {
        echo "<tr valign='top'>\n";
        $id = htmlspecialchars($counselor['id']);
        $firstname = htmlspecialchars($counselor['firstname']);
        $lastname = htmlspecialchars($counselor['lastname']);

        echo "<td>$id</td>\n";
        echo "<td>$firstname $lastname</td>\n";
        echo "</tr>\n";
    } while ($counselor = mysql_fetch_array($counselors)) ;

}

This isn't the "best" fix. This is just an illustration, pointing out what's happening in your code, and just one possible (albeit inelegant) way to reorganize the code so that it doesn't "skip" that first row. (You're fetching it, just not putting it out to the screen.)

As others will no doubt comment, the mysql_ interface is deprecated; you should be using either mysqli or PDO.

PHP query not showing all results

4 Answers4