Jump to content


Photo

Help with search, sorting and pagination

search pagination sorting

  • Please log in to reply
2 replies to this topic

#1 mulaus

mulaus

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 24 March 2013 - 10:51 AM

Hi Im new here..

 

Im a PHP noo-b i have this simple code with sorting and pagination with dummy data (firstname,lastname,age)

 

I would like to add some simple search to it..im not sure if im doing this right

 

currentlty the search is not working properly

 

for testing purpose, right now i limit 3 records per page

a. when i search record and search result is display..Pagination link is still display even i have only 1record search result

b.  if i have 4 records found, when i click the 2nd page, the search result is gone and table is back to default view

c. in table default view, clicking 2nd page and try to search record, records are not found because its looking under the 2nd page and not all of the table

 

sorry for my  English

 

 

 

<!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////

/////////// Now let us print the table headers ////////////////
$bgcolor="#f1f1f1";
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";

$query="SELECT * FROM $table where firstname like '%" .  $_POST['search']  .  "%' or lastname like '%" .  $_POST['search']  .  "%' or age like '%" .  $_POST['search']  .  "%'               ORDER BY $sort $sort_by limit $eu, $limit";

$result=mysql_query($query);
//echo $query, mysql_error();


$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)){
    // toggle bgcolor
    if($bgcolor=='#f1f1f1'){$bgcolor='#ffffff';}
    else{$bgcolor='#f1f1f1';}

    echo "<tr >";
    echo "<td align=left bgcolor=$bgcolor id='title'>&nbsp;<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 = 'center' 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,'','&amp;');
        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=center 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,'','&amp;');
            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,'','&amp;');
        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";
?>


Edited by mulaus, 24 March 2013 - 10:52 AM.


#2 mulaus

mulaus

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 25 March 2013 - 10:50 AM

 heres what i come up with so far
 
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..
<!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>&nbsp;<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>&nbsp;<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,'','&amp;');
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,'','&amp;');
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,'','&amp;');
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";
?>
 


#3 mulaus

mulaus

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 06 April 2013 - 11:19 PM


<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 = 5; // 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';
}


if (isset($_POST['search'])) {
$_POST['search'] = mysql_real_escape_string($_POST['search']);
$search = $_POST['search'];
if($search != ''){
    // form a simple LIKE '%search term%' comparison
    $where_clause = "where firstname like '%" . $_POST['search'] . "%' or lastname like '%" . $_POST['search'] . "%' or age like '%" . $_POST['search'] . "%'";
    }
}

/////////////// 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 $where_clause";
$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////

/////////// Now let us print the table headers ////////////////
$bgcolor="#f1f1f1";
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";

$query="SELECT * FROM $table $where_clause ORDER BY $sort $sort_by limit $eu, $limit";

$result=mysql_query($query);
//echo $query, mysql_error();

$count = (isset($eu) && $eu > 0) ? $eu+1 : 1;
if($search =!''){
echo "Totals of records Found: $nume";
}

//////////////// Now we will display the returned records in side the rows of the table/////////
while($rows = mysql_fetch_array($result)){
    // toggle bgcolor

    echo "<tr >";
    echo "<td>&nbsp;<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 = 'center' 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,'','&amp;');
        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=center 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,'','&amp;');
            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,'','&amp;');
        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";
?>

 

any php master outhere can help a newbie correct his code

 

when searching, clicking 2nd Page or Next the search criteria is not followed..

 

im still trying to figure this out


Edited by mulaus, 06 April 2013 - 11:20 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com