PDO Pagination Error

Question

I have been working on pagination using pdo but i cannot seem to get past this part. i have looked up other posts and tutorials but i hit a wall, can anyone help? **Edit full code added, updates have been added but still has errors

<?php
try{
    $handler = new PDO('mysql:host=127.0.0.1;dbname=clients','root','');
    $handler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    echo $e->getMessage();
    die();
}


?>
<html>
<head>
<link rel="stylesheet" type="text/css" href="mystyle.css">
</head>


<body bgcolor="#F5F1DE">

<div class="center">
  <br>
   <br>
    <br>
     <br>
      <br>
  <h2> Business Listings</h2>


<table  border="1" style="width:50%">
    <tr>
<?php
$per_page = 10;
$NoofRecords = $handler->query('SELECT COUNT(CompID) FROM Listings;');
$pages = ceil($NoofRecords / $per_page);

if(!isset($_GET['page'])){
    header(":location: Listings.php?page=1");
}
else{
    $page =$_GET['page'];
}

$start = (($page - 1)*$per_page);
echo "SELECT * FROM listings WHERE Accept=1 ORDER BY Premium LIMIT                  $start,$per_page;";
$query = $handler->query("SELECT * FROM listings WHERE Accept=1 ORDER BY Premium LIMIT $start,$per_page;");
while($r = $query->fetch()) {
    echo '<td>', $r['Name'], '</td>','<td>', $r['Email'], '</td>','<td>',         $r['Phone'], '</td>',
'<td>','<a href="' . $r['Name'] . '.php">Company page</a>', '</td>';
echo '</tr>';
}

for($number=1;$number<=$pages;$number++)
{
    echo '<a href="?page'.$number.'">.$number. </a>';
}   
echo"<BR>Current Page: $page"
?>

</table>
  </Div>
</div>


</div>
</body>
</html>

on line 34 its does not like the syntax for ceil also on line 43 it complains aobut an undefined varrible adn then this "Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1327 Undeclared variable: $start' in C:\wamp\www\Myserver\Listings.php on line 45 ( ! ) PDOException: SQLSTATE[42000]: Syntax error or access violation: 1327 Undeclared variable: $start in C:\wamp\www\Myserver\Listings.php on line 45" — user3676186, Apr 16 '15 at 17:47
put a try catch block around your pdo code. var_dump the exception and you will see what is going wrong. — akr, Apr 16 '15 at 17:48
What does `echo $_GET['page'];` give you? Can you link to the tutorial, maybe seeing the original code will make debugging this quicker? — chris85, Apr 16 '15 at 18:11
@chris85 [link](https://www.youtube.com/watch?v=KhHdt8CM4LU) — user3676186, Apr 16 '15 at 18:14
Bah a video... okay nevermind that what does the echo give you? — chris85, Apr 16 '15 at 18:15
@chris85 i echoed it and nothing appeared on the screen, not sure if error is covering it or it does nothing — user3676186, Apr 16 '15 at 18:18
Is there a question mark in the URL you are loading? Can you post the query string you are executing, after `.php` in the URL? — chris85, Apr 16 '15 at 18:23
@chris85 ok i changed the url to "Listings.php?page=1" and most of the errors disappear , but one remains " Object of class PDOStatement could not be converted to int in C:\wamp\www\Myserver\Listings.php on line 34". Thank you for putting up with me thus far, i have been bashing my head against this wall for 2 hours — user3676186, Apr 16 '15 at 18:28
no wait i am stupid it echoed "$pages = ceil($NoofRecords / $per_page)" — user3676186, Apr 16 '15 at 18:35
I'm not sure I'm following you. Please use the below code and let me know the result. — chris85, Apr 16 '15 at 18:43

Chris Tate · Answer 1 · 2015-04-16T17:51:43.917

1

Use double quotes " instead of single quotes ' when using variables in a string:

"SELECT * FROM listings WHERE Accept=1 ORDER BY Premium LIMIT $start,$per_page;"

edited Apr 16 '15 at 17:51

answered Apr 16 '15 at 17:46

Chris Tate

458
2
10

Fixed SQL statement. Hopefully that helps! – Chris Tate Apr 16 '15 at 17:52
Changed query still getting erros ,"Object of class PDOStatement could not be converted to int line 34 " and Undefined variable line 43 – user3676186 Apr 16 '15 at 17:54
Echo the statement and see what you are sending to the DB. `echo "SELECT * FROM listings WHERE Accept=1 ORDER BY Premium LIMIT $start,$per_page;";` – chris85 Apr 16 '15 at 17:56
Do you have the rest of the code? There are only 27 lines here. – Chris Tate Apr 16 '15 at 17:58
it gave the output "SELECT * FROM listings WHERE Accept=1 ORDER BY Premium LIMIT -10,10;" – user3676186 Apr 16 '15 at 17:58
The `-10` is your issue. `LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).` What is `$_GET['page']` equal to? – chris85 Apr 16 '15 at 18:02
i am not sure what you mean, i was following a tutorial and everything went horribly worng – user3676186 Apr 16 '15 at 18:07

score 0 · Accepted Answer · edited May 23 '17 at 10:10

Limit goes last in your query.

SELECT * FROM listings WHERE Accept=1 ORDER BY Premium LIMIT $start,$per_page ;

https://dev.mysql.com/doc/refman/5.0/en/select.html

You also need to use double quotes as Crystal has pointed out for PHP to process the variables. You should use prepared statements though this is open to injections, How can I prevent SQL injection in PHP?.

Updated, without prepared statements which you should still use..

<?php
try{
    $handler = new PDO('mysql:host=127.0.0.1;dbname=clients','root','');
    $handler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    echo $e->getMessage();
    die();
}
$per_page = 10;
$NoofRecords = $handler->query('SELECT COUNT(CompID) as counted FROM Listings;');
$fetchdata = $NoofRecords->fetch();
$recordcount = $fetchdata['counted'];
$pages = ceil($recordcount / $per_page);
if(empty($_GET['page'])){
    //this is a waste if this page is listings.php, just set $page to 1;
    header("Location: Listings.php?page=1");
} else{
    $page = $_GET['page'];
}
$start = (($page - 1)*$per_page);
echo "SELECT * FROM listings WHERE Accept=1 ORDER BY Premium LIMIT $start,$per_page;";
$query = $handler->query("SELECT * FROM listings WHERE Accept=1 ORDER BY Premium LIMIT $start,$per_page;");
while($r = $query->fetch()) {
    $table_contents = '<tr><td>' . $r['Name'] . '</td><td>' . $r['Email'] . '</td><td>'. $r['Phone'] . '</td><td><a href="' . $r['Name'] . '.php">Company page</a></td></tr>';
}
?>
<html>
<head>
<link rel="stylesheet" type="text/css" href="mystyle.css">
</head>
<body bgcolor="#F5F1DE">
<div class="center"> 
    <br>
    <br>
    <br>
    <br>
    <br>
    <h2> Business Listings</h2>
    <table  border="1" style="width:50%"> 
        <?php echo $table_contents; ?>
    </table>
    <?php
    for($number=1;$number<=$pages;$number++) {
        echo '<a href="?page'.$number.'">'.$number. '</a>';
    }   
    echo "<BR>Current Page: $page"; ?>

</div>
</div><!-- I dont know what these 2 divs are for... -->
</div> 
</body>
</html>

Thank you this worked , but there as an error that is hidden in the background behind the page content — user3676186, Apr 16 '15 at 18:44
Whats the error? Make sure to remove `echo "SELECT * FROM listings WHERE Accept=1 ORDER BY Premium LIMIT $start,$per_page;";`. Outputting content is a good way to diagnose and resolve errors in the future. — chris85, Apr 16 '15 at 18:48
It returned an error "Object of class PDOStatement could not be converted to int in C:\wamp\www\Myserver\Listings.php on line 11" — user3676186, Apr 16 '15 at 18:48
its a problem with converting the line "$pages = ceil($NoofRecords / $per_page);" into an int — user3676186, Apr 16 '15 at 18:53
Yes, just updated. You need to fetch the result from that query. Try updated code now. — chris85, Apr 16 '15 at 18:55
New error , undefined variable in " for($number=1;$number<=$pages;$number++) {" — user3676186, Apr 16 '15 at 18:58

score 0 · Answer 3 · answered Apr 16 '15 at 17:50

0

change your query line to this: the query syntax was wrong.

$query = $handler->query("SELECT * FROM listings WHERE Accept=1 ORDER BY Premium LIMIT $start,$per_page");

answered Apr 16 '15 at 17:50

akr

739
4
15

score 0 · Answer 4 · answered Apr 16 '15 at 17:57

Apart from the sql problems mentioned in the other answers (LIMIT at the end, double quotes so that you can use variables in the string), you already have an earlier problem:

$pages = ceil(($NoofRecords,0)/$per_page);
              ^^^^^^^^^^^^^^^^ What is supposed to happen here?

The ,0 part renders the whole statement invalid.

You just need:

$pages = ceil($NoofRecords / $per_page);

PDO Pagination Error

4 Answers4