mysql - PDO 双 LIKE 带参数

Question

我正在使用 PDO 执行查询，该查询不会通过 PHP 检索任何结果，但可以在 phpMyAdmin 中工作。我确信连接设置，因为它不是我的脚本的第一个查询，并且其他查询工作正常。这是 PHP 代码:

        $retour = array();
        $filters = array();
        $filters['media_type'] = 'mytype';
        $filters['libelle'] = 'sometext';
        $start = 0;
        $count = 9;

        $sql = "SELECT * FROM ".DB_PROD_PREFIX.$this->table." t ";
        $sql .= " LEFT JOIN ".DB_PROD_PREFIX.$this->table."_lang l ON t.id = l.id AND l.langue = :langue";
        $sql .= " WHERE 1";
        if (count($filter)>0){
            foreach($filter as $field => $value){
                $sql .= " AND ".$field." LIKE :".$field;
            }
        }
        $sql .= ($order!='' ? " ORDER BY ".$order : '');
        $sql .= ($count != '' ? " LIMIT ".($start != ''?':start':'0').", :count" : '');

        $stmt = $db->prepare($sql);
        if($start != '') $stmt->bindParam('start', $start, PDO::PARAM_INT);
        if($count != '') $stmt->bindParam('count', $count, PDO::PARAM_INT);
        if ($langue != '') $stmt->bindParam('langue', $langue);

        if (count($filter)>0){
            foreach($filter as $field => $value) {
                $f = '%'.$value.'%';
                $stmt->bindParam($field, $f, PDO::PARAM_STR);
            }
        }

        echo $stmt->queryString.print_r($filter, true);

        if (!$stmt->execute()) echo $stmt->errorInfo();
        $res = $stmt->fetchAll();
        foreach($res as $id => $row){
            $retour[]=$row;
        }
        return $retour;

如果我取消设置 $filter 数组的 2 个键之一，则查询可以正常工作。这是生成的 SQL:

SELECT * FROM table1 t  
LEFT JOIN table1_lang l ON t.id = l.id AND l.langue = :langue 

WHERE 1 
AND media_type LIKE :media_type 
AND libelle LIKE :libelle 

ORDER BY position LIMIT :start, :count

感谢您的帮助!

皮埃尔·M.

Answer 1

虽然效果不太好，但还是谢谢你的提示!下面是一段管理 LIKE 参数的代码:

$media_type = '%'.$filter['media_type'].'%';
$stmt->bindParam('media_type', $media_type, PDO::PARAM_STR);
$libelle = '%'.$filter['libelle'].'%';
$stmt->bindParam('libelle', $libelle, PDO::PARAM_STR);

为什么在数组上使用 foreach 执行相同的操作不起作用？

if (count($filter)>0){
    foreach($filter as $field => $value) {
        $value = "%$value%";
        $stmt->bindParam($field, $value, PDO::PARAM_STR);
    }
}