...

View Full Version : Pagination with PHP



greens85
10-23-2009, 04:46 PM
Hi all,

I'm trying to add page pagination to my page but its causing 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 '15' at line 1

I know it says its an SQL error but before I added the php pagination code the result were returning fine. Can anyone see any problems with the script?


<?php

$name = mysql_real_escape_string($_POST['name']);
$lea = mysql_real_escape_string($_POST['lea']);
$type = mysql_real_escape_string($_POST['type']);
$postcode = mysql_real_escape_string($_POST['postcode']);

if (isset($_GET['pageno'])) {
$pageno = $_GET['pageno'];
} else {
$pageno = 1;
}

$query = "SELECT count(*) FROM schools WHERE Name LIKE '%$name%' AND Leaname LIKE '%$lea%' AND SchoolType LIKE '%$type%' AND PostcodeSubArea LIKE '%$postcode' ORDER BY Name ASC";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

$rows_per_page = 15;
$lastpage = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno > $lastpage) {
$pageno = $lastpage;
}
if ($pageno < 1) {
$pageno = 1;
}

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

$query = "SELECT * FROM schools $limit";
$result = mysql_query($query) or die (mysql_error());
while ($row = mysql_fetch_array ($result)) {
// PROCESS CONTENTS ?>
<a href="schooldetails.php?RID=<?php echo $row['RID'];?>"><?php echo $row['Name'];?></a>
<?php
echo '<br/>';
echo '<br/>';
echo 'LEA:'.' '.'<span class="normalfont">'.$row['Leaname'].'</span>';
echo '<br/>';
echo 'Address:'.' '.'<span class="normalfont">'.$row['Address1'].'</span>';
echo '<br/>';
echo '<span class="normalfont">'.$row['Address2'].'</span>';
echo '<br/>';
echo '<span class="normalfont">'.$row['Address3'].'</span>';
echo '<br/>';
echo 'Postcode:'.' '.'<span class="normalfont">'.$row['Postcode'].'</span>';
echo '<br/>';
echo 'Telephone:'.' '.'<span class="normalfont">0'.$row['Phone'].'</span>';
echo '<br/>';
echo 'Fax:'.' '.'<span class="normalfont">0'.$row['Fax'].'</span>';
echo '<br/>';?>
Website: <span class="normalfont"><a href="<?php echo $row['url']?>"><?php echo $row['url'];?></a></span>
<?php
echo '<br/>';
echo '<br/>';
}

if ($pageno == 1) {
echo "FIRST PREV";
} else {
echo "<a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a>";
$prevpage = $pageno - 1;
echo "<a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a>";
}

echo "(Page $pageno of $lastpage)";

if ($pageno == $lastpage) {
echo "NEXT LAST";
} else {
$nextpage = $pageno + 1;
echo "<a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a>";
echo "<a href='{$_SERVER['PHP_SELD']}?pageno=$lastpage'>LAST</a>";
}

?>

Thanks

kbluhm
10-23-2009, 04:48 PM
Here is the error, or at least the first I came across:


$query = "SELECT * FROM schools $limit";

I'm assuming the value if $limit is 15.

greens85
10-23-2009, 04:59 PM
Here is the error, or at least the first I came across:


$query = "SELECT * FROM schools $limit";

I'm assuming the value if $limit is 15.

I got the script from here:

http://www.tonymarston.net/php-mysql/pagination.html

I was actually assuming the limit variable was related to amount of results actually displayed on the page, not limiting the amount taken out of the database.... is this not the case?

kbluhm
10-23-2009, 05:03 PM
http://dev.mysql.com/doc/refman/5.0/en/select.html

I see now in the code where $limit is assigned the value `LIMIT xxx`, so that is not the issue.

Did you alter the code? Change this line:


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

To this:


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

greens85
10-23-2009, 05:10 PM
http://dev.mysql.com/doc/refman/5.0/en/select.html

I see now in the code where $limit is assigned the value ` LIMIT xxx`, so that is not the issue.

Did you alter the code? Change this line:


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

To this:


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


The only code I've altered is the query, I've put a where clause in...

kbluhm
10-23-2009, 05:17 PM
That's weird, because in the link you provided, their code on that line contains: 'LIMIT ' (trailing space)

But yours contains 'LIMIT' (no trailing space).

greens85
10-23-2009, 05:24 PM
That's weird, because in the link you provided, their code on that line contains: 'LIMIT ' (trailing space)

But yours contains 'LIMIT' (no trailing space).

Ah thats done the trick, not sure how it got in there.... but many thanks for your help... I would never of put an error down to a trailing space. I have a long way to go with PHP I think!

greens85
10-23-2009, 05:48 PM
Actually think I may have spoken to soon on this one, as it seems to have stopped my search terms working... i.e. it splits results but doesnt take into account what search terms where entered!

Should the pagination script have an affect on this?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum