PDO:无效的参数编号:混合的命名和位置参数 [英] PDO: Invalid parameter number: mixed named and positional parameters

问题描述

我遇到了之前从未见过的警告:

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?

谢谢

推荐答案

更改

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);

这篇关于PDO:无效的参数编号:混合的命名和位置参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！