Pagination not working correctly

Mr Chris · November 23, 2006

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!):

[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="../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>

[/code]

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:

[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="../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>
[/code]

This however:

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

Can anyone please help?

Thanks

Chris

joshi_v · November 24, 2006

Well! For pagination you have to use 'LIMIT' clause.
First execute the main query and count number of rows in it.after that execute the same query with LIMIT clause
for this you have to pass two variables.one is the starting point of the result and second one is number of rows is should fetch from the result.
example : LIMIT 0,20.then it will fetch 0,20 rows

Next when you are going to next page you should increment these values.
Let us suppose maximum rows you want to display is '25' rows per page
So when you came to the first paeg these values should be
0,25
second time , it should be 26,50;
You can do this by calculating with page numbers like

$showrows=25;
if ($currrow=='')
{
$currrow=0;
}
if ($page!='' && $page>=1)
{
$currrow=($page-1) * $showrows;
}
else
{
$page=1;
}

then limit clause will be

LIMIT $currrow, $showrows

Hope i haven't much confuse you!

Sign In

Pagination not working correctly

Recommended Posts

Mr Chris

Link to comment

Share on other sites

joshi_v

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information