PHP Pagination problem with MySQL query result

PHP
1

As a newbie to php and mysql but an old foxpro programmer I am having problems with getting the pagination to work with mysql query result. Depending on code changes I get all of the records (50) in my test table when I am actually trying to get just (15) records that the query should return.

I am attaching the php code that responds to an html form page search that is captured with the GET code.

Any help would be greatly appreciated.

DickM

===============
<html>
<body>

<?php
//Make the connection.

$conn = mysql_connect ('localhost', 'username', 'password'); //OR die ('Count not connect to MySQL: '. mysql_error() );

// Select the database.

if(!$conn) die("Failed to connect to database!");
$status = mysql_select_db('capweb', $conn);
if(!$status) die("Failed to select database!");

//get data passed from history_search.html form as entered by user

$search=$_GET["search_string"]; // Get the value from the html form

// how many rows to show per page
$rowsPerPage = 5;

// by default we show first page
$pageNum = 1;

// if $_GET[‘page’] defined, use it as page number
if(isset($_GET[‘page’]))
{
$pageNum = $_GET[‘page’];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

//Get query

$result = mysql_query(“SELECT cusid,cname,mycustomer,jobsite1,estimate FROM plnsdec WHERE cname LIKE ‘$search%’ ORDER BY cname”) or die(mysql_error());
$num_rows = mysql_num_rows($result);
$row = mysql_fetch_array($result);

// print the data found

echo "&lt;table border='1'&gt;
&lt;tr&gt;
&lt;th&gt;Customer ID&lt;/th&gt;
&lt;th&gt;Company&lt;/th&gt;
&lt;th&gt;My Customer&lt;/th&gt;
&lt;th&gt;Jobsite&lt;/th&gt;
&lt;th&gt;Estimate&lt;/th&gt;
&lt;/tr&gt;";
while($row = mysql_fetch_assoc($result)) {
	echo "&lt;tr&gt;";
	echo "&lt;td&gt;" . $row['cusid'] . "&lt;/td&gt;";
	echo "&lt;td&gt;" . $row['cname'] . "&lt;/td&gt;";
	echo "&lt;td&gt;" . $row['mycustomer'] . "&lt;/td&gt;";
	echo "&lt;td&gt;" . $row['jobsite1'] . "&lt;/td&gt;";
	echo "&lt;td&gt;" . $row['estimate'] . "&lt;/td&gt;";
	echo "&lt;/tr&gt;";
}
echo "&lt;/table&gt;";

// how many pages we have when using paging?
$maxPage = ceil($num_rows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER[‘PHP_SELF’];

// creating previous and next link
// plus the link to go straight to
// the first and last page

if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\“$self?page=$page\”>[Prev]</a> ";

$first = " <a href=\“$self?page=1\”>[First Page]</a> ";
}
else
{
$prev = ’ '; // we’re on page one, don’t print previous link
$first = ’ '; // nor the first page link
}

if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\“$self?page=$page\”>[Next]</a> ";

$last = " <a href=\“$self?page=$maxPage\”>[Last Page]</a> ";
}
else
{
$next = ’ '; // we’re on the last page, don’t print next link
$last = ’ '; // nor the last page link
}

// print the navigation link

echo $first . $prev . " Showing page $pageNum of $maxPage pages " . $next . $last;

?>

</body>
</html>

2

Welcome to SP :slight_smile:

Your query actually returns all rows that satisfy the WHERE conditions. If you want to limit the result to 15 rows max at a time, then you’ll have to add a LIMIT clause.

Oh, and please, next time you post some code, put the correct code tags around them.

3

The best way to limit the number of results is with the LIMIT clause.
See: http://dev.mysql.com/doc/refman/5.0/en/select.html

4

Thanks to both replies and sorry about not having the correct tags. As I said I am new at this posting.

However, my initial query should limit the results because there are only 15 records that meet the where condition, and the first page result that is print out contains 14 records, so I thought something was wrong in the pagination.

dickm

5

What is happening about the originally reported 50 results that you were getting instead of 15?

6

Try this:


  $query = "
    SELECT 
        cusid
      , cname
      , mycustomer
      , jobsite1
      , estimate 
    FROM plnsdec 
    WHERE cname LIKE '" . mysql_real_escape_string($search) . "%'  
    ORDER BY cname
  ";
  echo "query: '$query'<br/>";
  $result = mysql_query($query) or die(mysql_error());

It’ll echo the query so you can check if all is ok.
And as you can see, I also pass the user input ($search) through mysql_real_escape_string, to prevent sql injection.

7

The query is returning all 50 records instead of the 15 that match the WHERE condition, and the pagination is displaying 14 records per page instead of the 10 as stated in the control variable.

Thanks for any assistance

8

thanks guido2004,
I tried your code and the first page echo returned the correct full query…
query: ’ SELECT cusid ,cname ,mycustomer ,jobsite1 ,estimate FROM plnsdec WHERE cname LIKE ‘cemex%’ ORDER BY cname ’

But it displayed 14 records, not the 10 called for by Pagination, then the next page result echo query was:
query: ’ SELECT cusid ,cname ,mycustomer ,jobsite1 ,estimate FROM plnsdec WHERE cname LIKE ‘%’ ORDER BY cname ’
which is missing the variable search value

And it is repeating the first page resuts and display all the rest of the records in the table.

I am very confused.

Thank you for your help

9

It looses the search criteria, because you don’t send it to the next page (in the query string, or using sessions). Without the search criteria, your query extracts all rows.

The first page extracts 14 rows because there are 14 rows in the table that satisfy the search criteria cname LIKE ‘cemex%’.
If you want to extract only 10 rows at a page, you’ll have to add a LIMIT clause to your query.