Problem with 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

Problem with LIMIT syntax

Post by social_experiment »

i can't retrieve information from the table when i use the code below;

Code: Select all

<?php
$sql = "SELECT 
`ip`,
`action_date`
FROM
`admin_logins` 
WHERE 
`ip` = ? LIMIT ?, ?";			
					
$sth2 = $dbh->prepare($sql);		
$sth2->execute(array($ip, 0, 4));  // <- line 74
?>
The SQL below is what works, when used in in phpMyAdmin

Code: Select all

SELECT * 
FROM `admin_logins` 
WHERE `ip`= '41.52.92.107'
LIMIT 0, 5
The error i am receiving via error_log is the following
PDOStatement::execute(): 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 ''0', '4'' at line 7 in /nanya/search.php on line 74

If i remove the LIMIT clause from the query it works;

any ideas / suggestions appreciated

Edit
Did the same script using mysqli_ functions and it seems the problem (seems to me anyway) lies with the ip address not being 'escaped' (?)
“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: Problem with LIMIT syntax

Post by Celauran »

There was a bug in the MySQL driver -- apparently fixed in 5.5.6 -- that causes it to quote numeric arguments, so your query ends up with LIMIT '0', '5'. You can disable emulated prepares

Code: Select all

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
or sprintf the query to pass in the limit values

Code: Select all

$sql = sprintf("SELECT 
`ip`,
`action_date`
FROM
`admin_logins` 
WHERE 
`ip` = ? LIMIT %d, %d", $foo, $bar);
Of course, if 0 and 4 are static values, you can just include them in the query directly.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Problem with LIMIT syntax

Post by social_experiment »

thanks for letting me know; i wasn't aware of this :)
“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
Post Reply