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

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 lo开发者_运维百科oking 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

---

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.

---

You are fetching the first result outside your while loop.

---

    $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);

---

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.

---

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>