Pagination! oh where oh where did i go wrong :)

[aeafisme23]

Okay, so im having some problems doing pagination. I have attempted many times and luckily i am not receiving errors....but the script is not working as it should..... Here is my SQL result code followed by the pagination code.. Any advice would be great because pagination has always been a downfall of mine and never have gotten it to work. RIGHT NOW it displays this on the bottom of the page for pagination FIRST PREV (Page 1 of 0) NEXT LAST ..... I set it to only display 3 results so i can test it and rigth now my sql is pulling all my results (9 in total).

 

Scroll half way down the code to see BEGIN PAGINATION CODE

 

CODE:

function list_users() 
{ 
    $y = 0; //counter 
    $sql = "select * from contract_status order by station_id;";  
    $result = conn($sql); 

include "smsinclude.php"; 


  echo "<table width=\"700\" cellpadding=\"0\" cellspacing=\"0\"> 
        	<tr><td colspan=\"2\" style=\"font-size:18px; font-weight:bold;\">Manage Contract Status</td></tr> 
        	<tr>
			<td valign=\"top\"><br><a href='".$_SERVER['PHP_SELF']."?action=add'>Add info to Contract Status</a></td>
			<td valign=\"top\"><br>$navigation</td>
		</tr>
	</table>
	<table width=\"760\" cellpadding=\"5\" cellspacing=\"0\">
		<tr>
			<td width=\"80\"><b><u>Station</u></b></td>
			<td width=\"300\"><b><u>Status</u></b></td>
			<td width=\"300\"><b><u>Notes</u></b></td>
			<td width=\"80\"><b><u>Delete</u></b></td></tr>";

   if (mysql_num_rows($result)){ 
      //show a list of kids with name as a link to the prepopulated form with their data in it 
      while($rows = mysql_fetch_array($result)){ 
        
        //change row background color 
        (($y % 2) == 0) ? $bgcolor = "#F8F7F2" : $bgcolor=" #FFFFFF"; 
        
        //build strings to make life easier 
        $station_id = $rows['station_id']; 
        $status = $rows['status']; 
        $notes = $rows['notes']; 
        $id = $rows['id']; 
        
        //pass the url to delete the correct table
  		$field_data = "contract_status";	
        
        //echo out the row 
        echo "<tr style='background-color:$bgcolor;'>
			<td width=\"80\" valign=\"top\"><a href='".$_SERVER['PHP_SELF']."?id=$id'>$station_id</a></td>
			<td valign=\"top\" height=\"30\" valign=\"middle\">$status</td>
			<td valign=\"top\" height=\"30\" valign=\"middle\">$notes</td>
			<td width=\"80\" valign=\"top\" height=\"30\" valign=\"middle\"><a href='delete_record.php?id=$id&field_data=$field_data'>Delete</a></td>
		<tr>"; 
        $y++;  //increment the counter 
      }//end while 
      echo "</table><br>$navigation2 <br><br>If you wish to print this page you must use Landscape Printing for best results"; 
  }else{ 
    //handle no results 
    echo "<tr><td colspan='3' align='center'><b>No data found.</b></td></tr>"; 
  }//endif 

echo "<br>";
  
// BEGIN CODE FOR PAGINATION  
  
  
if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} // if

/* 2. Identify how many database rows are available
This code will count how many rows will satisfy the current query. */

$query = "SELECT count(*) FROM contract_status";
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

/* 3. Calculate number of $lastpage
This code uses the values in $rows_per_page and $numrows in order to identify the number of the last page.*/

$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);

/*4. Ensure that $pageno is within range
This code checks that the value of $pageno is an integer between 1 and $lastpage.*/

$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if
if ($pageno < 1) {
   $pageno = 1;
} // if

/*5. Construct LIMIT clause
This code will construct the LIMIT clause for the sql SELECT statement.*/

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

/*6. Issue the database query
Now we can issue the database qery and process the result.*/

$query = "SELECT * FROM contract_status $limit";

/*7. Construct pagination hyperlinks
Finally we must construct the hyperlinks which will allow the user to select other pages. We will start with the links for any previous pages.*/

if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
} // if

/*Next we inform the user of his current position in the sequence of available pages.*/

echo " ( Page $pageno of $lastpage ) ";

/*This code will provide the links for any following pages.*/

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
} // if
  
// end code for pagination
  
} 

 

Could the pagination be screwed up by my first SQL call to display all fields, where in the pagination sql it counts the fields?

 

Thanks!

[Boo-urns]

I believe you could be correct on the first query calling to display all fields.  Here is what I usually do when I am doing just php pagination:

 

<?php
// Define the number of results per page
$max_results = 5;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);    // page is exactly like your 'pageno' variable just getting it from the URL

// Perform MySQL query on only the current page number's results
$sql = mysql_query("SELECT * FROM contract_status ORDER BY station_id DESC LIMIT $from, $max_results");
?>

 

then in your document:

<?php
while($row = mysql_fetch_array($sql)){
//echo your information you need
}

// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM contract_status"),0);
// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);



// Build Page Number Hyperlinks
echo "<br />";

// Build Previous Link
if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\">"."<< "."Previous</a>";
}

for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
     echo "<a href=\"".$_SERVER['PHP_SELF']."?pageno=$i\">$i</a> ";
} else {
    echo "<a href=\"".$_SERVER['PHP_SELF']."?pageno=$i\">$i</a> ";
        }
}

// Build Next Link
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?pageno=$next\">Next >></a>";
}
?>

 

I didn't test this code so i'm not sure if it works perfectly but it is the basis of the pagination tutorial phpfreaks had. Hope this helps!