PDO Limit Query Not Working using Binding in Execute

Question

I'm trying to get a limit query working. So far I've verified the following:

All input it valid and correct. The Query works when manually run against MySQL The Values are Cast to INT. I have even replcated the variables with the int value.

       try {
             $sql = "SELECT * FROM sensor_data WHERE sid=:sid ORDER BY id DESC LIMIT :starting_limit, :recordlimit";
             $core = CoreDB::getInstance();
             $sth = $core->dbh->prepare($sql);
             $sth->execute(array(':sid' => $sensor->getID(),  ':starting_limit' => (int)0, ':recordlimit' => (int)10));
            // $sth->execute(array(':sid' => $sensor->getID()));
             $results = $sth->fetchAll();
             var_dump($sth->rowCount());
             if ($sth->rowCount() > 0) {
                 foreach($results as $row){
                   $id = $row['id'];
                   var_dump($id);
                   }
                 }
               }
               catch(Exception $e){
               }

Any advice is appreciated

Define "*not working*". What does your `var_dump` output? You could always output both `$sth` and `$results` in your `catch()` — Obsidian Age, Jun 04 '18 at 23:50
@ObsidianAge If I complete that query manually putting 0 in place of :standing_limit and 10 in place of :recordlimit I get a var_dump for rowcount of 8. When I do it as shown above I get 0. — David_Hogan, Jun 04 '18 at 23:55
Possible duplicate of [How to apply bindValue method in LIMIT clause?](https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause) — , Jun 04 '18 at 23:57
@smith I'm casting to int and binding within the execute method so I would have thought that would cover that issue. I'll rewrite using bindValue and see does it resolve the issue. — David_Hogan, Jun 05 '18 at 00:02

score 0 · Accepted Answer · answered Jun 05 '18 at 00:05

The only solution I've found is to manually bind params rather than put them in an array in the execute method.

         try {
             $sql = "SELECT * FROM sensor_data WHERE sid=:sid ORDER BY id DESC LIMIT :starting_limit, :recordlimit";
             $core = CoreDB::getInstance();
             $sth = $core->dbh->prepare($sql);
             $sth->bindValue(':sid', $sensor->getID(), PDO::PARAM_INT);
             $sth->bindValue(':starting_limit', 0, PDO::PARAM_INT);
             $sth->bindValue(':recordlimit', 10, PDO::PARAM_INT);
             $sth->execute();
             $results = $sth->fetchAll();
             var_dump($sth->rowCount());
             if ($sth->rowCount() > 0) {
                 foreach($results as $row){
                   $id = $row['id'];
                   var_dump($id);
                   }
                 }
               }
               catch(Exception $e){
               }

If anyone is aware of how to get it working in the execute method please let me know and I will update the answer.

PDO Limit Query Not Working using Binding in Execute

1 Answers1