[SOLVED] Problem with My Pagination. Can You See What's Wrong?

[Fluoresce]

I have a simple pagination script on my movie_categories.php page (see below). I have added the following to the page:

 

$cat_id = intval($_GET['cat_id']);

 

Now, when someone clicks on, say, movie_categories.php?cat_id=horror, they will land on movie_categories.php and all of the horror movies will be presented. Everything works fine, except, when there is more than 1 page of results, the other pages don't work. For example, if there are 7 movies, and movies 1 to 5 are presented on Page 1, movies 6 and 7 are not presented on Page 2. Page 2 just says 'None'. Can anyone see why this is happening? I will appreciate the help very much.

 

movie_categories.php:

 

<?php

$conn = mysql_connect('localhost', 'root') or trigger_error("SQL", E_USER_ERROR);  
mysql_select_db('hidden', $conn) or trigger_error("SQL", E_USER_ERROR);


$cat_id = intval($_GET['cat_id']);


$query = "SELECT COUNT(*) FROM tbl_movies WHERE cat_id='$cat_id'";
$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];


if (isset($_GET['pageno'])) {
$pageno = $_GET['pageno'];
} else {
$pageno = 1;
} // end else
$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);
$pageno = (int)$pageno;
if ($pageno > $lastpage) {
$pageno = $lastpage;
} // end if
if ($pageno < 1) {
$pageno = 1;
} // end if
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;


$select = "SELECT . . . WHERE cat_id='$cat_id' $limit";
$select_results = mysql_query($select, $conn) or trigger_error("SQL", E_USER_ERROR);


if (mysql_num_rows($select_results) >= 1) {


if ($pageno == 1) {
echo " ";
} else {
echo " <a class=\"pagination\" href='{$_SERVER['PHP_SELF']}?pageno=1'>First</a> ";
$prevpage = $pageno-1;
echo " <a class=\"pagination\" href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>Prev</a> ";
} // end else
echo " Page $pageno of $lastpage ";
if ($pageno == $lastpage) {
echo " ";
} else {
$nextpage = $pageno+1;
echo " <a class=\"pagination\" href='{$_SERVER['PHP_SELF']}?pageno=$nextpage?'>Next</a> ";
echo " <a class=\"pagination\" href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>Last</a> ";
} // end else


while($row = mysql_fetch_assoc($select_results)) {
echo "Result . . .";


} // end while

} // end first if


else 
{
echo "None";
}

?>

[Andy-H]

$btmLimit = round( (($pageno - 1) * $rows_per_page), 0);
$topLimit = round( ($btmLimit + $rows_per_page), 0);
$limit = 'LIMIT ' . $btmLimit .' , ' . $topLimit;

[Fluoresce]

Thanks, Andy-H. But can you please clarify your response? What do I do with that code? I swapped it with

 

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

 

and it didn't work.

[Andy-H]

I was looking at that part of the code in yours and it had the higher limit set as the $rows_per_page variable so say if pagenum was 1 it would have a lower limit of

($pageno - 1) * $rows_per_page

or ( 0 * 5 = 0 )

then the higher limit of 5 would work

 

but then the higher limit in your code was static (always 5) so I just made it so its always 5 more than the lower limit in the query...

 

No idea why it still doesnt work tho..  ???

[kittrellbj]

$btmLimit = round( (($pageno - 1) * $rows_per_page), 0);
$topLimit = round( ($btmLimit + $rows_per_page), 0);
$limit = 'LIMIT ' . $btmLimit .' , ' . $topLimit;

 

Before the query, I would make sure that you do not have a negative value being put in there, it can cause problems.  i.e. :

 

<?php
$btmLimit = round( (($pageno - 1) * $rows_per_page), 0);
$topLimit = round( ($btmLimit + $rows_per_page), 0);
if ($btmLimit < 1) { $btmLimit = 1; }
if ($topLimit < 1) { $topLimit = 1; }
$limit = 'LIMIT ' . $btmLimit .' , ' . $topLimit;
?>

 

The current page number is 0 on the first page, unless it has been set elsewhere by the URL $_GET variable, so -1 to 0 is -1.  The database might not like that very much.  It also would fix a user putting in a negative number who doesn't know better or who is trying to disrupt your site.

 

In addition, I have a pagination system working on my custom-designed forums, so this snippet may help some.  (maybe)   Just to show you how I do it.

 

I am not seeing anything stand-out obviously wrong with your code, though.

 

<?php
$records_per_page = 10;
$reply_query = "SELECT * FROM . . . WHERE topic=" . $_GET['topic'];
$q = mysql_query($reply_query);
$total_records = mysql_num_rows($q);
$total_pages = ceil($total_records / $records_per_page);
if ($total_pages < 1) { $total_pages = 1; }
$page = intval($_GET['page']);
if ($page < 1 || $page > $total_pages) {
$page = 1; }
$offset = ($page - 1) * $records_per_page;
$reply_query = $reply_query . " LIMIT $offset, $records_per_page";
$reply_result = mysql_query($reply_query);
$reply_count_rows = mysql_num_rows($reply_result);
// and so on
?>

[Fluoresce]

Thanks, guys, but your suggestions didn't work. It's like the script doesn't support URLs with variables in them. When I land on Page 1, the URL in the browser address bar looks like this:

 

movie_categories.php?id=horror.

 

But when I click to Page 2, it looks like this:

 

movie_categories.php?pageno=2.

 

Anyone understand what's going on?

 

 

[Fluoresce]

I have realised that I should have added ?cat_id=$cat_id to my pagination links, thus:

 

if ($pageno == 1) {
echo " ";
} else {
echo " <a href='{$_SERVER['PHP_SELF']}?cat_id=$cat_id?pageno=1'>First</a> ";
$prevpage = $pageno-1;
echo " <a href='{$_SERVER['PHP_SELF']}?cat_id=$cat_id?pageno=$prevpage'>Prev</a> ";
}
echo " Page $pageno of $lastpage ";
if ($pageno == $lastpage) {
echo " ";
} else {
$nextpage = $pageno+1;
echo " <a href='{$_SERVER['PHP_SELF']}?cat_id=$cat_id?pageno=$nextpage?'>Next</a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?cat_id=$cat_id?pageno=$lastpage'>Last</a> ";
}

 

But having done so, it still doesn't work.

 

When I land on Page 1, its URL looks like this in the browser address bar:

 

movie_categories.php?cat_id=horror.

 

When I click to Page 2, the URL changes to this

 

movie_categories.php?cat_id=horror?pageno=2,

 

but it still displays Page 1's results, not Page 2's.  ???

 

Anyone know what's going on? Is it re-selecting Page 1's results because of $cat_id = intval($_GET['cat_id']); at the top of the page?

[xylex]

URL's wrong.

 

movie_categories.php?cat_id=horror?pageno=2

 

should be

 

movie_categories.php?cat_id=horror&pageno=2

 

[Fluoresce]

Xylex, I thank you very much. I've been puzzled all day on that one. Your fix worked! I can now relax.