PHP PDO error when using placeholders in MySQL query LIMIT clause

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 the error:

Query error: SQLSTATE [42000]: syntax error or access violation: 1064 You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to "5" on line 1.

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

I tried both $st->bindParam and $st->bindValue , both did not work.

Answer 1

I decided it.I Type cast :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 that ' should be used instead.'

Answer 2

I think the problem is with TBL_MEMBERS. I assume this is a view (subtitle). So, if you have a product table and you want to follow these instructions:

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

You will get the following error:

SQL Error (1064): You have an error in the SQL syntax; check the manual that matches your MySQL server version for the correct syntax to use next to "select id, code, name, slug, info from products order by code" on line 1

But if you change the request to:

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

it will work.

To summarize, TBL_MEMBERS is a representation that should be placed in brackets and given an alias (i my example alias is 'v')

Answer 3

I recommend looking at the text of the SQL query that produces the PDO. You can do this using MySQL common query log .

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

This is even a matter of PDO::PARAM_INT , when the formal parameter type is not an integer ( is_int returns false ), the PDO wraps it in quotation marks. Thus, before binding them, you need to pass parameters to targets (for example, using intval ):

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