How to bind LIKE values using the PDO extension?

In this query

select wrd from tablename WHERE wrd LIKE '$partial%'

I'm trying to bind the variable '$partial%' with PDO. Not sure how this works with the % at the end.

Would it be

select wrd from tablename WHERE wrd LIKE ':partial%'

where :partial is bound to $partial="somet"

or would it be

select wrd from tablename WHERE wrd LIKE ':partial'

where :partial is bound to $partial="somet%"

or would 开发者_如何学编程it be something entirely different?

---

You could also say:

SELECT wrd FROM tablename WHERE wrd LIKE CONCAT(:partial, '%')

to do the string joining at the MySQL end, not that there's any particular reason to in this case.

Things get a bit more tricky if the partial wrd you are looking for can itself contain a percent or underscore character (since those have special meaning for the LIKE operator) or a backslash (which MySQL uses as another layer of escaping in the LIKE operator — incorrectly, according to the ANSI SQL standard).

Hopefully that doesn't affect you, but if you do need to get that case right, here's the messy solution:

$stmt= $db->prepare("SELECT wrd FROM tablename WHERE wrd LIKE :term ESCAPE '+'");
$escaped= str_replace(array('+', '%', '_'), array('++', '+%', '+_'), $var);
$stmt->bindParam(':term', $escaped);

---

$var = "partial%";
$stmt = $dbh->prepare("select wrd from tablename WHERE wrd LIKE :partial");
$stmt->bindParam(":partial", $var);
$stmt->execute(); // or $stmt->execute(array(':partial' => $var)); without 
                  // first calling bindParam()
$rs = $stmt->fetchAll();

Using question mark parameters:

$stmt = $dbh->prepare('select wrd from tablename WHERE wrd LIKE ?');
$stmt->execute(array('partial%'));
$rs = $stmt->fetchAll();

http://www.php.net/manual/en/pdo.prepare.php

---

You can use addcslashes before prepared statement. I tested on mysql.

$value = addcslashes($value, '%');
$stmt = $db->prepare('select * from products where description like ?');
$stmt->execute(["$value%"]);

---

I think the accepted answer (by @bobince) can be simplified a bit.

You can reduce it to something like this to handle underscore, percentage, etc in the param but still match the LIKE query with partial%:

$stmt = $dbh->prepare("select wrd from tablename WHERE wrd LIKE :partial");
$stmt->execute([":partial" => addcslashes($value, '_%') . "%"]);
$rows = $stmt->fetchAll();

---

The below code it shows only the first keywords in the database!

"SELECT wrd FROM tablename WHERE wrd LIKE CONCAT(:partial, '%')"

Try this one if you want to search all the keywords from the database

"SELECT wrd FROM tablename WHERE wrd LIKE :partial";
$stmt->execute(array(':partial'=>'%'.$YourVarHere.'%'));

---

This is how you should do it

bindValue(':partial', '%' . $_GET['partial'] . '%');

Thanks,

Qwerty

---

Who has written the answare (may be karim79):

$var ="partial%"
$stmt =$dbh->prepare("select wrd from tablename WHERE wrd LIKE :partial")
$stmt->bindParam(":partial",$var)
$stmt->execute(); //or$stmt->execute(array(':partial'=>$var)); without 
                  // first calling bindParam()
$rs =$stmt->fetchAll();

Using question mark parameters:

$stmt =$dbh->prepare('select wrd from tablename WHERE wrd LIKE ?');
$stmt->execute(array('partial%'));
$rs =$stmt->fetchAll();

Many thanks to him. I was searching for the code & saw many examples, but i couldn't resolve my issue. This time I have succeed to do it. I used the 'Using question mark parameters:' section of the code.

For others help, if you want to retrieve the value from a variable you may change the code to

$stmt->execute(array($variable.'%'));

instead of

$stmt->execute(array('partial%'));

Because the word 'partial' is specified in the answer and can't be changed. Thanks a lot.