問題描述

我在綁定 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;
}
?>

這篇關于使用命名占位符設置 PDO/MySQL LIMIT的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！