how to bind limit value in pdo statement?

Question

this is my code :

$sql="SELECT duration,address,roozhaye_bargozari, ";
        $sql.="ostan_name,city_name,cluster_name,education_node.name ";
        $sql.="FROM class ";
        $sql.="LEFT JOIN education_cluster ec ON ec.id=class.cluster_id ";
        $sql.="LEFT JOIN education_node ON education_node.id=class.node_id ";
        $sql.="LEFT JOIN ostan ON ostan.id=class.ostan_id ";
        $sql.="LEFT JOIN city ON city.id=class.city_id ";
        $sql.="WHERE class.master_id=? LIMIT ?,?";

        $result=$db->prepare($sql);
        $result->execute(array($master_id,$startpos,$endpos));
        $final_result=$result->fetchAll(PDO::FETCH_ASSOC);

when i use this code , this error message apear: PDOException: 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','2'' at line 1 in C:\wamp\www\fani\inc\class\user.php on line 239

but if i use this :

.
.
.
$sql.="WHERE class.master_id=? LIMIT $startpos,$endpos";

        $result=$db->prepare($sql);
        $result->execute(array($master_id);
        $final_result=$result->fetchAll(PDO::FETCH_ASSOC)

the code run correctly, whats the problem?

You can only bind column values. Not column names, table names or limits. — juergen d, Apr 04 '15 at 08:02
i try this , and it's worked. but i'm not sure it's the best way or not. $PDO->setAttribute( PDO::ATTR_EMULATE_PREPARES, false ); http://stackoverflow.com/questions/2269840/how-bindvalue-in-limit — Peyman abdollahy, Apr 04 '15 at 08:12

EternalHour · Answer 1 · 2015-04-04T09:38:31.763

1

Look closely at the error...

''0','2''

and notice the quotes there. execute is causing the query to fail because it is adding single quotes and treating the integers as a string. The reason your second query works is because you are inserting the numbers directly into the query rather than binding them. In order to make it work, you will need to do something like this...

$result=$db->prepare($sql);
$result->bindParam(1, $master_id, PDO::PARAM_INT);
$result->bindParam(2, $startpos, PDO::PARAM_INT);
$result->bindParam(3, $endpos, PDO::PARAM_INT);
$result->execute();
$final_result=$result->fetchAll(PDO::FETCH_ASSOC);

edited Apr 04 '15 at 09:38

answered Apr 04 '15 at 09:13

EternalHour

8,308
6
38
57

yes, it's good. but it's long. how to handle just with execute? i found something ($PDO->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );) and it's work. but i'm not sure it's a best way or not – Peyman abdollahy Apr 04 '15 at 11:03
I'm not sure about that, if it works PDO is probably not using a prepared statement which removes the security. I know it's long, but you can't bind the `LIMIT` with execute. – EternalHour Apr 04 '15 at 18:53

how to bind limit value in pdo statement?

1 Answers1