....not the right way but heres what i come up with..
1 problem - when my search result is > page limit
search result is display in a single page not following the page limit and the pagination is display..
if my search result < page limit
it is working ok..
Code:
<!doctype html public "-//w3c//dtd html 3.2//en">
<html>
<head>
<title>Paging</title>
</head>
<body bgcolor="#ffffff" text="#000000" link="#0000ff" vlink="#800080" alink="#ff0000">
<form method="post" action="" >
<br />Search <input type="text" name="search" />
<input type="submit" value="Search"/></form><br />
<?php
require "connect.php"; // All database details will be included here
// define values the rest of the code uses
$table = 'person'; // database table name
$sorts = array('firstname'=>'First Name','lastname'=>'Last Name','age'=>'Age'); // list of permissible sort choices
$sort_bys = array('asc','desc'); // list of permissible order by choices
$limit = 3; // No of records to be shown per page.
// condition inputs/set default values
$start = (isset($_GET['start'])) ? (int)$_GET['start'] : 0; // default to starting row 0 if not specified
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'firstname'; // default to firstname if not specified
$sort_by = (isset($_GET['sort_by'])) ? $_GET['sort_by'] : 'asc'; // default to asc if not specified
// validate sort input (used as a keyword in query statement)
if(!array_key_exists($sort,$sorts)){
$sort = 'firstname'; // default to firstname
}
// validate sort_by input (used as a keyword in query statement)
if(!in_array($sort_by,$sort_bys)){
$sort_by = 'asc'; // default to asc
}
$eu = $start;
$this1 = $eu + $limit;
$back = $eu - $limit;
$next = $eu + $limit;
// get opposite sort_by value for producing toggle links in the table heading (only)
if($sort_by == 'asc'){
$sort_order = 'desc';
} else {
$sort_order = 'asc';
}
/////////////// WE have to find out the number of records in our table. We will use this to break the pages///////
$query="SELECT COUNT(*) FROM $table";
$result=mysql_query($query);
//echo $query, mysql_error();
list($nume) = mysql_fetch_row($result);
/////// The variable nume above will store the total number of records in the table////
echo "Totals of records: $nume | Page limit: $limit<br />";
/////////// Now let us print the table headers ////////////////
echo "<table border='1'>" ;
echo "<tr> <th>ID</th>";
// dynamically produce choices/columns
foreach($sorts as $key=>$value){
echo "<th><a href='?sort=$key&sort_by=$sort_order'>$value</a></th>";
}
echo "</tr>";
////////////// Now let us start executing the query with variables $eu and $limit set at the top of the page///////////
//$query="SELECT * FROM $table ORDER BY $sort $sort_by limit $eu, $limit";
if (isset($_POST['search'])) {
$_POST['search'] = mysql_real_escape_string($_POST['search']);
//run the query for searching
$result= mysql_query("SELECT * FROM $table where firstname like '%" . $_POST['search'] . "%' or lastname like '%" . $_POST['search'] . "%' or age like '%" . $_POST['search'] . "%' ORDER BY $sort $sort_by");
//$query="SELECT * FROM $table ORDER BY $sort $sort_by limit $eu, $limit";
//$result=mysql_query($query);
//echo $query, mysql_error();
$search_result=mysql_num_rows($result);
$nume=$search_result;
echo "Totals of record search: $nume | Page limit: $limit<br />";
if ($_POST['search'] =='')
{
echo "Please input Keyword";
$query="SELECT COUNT(*) FROM $table";
$result=mysql_query($query);
//echo $query, mysql_error();
list($nume) = mysql_fetch_row($result);
$query="SELECT * FROM $table ORDER BY $sort $sort_by limit $eu, $limit";
$result=mysql_query($query);
}
else
{
echo "$search_result";
echo " record of <b>" . $_POST['search'] . "</b> found";
}
//Show all if result 0
if ($search_result=='0')
{
$query="SELECT COUNT(*) FROM $table";
$result=mysql_query($query);
//echo $query, mysql_error();
list($nume) = mysql_fetch_row($result);
$query="SELECT * FROM $table ORDER BY $sort $sort_by limit $eu, $limit";
$show=mysql_query($query);
$count = (isset($eu) && $eu > 0) ? $eu+1 : 1;
//////////////// Now we will display the returned records in side the rows of the table/////////
while($rows = mysql_fetch_array($show)){
echo "<tr >";
echo "<td> <font face='Verdana' size='2'>";
echo $count++;
echo "</font></td>";
echo "<td>$rows[firstname]</td>";
echo "<td>$rows[lastname]</td>";
echo "<td>$rows[age]</td>";
echo "</tr>";
}
}
}
else
{
$query="SELECT COUNT(*) FROM $table";
$result=mysql_query($query);
//echo $query, mysql_error();
list($nume) = mysql_fetch_row($result);
$query="SELECT * FROM $table ORDER BY $sort $sort_by limit $eu, $limit";
$result=mysql_query($query);
//$nume=$result;
}
$count = (isset($eu) && $eu > 0) ? $eu+1 : 1;
//////////////// Now we will display the returned records in side the rows of the table/////////
while($rows = mysql_fetch_array($result)){
echo "<tr >";
echo "<td> <font face='Verdana' size='2'>";
echo $count++;
echo "</font></td>";
echo "<td>$rows[firstname]</td>";
echo "<td>$rows[lastname]</td>";
echo "<td>$rows[age]</td>";
echo "</tr>";
}
echo "</table>";
////////////////////////////// End of displaying the table with records ////////////////////////
///////////////////////////////
if($nume > $limit ){ // Let us display bottom links if sufficient records are there for paging
/////////////// Start the bottom links with Prev and next link with page numbers /////////////////
echo "<table align = 'left' width='50%'><tr><td align='left' width='30%'>";
//// if our variable $back is equal to 0 or more then only we will display the link to move back ////////
if($back >=0){
$_GET['start'] = $back; // only modify the 'start' value, all others left as is
$q = http_build_query($_GET,'','&');
print "<a href='?$q'><font face='Verdana' size='2'>PREV</font></a>";
}
//////////////// Let us display the page links at center. We will not display the current page as a link ///////////
echo "</td><td align=left width='30%'>";
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$limit){
if($i <> $eu){
$_GET['start'] = $i; // only modify the 'start' value, all others left as is
$q = http_build_query($_GET,'','&');
echo " <a href='?$q'><font face='Verdana' size='2'>$l</font></a> ";
} else {
echo "<font face='Verdana' size='4' color=red>$l</font>";} /// Current page is not displayed as link and given font color red
$l=$l+1;
}
echo "</td><td align='right' width='30%'>";
///////////// If we are not in the last page then Next link will be displayed. Here we check that /////
if($this1 < $nume){
$_GET['start'] = $next; // only modify the 'start' value, all others left as is
$q = http_build_query($_GET,'','&');
print "<a href='?$q'><font face='Verdana' size='2'>NEXT</font></a>";
}
echo "</td></tr></table>";
}// end of if checking sufficient records are there to display bottom navigational link.
include "menu.php";
?>
Help with pagination and Table sorting
Reply With Quote
Bookmarks