使用命名占位符设置 PDO/MySQL LIMIT

2021-12-26 00:00:00 php mysql pdo

我在绑定 SQL 查询的 LIMIT 部分时遇到问题.这是因为查询是作为字符串传递的.我在这里看到了 another Q 这里涉及绑定参数，没有涉及 Named数组中的占位符.

I'm having an issue binding the LIMIT part of an SQL query. This is because the query is being passed as a string. I've seen another Q here that deals with binding parameters, nothing that deals with Named Placeholders in an array.

这是我的代码:

public function getLatestWork($numberOfSlides, $type = 0) {

$params = array();
$params["numberOfSlides"] = (int) trim($numberOfSlides);
$params["type"] = $type;

$STH = $this->_db->prepare("SELECT slideID 
    FROM slides
    WHERE visible = 'true'
        AND type = :type
    ORDER BY order
    LIMIT :numberOfSlides;");

$STH->execute($params);

$result = $STH->fetchAll(PDO::FETCH_COLUMN);

return $result;        
}

我得到的错误是:'20''附近的语法错误或访问冲突(20 是 $numberOfSlides 的值).

The error I'm getting is: Syntax error or access violation near ''20'' (20 is the value of $numberOfSlides).

我该如何解决这个问题?

How can I fix this?

推荐答案

问题在于 execute() 引用了数字 并将其视为字符串:

The problem is that execute() quotes the numbers and treats as strings:

来自手册 - 一个值数组，其元素与正在执行的 SQL 语句中的绑定参数一样多.所有值都被视为 PDO::PARAM_STR.

From the manual - An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR. 

<?php 
public function getLatestWork($numberOfSlides=10, $type=0) {

    $numberOfSlides = intval(trim($numberOfSlides));

    $STH = $this->_db->prepare("SELECT slideID
                                FROM slides
                                WHERE visible = 'true'
                                AND type = :type
                                ORDER BY order
                                LIMIT :numberOfSlides;");

    $STH->bindParam(':numberOfSlides', $numberOfSlides, PDO::PARAM_INT);
    $STH->bindParam(':type', $type, PDO::PARAM_INT);

    $STH->execute();
    $result = $STH->fetchAll(PDO::FETCH_COLUMN);

    return $result;
}
?>

相关文章