How to Create a Pdo Parameterized Query With a Like Statement

How do I create a PDO parameterized query with a LIKE statement?

Figured it out right after I posted:

$query = $database->prepare('SELECT * FROM table WHERE column LIKE ?');
$query->execute(array('value%'));

while ($results = $query->fetch())
{
    echo $results['column'];
}

implement LIKE query in PDO

You have to include the % signs in the $params, not in the query:

$query = "SELECT * FROM tbl WHERE address LIKE ? OR address LIKE ?";
$params = array("%$var1%", "%$var2%");
$stmt = $handle->prepare($query);
$stmt->execute($params);

If you'd look at the generated query in your previous code, you'd see something like SELECT * FROM tbl WHERE address LIKE '%"foo"%' OR address LIKE '%"bar"%', because the prepared statement is quoting your values inside of an already quoted string.

How can create parameterized query with AND and Like operator using PDO?

If you've read prepare manual carefully, you will notice that

The SQL statement can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed. You cannot use both named and question mark parameter markers within the same SQL statement; pick one or the other parameter style.

So, proper queries are:

$PromotionSts=$this->pdoConnection->prepare("SELECT * FROM `ws_promotion` AS prm LEFT JOIN `ws_pro_cat` AS prmCat ON prm.`pm_id` = prmCat.`pm_id` 
                WHERE prmCat.`cat_id` = :cat_id AND prm.`pm_title` LIKE :pm_title");

// Both query parameteres are named parameters
$PromotionSts->bindParam(':cat_id', $cat_id);
// variables in single quotes are NOT parsed
$PromotionSts->bindParam(':pm_title', '%' . $pm_title . '%');
// this lines are useless
//$cat_id = $cat_id;  
//$pm_title = $pm_title;
$PromotionSts->execute();

Or:

$PromotionSts=$this->pdoConnection->prepare("SELECT * FROM `ws_promotion` AS prm LEFT JOIN `ws_pro_cat` AS prmCat ON prm.`pm_id` = prmCat.`pm_id` 
                WHERE prmCat.`cat_id` = ? AND prm.`pm_title` LIKE ? ");
// Both query parameteres are ? marks
$PromotionSts->execute(array(3,'%Essa%'));

How to escape 'LIKE' statement in custom PDO query

You need to put the wildcard match characters in the placeholder, not in the query, so instead of doing:

$search = 'find this string';
$db::query("SELECT ... FROM table WHERE col LIKE '%?%' ");

You do:

$search = '%find this string%';
$db::query("SELECT ... FROM table WHERE col LIKE ? ");

Using A PDO Prepared Statement On A Search Form Query - PHP

Alternative 1:

$sql = "SELECT imgp.*, u.id, u.firstname, u.lastname
  FROM imageposts AS imgp
  INNER JOIN users AS u ON imgp.user_id = u.id 
  WHERE image_title LIKE CONCAT('%', ?, '%')";
$stmt = $connection->prepare($sql);
$stmt->execute([$searchQuery]);

Alternative 2:

$sql = "SELECT imgp.*, u.id, u.firstname, u.lastname
  FROM imageposts AS imgp
  INNER JOIN users AS u ON imgp.user_id = u.id 
  WHERE image_title LIKE ?";
$stmt = $connection->prepare($sql);
$stmt->execute(["%{$searchQuery}%"]);

How to use bindValue with LIKE operator in SQL query?

You probably want :

$query = "SELECT * 
          FROM books
          WHERE title LIKE CONCAT( '%', :title, '%')";
...
...
statement->bindValue(':title', $title, PDO::PARAM_STR);

The bind parameter should be used as a litteral string. CONCAT can be used to concatenate the parameter with percent signs on both ends.

---

Related Topics