Paginating, interesting results

[je150]

Im trying to paginate some data. The first part shows up fine but when i click the next or numbered buttons the data disappears. It's as if the links are causing the script to run the $query again. Here's the code. Maybe i missed something elementary again. You'd think a simple thing like paginating wouldn't be this much trouble.

<?php
include "include/include_common.php";
include "include/include_dbconnect.php";
ConnectToDatabase();


$DB_Name = "emplog";

//Run The Query Without a Limit to get Total result
$SQL="SELECT COUNT(*) AS Total 
FROM $dbtable";
$SQL_Result=mysql_db_query($DB_Name, 
$SQL);
$SQL_Result_Array=mysql_fetch_array($SQL_Result);
$Total=$SQL_Result_Array['Total'];

if (empty($_GET['Result_Set']))
 
  {
   $Result_Set=0;
   $SQL.=" LIMIT $Result_Set, $per_page";
   }else
   {
 
  $Result_Set=$_GET['Result_Set'];
   $SQL.=" LIMIT $Result_Set, $per_page";
 
  }

// Run The Query With a Limit to get result
$SQL_Result=mysql_db_query('emplog', 
$SQL);
$SQL_Rows=mysql_num_rows($SQL_Result);

[COLOR=red]print $Result_Set;[/color]
[b]This part of the code is printing out 0 and on the first time i click next page it prints out 10, then next page again and it prints out 20, problem is it isnt being used as far as i can tell below, at least this script isnt requerying the database because when i replace the variable below in blue with either 10 or 20 it displays different records like it should.[/b]


$result = mysql_query("SELECT * FROM $dbtable ORDER BY date LIMIT [COLOR=blue]$Result_Set[/color],$per_page",$authentication);

print $Result_Set; [COLOR=green]//This is also displaying the correct number...[/color]

// Display Results using a for loop
 for 
($a=0; $a < $SQL_Rows; $a++)
    {
	

	$myrow = mysql_fetch_array($result); 
 
  echo "<table WIDTH=100% BORDER=\"1\" CELLPADDING=\"1\" bgcolor=\"" . $gridBGColor1 . "\">";

  echo "<tr bgcolor=\"#CCCCCC\"><th colspan=\"3\">Sort By:</th><th>Name</th><th>Month</th><th>Day</th><th>Year</th><th>Hours</th><th>Project ID</th><th>Description / Comments</th></tr>\n";

  do {
	printf("<tr>
		<td bgcolor=\"#787878\" valign=\"top\"><a href='edit.php?id=".$myrow["id"]."&action=edit'>Edit</a></td>
		<td bgcolor=\"#787878\" valign=\"top\"><a href='edit2.php?id=".$myrow["id"]."&action=edit'>Copy</a></td>
		<td bgcolor=\"#787878\" valign=\"top\"><a href='edit.php?id=".$myrow["id"]."&action=delete' onclick=\"return confirm('Are you sure you want to delete this entry?')\">Delete</a></td>
		<td valign=\"top\">%s</td><td valign=\"top\">%s</td>
		<td valign=\"top\">%s</td><td valign=\"top\">%s</td>
		<td valign=\"top\">%s</td><td width=75  valign=\"top\">%s-%s</td>
		<td>%s</tr>\n", 
	$myrow["name"], $myrow["month"], $myrow["day"], $myrow["year"], $myrow["hours"], $myrow["pid1"], $myrow["pid2"], $myrow["comment"]);

  } while ($myrow = mysql_fetch_array($result));

	echo "</table>\n";

}

// Create Next / Prev Links 
//and $Result_Set Value
if ($Total>0)
   {
   if ($Result_Set<$Total && 
$Result_Set>0)
      {
      $Res1=$Result_Set-$per_page;
      echo "<A HREF=\"testview.php?Result_Set=$Res1\">
 
    << Previous Page</A> ";
      }
   // Calculate and Display Page 
# Links
   $Pages=$Total / $per_page;
   if ($Pages>1)
      {
      for ($b=0,$c=1; 
$b < $Pages; $b++,$c++)
          {
          $Res1=$per_page * $b;
       
   echo 
 "<A HREF=\"testview.php?Result_Set=$Res1\">
 
        $c</A> ";
          }
      }
   if ($Result_Set>=0 && $Result_Set<$Total)
 
     {
      $Res1=$Result_Set+$per_page;
      if ($Res1<$Total)
         
{
         echo " <A HREF=\"testview.php?Result_Set=$Res1\">
 
       Next Page >></A>";
         }
      }
   }




?>

 

[DRJ478]

You are appending the LIMIT clause to your initial SELECT count(*) query. That won't work. You need to write a new SQL query "SELECT * FROM ...."

Also be aware that mysql_db_query() is deprecated. The PHP documentation says

Note: This function has been deprecated since PHP 4.0.6. Do not use this function. Use mysql_select_db() and mysql_query() instead.

 

[sleipnir214]

I agree with DRJ478. Except that you should be able to run mysql_num_rows() against the query that actually fetches your data to be displayed.

Also, you don't need this count at all. Since PHP's mysql_fetch_*() functions all return FALSE when there is no more data to be retrieved from the resultset, you can just use a while-loop in the place of the for-loop and not worry about getting the count of records retreived.

Have you looked at my FAQ titled "Paging data" in this forum? faq434-5244



Want the best answers? Ask the best questions!

TANSTAAFL!!