Trying to get to the bottom of a paging problem

[slaterino]

Hi,

I've been working on a query that allows for a number of GET queries from links and a search function. I have almost got the query working perfectly except for the $maxPage field, which forms part of the paging function. For some reason this is defaulting to 1 and I can't work out why. I have included the code below. The last line echoes the $maxPage field. For some reason this always shows as 1, so for example when the page is catalogue.php?page=1 the last line echoes 'Showing Page 1 of 1'. Then also if the page is catalogue.php?page=22 the line would echo 'Showing Page 22 of 1 Pages.' Can anyone spot what the error might be?

 

$var = @$_GET['qsearch'] ;
$trimmed = trim($var); //trim whitespace from the stored variable

$rowsPerPage = 7;
$pageNum = 1;

if(isset($_GET['page']))
{
   $pageNum = $_GET['page'];
}

$offset = ($pageNum - 1) * $rowsPerPage;

$self = $_SERVER['PHP_SELF'];

$catalogue_query  = "Product_ID, Genus, Division FROM products";
if (isset($_GET['qsearch'])) {
$catalogue_query .=" WHERE Common_Name OR Genus like \"%$trimmed%\"";
}
else {
if (isset($_GET['Genus'])) {
$catalogue_query .=" WHERE Genus = '" .mysql_real_escape_string($_GET['Genus']). "'";
}
if (isset($_GET['Division'])) {
$catalogue_query .=" AND Division = '" .mysql_real_escape_string($_GET['Division']). "'";
}
}
$result = mysql_query("SELECT COUNT(Product_ID) AS numrows, $catalogue_query GROUP BY Product_ID") or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
$maxPage = ceil($numrows/$rowsPerPage);

$catalogue_query .= " ORDER BY Product_ID DESC LIMIT $offset, $rowsPerPage";
$result = mysql_query("SELECT ".$catalogue_query) or die('Error, query failed');

echo " Showing page $pageNum of $maxPage pages ";

while($row = mysql_fetch_array($result, MYSQL_ASSOC))

 

Thanks!

Russ

[rubing]

Why don't you try debugging it as follows:

 

echo $numrows;
echo $rowsPerPage;
$maxPage = ceil($numrows/$rowsPerPage);

 

Now you can figure out which of those values is being set incorrectly!

[Psycho]

$maxPage is only set once here

$maxPage = ceil($numrows/$rowsPerPage);

 

So it is based upon that evaluated value. $rowsPerPage is hard-coded at the top of the page to a value of 7 and is not changed. So, the problem is obviously with $numrows. So, let's look at how it is set.

 

$result = mysql_query("SELECT COUNT(Product_ID) AS numrows, $catalogue_query GROUP BY Product_ID")
     or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

I am sure that $numrows is between 1 and 7 - which would result in $maxPage set to 1. But, the query doesn't make sense to me for a pagination script. If you have many proudct IDs in that table and some are duplicated then that query would return a result set with a count for each group of IDs. I *think* what you really want is just the number of records - not the counts for the grouped records (since your diplay query is not grouping records). Your count query should be identical to the one you run to get the records without the LIMIT.

 

Try this:

$result = mysql_query("SELECT $catalogue_query")
      or die('Error, query failed');
$numrows = mysql_num_rows($result);

 

EDIT: I changed the above after a second look at your code. It looks as if you should be counting ALL the records, not just the unique IDs

[slaterino]

Ah yeah!

 

Works perfectly, cheers for that!