Page 1 of 1

PDO and LIMIT syntax

Posted: Thu Aug 27, 2015 9:02 am
by social_experiment
I have the following code:

Code: Select all

<?php
$result = false;
$statement = $this->_pdoObj->prepare("SELECT * FROM shop_stock LIMIT 0, ?");
//$statement->bindParam(':amount', $amount);		
$statement->bindValue(1, $amount);
?>
$amount is a value passed to the function and is an integer. An error occurs when I execute the query

SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''2'' at line 2

If i try the code with a value not passed from the function, the query executes and the script does what it's supposed to. I also bound a parameter to see if that might work but I receive the same error. I'm using PHP Version 5.3.8 if that's of any help.

Thanks

Re: PDO and LIMIT syntax

Posted: Thu Aug 27, 2015 9:22 am
by Celauran
PDO binds parameters as strings by default. You need to explicitly bind it as an integer.

http://php.net/manual/en/pdostatement.bindparam.php

Code: Select all

$statement = $this->_pdoObj->prepare("SELECT * FROM shop_stock LIMIT 0, ?");
$statement->bindParam(':amount', $amount, PDO::PARAM_INT);

Re: PDO and LIMIT syntax

Posted: Thu Aug 27, 2015 9:31 am
by Weirdan
Also, variable limit parameters are only supported since MySQL 5.0.7 . Older MySQL versions won't accept them, regardless the type.

Re: PDO and LIMIT syntax

Posted: Thu Aug 27, 2015 9:45 am
by social_experiment
thanks, adding that additional parameter solved the problem.
Weirdan wrote:Also, variable limit parameters are only supported since MySQL 5.0.7
with this, do you mean that the LIMIT syntax won't work with a MySQL version prior to the one mentioned?

Re: PDO and LIMIT syntax

Posted: Thu Aug 27, 2015 9:49 am
by Weirdan
'limit 10, 20' would work, but 'limit :something' or 'limit ?' would not.