MySQL，PDO-Like语句无法使用bindParam正常工作 [英] Mysql, PDO - Like statement not working using bindParam

问题描述

我正在尝试对网站的搜索功能进行编码，并且被困在sql语句中的类似比较中.出于某种原因，当我使用?和pindparam包含类似比较字符串的变量，它会不断返回，但未找到结果.如果我删除了?然后只需输入比较post_title LIKE '%something%'即可.

I am trying to code search functionality for a site and am stuck at the like comparison in the sql statement. For some reason when I use ? and pindparam the variable containing the string of like comparisons it keeps coming back with no results found. If I remove the ? and just type the comparison post_title LIKE '%something%' it works.

这是我的代码:

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

        }
        $searcharray = array();
        $where = "";

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

        foreach($searchfield AS $key=>$searchword){
            $where .= "post_title LIKE '%".$searchword."%'";
            if($key != ($total_words - 1)){
                $where .= " OR ";
                echo $searchword . '<br>';
            }
        }
        echo $where;
        $stmt = $dbh->prepare("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 ?
                                ORDER BY post_date
                                DESC
                                LIMIT 9");
        $stmt->bindParam(1,$where);
        $stmt->execute();

        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

                $searcharray[] = $row;
        }

        return $searcharray;
    }


// Check for errors in search field
    function search_errors($searchfield){
        $searcherrors = array();
        if(empty($searchfield)){
            $searcherrors[] = '<p>Please enter a search term.</p>';
        }else if(strlen($searchfield)<3){
            $searcherrors[] = '<p>Your search term must be three characters or more.</p>';
        }else if(retrieve_search_posts($searchfield) == false){
            $searcherrors[] = '<p>Your search for '.$searchfield.' returned no results.</p>';
        }
        return $searcherrors;
    }

search.php

search.php

// Get the search terms posted 
    $searchfield = trim($_POST['searchfield']);

    // Check if there are any errors with the search terms
    $searcherrors = search_errors($searchfield);

    // If there are errors
    if(!empty($searcherrors)){
        // Display them here
        foreach($searcherrors AS $value){
            echo $value .'<br />';
        }
    }
    $searcharray = retrieve_search_posts($searchfield);


    echo '<div id="content-wrap">';
    foreach($searcharray AS $value){
        $filename = substr($value['img_file_name'],9);
        $cat_id = $value['cat_id'];
        echo '<article class="post">';
        echo '<div class="post_title">' . $value['post_title'] . '</div>';
        echo '<div class="post_info">' . 
        'Category: ' . $cat_name = get_cat_name($cat_id) .'<br />'. 
        'Year: ' . $value['post_year'] .'<br />'. 
        $value['post_desc'] .'<br />'. 
        '</div>';
        echo '<div class="link-to-post"><a href="#">Click to view</a></div>';
        echo '<a name="'.$value['post_id'].'"></a><a href="#'.$value['post_id'].'" class="linktopost"><img class="post-thumb" src="img/thumb_/'.$filename.'" alt="MJbox Michael Jackson memorabilia thumbnail" data-postid="'.$value['post_id'].'"/></a>';
        echo '<a name="'.$value['post_id'].'"></a><a href="#'.$value['post_id'].'" class="linktopost"><img class="cover-img" src="img/post-bg-1.png" alt="test" data-postid="'.$value['post_id'].'"/></a>';
        echo '</article>';

    }
    echo '</div>';

在mysql数据库中肯定有一个条目，其中包含我放入where字符串中的单词.当我只是输入类似的比较而不是使用?时，该语句在mysql查询和我的网站上均有效.

There is definately an entry in the mysql database that contains the word(s) I put into the where string. The statement works both in mysql query and on my website when i just type in the like comparison instead of using ?.

推荐答案

您需要分别绑定每个参数，可以通过第二个循环进行绑定.

You need to bind each parameter separately, you can do so with a second loop.

function retrieve_search_posts(PDO $pdo, $search_field) {
    /*
     * Get the PDO object as an argument, this function shouldn't care
     * how the PDO object is created, that's the factory's job.
     */

    /*
     * Use $underscored_names or $camelCase for variable names, easier on the eye
     */

    ## Variable initializations ##
    $where = array();

    ##Function start
    $words = preg_split("/\s+/", $search_field);

    for ($i = 0; $i < count($words); $i++) {
        /*
         * We don't need to have the word in here,
         * so we aren't even using the foreach loop, just a normal for
         */
        $where[] .= "`post_title` LIKE ?";
    }
    /*
     * For cleaner code, use an array and implode the pieces with OR,
     * this way, you don't get an OR at the beginning, nor the end.
     */
    $where_string = implode(" OR ", $where);

    $query = <<<MySQL
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 ?
    ORDER BY post_date DESC
    LIMIT 9
MySQL;

    $sth = $pdo->prepare($query);

    /*
    * Iterate over the array again,
    * this time, we're binding the values based on the index
    */
    foreach ($words as $index => $word) {
        $sth->bindValue($index+1, $word, PDO::PARAM_STR);
    }

    $sth->execute();

    $result = $sth->fetchAll(PDO::FETCH_ASSOC); //Fetch all the results in associative array form

    return $result;

}

请参阅代码注释，以说明所做的更改.

See the comments on the code to explain the changes made.

这篇关于MySQL，PDO-Like语句无法使用bindParam正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！