PDO LIKE with parameter in query doesn't have result

Question

I use PDO for a pgsql database. The query doesn't work. I suppose the problem is about the commas where I make a query. A similarly query works fine directly in pgAdminIII. I was tried different variants of forming this query, but a the result is the same 'Nothing found'.

// Get Search
$search_string = preg_replace("/[^A-Za-z0-9]/", " ", $_POST['query']);
$search_string = $conn->quote($search_string);
echo $search_string;
$s1 = '%'.$search_string.'%';


// Check Length More Than One Character
if (strlen($search_string) >= 1 && $search_string !== ' ') {
    // query

$query =  $conn->prepare('SELECT title FROM book WHERE author LIKE ?');
$query->bindValue(1, '\'$s1\'', PDO::PARAM_STR);
    $query->execute();

    if (!$query->rowCount() == 0) {
        while ($results = $query->fetch()) {
            echo $results['title'] . "<br />\n";
        }
    } else {
        echo 'Nothing found';
    };

Answer 1

Here's a self-contained example of an SQLite database being built and then queried with a LIKE value that came from $_POST

<?php

$_POST['searchterm'] = 'King';      /* This is just for demonstration */


/* Create our database first */
$books = array(
        array(':id' => 0, ':author' => 'J. R. R. Tolkien', ':title' => 'The Lord of the Rings: The Fellowship of the Ring',),
        array(':id' => 1, ':author' => 'J. R. R. Tolkien', ':title' => 'The Lord of the Rings: The Two Towers',),
        array(':id' => 2, ':author' => 'J. R. R. Tolkien', ':title' => 'The Lord of the Rings: The Return of the King',),
);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result = $pdo->exec('CREATE TABLE books(id int, title varchar(255), author varchar(255))');
$stmt = $pdo->prepare('INSERT INTO books(id, title, author) values(:id, :title, :author)');
try {
        foreach($books as $book) $stmt->execute($book);
} catch (PDOException $e) {
        echo $e;
}

$stmt = $pdo->prepare("select * from books where title LIKE :search");

if (! $stmt->execute(array(':search' => '%'.$_POST['searchterm'].'%'))) {
        /* No matches, you should do something intelligent about it */
}
foreach($stmt->fetchAll(PDO::FETCH_BOTH) as $row) {
        var_dump($row);  /* For demo only; not practical in the real-world */
}

You can see I chose to add the wildcards around the search term on the PHP side; if you want to have the client-side do that, you can do that, too.