Help with paging function

[slaterino]

Hi, I currently use a paging function that works fine for breaking down all entries in a table. However, I sometimes use filters, and I am trying to find a way for these to work correctly with my paging function.

 

For example,

when looking at the entire table, my paging function will break all entries down to 29 pages and echoes Page 1 of 29 Pages. When I use a filter such as a link for catalogue.php?Genus=Tulipa the paging still shows as Page 1 of 29 pages even if there is only one entry that fits that criteria.

 

How can I alter my code so that the paging function takes into account any GET statements I am using. Should I just change the paging query so that it has the same WHERE statement as the catalogue query or is there a simpler way of doing this? This is the code I have so far:

 

$rowsPerPage = 6;

$pageNum = 1;

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

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

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

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

$self = $_SERVER['PHP_SELF'];

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

$catalogue_query  = "SELECT Product_ID, Genus, Division FROM products";
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']). "'";
}
$catalogue_query  .= " ORDER BY Product_ID DESC LIMIT $offset, $rowsPerPage";
$result = mysql_query($catalogue_query) or die('Error, query failed');

[MadTechie]

thats because you are doing 2 queries.. put them into one and it will be fine

 

I would really need to see a little more code but assume the fetch from the catalogue query goes to $row..

 

ie

<?php
$rowsPerPage = 6;

$pageNum = 1;

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

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

$self = $_SERVER['PHP_SELF'];

$catalogue_query  = "SELECT COUNT(Product_ID) AS numrows, Product_ID, Genus, Division FROM products";
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']). "'";
}
$catalogue_query  .= " ORDER BY Product_ID DESC LIMIT $offset, $rowsPerPage";
$result = mysql_query($catalogue_query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
$maxPage = ceil($numrows/$rowsPerPage);

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

[slaterino]

Hi,

Well I think I'm on the right track. My paging is now calculating correctly. However, each page is only showing one entry. I have had to change my previous code as previously for the catalogue query I was bringing up the results using:

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

while(list($Product_ID, $Genus, $Division)= mysql_fetch_array($result))
{

 

I have now changed the script as you suggested. This has also meant changing entries from $Product_ID to $row['Product_ID'] but this doesn't explain why only one page is now being shown instead of 6 on each page. Here is the current code:

 

$rowsPerPage = 6;

$pageNum = 1;

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

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

$self = $_SERVER['PHP_SELF'];

$catalogue_query  = "SELECT COUNT(Product_ID) AS numrows, Product_ID, Genus, Division FROM products";
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']). "'";
}
$catalogue_query .= " ORDER BY Product_ID DESC LIMIT $offset, $rowsPerPage";
$result = mysql_query($catalogue_query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
$maxPage = ceil($numrows/$rowsPerPage);

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

echo   

 

After the final 'echo' is a table which contains all the fields from the Products table and should be showing 6 entries on each page.

[MadTechie]

Its because of the LIMIT

Change

$catalogue_query .= " ORDER BY Product_ID DESC LIMIT $offset, $rowsPerPage";
$result = mysql_query($catalogue_query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
$maxPage = ceil($numrows/$rowsPerPage);

 

to

$result = mysql_query($catalogue_query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
$maxPage = ceil($numrows/$rowsPerPage);
//Count before setting the limit,
// then add the limit and search again
$catalogue_query .= " ORDER BY Product_ID DESC LIMIT $offset, $rowsPerPage";
$result = mysql_query($catalogue_query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);

[slaterino]

I've made the change. Frustratingly, it hasn't made any difference, still showing 1 row instead of 6. However, I think I have worked out the problem. I cut the query down so it was in the most basic form possible and it is still just showing 1 entry when in fact it should be showing all entries. This is the code I've stripped it down to:

 

$catalogue_query  = "SELECT Product_ID, Genus, Division FROM products";
$result = mysql_query($catalogue_query) or die('Error, query failed');
$row    = mysql_fetch_array($result, MYSQL_ASSOC);

{

echo $row['Genus'];
}

 

From this query I only get one entry. Why is this? Any ideas?

[MadTechie]

can you post the fully revised code please

[slaterino]

Here's the full code. The thing is if I take the limit off the query it is still just showing 1 row which is why I think the problem is with the base query rather than the limit or the paging.

 

 

$rowsPerPage = 6;

$pageNum = 1;

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

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

$self = $_SERVER['PHP_SELF'];

$catalogue_query  = "SELECT COUNT(Product_ID) AS numrows, Product_ID, Genus, Division FROM products";
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($catalogue_query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
$maxPage = ceil($numrows/$rowsPerPage);
//Count before setting the limit,
// then add the limit and search again
$catalogue_query .= " ORDER BY Product_ID DESC LIMIT $offset, $rowsPerPage";
$result = mysql_query($catalogue_query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);

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

[MadTechie]

Opps Count require a group

try this

$rowsPerPage = 6;
$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['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');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);

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

[slaterino]

Hey,

Thanks so much for all your help on this! However, I have tried using the exact code you have listed and it is still only showing one entry, and additionally the paging is now showing as Page 1 of 1, so still yet to solve this one.

 

I've been away from my computer for a few days but will have a good go at solving this now. Let us know if you have any other ideas.

 

Thanks!

[slaterino]

Okay,

So I've made one change which is now meaning that six rows are showing on the page. However, I still have the problem of the paging showing as Page 1 of 1 when it should be Page 1 of 29. To get 6 rows on the page I changed the last line so that it was a WHILE loop. My script now looks like this:

 

$rowsPerPage = 6;

$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['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))

{

 

Does anyone know why the paging section isn't working properly?

 

Thanks

Russ