PDO and SQL LIMIT

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
The_Anomaly
Forum Contributor
Posts: 196
Joined: Fri Aug 08, 2008 4:56 pm
Location: Tirana, Albania

PDO and SQL LIMIT

Post by The_Anomaly »

Well, I haven't been to these boards in a while as I've been working on a project that's been consuming my life, but I'm humbled by a problem that no matter how hard I try, I can not solve. It's all OOP, and as opposed to putting down several classes here, let me explain my problem.

I have a Data Mapper class, and it's been working extremely well. It uses PDO, and I bind the parameters like a good, security minded dev. This is all well and good, until I start screwing around with pagination. Basically, I have the following query (simplified):

Code: Select all

SELECT * FROM comment LIMIT :recordNumber, 3
After preparing the statement and all of that loveliness:

Code: Select all

$prepared->execute(array(':recordNumber'=>$number) )
In the past, such an array has bound perfectly to all of the variables resulting in a great amount of joy and happiness. However, now, I get this error:
Structured Query Language Error wrote:error_FatalException: Mapper::doStatement(): 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 ''1', 3' at line 1 in {path} on line 163
Line 163 being the execute( ) function above.

Obviously, the parameter is binding alright (:recordNumber was replaced by 5 in the error). I went to the mysql prompt, and tested the query. It worked very well, UNTIL I tried putting a single quote around the parameter that was bound in my code. I think that's the problem. When I bind the value, it puts a single quote around it, and although this is okay in other situations, a LIMIT doesn't like it.

If you guys could help me out with this, I'd appreciate it. I really don't want to have to change my entire mapper to accommodate this, as I have at least five different modules depending on it. Would be a shame to change the core of it all just because of simple pagination.


Thanks guys.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: PDO and SQL LIMIT

Post by EverLearning »

You only solution is to use bindParam() or bindValue() and specifying the param type

Code: Select all

$stmt->bindValue(':recordNumber', $number, PDO::PARAM_INT);
Post Reply