0

I'm having a problem with a query: 

$sth = $Db->dbh->prepare(
   "SELECT * 
   FROM loader 
   WHERE download = 0 
    AND lastconnected BETWEEN DATE_SUB(NOW(),INTERVAL 15 MINUTE) AND NOW() 
   ORDER BY lastconnected DESC 
   LIMIT :amount");

The LIMIT isn't working for some reason, if I change :amount to a hardcoded number it'll work but as soon as I use it as :amount it's giving me this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''1'' at line 5

This is what I'm using to execute the prepared query:

$sth->execute(array(':amount' => $amount));

Been trying to figure it out for a couple of hours now. Hopefully someone can see something I'm not.

Wesley Bland
  • 8,816
  • 3
  • 44
  • 59
user2879055
  • 176
  • 9
  • 1
    Is this putting single quotes around the amount? That might cause the problem. – AdamMc331 Nov 05 '14 at 16:09
  • Obvious questions: what language are you writing this in? Have you tried printing ":amount" to see if it has a valid value? – PaulJ Nov 05 '14 at 16:10
  • what is the value of $amount? please assign here your Amount variable.. – Syed Arif Iqbal Nov 05 '14 at 16:11
  • The value of amount is also in the error use near ''1'' at line 5 the value of amount is 1. So it's picking up amount but for some reason putting 2 qoutes around it? Also the langauge is PHP – user2879055 Nov 05 '14 at 16:11

1 Answers1

0

If you're using a variable LIMIT with PDO, you have to bind each parameter with PDOStatement::bindParam() and explicitly specify that the parameter's value has integer type (PDO::PARAM_INT). PDOStatement::execute() with an array of input parameter values treats all values as strings (PDO::PARAM_STR), not the actual PHP types, as does calling PDOStatement::bindParam() without a type, but MySQL's LIMIT keyword does not take a string argument. This is documented in the manual page for the method PDOStatement::execute(), and there's an open feature request to change the behavior.

Damian Yerrick
  • 4,602
  • 2
  • 26
  • 64
  • Tepples, so I can't pass intergers in the array? – user2879055 Nov 05 '14 at 16:13
  • @user2879055 Pass the integer through `bindParam()` instead of the array. – Damian Yerrick Nov 05 '14 at 16:15
  • I made it into ` $sth = $Db->dbh->prepare("SELECT * FROM loader WHERE download = 0 AND lastconnected BETWEEN DATE_SUB(NOW(),INTERVAL 15 MINUTE) AND NOW() ORDER BY lastconnected DESC LIMIT :amount"); $sth->bindParam(':amount',$amount); $sth->execute(); ` But it's still giving me the exact same error. – user2879055 Nov 05 '14 at 16:18
  • @user2879055 `$sth->bindParam(':amount',$amount)` means `$sth->bindParam(':amount',$amount,PDO::PARAM_STR)`. Specify a type if you don't want PDO to treat it as a string. – Damian Yerrick Nov 05 '14 at 16:24
  • Made the bindParam into this now: `$sth->bindParam(':amount', $amount, PDO::PARAM_INT);` But still having the same results. – user2879055 Nov 05 '14 at 16:29