• 0

PHP/MYSQL Pagination

Asked by game_over,

 Share

Question

Grand Master

game_over

Posted
Posted

I'm trying to add Pagination to a php/mysql page that pulls a list of cars from a database. This works fine, but trying to implement Pagination has thrown a couple of errors at me i can't seem to fix.

Query failed: 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 'LIMIT (1-1)*20, .20' at line 1

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/uplift/public_html/index.php on line 86

I think it will be much easier here to just paste the whole page:

The first error refers to this line:

$limit = "LIMIT ($pageno-1)*$perPage, .$perPage";

second error refers to this line:

while($row = mysql_fetch_array($result)) {

Can anyone spot the errors? also, is there not an easier way to do this, it seems really complicated.

<?php
include('header.php');


if(isset($_GET['pageno'])) {
	if(!is_numeric($_GET['pageno'])) {
		echo 'Error.';
			exit();
	}
	$pageno = $_GET['pageno'];
} else {
	$pageno=1;
}
$queryCount = 'SELECT count(*) FROM used';
$resultCount = mysql_query($queryCount);
$fetch_row = mysql_fetch_row($resultCount);
$numrows = $fetch_row[0];

// if there is no results
if($numrows == 0) {
	echo 'Sorry, we have no products yet.';
	exit();
}

$perPage = 20;
$lastpage = ceil($numrows/$perPage);
$pageno = (int)$pageno;
if($pageno<1) {
	$pageno=1;
}
elseif($pageno>$lastpage) {
	$pageno=$lastpage;
}

// ----- PAGE LINKS -----
if($pageno==1) {
	$pages .= 'FIRST | PREVIOUS ';
}
else {
	$pages .= " <a href=\"index.php?pageno=1\">FIRST</a> | ";
	$prevpage=$pageno-1;
	$pages .= " <a href=\"index.php?pageno=".$prevpage.">PREVIOUS</a> ";
}
$pages .= ' ( Page '.$pageno.' of '.$lastpage.' ) ';
if($pageno==$lastpage) {
	$pages .= ' NEXT | LAST ';
}
else {
	$nextpage = $pageno+1;
	$pages .= " <a href=\"index.php?pageno=".$nextpage.">NEXT</a> | ";
	$pages .= " <a href=\"index.php?pageno=".$lastpage.">LAST</a>";
}

$limit = "LIMIT ($pageno-1)*$perPage, .$perPage";

?>

<body>
	<div id="header">
		<div class="logo">
			<a href="/"><img src="/images/logo.png" alt="New and Used Ford and Fiat Franchise Sales In County Durham - GMD Group" /></a>
			<img src="/images/logo_r.png" alt="New and Used Ford and Fiat Franchise Sales In County Durham - GMD Group" />
		</div>
	</div>
	<div id="nav">
		<div class="menu">
			<ul>
				<?php require_once('includes/nav.php'); ?>
			</ul>
		</div>
	</div>

	<div id="main">
		<h2>GMD Group - Browse our latest used vehicles for sale</h2>
	</div>

	<?php
	$imgLocation = "/cars/used/";
	$result = mysql_query("SELECT * FROM used ORDER BY id DESC");

	$query = $query.$limit;
	$result = mysql_query($query);
	if(!$result) {
		echo 'Query failed: '.mysql_error();
	}
	while($row = mysql_fetch_array($result)) { ?>
		<div id="usedcars">
		<h2><?php echo $row['name']; ?></h2>
		<table width="860" border="0" cellpadding="0" cellspacing="0">
			<tr>
				<td width="210" align="center" valign="top">
					<a href="<?php echo $imgLocation; echo $row['photo1']; ?>" rel="shadowbox" title="<?php echo $row['name']; ?>"><img src="<?php echo $imgLocation; echo $row['photo1']; ?>" width="200px" /></a>
					<h1 style="color:red;">£<?php echo $row['price']; ?></h1>
				</td>
				<td width="644" valign="top">
					<p><?php echo $row['description']; ?></p>
					<a href="<?php echo $imgLocation; echo $row['photo2']; ?>" rel="shadowbox" title="<?php echo $row['name']; ?>"><img src="<?php echo $imgLocation; echo $row['photo2']; ?>" width="100px" /></a>
					<a href="<?php echo $imgLocation; echo $row['photo3']; ?>" rel="shadowbox" title="<?php echo $row['name']; ?>"><img src="<?php echo $imgLocation; echo $row['photo3']; ?>" width="100px" /></a>
					<a href="<?php echo $imgLocation; echo $row['photo4']; ?>" rel="shadowbox" title="<?php echo $row['name']; ?>"><img src="<?php echo $imgLocation; echo $row['photo4']; ?>" width="100px" /></a>
					<a href="<?php echo $imgLocation; echo $row['photo5']; ?>" rel="shadowbox" title="<?php echo $row['name']; ?>"><img src="<?php echo $imgLocation; echo $row['photo5']; ?>" width="100px" /></a>
					<a href="<?php echo $imgLocation; echo $row['photo6']; ?>" rel="shadowbox" title="<?php echo $row['name']; ?>"><img src="<?php echo $imgLocation; echo $row['photo6']; ?>" width="100px" /></a>
				</td>
			</tr>
		</table>
		</div>
	<?php }

	echo "<div style=\"width: 100%; text-align: center; font-size: smaller; color: rgb(153, 153, 153);\">".$pages."</div>";
	echo "Total number of products: ".$numrows." ---";

	include('footer.php');
	?>

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0
Grand Master

+theblazingangel MVC

Posted
  • MVC
Posted

err, because

$limit = "LIMIT ($pageno-1)*$perPage, .$perPage";
results in
"LIMIT (1-1)*20, .20"
which isn't valid SQL...you want to end up with something like
"LIMIT 20, 20"
so you want
$limit = "LIMIT " . ($pageno-1)*$perPage . ", " . $perPage;
Link to comment
Share on other sites
  • 0
Grand Master

Andrew Lyle Global Moderator

Posted
  • Global Moderator
Posted

Just to expand on what theblazingangel said

This will display results 1-10 (depending on sort order)

LIMIT 10

This will display results 6-10

LIMIT 5, 10

This will display results 11-20

LIMIT 10, 10

Link to comment
Share on other sites
  • 0
Grand Master

game_over

Posted
  • Author
Posted

i tried this:

$limit = "LIMIT " . (($pageno-1)*$perPage) . ", " . $perPage;

then the code you suggested but neither have an impact on the error

Link to comment
Share on other sites
  • 0
Contributor

Sparky Marky

Posted
Posted

Have you thought of using 

$offset = $perPage*($pageno-1);
LIMIT $perPage OFFSET $offset

OFFSET is for compatibility with PostgreSQL, but might be useful for finding errors in your math/sql statement.

Also, in your error message it has , .20 (ie decimal point 20, which would be effectivly)

LIMIT (1-1)*20, 0.20

You should remove this.

Link to comment
Share on other sites
  • 0
Experienced

AnthonySterling

Posted
Posted

script.php?page=<INSERT NUM>

&lt;?php
error_reporting(-1);
ini_set('display_errors', true);

function get_value($key, $default){
 if(false === empty($_GET[$key])){
	if(0 !== (int)$_GET[$key]){
 	return (int)$_GET[$key];
	}
 }
 return $default;
}

$per_page = 5;
$page_num = get_value('page', 0);

printf(
 'SELECT id, foo, bar FROM table LIMIT %d, %d',
 $page_num * $per_page,
 $per_page
);
?&gt;

:cool:

Link to comment
Share on other sites
  • 0
Grand Master

game_over

Posted
  • Author
Posted

fixed :)

$result = mysql_query("SELECT * FROM used ORDER BY id DESC");

$query = $query.$limit;

changed to:

$query = "SELECT * FROM used ORDER BY id DESC";

$query = $query . " " . $limit;

$result = mysql_query($query);

thanks for all the suggestions.

AnthonySterling, will take a look of that, thanks

Link to comment
Share on other sites
  • 0
Experienced

AnthonySterling

Posted
Posted

fixed :)

$result = mysql_query("SELECT * FROM used ORDER BY id DESC");

$query = $query.$limit;

changed to:

$query = "SELECT * FROM used ORDER BY id DESC";

$query = $query . " " . $limit;

$result = mysql_query($query);

thanks for all the suggestions.

AnthonySterling, will take a look of that, thanks

You're welcome. :)

However, if that's the code you're using, it isn't really fixed and it appears you're open to more than one vulnerability there.

Glad to hear you're looking at the code I supplied though.

Anthony.

Link to comment
Share on other sites
This topic is now closed to further replies.
 Share
Go to question listing

  • Recently Browsing   0 members

    • No registered users viewing this page.