PDO-查询未给出结果 [英] PDO - query giving no results

问题描述

这是我准备的陈述.

SELECT `id`, `title`, `image`, `discount`, `price`, `new_price`, `img_url` FROM `deals` WHERE `active`="1" AND `category`=:ctid AND `img_url`!=""  AND `Brands`=:p1 ORDER BY `order`, `id` ASC LIMIT 0, 12;

这是我在bindParam中使用的数组.

This is the array that i am using in bindParam.

Array
(
    [:ctid] => 1
    [:p1] => Apple
)

这是PHP代码:

$sql = 'SELECT `id`, `title`, `image`, `discount`, `price`, `new_price`, `img_url` FROM `deals` WHERE `active`="1" AND `category`=:ctid AND `img_url`!=""  AND `Brands`=:p1 ORDER BY `order`, `id` ASC LIMIT 0, 12;';
$sql = $link->prepare( $sql );

$binders = array(
  ':ctid' => 1,
  ':p1' => 'Apple'
);

foreach( $binders as $key => $value ) {
    $sql->bindParam( $key, $value );
}

$sql->execute();
$sql->setFetchMode( PDO::FETCH_ASSOC );
$result = $sql->fetchAll();

这没有结果.

但是，如果我直接查询，则会从数据库中获得结果.上面的查询可能出了什么问题.

But, if i do a direct query, i get results from the db. What could be wrong in the above query.

感谢您的帮助.

推荐答案

此处的问题是您使用bindParam绑定了参数，而bindParam则通过引用进行绑定.在您的情况下，应改用bindValue

The problem here is that you binding parameters with bindParam, which uses binding by reference. In your case you should use bindValue instead:

foreach( $binders as $key => $value ) {
    $sql->bindValue( $key, $value );
}

或者您可以将数组直接传递给execute()方法:

Or you can pass your array directly to execute() method:

$sql->execute( $binders );

如手册所述:

the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

因此，当您的foreach循环结束时，$value具有最后一个数组项Apple的值.因此，当execute运行时，:ctid和:p1的值都将等于Apple.当然，这不是您想要的东西

So when your foreach loop ends $value has value of last array item Apple. So when execute runs, both :ctid and :p1 values are becoming equal to Apple. Surely, this is not what you want)

这篇关于PDO-查询未给出结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！