PHP mysql_fetch_array is not returning all rows - one row is always ignored

Question

I am running a fairly straight forward mysql request and returning the results to a table. There were three record in the db, and the query is pulling from two tables. As a result, I am getting a count of three records (echoing mysql_num_rows), but only two show in the table. Using a print_r command on the array result shows only one particular record - the other records do show in the print-r.. I added another record to the db, and now three records show - and the same record as before does not show and is the only record in the print_r command. Here's the relevant code:

<td id="page1"> 
  <?php             
    $limit  = 15;             // Set limit to show for pagination
    $page   = $_GET['page'];  // get page number from submit

    if($page) 
      $start = ($page - 1) * $limit; // first item to display on this page
    else
      $start = 0; // if no page var is given, set start to 0

    $query  = "SELECT PartyMstr.PartyMstrID, UserName, FirstName, LastName, XrefPartyRoleID
                FROM PartyMstrRole, PartyMstr
                WHERE PartyMstr.PartyMstrID = PartyMstrRole.PartyMstrID &&
                      PartyMstrRole.XrefPartyRoleID = 1 
                ORDER BY LastName, FirstName ASC
                LIMIT $start, $limit
              ";

    $result = mysql_query($query, $connection);

    $row = mysql_fetch_array($result) or die(mysql_error());
    $totalitems1 =  mysql_num_rows($result);
  ?>
  <center><h3> Admin User List </h3></center>
  <?php
    echo "<table border=\"1\" align=\"center\">";
    echo "<tr><th>PartyMaster ID</th>";
    echo "<th>UserName</th>";
    echo "<th>Last, First</th>";
    echo "<th>Link</th></tr>";

    while($row = mysql_fetch_array($result)) {
      echo "<tr><td>";
      echo $row['PartyMstrID'];
      echo "<td>";
      echo $row['UserName'];
      echo "<td>";
      echo " " . $row['LastName'] . ", " . $row['FirstName'] . " ";
      echo "<td>";
      echo "<a href = \"http://www.505575.com/editUser.php?id=" . $row['PartyMstrID'] . "\" >Edit</a>";

      // echo "<td>";
      // echo $row['XrefPartyRoleID'];

      echo "</td></tr>";
    }

    echo "</table><br/><br/> ";

    $paginaton  = getPaginationString( $page, $totalitems, $limit,
                    $adjacents  = 1,
                    $targetpage = "adminUserList.php",
                    $pagestring = "?page="
    ); // Functon found in functions.php

    echo $paginaton; 
  ?>
</td>

I've spent a lot of time online looking for an explanation without success. I've switched off the $pagination code line without effect. I have tried various other tricks and echoed output. The number of rows returned (n) is always correct, but only n-1 rows appear in the table. Any ideas out there?

Thanks - Don

As a clarification: did you stick do print_r($row) just inside your while-loop? — Levi Morrison
– Levi Morrison, Commented Oct 11, 2011 at 3:23
You might consider learning PDO, this is going to be deprecated anyways. — user529649
– user529649, Commented Oct 11, 2011 at 3:41

Mahdi.Montgomery · Accepted Answer · 2011-10-11 03:29:44Z

9

Every time you call mysql_fetch_array you are taking a row from the resource. When the resource has no more rows to give, it returns false. That's how while ($a = mysql_fetch_array($resource)) loops work.

    $result = mysql_query($query, $connection);
    $row = mysql_fetch_array($result) or die(mysql_error());    
    $totalitems1 =  mysql_num_rows($result);

    // first row is taken from resource

    ....

    while($row = mysql_fetch_array($result))

    // now take the rest of the rows

As you can see, your code is doing exactly what you tell it to! Just remove the first $row = mysql_fetch_array($result) or die(mysql_error()); as it doesn't serve any purpose anyway.

answered Oct 11, 2011 at 3:29

Mahdi.Montgomery

2,0244 gold badges17 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Don Clark Over a year ago

Thank to everyone who answered - the answer is obvious once pointed out. I checked the first of the many right answers as the correct one just for simplicities sake. Thanks again.

K-ballo · Accepted Answer · 2011-10-11 03:24:29Z

5

You are fetching the first result outside your while loop.

answered Oct 11, 2011 at 3:24

K-ballo

81.6k20 gold badges165 silver badges172 bronze badges

Comments

8BallDZigns · Accepted Answer · 2011-10-11 03:28:40Z

    $query = "SELECT PartyMstr.PartyMstrID, UserName, FirstName, LastName, XrefPartyRoleID
        FROM PartyMstrRole, PartyMstr 
        WHERE PartyMstr.PartyMstrID = PartyMstrRole.PartyMstrID  && PartyMstrRole.XrefPartyRoleID = 1 
        ORDER BY LastName, FirstName ASC
        LIMIT $start,$limit";
    $result = mysql_query($query, $connection);
    $row = mysql_fetch_array($result) or die(mysql_error());    
    $totalitems1 =  mysql_num_rows($result);

needs to be:

    $query = "SELECT PartyMstr.PartyMstrID, UserName, FirstName, LastName, XrefPartyRoleID
        FROM PartyMstrRole, PartyMstr 
        WHERE PartyMstr.PartyMstrID = PartyMstrRole.PartyMstrID  && PartyMstrRole.XrefPartyRoleID = 1 
        ORDER BY LastName, FirstName ASC
        LIMIT $start,$limit";
    $result = mysql_query($query, $connection);
    $totalitems1 =  mysql_num_rows($result);

Jordan Running · Accepted Answer · 2011-10-11 03:32:10Z

0

As the others intimate, the problem is that you're calling mysql_fetch_array() once, on the line after $result = mysql_query( ..., before you go into your while loop. This takes the first row from your results, but you never do anything with it. Then when you start your while loop you call mysql_fetch_array() again, but since you've already taken the first row, it starts with the second row.

answered Oct 11, 2011 at 3:32

Jordan Running

107k18 gold badges193 silver badges189 bronze badges

Comments

Mohit Bumb · Accepted Answer · 2011-10-11 05:04:38Z

okay you must understand why it ignore 1 row lets see $row = mysql_fetch_array($result) or die(mysql_error()); this code fetch your 1st row already and then you fetch in loop so it pointed row after the row is already fetched.

 $limit  = 15;             // Set limit to show for pagination
    $page   = $_GET['page'];  // get page number from submit

    if($page) 
      $start = ($page - 1) * $limit; // first item to display on this page
    else
      $start = 0; // if no page var is given, set start to 0

    $query  = "SELECT PartyMstr.PartyMstrID, UserName, FirstName, LastName, XrefPartyRoleID
                FROM PartyMstrRole, PartyMstr
                WHERE PartyMstr.PartyMstrID = PartyMstrRole.PartyMstrID &&
                      PartyMstrRole.XrefPartyRoleID = 1 
                ORDER BY LastName, FirstName ASC
                LIMIT $start, $limit
              ";

    $result = mysql_query($query, $connection);

    $row = mysql_fetch_array($result) or die(mysql_error());
    $totalitems1 =  mysql_num_rows($result);
  ?>
  <center><h3> Admin User List </h3></center>
  <?php
    echo "<table border=\"1\" align=\"center\">";
    echo "<tr><th>PartyMaster ID</th>";
    echo "<th>UserName</th>";
    echo "<th>Last, First</th>";
    echo "<th>Link</th></tr>";

    while($row = mysql_fetch_array($result)) {
      echo "<tr><td>";
      echo $row['PartyMstrID'];
      echo "<td>";
      echo $row['UserName'];
      echo "<td>";
      echo " " . $row['LastName'] . ", " . $row['FirstName'] . " ";
      echo "<td>";
      echo "<a href = \"http://www.505575.com/editUser.php?id=" . $row['PartyMstrID'] . "\" >Edit</a>";

      // echo "<td>";
      // echo $row['XrefPartyRoleID'];

      echo "</td></tr>";
    }

    echo "</table><br/><br/> ";

    $paginaton  = getPaginationString( $page, $totalitems, $limit,
                    $adjacents  = 1,
                    $targetpage = "adminUserList.php",
                    $pagestring = "?page="
    ); // Functon found in functions.php

    echo $paginaton; 
  ?>
</td>

Collectives™ on Stack Overflow

PHP mysql_fetch_array is not returning all rows - one row is always ignored

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related