Advanced Search

Results 1 to 8 of 8

Thread: Help with pagination and Table sorting

  1. #1
    Join Date
    Sep 2009
    Posts
    45
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Question Help with pagination and Table sorting

    Hi,

    I have this php sort code and its working but how do i make it so when we sort it we could switch views while only using one link

    eg : click type it sorts by type ASC, clicked the same link again it sort by type DESC,


    PHP Code:

    <table>
        <tr>
            <th>
                <a href="?orderBy=type">Type:</a>
            </th>
            <th>
                <a href="?orderBy=description">Description:</a>
            </th>
            <th>
                <a href="?orderBy=recorded_date">Recorded Date:</a>
            </th>
            <th>
                <a href="?orderBy=added_date">Added Date:</a>
            </th>
        </tr>
    </table>
    <?php
    $orderBy 
    = array('type''description''recorded_date''added_date');

    $order 'type';
    if (isset(
    $_GET['orderBy']) && in_array($_GET['orderBy'], $orderBy)) {
        
    $order $_GET['orderBy'];
    }

    $query 'SELECT * FROM aTable ORDER BY '.$order;

    // retrieve and show the data :)
    ?>
    Last edited by mulaus; 03-23-2013 at 11:52 AM. Reason: title edit - to add pagination

  2. #2
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,154
    Thanks
    260
    Thanked 690 Times in 678 Posts

    Default

    You have two options:

    1. Create a single, static link that sends a value of "change order" to the server; then store the current order on the server (including a default value), perhaps in a session variable.

    2. Create a dynamic link that inverts as you display one or the other; so when it is DESC, display an ASC link; when it's ASC, display a DESC link (basically the same logic you have now, but for the link, and inverted).
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  3. #3
    Join Date
    Sep 2009
    Posts
    45
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Default

    TQ

    Found this piece of code. Problem solved


    PHP Code:
    <?php  
    $con 
    mysql_connect("localhost","peter","abc123");  
    if (!
    $con)  
      {  
      die(
    'Could not connect: ' mysql_error());  
      }  
        
    mysql_select_db("my_db"$con);  

        
    $sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'FirstName';  
          
        
    $sort_order 'asc';  
        if(isset(
    $_GET['sort_by']))  
        {  
            if(
    $_GET['sort_by'] == 'asc')  
            {  
                
    $sort_order 'desc';  
            } else  
            {  
                
    $sort_order 'asc';  
            }  
        }  
          
        switch(
    $sort)  
        {  
            case 
    'FirstName':  
                
    $order_by 'FirstName';  
                break;  
            case 
    'LastName':  
                
    $order_by 'LastName';  
                break;  
            case 
    'Age':  
                
    $order_by 'Age';  
                break;  
         }  

    $sql  "SELECT * FROM persons ORDER BY $sort $sort_order";  
    $result mysql_query($sql) or die("MySQL error: $sql<br />" mysql_error());  

    echo 
    '<table border="1">  
    <tr>  
    <th><a href="?sort=FirstName&sort_by='
    .$sort_order.'">Firstname</th>  
    <th><a href="?sort=LastName&sort_by='
    .$sort_order.'">Lastname</th>  
    <th><a href="?sort=Age&sort_by='
    .$sort_order.'">Age</th>  
    </tr>'
    ;  

    while(
    $row mysql_fetch_array($result))  
      {  
      echo 
    "<tr>";  
      echo 
    "<td>" $row['FirstName'] . "</td>";  
      echo 
    "<td>" $row['LastName'] . "</td>";  
      echo 
    "<td>" $row['Age'] . "</td>";  
      echo 
    "</tr>";  
      }  
    echo 
    "</table>";  

    mysql_close($con);  
    ?>

  4. #4
    Join Date
    Sep 2009
    Posts
    45
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Default

    i have added pagination

    PHP 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">
    <?php
    require "connect.php";           // All database details will be included here 

    $page_name="paging.php"//  If you use this code with a different page ( or file ) name then change this 
    $start=$_GET['start'];
    if(
    strlen($start) > and !is_numeric($start)){
    echo 
    "Data Error";
    exit;
    }


    $eu = ($start 0); 
    $limit 3;                                 // No of records to be shown per page.
    $this1 $eu $limit
    $back $eu $limit
    $next $eu $limit


    $sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'firstname';  

        
    $sort_order 'asc';  
        if(isset(
    $_GET['sort_by']))  
        {  
            if(
    $_GET['sort_by'] == 'asc')  
            {  
                
    $sort_order 'desc';  
            } else  
            {  
                
    $sort_order 'asc';  
            }  
        }  

         
        switch(
    $sort)  
        {  
    case 
    'firstname':  
    $order_by 'firstname';  
    break;  
    case 
    'lastname':  
    $order_by 'lastname';  
    break;  
    case 
    'age':  
    $order_by 'age';  
    break;  

         }  

    /////////////// WE have to find out the number of records in our table. We will use this to break the pages///////
    $query2=" SELECT * FROM person  ";
    $result2=mysql_query($query2);
    echo 
    mysql_error();
    $nume=mysql_num_rows($result2);
    /////// 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>";
    echo 
    "<th><a href='?sort=firstname&sort_by=$sort_order'>First Name</a></th>";
    echo 
    "<th><a href='?sort=lastname&sort_by=$sort_order'>Last Name</a></th>";
    echo 
    "<th><a href='?sort=age&sort_by=$sort_order'>Age</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 person ORDER BY $sort $sort_order limit $eu$limit ";
    $result=mysql_query($query);
    echo 
    mysql_error();
    $count = (isset($eu) && $eu 0) ? $eu+1;
    //////////////// Now we will display the returned records in side the rows of the table/////////
    while($rows mysql_fetch_array($result))
    {
    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) { 
    print 
    "<a href='$page_name?start=$back'><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){
    echo 
    " <a href='$page_name?start=$i'><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) { 
    print 
    "<a href='$page_name?start=$next'><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";
    ?>
    </body>

    </html>
    pagination and sorting asc/desc works but with 1 problem

    when using sorting it works but when we click another page or next page sorting backs to default sorting by firstname

    How do i solve this

    sorry for newbie code...just testing this in localhost

    thanks

  5. #5
    Join Date
    Sep 2009
    Posts
    45
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Default

    thanks to Mab of hsf

    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">
    <?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";
    $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";
    ?>

  6. #6
    Join Date
    Sep 2009
    Posts
    45
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Default

    Hi its me again..

    i want to further develop this to add some simple search

    i have added search by firstname, lastname, age

    so far i have did this..

    PHP 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"/><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']);  

    //run the query for searching
    $resultmysql_query("SELECT * FROM $table where firstname like '%" .  $_POST['search']  .  "%' or lastname like '%" .  $_POST['search']  .  "%' or age like '%" .  $_POST['search']  .  "%' ORDER BY firstname");

     
    $search_result=mysql_num_rows($result);
    if (
    $_POST['search'] =='')
    {
    echo 
    "Please input Keyword";

    }
    else
    {
    echo 
    "$search_result";
    echo 
    " record of <b>" $_POST['search'] . "</b> found";

     } 
     }



     

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

    if (isset($_POST['search']))  {
    $_POST['search'] = mysql_real_escape_string($_POST['search']);  

    //run the query for searching
    $resultmysql_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");
    }
    else {
    $query="SELECT * FROM $table ORDER BY $sort $sort_by limit $eu$limit";

    $result=mysql_query($query);
    }

    //echo $query, mysql_error();
    $count = (isset($eu) && $eu 0) ? $eu+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";
    ?>
    im stuck with how search result to follow pagination/sorting rules
    right now
    paging is still displaying even i have only 1 search result and sorting not working with search result
    Last edited by mulaus; 03-24-2013 at 05:37 AM.

  7. #7
    Join Date
    Sep 2009
    Posts
    45
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Default

    ....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>&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";
    ?>

  8. #8
    Join Date
    Sep 2009
    Posts
    45
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Default

    still 1 problem left : when searching, clicking 2nd Page or Next returns the table back to default view ..can any php master ot there correct my code ?


    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 = 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";
    ?>

Similar Threads

  1. sorting xml from more than one other site
    By g_force in forum Other
    Replies: 0
    Last Post: 08-04-2010, 04:58 AM
  2. Resolved Sorting by table
    By james438 in forum MySQL and other databases
    Replies: 1
    Last Post: 05-02-2010, 08:00 PM
  3. sorting arrays
    By james438 in forum PHP
    Replies: 4
    Last Post: 02-29-2008, 07:56 AM
  4. sorting rows
    By boogyman in forum PHP
    Replies: 0
    Last Post: 08-27-2007, 08:44 PM
  5. Sorting My Database
    By tomyknoker in forum PHP
    Replies: 7
    Last Post: 03-14-2007, 03:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •