在MySQL查询的LIMIT子句中使用占位符时，PHP PDO错误 [英] PHP PDO error when using placeholders in the LIMIT clause of a MySQL query
问题描述
$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());
}
这里出现错误:
查询失败:SQLSTATE [42000]:语法错误或访问冲突:1064您的SQL语法有错误；请查看与您的MySQL服务器版本相对应的手册，以获取在第1行的"5"附近使用的正确语法.
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.
LIMIT :startRow, :numRows在:numRows中有问题.
我已经尝试了$st->bindParam和$st->bindValue，但是都没有用.
I have tried both $st->bindParam and $st->bindValue, both didn't work.
推荐答案
我解决了.我键入了:numRows占位符.
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();
$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 ".
这篇关于在MySQL查询的LIMIT子句中使用占位符时，PHP PDO错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
