Pagination Issue....

[sonny100]

Hi there,

I'm having some trouble with pagination, or mainly, the SQL query string that i'm using to get the results.

When I use the normal query string...

$data_p = mysql_query("SELECT * from user WHERE sex = '$sex1' AND location = '$location1' $q $max ") or die(mysql_error());

The first page of results are showing correctly, but the second page and so on are not..

If I take out the variables in the string, and just enter static data like so...

$data_p = mysql_query("SELECT * from user WHERE sex = 'Male' AND location = 'Avon' $q $max ") or die(mysql_error());

Then all of the pages appear correctly (!?)

Any idea what I could be doing wrong??

Here is the code in it's entirity, but the problem is definitely with the string above....

  $sex1 = $_REQUEST['sex'];
 $age1 = $_REQUEST['age'];
 $location1 = $_REQUEST['location'];
 $interests1 = $_REQUEST['interests'];

 $bang = explode(' ',$interests1);
foreach ($bang as $word){
$q .=" AND Interests LIKE '%$word%' ";
}

// Connects to your Database
include_once('code/main.php');



//This checks to see if there is a page number. If not, it will set it to page 1
if (!(isset($pagenum)))
{
$pagenum = 1;
}

//Here we count the number of results
//Edit $data to be your query
$data = mysql_query("SELECT * from user WHERE sex = '$sex1' AND location = '$location1' $q ") or die(mysql_error());
$rows = mysql_num_rows($data);



//This is the number of results displayed per page
$page_rows = 5;

//This tells us the page number of our last page
$last = ceil($rows/$page_rows);

//this makes sure the page number isn't below one, or more than our maximum pages
if ($pagenum < 1)
{
$pagenum = 1;
}
elseif ($pagenum > $last)
{
$pagenum = $last;
}

//This sets the range to display in our query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows; 

echo "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 BORDERCOLOR=#540EAD>";
//This is your query again, the same one... the only difference is we add $max into it
$data_p = mysql_query("SELECT * from user WHERE sex = '$sex1' AND location = '$location1' $q $max ") or die(mysql_error());
echo $sex1;

//This is where you display your query results
while ($info = mysql_fetch_array($data_p)) {

$id2 = $info['ID'];
$photo2 = $info['photo'];
$username2 = $info['username'];
$location2 = $info['location'];
$email2 = $info['email'];

echo "

        <TR>
		<TD width=75><a href= 'masterdetails.php?id=$username2''><img src=upload_images/{$photo2} width=150 /></a></TD>
		<TD width=100 border=1 border-style=\"solid\" border-color=\"RED\" align=centre>$username2<p>{$row['age']}<p>$location2</TD>
        <TD width=450>About $username2<p>{$row['about']}</TD>
		</TR>";


}
echo "</TABLE>"; 

// This shows the user what page they are on, and the total number of pages
echo " --Page $pagenum of $last-- <p>";

// First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.
if ($pagenum == 1)
{
}
else
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";
echo " ";
$previous = $pagenum-1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";
}



//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
if ($pagenum == $last)
{
}
else {
$next = $pagenum+1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";
echo " ";
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";
}

Thanks in advance!

[ixalmida]

Assign your query to a string variable first. I've had strange things happen when I put the string directly into a mysql_query() statement. Plus, that way you can output the query string later to see if there were any obvious problems.

$query = "SELECT * from user WHERE sex = '$sex1' AND location = '$location1' $q ";
$data = mysql_query($query)
  or die(mysql_error()."<br>Faulty query: ".$query); 
$rows = mysql_num_rows($data);

[sonny100]

Hi there,

Thank you for the very quick response!

I've just given that a go, and the query's now look like this....

$query = "SELECT * from user WHERE sex = '$sex1' AND location = '$location1' $q ";
$data = mysql_query($query) or die(mysql_error()."<br>Faulty query: ".$query);

and the second one...

$query2 = "SELECT * from user WHERE sex = '$sex1' AND location = '$location1' $q $max ";
$data_p = mysql_query($query2) or die(mysql_error()."<br>Faulty query: ".$query2);

Again though, the first page of the results are showing absolutely fine, it's just when I move to the second page of results that I get the following error...

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 '-5,5' at line 1
Faulty query: SELECT * from user WHERE sex = '' AND location = '' AND Interests LIKE '%%' limit -5,5

I'm beginning to think that it may be something to do with the $max variable, and how that is set. The query must be working ok I presume, because the first page of results are showing, but that may be a red herring, as when the query string has no variables, everything works as it should, even if the $max variable is still in the string...

[dagon]

$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;

should be

$max = 'limit ' .($pagenum * $page_rows) - $page_rows .',' .$page_rows;

[ixalmida]

In other words, your LIMIT clause is using a negative record number to reference the starting record, but recordsets start at 0 and can't be negative. If you want to grab records from the end you should change your sort order.

[sonny100]

Thanks again guys, i've just tried what was last suggested...

I've tried both of your suggestions together, assigning the query string to a variable AND changing the $max variable to $max = 'limit ' .($pagenum * $page_rows) - $page_rows .',' .$page_rows;

But I get the following error...

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 '-5,5' at line 1
Faulty query: SELECT * from user WHERE sex = 'Male' AND location = 'Avon' AND Interests LIKE '%%' -5,5

I've already seen that assigning the query string to a variable by itself doesn't seem to work either, so I tried leaving the query string as it was, and just changing the $max variable as suggested, but all I get is this error....

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 '-5,5' at line 1

Any clues? 🙁

[dagon]

the formula is correct so the inputs must be wrong, echo $pagenum and $page_rows

[sonny100]

Just echo'd both and they are coming out as....

Pagenum = 1
Page_rows = 5

Does that give any indication?

[dagon]

$max = 'limit ' .($pagenum * $page_rows) - $page_rows .',' .$page_rows;

$max = limit (1 *5) -1, 5

$max = limit 0, 5

[sonny100]

So i'm guessing that there is something else further down in the code that is changing the $pagenum..

I've just changed the code back to

$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;

and the first page of results is now displaying (this didn't happen with the amended version that you recommended)

I've echo'd the $pagenum at the top of the page and it is showing as '1', but when I move to the second page of results, I get the SQL error message again

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 '-7,7' at line 1
Faulty query: SELECT * from user WHERE sex = '' AND location = '' AND Interests LIKE '%%' limit -7,7

AND

The $pagenum is now set to '0'..??

So could something else in the code be changing this further down the page, or when I move to the second page of results?

If so, why does everything work ok when I put in static info instead of a variable in the query string?

[sonny100]

The one thing that is REALLY puzzling me though, is why the whole thing works absolutely fine if I enter static data into the query string, like so...

$query = "SELECT * from user WHERE sex = 'Male' AND age = '25' AND location = 'Hampshire' $q "; 

But all falls apart when I use variables, like so.....

$query = "SELECT * from user WHERE sex = '$sex1' AND age = '$age1' AND location = '$location1' $q "; 

All of the pages work fine, I can see all of the results and move back and forth between the pages with no problem if the data is static.

Although, the query string can't be wrong, because the first page of results is showing ok when I use variables in the string, it's only when I move to other pages of the results that I get errors.

Very odd and frustrating!

[dagon]

do you pass the sex and age vars on to page 2+

[sonny100]

YEEEEEEEEEEEEEEEESSSSSSSSSSS!!!!!

Hooray!!! Thank you!!

Right, here's what i've done...

I've added the variables that are in the query string to the URL on the 'Next' and 'Previous' page links, like so...

<a href='{$_SERVER['PHP_SELF']}?pagenum=$last&sex=$sex12&location=$location12'>Last ->></a> ";

Then (because the page posts to itself) i've added a few lines above that check to see if $GET['sex'] is set. If it is, then it re-assigns the variables in the string, $sex12 & $location1, to whatever $GET['sex'] is, like so....

if (isset($_GET['sex']))
{
	$sex1 = $_GET['sex'];
	$location12 = $_GET['location'];
}

This way, it will ignore the above snippet of code if the page hasn't moved on yet 🙂

Thank you so much for your help, we got there in the end and this has been driving me nuts! It didn't even occur to me that I needed to pass the variable, as I was posting to the same page!