PDO:无效的参数号:混合命名和位置参数

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

我遇到了这个我以前从未见过的警告:

I have come across this warning I've not seen before:

警告:PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: 无效的参数号:混合命名和位置参数...

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters in...

参考下面的PDO查询(为了方便阅读，已经简化了功能):

Referring to the following PDO query (have simplified the function for ease of reading):

$offset = 0;
$limit = 12;
function retrieve_search_posts($searchfield, $offset, $limit){


        $where = array();

        $words = preg_split('/[s]+/',$searchfield);

        array_unshift($words, '');
        unset($words[0]);

        $where_string = implode(" OR ", array_fill(0,count($words), "`post_title` LIKE ?"));

        $query = "
                                SELECT  p.post_id, post_year, post_desc, post_title, post_date, img_file_name, p.cat_id
                                FROM    mjbox_posts p
                                JOIN    mjbox_images i
                                ON      i.post_id = p.post_id
                                        AND i.cat_id = p.cat_id
                                        AND i.img_is_thumb = 1
                                        AND post_active = 1
                                WHERE $where_string
                                ORDER BY post_date
                                LIMIT :offset, :limit
                                DESC";
        $stmt = $dbh->prepare($query);

        foreach($words AS $index => $word){
            $stmt->bindValue($index, "%".$word."%", PDO::PARAM_STR);
        }
        $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        $searcharray = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $searcharray;
    }

函数和 PDO 查询在查询中没有包含偏移量和限制变量的情况下工作正常.那么是什么原因导致了这个警告?

The function and PDO query works fine without the offset and limit variables included in the query. So what might be causing this warning?

谢谢

推荐答案

Change

LIMIT :offset, :limit

到

LIMIT ?, ?

和

$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);

到:

$stmt->bindValue($index+1, $offset, PDO::PARAM_INT);
$stmt->bindValue($index+2, $limit, PDO::PARAM_INT);

相关文章