警告:PDOStatement :: execute()[pdostatement.execute]:SQLSTATE [HY093]:无效的参数号: [英] Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number:

问题描述

我正在尝试使用以下代码在搜索字段中使用单词搜索数据库.

I am attempting to use the following code to search a database using words in a search field.

由于某种原因，我遇到以下错误

For some reason I am getting the following error

警告:PDOStatement :: bindValue()[pdostatement.bindvalue]: SQLSTATE [HY093]:无效的参数号:列/参数为 从1开始.

Warning: PDOStatement::bindValue() [pdostatement.bindvalue]: SQLSTATE[HY093]: Invalid parameter number: Columns/Parameters are 1-based in..

这是功能代码:

// Retrieve search results
    function retrieve_search_posts($searchfield){
        //test the connection
        try{
            //connect to the database
            $dbh = new PDO("mysql:host=localhost;dbname=mjbox","root", "usbw");
        //if there is an error catch it here
        } catch( PDOException $e ) {
            //display the error
            echo $e->getMessage();

        }

        $where = array();

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

        $total_words = count($searchfield);

        for($i = 0; $i < count($words); $i++){

            $where[] .= "`post_title` LIKE ?";

        }

        $where_string = implode(" OR ", $where);

        $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 post_title LIKE ?
                                ORDER BY post_date
                                DESC";

        $stmt = $dbh->prepare($query);

        foreach($words AS $index => $word){
            $stmt->bindValue($index, $word, PDO::PARAM_STR);
        }

        $stmt->execute();

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

        return $searcharray;
    }

我做错了什么导致输出错误消息?

What have I done wrong to cause the outputted error message?

推荐答案

首先，您没有使用在循环中精心构造的...OR LIKE子句的字符串.

Firstly, you didn't use the string of ...OR LIKE clauses you carefully constructed in the loop.

第二，如错误所示，准备好的语句参数是1索引的，而数组是0索引的.您需要将数组中的所有索引上移1，以使其能够与当前的foreach一起使用，或者在循环过程中向其添加1.

Secondly, as the error says, prepared statement parameters are 1-indexed, whereas your array is 0-indexed. You need to shift all the indexes in the array up 1 in order to get it to work with your current foreach, or add 1 to them during the loop.

尝试以下方法:

function retrieve_search_posts($searchfield){
    //test the connection
    try{
        //connect to the database
        $dbh = new PDO("mysql:host=localhost;dbname=mjbox","root", "usbw");
    //if there is an error catch it here
    } catch( PDOException $e ) {
        //display the error
        echo $e->getMessage();

    }

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

    // Easy way to 1-index a 0-indexed array
    array_unshift($words, '');
    unset($words[0]);

    // Never used and meaningless - $searchfield is a string
    // $total_words = count($searchfield);

    // Tidier and more resilient than the loop
    $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
       DESC
    ";

    $stmt = $dbh->prepare($query);

    foreach ($words AS $index => $word){
        // You may want to use "%$word%" or similar below, as it is the LIKE
        // keyword in your query is doing nothing and you might as well use =
        $stmt->bindValue($index, $word, PDO::PARAM_STR);
    }

    // Handle the potential error here!
    $stmt->execute();

    return $stmt->fetchAll(PDO::FETCH_ASSOC);

}

这篇关于警告:PDOStatement :: execute()[pdostatement.execute]:SQLSTATE [HY093]:无效的参数号:的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！