Mysql, PDO - Like statement not working using bindParam

Question

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.

here is my code:

// 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

// 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>';

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 parameters along with its `%` symbols. — Madara's Ghost, Jun 12 '12 at 16:17
The $where variable contains a string of like comparisons, for each word in the search field a new like comparison is made — crmepham, Jun 12 '12 at 16:20

Madara's Ghost · Accepted Answer · 2012-06-13T17:49:07.850

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.

This returns an error: `Warning: PDOStatement::bindValue() [pdostatement.bindvalue]: SQLSTATE[HY093]: Invalid parameter number: Columns/Parameters are 1-based in...` — crmepham, Jun 13 '12 at 11:46
@crm: Corrected. bindValue starts counting from 1. (I hate those PHP inconsistencies) — Madara's Ghost, Jun 13 '12 at 17:49

score 1 · Answer 2 · answered Jun 12 '12 at 16:22

1

You cannot bind fields and values together, PDO will wrap it in a quote as it assumes the ? is a value for a query:

'post_title LIKE \'%something%\''

You could leave the column field in, and then bind the param:

post_title LIKE ?

Then check if the search field is set. Bind the param using '%'.$where.'%' or if nothing is entered, then just '%'

answered Jun 12 '12 at 16:22

Tim Withers

12,072
5
43
67

Ah thank you, so because I was including the `post_title LIKE` pdo assumed that it was part of the string instead of mysql functions. Thank you. – crmepham Jun 12 '12 at 16:29
I don't understand this answer. What exactly do you want him to do? – Madara's Ghost Jun 12 '12 at 16:49
He was stating that because I include the mysql keywords wihtin my string they were treated as only string statements and not actual mysql keywords. So I had to include the `post_title LIKE` in the prepared statement and only have the search words in the string passed into pdo via bindParam – crmepham Jun 12 '12 at 21:43

Mysql, PDO - Like statement not working using bindParam

2 Answers2

Linked