SQL prepared statement returns no result (empty result)

Question

Following prepared statement returns no result if I try like search('samsung').

public function search($searchFor) {
        try{
            //connect to db
            $pdo = $this->_db->connect();
            //set up SQL and bind parameters            
            $sql = "select * from item where itemName like '%:searchfor%' or description like '%:searchfor%'";
            $stmt = $pdo->prepare($sql);
            $stmt->bindParam(':searchfor', $searchFor, PDO::PARAM_STR);
            //execute SQL
            $rows = $this->_db->executeSQL($stmt);
            return $rows;
        }
        catch (PDOException $e)
        {
            throw $e;
        }
    }

$rows return an empty array. But if I try

select * from item where itemName like '%samsung%' or description like '%samsung%;

it returns a matched item and works as expected.

I found

$sql = "select * from item where itemName like :searchfor or description like :searchfor";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(":searchfor", "%$searchFor%");

works. I had to use bindValue instead. This was a totally different issue in that the SQL was correct but I used bindParam instead of bindValue (which is the correct method), hence this is not a duplicate.

I had to use bindValue instead – Logan Lee Feb 22 '19 at 07:14 — Logan Lee, Feb 22 '19 at 07:14

score 1 · Answer 1 · answered Feb 22 '19 at 05:56

did you try to use a placeholder for the whole part of the statement?

$sql = "select * from item where itemName like :searchfor or description like :searchfor";
$stmt = $pdo->prepare($sql);
$search_string = "'%" . $searchFor . "'%";
$stmt->bindParam(':searchfor', $search_string, PDO::PARAM_STR);

Altenatively without named params:

$sql = "select * from item where itemName like ? or description like ?";
$stmt = $pdo->prepare($sql);
$search_string = "'%" . $searchFor . "'%";
$stmt->bindParam('ss', $search_string, $search_string);

As far as I remember the manual, like need to thave the whole string in the variable, not only the content to look after.

Aug

Your second `bindParam` call is wrong. This is PDO, not MySQLi. Also, you can't re-use named parameters unless `PDO::ATTR_EMULATE_PREPARES` is on — Phil, Feb 22 '19 at 06:56

score 0 · Answer 2 · answered Feb 22 '19 at 06:28

The prepared statement's placeholder tells php to treat the specific value that is passed into the placeholder, as a string. Instead of this:

$sql = "select * from item where itemName like '%:searchfor%' or 
description like '%:searchfor%'";

Do this:

$sql = "select * from item where itemName like :searchfor or 
description like :searchfor";

Then bind whole values into the placeholders:

$stmt->bindParam(':searchfor', '%yourkeyword%', PDO::PARAM_STR);

SQL prepared statement returns no result (empty result)

2 Answers2