Geek Answers Handbook

PHP mysql_fetch_array does not return all rows - one row is always ignored

I execute a rather simple mysql query and return the results to a table. There were three entries in db, and the query is retrieved from two tables. As a result, I get a count of three records (mysql_num_rows echo messages), but only two tables are displayed in the table. Using the print_r command in the results of an array shows only one specific record - the other records show in print-r .. I added another record in db and now three records are displayed - the same record as before is not displayed and is the only one an entry in the print_r command. Here is 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 spent a lot of time on the Internet looking for explanations without success. I disabled the $pagination line of code without effect. I tried various other tricks and repeated the conclusion. The number of rows returned ( n ) is always correct, but only n-1 rows are displayed in the table. Any ideas there?

Thanks - Don

+4
source share
5 answers

Each time you call mysql_fetch_array , you take a string from the resource. When a resource has no more rows, it returns false. How the while ($a = mysql_fetch_array($resource)) loops work while ($a = mysql_fetch_array($resource)) .

  $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 does exactly what you tell it! Just remove the first $row = mysql_fetch_array($result) or die(mysql_error()); since it in no way serves any purpose.

+8
source

You are retrieving the first result outside the while loop.

+5
source 
  $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);

should 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);
+1
source

Like other close ones, the problem is that you call mysql_fetch_array() once, in the line after $result = mysql_query( ... , before going into your while . This takes the first row from your results, but you never You do nothing with it, then when you start the while loop, you call mysql_fetch_array() again, but since you have already done the first line, it starts on the second line.

0
source

In order, you should understand why it ignores 1 row, allows you to see $ row = mysql_fetch_array ($ result) or die (mysql_error ()); this code already picks up your first row, and then you select in the loop so it indicated the row after the row has already been extracted.

  $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>
0
source

More articles:


All Articles