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

本文介绍了使用命名占位符设置PDO/MySQL LIMIT的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在绑定SQL查询的LIMIT部分时遇到问题.这是因为查询是作为字符串传递的.我已经看到另一个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; }

我得到的错误是:Syntax error or access violation near ''20''($numberOfSlides的值是20).

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

我该如何解决?

推荐答案

问题是execute() quotes the numbers并视为字符串:

从手册开始-一个值数组，其元素与正在执行的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; } ?>