PHP PDO error when using placeholders in the LIMIT clause of a MySQL query

Question

$sql = "SELECT sql_calc_found_rows * FROM members".
       " ORDER BY username LIMIT :startRow, :numRows";

try {
    $st = $conn->prepare($sql);
    $st->bindParam(":startRow", $startRow, PDO::PARAM_INT);
    $st->bindParam(":numRows", $numRows, PDO::PARAM_INT);
    $st->execute();
} catch (PDOException $e) {
    die("Query failed: " . $e->getMessage());
}

Here I get error:

Query failed: 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 ''5'' at line 1.

The LIMIT :startRow, :numRows has problem in :numRows.

I have tried both $st->bindParam and $st->bindValue, both didn't work.

Can you try to remove th blank between `:startRow, :numRows` — Jens, May 22 '15 at 06:29
Jens, I have tried but it wont work.it only shows problem in :numRows. — Sujin Shrestha, May 22 '15 at 07:00
error is because of $order please put static value for testing. rest everything is looks perfect — kamlesh.bar, May 22 '15 at 07:00
Danyal value of $order is "username" and sql_calc_found_rows is sql function to retrieve the number of rows in table i guess — Sujin Shrestha, May 22 '15 at 07:00
I have tested your sql query there is no error try remove order for testing. i think thats causing problem — kamlesh.bar, May 22 '15 at 07:04
kamlesh.bar , this is a public static function with parameters $startRow, $numRows, $order. — Sujin Shrestha, May 22 '15 at 07:04
I understand it's public static function there is nothing wrong with that as you mention you have mysql error and i think problem lies near $order variable — kamlesh.bar, May 22 '15 at 07:07
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/78496/discussion-between-sujin-shrestha-and-kamlesh-bar). — Sujin Shrestha, May 22 '15 at 07:12
you should add your answer below and you can accept your answer that way you can imporve your score — kamlesh.bar, May 22 '15 at 09:32
not like this you can answer your question in below Box "your Answer" and than accept by click on right image — kamlesh.bar, May 22 '15 at 10:37
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/78588/discussion-between-sujin-shrestha-and-kamlesh-bar). — Sujin Shrestha, May 23 '15 at 06:41

score 0 · Answer 1 · answered May 22 '15 at 12:20

I think the problem is with TBL_MEMBERS I suppose this is a view(subselect). So, if you have product table for example and you want to execute following statement:

select sql_calc_found_rows * from select id, code, name, slug, info from products order by code

you will receive following error:

SQL Error (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 'select id, code, name, slug, info from products order by code' at line 1

But if you change query to:

select sql_calc_found_rows * from (select id, code, name, slug, info from products) v order by code

this will work.

To summarize, TBL_MEMBERS is a view which should be put in parenthesis and given alias(i my example alias is 'v')

`TBL_MEMBERS` is a definition. `define("TBL_MEMBERS","members");` I dont think that's the problem. — Sujin Shrestha, May 23 '15 at 02:37

dened · Answer 2 · 2015-05-23T06:30:44.963

I recommend to look at the SQL query text what PDO actually produces. You can do this with help of MySQL's general query log.

Most probably, the formal types of $startRow and/or $numRows are strings, not integers, and resulting query is therefore something like LIMIT '0', '5' (syntax error) instead of LIMIT 0, 5 (correct).

The thing is, even with PDO::PARAM_INT, when the parameter's formal type is not integer (is_int returns false), PDO wraps it in quotes. So, you have to cast parameters to integers before binding them (e.g. using intval):

$st->bindParam(":startRow", intval(trim($startRow)), PDO::PARAM_INT);
$st->bindParam(":numRows", intval(trim($numRows)), PDO::PARAM_INT);

score -2 · Accepted Answer · edited May 10 '21 at 08:57

I solved it.I Type casted the :numRows placeholder.

$numRows=(int)$numRows;
$sql = 'SELECT sql_calc_found_rows * FROM ' . 
    TBL_MEMBERS .'ORDER BY'.  $order .'LIMIT :startRow,:numRows';
    try {
        $st = $conn->prepare($sql);
        $st->bindValue(":startRow", $startRow, PDO::PARAM_INT);
        $st->bindValue(":numRows", $numRows, PDO::PARAM_INT);
        $st->execute();
    ...

And it worked. I also noticed the ' should be use instead of ".

PHP PDO error when using placeholders in the LIMIT clause of a MySQL query

3 Answers3

Linked