PHP pagination error!

[public-image]

Hello everyone, il try to explain how my pagination works in an effort for you to understand clearly what the code is looking at and what might be causing my error.

 

My pagination is being used on a view topic/post page. There are two gets on my page

$page = $_GET['p'];  // used to define which page of pagination the user is requesting / looking at

$ids = $_GET['id']; // used to collect data from row for topic and its replies

 

My error:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in FILE  on line 233

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-10, 10' at line 1

 

Variables you may want to know:

 

$query3 = mysql_query("SELECT * FROM `posts` WHERE special='$ids'") or die (mysql_error());

while ($row3 = mysql_fetch_array($query3));

 

$rows = mysql_num_rows($query3);

 

$pn = $page - 1;

$pn = $pn * $prows;

 

$lastp = ceil($rows / $prows);

 

$prows = 10; // amount of rows/replies allowed per page

 

This line (233 is):

$q = mysql_query("SELECT * FROM `posts` WHERE special='$ids' ORDER BY `id` ASC LIMIT $pn, $prows");

while($f = mysql_fetch_array($q));  // this searches the table posts to find replies that have the special id same as the id in the URL

 

 

(HOWEVER) I found that this problem only occurs when there is only a begining post thus being no replies for that topic yet. On other topics with replies the error is gone.

 

Any help would be great and if you require any more information please just ask.

[kenrbnsn]

Replace these lines

<?php
$q = mysql_query("SELECT * FROM `posts` WHERE special='$ids' ORDER BY `id` ASC LIMIT $pn, $prows");
while($f = mysql_fetch_array($q))
?>

with

<?php
$q = "SELECT * FROM `posts` WHERE special='$ids' ORDER BY `id` ASC LIMIT $pn, $prows";
$rs = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
while ($f = mysql_fetch_array($rs)) {
?>

This will show you what the query looks like when you get the error.

 

Ken

 

[foxsoup]

It sounds very much like you're getting that error simply because you have no reply posts in the table to fetch.  Try putting some sanity-check code around the while statement, i.e.

 

if (mysql_num_rows($q) > 0) {
while ($f = mysql_fetch_array($q)) {
	// put your code in here
}
}

 

This way your code will only try to run the while loop if there are any results to fetch.  Without a num_rows check like this your code is trying to fetch results from an empty result resource, resulting in an error.

[kenrbnsn]

That error is returned when the mysql_query() function has a problem, not when there are no rows returned.

 

Ken

[public-image]

Okay I tried both;

 

with the rows if statement for the while it returns;

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in file  on line 233

 

Line 233:

if (mysql_num_rows($q) > 0) { // I did remember to close the bracket so thats not the problem.

 

 

--- Case 2 ---

 

When I changed the code I got;

 

Problem with the query: SELECT * FROM `posts` WHERE special='123456' ORDER BY `id` ASC LIMIT -10, 10

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-10, 10' at line 1

 

Which I believe is referring to the variables in the pagination code:

 

$prows = 10;

 

$pn = $page - 1;

$pn = $pn * $prows;

 

$lastp = ceil($rows / $prows);

 

 

[foxsoup]

Ah, kenrbnsn is indeed right with the assertation that it's your generated SQL statement that's wrong.    Your code to generate the numbers for the LIMIT part of the statement is in error, since it's creating a negative number for $pn.  Given that $prows is the number of results to show per page, and $page is the page number taken from the $_GET variable, try using these lines to get the page number and LIMIT offset:

 

$page = isset($_GET['p']) ? $_GET['p'] : 1;
$pn = ($page * $prows) - $prows;

[ignace]

You are calculating the number of pages inside the loop get them out as no matter how many times you calculate it if you have 6 items as a result and only show 5 on each then you still have 2 pages.