PDO and LIMIT syntax

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

PDO and LIMIT syntax

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: PDO and LIMIT syntax

Post 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);
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: PDO and LIMIT syntax

Post by Weirdan »

Also, variable limit parameters are only supported since MySQL 5.0.7 . Older MySQL versions won't accept them, regardless the type.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: PDO and LIMIT syntax

Post 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?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: PDO and LIMIT syntax

Post by Weirdan »

'limit 10, 20' would work, but 'limit :something' or 'limit ?' would not.
Post Reply