Jump to content

Results script with Pagination help please!

Mr Chris

By Mr Chris
in PHP Coding Help

Recommended Posts

Mr Chris Advanced Member

Mr Chris

Posted
Posted
Hi Guys,

Have built a results for a search script which works with pagination:

[code=php:0]
<html>
<head>
<title>Search Results ...</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="ssheet.css">
</head>

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

// 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 pagination script and state amount of records per page

    $limit  = 5;               
    $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); 

// End pagination script and state amount of records per page
     

// 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 .= "LIMIT $limitvalue, $limit";

// End building the query and order the listings by the company name
       

// 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 and Start to find how many pagination pages I have
        else
        {
        echo "<b>Your search returned ".$number_of_results." result(s).</b> <br /><br />Here are those results, listed in ascendng order. <br /><br />";

if($page != 1){ 
        $pageprev = $page - 1;
       
         
        echo("<a href=\"$PHP_SELF?page=$pageprev\">PREV</a> "); 
    }else{
        echo("PREV");
    }
 
    $numofpages = $number_of_results/ $limit;
   
    #echo "<br>", $totalrows;
  #exit;
     
    for($i = 1; $i <= $numofpages; $i++){
        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"); 
    }

        }  // End of how many results I have found
     
    mysql_free_result($result);


// End of Else and to find how many pagination pages I have
                                               

?>

<?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>
[/code]

Now says the page returns the results found - for example [b]1 result found[/b],

But then my pagination decides to show the rest of the entries in the database when you click on page 2 then 3 then 4 etc...

But as there is only [b]1[/b] result found there should not be page 2 / 3 /4 etc...

Can anyone please help – as this is driving me absolutely bananas – I’ve been trying to solve this all day!!!?

Please also try it yourself to see what I mean:

http://www.slougheaz.org/directory/search/search.php

and the first box type in

[b]Tax Consultants[/b], and hit submit  - only one result should be returned.

Thanks

Chris
printf Newbie

printf

Posted
Posted
You have to base your paging on results found matching what you are searching for, not by how many results are in the database. So you need to move the paging code lower than your search option building...

So, delete this...

[code]// Start pagination script and state amount of records per page

    $limit  = 5;               
    $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); 

// End pagination script and state amount of records per page
[/code]


then, change this...

[code]// 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 .= "LIMIT $limitvalue, $limit";

// End building the query and order the listings by the company name


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


to this...

[code]// Start to build the query and order the listings by the company name

$add = '';

$search_query = "select * from directory_listings where";

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

// End building the query and order the listings by the company name


// Start pagination script and state amount of records per page

$limit = 5;
$query_count = mysql_query ( "SELECT COUNT(*) AS total FROM directory_listings WHERE " . $add );
$result_count = mysql_fetch_assoc ( $query_count );
$totalrows = $result_count['total'];
$PHP_SELF = $_SERVER['PHP_SELF'];

if( ! isset ( $_GET['page'] ) )
{
$page = 1;
}
else
{
$page = $_GET['page'];
}

$limitvalue = $page * $limit - ($limit); 

// End pagination script and state amount of records per page


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

$search_query .= $add . " LIMIT " . $limitvalue . ", " . $limit;

[/code]


printf
Mr Chris Advanced Member

Mr Chris

Posted
  • Author
Posted
Cheers Print F,

That all works apart from:

This line:

[b]$search_query .= $add . " LIMIT " . $limitvalue . ", " . $limit;[/b]

It DOES return the right results and Limit's it to 25 on each page, But it has an effect on [b]number_of_results[/b]

It basically says I have [b]25 records in my database[/b] (which it does on the first page), but the results run over two pages and there are 31 results - not 25, (6 more when i click on the 2nd paginated page - 25+6=31)

So with some advice I cam accross FOUND_ROWS(), and thought this may work.  However this says in my db of 31 records;

[b]On Page 1[/b]
Your search returned 25 result(s).    //Wrong!

[b]On Page 1[/b]
Your search returned 31 result(s).    //Right!!!

Even more bizzare!

Any help please?

[code=php:0]

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

        $search_query .= $add . " LIMIT " . $limitvalue . ", " . $limit;
        $search_results = mysql_query($search_query, $can_i_connect);
        $result = mysql_query($search_query) or die (mysql_error());

// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT FOUND_ROWS()"), 0);
       
        if($total_results <= 0)
        {
        echo "Sorry, there were no results for your search in the Fife & Kinross Online Edition.";
        }

// Else and Start to find how many pagination pages I have
        else
        {
        echo "<b>Your search returned ".$total_results." result(s).</b> <br /><br />Here are those results, listed in ascendng order. <br /><br />";

if($page != 1){ 
        $pageprev = $page - 1;
       
         
        echo("<a href=\"$PHP_SELF?page=$pageprev\">PREV</a> "); 
    }else{
        echo("PREV");
    }
 
    $numofpages = $number_of_results/ $limit;
   
    #echo "<br>", $totalrows;
  #exit;
     
    for($i = 1; $i <= $numofpages; $i++){
        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"); 
    }

        }  // End of how many results I have found
     
    mysql_free_result($result);


// End of Else and to find how many pagination pages I have

[/code]

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.