how to give parameters for pagination using prepared stmt pdo?

Question

I want to give pagination parameters to stmt execute array but keep getting wrong parameters warning need your help.

here is part of the code:

        if($stmt = $pdo->prepare("SELECT * FROM posts LIMIT :page, :per_page")){
            $stmt->execute(array(':page'=>$page, ':per_page'=>$per_page,));
                if($stmt->rowCount() > 0){
                    $rowUser = $stmt->fetchAll(PDO::FETCH_ASSOC);
                }
        }

I tried like this is well :

$stmt->execute(array($page, $per_page));

and

$stmt->execute([$page, $per_page]);

here is total code. I get white page after deleting : from parameters.

if($stmt = $pdo->prepare("SELECT * FROM posts LIMIT :page, :per_page")){
  $stmt->execute(array('page'=>$page, 'per_page'=>$per_page));
    if($stmt->rowCount() > 0){
         $rowUser = $stmt->fetchAll(PDO::FETCH_ASSOC);
                        $stmt = $pdo->prepare("SELECT * FROM posts INNER JOIN users ON posts.user_id = users.id WHERE user_id = user_id");
                        $stmt->execute(array('user_id'=>$sid));
                        $count = $stmt->rowCount();
                        $paginations = ceil($count / $per_page);
                                    echo '<div><h1>Total posts ('.$count.')</h1></div>';
                            foreach($rowUser as $row) {
                                    echo $row['title'];
                                }
                    ?>

This is strange. You were first getting a non-standard error message and then a *500 Server Error*. I suggest you double-check that your development environment is configured to display all error information (you can test by making simple intentional errors, such as invalid PHP, invalid SQL or an undefined variable). You may also want to verify that you don't have a custom error handling that's masking actual errors. — Álvaro González, Nov 20 '18 at 18:32

score 0 · Accepted Answer · answered Nov 20 '18 at 07:48

0

Remove the colon:

if($stmt = $pdo->prepare("SELECT * FROM posts LIMIT :page, :per_page")){
  $stmt->execute(array('page'=>$page, 'per_page'=>$per_page,));
    if($stmt->rowCount() > 0){
         $rowUser = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

or use question marks and make it a plain array:

if($stmt = $pdo->prepare("SELECT * FROM posts LIMIT ?, ?")){
  $stmt->execute(array($page, $per_page));
    if($stmt->rowCount() > 0){
         $rowUser = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

answered Nov 20 '18 at 07:48

Bart Friederichs

33,050
15
95
195

https://stackoverflow.com/questions/9778887/is-the-leading-colon-for-parameter-names-passed-to-pdostatementbindparam-opt – Nigel Ren Nov 20 '18 at 07:55
now its turned to a white and clean page no errors or posts :( adding total codes to posts – Nov 20 '18 at 08:04

how to give parameters for pagination using prepared stmt pdo?

1 Answers1