SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pagination with search results

    Hi Guys,

    On my search page I built yesterday I got it working fine, and today i'm trying to add pagination to it (which is quite hard to get your head round!):

    PHP Code:
    <html>
    <head>
    <title>Search Results ...</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <link rel="stylesheet" href="../search/ssheet.css">
    </head>

    <body>
    <p>Search Results</p><?php

    // Start to get the data from the form and trim any whitespace
            
            
    if($_SERVER["REQUEST_METHOD"]=='POST')
            {
                
    $business_type trim($_POST['business_type']);
                
    $town trim($_POST['town']);
                
    $company_name trim($_POST['company_name']);
            }
               else
            {
                
    $business_type trim($_GET['business_type']);
                
    $town trim($_GET['town']);
                
    $company_name trim($_GET['company_name']);
            }

    // End getting the data from the form and trimming any whitespace
            
            
    // Start to build the query and order the listings by the company name
            
            
    $search_query "select * from directory_listings where ";

            if(
    $business_type == ''//Nothing entered
            
    {
                
    $search_query .= " business_type LIKE '%'";
            }
            else
            {
                
    $search_query .= " business_type = '$business_type'";
            }
            if(!empty(
    $town))
            {
                
    $search_query .= " AND town LIKE '%$town%'";
            }
            if(!empty(
    $company_name))
            {
                
    $search_query .= " AND company_name = '$company_name'";
            }
            
            
    $search_query .= " order by company_name asc";

    // End building the query and order the listings by the company name
            
            
            
    // Start the connection to the database
            
            
    include('*****************');
            
    $can_i_connect db_connect(); // by db_connect function is in my include file
            
    if(!$can_i_connect)
            {
                echo 
    "Could not connect to database";
            }

    // End the connection to the database

    // Start to find how many search results are being found for the query

            
    $search_results mysql_query($search_query$can_i_connect);
            
    $result mysql_query($search_query) or die (mysql_error()); 

            
    $number_of_results mysql_num_rows($search_results);
            
            if(
    $number_of_results <= 0)
            {
                echo 
    "Sorry, there were no results for your search in the Fife & Kinross Online Edition.";
            }
            else
            {    
                echo 
    "<b>Your search returned ".$number_of_results." result(s).</b> <br /><br />Here are those results, listed in ascendng order. <br /><br />";
            }
    // End to find how many search results are being found for the query

    ?>

    <?while ($obj=mysql_fetch_object($search_results)){?> 
    <table width='100%'> 
      <tr> 
        <td height="26"><?echo $obj->business_name?></td><td height="26"><?echo $obj->business_type?></td></tr> 
      <tr> 
        <td height="28"><?echo $obj->town?></td><td height="28"><a href="full_details.php?business_id=<?echo $obj->business_id?>"><img src="../images/more.jpg" width="80" height="19" alt="More..."></a></td></tr> 
    </table><?}?> 
    </body>
    </html>
    However I want to add pagination to this search results page, so Iíve put together this page to add pagination with my search results:

    PHP Code:
    <html>
    <head>
    <title>Search Results ...</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <link rel="stylesheet" href="../search/ssheet.css">
    </head>

    <body>
    <p>Search Results</p>
    <?php

    // Start to get the data from the form and trim any whitespace
            
            
    if($_SERVER["REQUEST_METHOD"]=='POST')
            {
                
    $business_type trim($_POST['business_type']);
                
    $town trim($_POST['town']);
                
    $company_name trim($_POST['company_name']);
            }
               else
            {
                
    $business_type trim($_GET['business_type']);
                
    $town trim($_GET['town']);
                
    $company_name trim($_GET['company_name']);
            }

    // End getting the data from the form and trimming any whitespace
            
            
    // Start to build the query and order the listings by the company name
            
            
    $search_query "select * from directory_listings where ";

            if(
    $business_type == ''//Nothing entered
            
    {
                
    $search_query .= " business_type LIKE '%'";
            }
            else
            {
                
    $search_query .= " business_type = '$business_type'";
            }
            if(!empty(
    $town))
            {
                
    $search_query .= " AND town LIKE '%$town%'";
            }
            if(!empty(
    $company_name))
            {
                
    $search_query .= " AND company_name = '$company_name'";
            }
            
            
    $search_query .= " order by company_name asc";

    // End building the query and order the listings by the company name
            
            
            
    // Start the connection to the database
            
            
    include('**************************');
            
    $can_i_connect db_connect(); // by db_connect function is in my include file
            
    if(!$can_i_connect)
            {
                echo 
    "Could not connect to database";
            }

    // End the connection to the database

    // Start to find how many search results are being found for the query

            
    $search_results mysql_query($search_query$can_i_connect);
            
    $result mysql_query($search_query) or die (mysql_error()); 

            
    $number_of_results mysql_num_rows($search_results);
            
            if(
    $number_of_results <= 0)
            {
                echo 
    "Sorry, there were no results for your search in the Fife & Kinross Online Edition.";
            }
            else
            {    
                echo 
    "<b>Your search returned ".$number_of_results." result(s).</b> <br /><br />Here are those results, listed in ascendng order. <br /><br />";
            }
    // End to find how many search results are being found for the query

    // Start the pagination

        
    $limit  25;                 
        
    $query_count "SELECT * FROM directory_listings";      
        
    $result_count mysql_query($query_count);      
        
    $totalrows mysql_num_rows($result_count);   
       
        
    $PHP_SELF $_SERVER['PHP_SELF']; 
          
        if(!isset(
    $_GET['page'])){ 

            
    $page 1

        } else{ 
             
            
    $page $_GET['page']; 
        } 

        
    $limitvalue $page $limit - ($limit);   
        
    // Ex: (page2 * 5(items per page) = 10) - 5 = 5 <- data starts at 5 
          
        
    if($page != 1){   
            
    $pageprev $page 1;  
             
              
            echo(
    "<a href=\"$PHP_SELF?page=$pageprev\">PREV</a> ");   
        }else{ 
            echo(
    "PREV"); 
        } 

        
    $numofpages $totalrows $limit;   
        
         
        
    #echo "<br>", $totalrows; 
       #exit; 
          
        
    for($i 1$i <= $numofpages$i++){ 
            
            
    than $numofpages. */ 
            if(
    $i == $page){ 
                echo(
    $i." "); 
            }else{ 
                echo(
    "<a href=\"$PHP_SELF?page=$i\">$i</a> "); 
            } 

        if((
    $totalrows $limit) != 0){ 
            if(
    $i == $page){ 
                echo(
    $i." "); 
            }else{ 
                echo(
    "<a href=\"$PHP_SELF?page=$i\">$i</a> "); 
            } 
        } 

        if((
    $totalrows - ($limit $page)) > 0){ 
            
    $pagenext $page 1
               
            echo(
    "<a href=\"$PHP_SELF?page=$pagenext\">NEXT</a>");   
        }else{ 
            echo(
    "NEXT");   
        } 
          
        
    mysql_free_result($result); 

    // End Pagination

    ?>

    <?while ($obj=mysql_fetch_object($search_results)){?> 
    <table width='100%'> 
      <tr> 
        <td height="26"><?echo $obj->business_name?></td><td height="26"><?echo $obj->business_type?></td></tr> 
      <tr> 
        <td height="28"><?echo $obj->town?></td><td height="28"><a href="full_details.php?business_id=<?echo $obj->business_id?>"><img src="../images/more.jpg" width="80" height="19" alt="More..."></a></td></tr> 
    </table><?}?> 
    </body>
    </html>
    This however:

    - Returns the correct result(s) I searched for
    - Then on the bottom has a pagination BUT when I click on the next page of the pagination it then displays ALL the results in my database.

    Can anyone please help?

    Thanks

    Chris

  2. #2
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    Belgian in Mexico
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should use LIMIT in your SQL query, to return the correct records. Something like:
    PHP Code:
    $limit 25;
    $offset = (isset($_GET['page']) and is_int($_GET['page'])) ? $_GET['page']) - 0;
    $sql "SELECT * FROM directory_listings LIMIT $offset$limit"
    Hope that helps,

    MichaŽl
    MichaŽl Niessen
    http://assemblysys.com
    (Countries/states/cities with latitude & longitude,
    weathercodes & topical databases)

  3. #3
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, but that seems to disable the previous and next pagination all-together. Can anyone else help?

  4. #4
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    Belgian in Mexico
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 7724
    Can anyone else help?
    Do you mean you don't want me to try and resolve your problem anymore?...
    MichaŽl Niessen
    http://assemblysys.com
    (Countries/states/cities with latitude & longitude,
    weathercodes & topical databases)

  5. #5
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By all means,

    If you could take a fiddle with my code (ooh err mrs!) i'd me most greatful!

  6. #6
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    Belgian in Mexico
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 7724
    Thanks, but that seems to disable the previous and next pagination all-together.
    You used the LIMIT on you're first query, right? (the one that actually returns the results, with the where conditions (I know... I was a little lazy and just wrote the minimum code to show you how to use it)).

    If you used it on your second query, it's normal that it break the next/prev page, since it would return only enough records for one page. btw, you shouldn't use a full select just to count the number of rows. Simply use SELECT COUNT(*) (and include the WHERE clause as well, or you risk having a number of pages that doesn't necessarily match the number of actual, filtered, records).

    Regards,

    MichaŽl
    MichaŽl Niessen
    http://assemblysys.com
    (Countries/states/cities with latitude & longitude,
    weathercodes & topical databases)

  7. #7
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Michael,

    Did not use it on my first query as that's for extracting the data, not finding out how many records there are. Kindly used your code on the second query as below:

    PHP Code:
    <html>
    <head>
    <title>Search Results ...</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <link rel="stylesheet" href="../search/ssheet.css">
    </head>

    <body>
    <p>Search Results</p>
    <?php

    // Start to get the data from the form and trim any whitespace
            
            
    if($_SERVER["REQUEST_METHOD"]=='POST')
            {
                
    $business_type trim($_POST['business_type']);
                
    $town trim($_POST['town']);
                
    $company_name trim($_POST['company_name']);
            }
               else
            {
                
    $business_type trim($_GET['business_type']);
                
    $town trim($_GET['town']);
                
    $company_name trim($_GET['company_name']);
            }

    // End getting the data from the form and trimming any whitespace
            
            
    // Start to build the query and order the listings by the company name
            
            
    $search_query "select * from directory_listings where ";

            if(
    $business_type == ''//Nothing entered
            
    {
                
    $search_query .= " business_type LIKE '%'";
            }
            else
            {
                
    $search_query .= " business_type = '$business_type'";
            }
            if(!empty(
    $town))
            {
                
    $search_query .= " AND town LIKE '%$town%'";
            }
            if(!empty(
    $company_name))
            {
                
    $search_query .= " AND company_name = '$company_name'";
            }
            
            
    $search_query .= " order by company_name asc";

    // End building the query and order the listings by the company name
            
            
            
    // Start the connection to the database
            
            
    include('**************');
            
    $can_i_connect db_connect(); // by db_connect function is in my include file
            
    if(!$can_i_connect)
            {
                echo 
    "Could not connect to database";
            }

    // End the connection to the database

    // Start to find how many search results are being found for the query

            
    $search_results mysql_query($search_query$can_i_connect);
            
    $result mysql_query($search_query) or die (mysql_error()); 

            
    $number_of_results mysql_num_rows($search_results);
            
            if(
    $number_of_results <= 0)
            {
                echo 
    "Sorry, there were no results for your search in the Fife & Kinross Online Edition.";
            }
            else
            {    
                echo 
    "<b>Your search returned ".$number_of_results." result(s).</b> <br /><br />Here are those results, listed in ascendng order. <br /><br />";
            }
    // End to find how many search results are being found for the query

    // Start the pagination

        
    $limit  25;                 
        
    $offset = (isset($_GET['page']) and is_int($_GET['page'])) ? ($_GET['page']) - 0;
        
    $query_count "SELECT * FROM directory_listings LIMIT $offset$limit"
      
        
    $result_count mysql_query($query_count);      
        
    $totalrows mysql_num_rows($result_count);   
       
        
    $PHP_SELF $_SERVER['PHP_SELF']; 
          
        if(!isset(
    $_GET['page'])){ // Checks if the $page variable is empty (not set) 
            
    $page 1;     // If it is empty, we're on page 1 
        
    } else{ 
             
            
    $page $_GET['page']; 
        } 

        
    $limitvalue $page $limit - ($limit);   
        
    // Ex: (page2 * 5(items per page) = 10) - 5 = 5 <- data starts at 5 
          
        
    if($page != 1){   
            
    $pageprev $page 1//decrementing $page-- does not work on all php configurations. 
             
              
            
    echo("<a href=\"$PHP_SELF?page=$pageprev\">PREV</a> ");   
        }else{ 
            echo(
    "PREV"); 
        } 

        
    $numofpages $totalrows $limit;   
        
         
        
    #echo "<br>", $totalrows; 
       #exit; 
          
        
    for($i 1$i <= $numofpages$i++){ 
            
    /* This for loop will add 1 to $i at the end of each pass until $i is greater   
            than $numofpages. */ 
            
    if($i == $page){ 
                echo(
    $i." "); 
            }else{ 
                echo(
    "<a href=\"$PHP_SELF?page=$i\">$i</a> "); 
            } 
        } 
    //code above has been decoded lozza 


        
    if(($totalrows $limit) != 0){ 
            if(
    $i == $page){ 
                echo(
    $i." "); 
            }else{ 
                echo(
    "<a href=\"$PHP_SELF?page=$i\">$i</a> "); 
            } 
        } 

        if((
    $totalrows - ($limit $page)) > 0){ 
            
    $pagenext $page 1//incrementin $page like $page++ does not work in all enviroments 
               
            
    echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT</a>");   
        }else{ 
            echo(
    "NEXT");   
        } 
          
        
    mysql_free_result($result); 

    ?>

    <?while ($obj=mysql_fetch_object($search_results)){?> 
    <table width='100%'> 
      <tr> 
        <td height="26"><?echo $obj->business_name?></td><td height="26"><?echo $obj->business_type?></td></tr> 
      <tr> 
        <td height="28"><?echo $obj->town?></td><td height="28"><a href="full_details.php?business_id=<?echo $obj->business_id?>"><img src="../images/more.jpg" width="80" height="19" alt="More..."></a></td></tr> 
    </table><?}?> 
    </body>
    </html>

  8. #8
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    Belgian in Mexico
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have to go to work now, so I won't be able to check the full code in depth for a few hours, but the logic should be something like:
    PHP Code:
    // display the records of the current page
    $limit 25
    $offset = (isset($_GET['page']) and is_int($_GET['page'])) ? ($_GET['page']) - 0
    $sql "SELECT * FROM table
        WHERE ...
        LIMIT 
    $offset$limit"
    $result mysql_query($sql);
     
    // count the total number of records
    $sql "SELECT COUNT(*) as records_number FROM table
    WHERE ..."
    ;
    $result mysql_query($sql);
    $row mysql_fetch_assoc($result);
    $number_of_records $row['records_number']; // use this with $limit and the page number to build your page navigation 
    Hope it will allow you to make your code work.

    MichaŽl
    MichaŽl Niessen
    http://assemblysys.com
    (Countries/states/cities with latitude & longitude,
    weathercodes & topical databases)

  9. #9
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers Michael,

    Do appreciate your help, but the above has totally lost me now with regards to pagination?

    Do I need three queries now then?

    Also my first query is part of a posted form with if statements and the second query you posted included a where clause, when I don't need a where clause as the data is being processed via a form?

    Many Thanks for trying to help though (enjoy work!) - it's probably just me being thick!!

    Chris

  10. #10
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need two queries; one to count the total results and the other to fetch the results to display on the page requested.


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
  •