PHP Binding a Wildcard

Question

Hope Somebody will help me about how I bind a parameter in mysqli when a multiple character wildcard needs to be next to the variable value. I found that it worked for me when creating a SQL statement, like this:

$sql = "SELECT item_title FROM item WHERE item_title LIKE '%$title%'";

However, I tried to bind the variable following the same pattern, and found that it failed. They used this code:

$sql = "SELECT item_title FROM item WHERE item_title LIKE '%?%'";

It raised this error:

Warning: mysqli_stmt_bind_param() [function.mysqli-stmt-bind-param]: Number of variables doesn't match number of parameters in prepared statement in program_name on line......

Can anybody tell me how fix this problem? Thank you.

Try either `LIKE ?` then `bind_param("%".$string."%")` or `LIKE '%' || ? || '%'`. — Passerby, Feb 14 '13 at 09:56

score 5 · Answer 1 · answered Feb 14 '13 at 09:56

5

You can bind only data literals not arbitrary query parts.
So, prepare your literal first

$var = "%$var%";
$sql = "SELECT item_title FROM item WHERE item_title LIKE ?";

answered Feb 14 '13 at 09:56

Your Common Sense

156,878
40
214
345

thanks for reply. I used same variable twice in where clause in my query, Like this - WHERE (s2.subject_name LIKE '%$keyword%' OR c.city_name LIKE '%$keyword%' ). Then can you tell me how I bind this variables. – TNK Feb 14 '13 at 10:20
@TharangaNuwan I have updated answer as per your comments, see below – Minesh Feb 14 '13 at 10:34
Doesn't this leave a security hole. Specifically if the $var = 'hello%world' you have an unintended placeholder token. – danielson317 Jul 04 '18 at 20:11

Minesh · Answer 2 · 2013-02-14T10:33:48.337

You can do like this way:

$sql = "SELECT item_title FROM item WHERE item_title LIKE ? ";

and then

$title_new =  '%'.$title.'%';
mysqli_stmt_bind_param($stmt, 's', $title_new);

Updated based on user's comment

To implement below SQL

 s2.subject_name LIKE '%$keyword%' OR c.city_name LIKE '%$keyword%'

Use below MySqli statement s2.subject_name LIKE ? OR c.city_name LIKE ?

 $keyword = '%'.$keyword.'%';
 mysqli_stmt_bind_param($stmt, 'ss', $keyword, $keyword);

score 1 · Answer 3 · answered Feb 22 '13 at 20:45

1

   $sql="SELECT item_title FROM item WHERE item_title LIKE concat ('%',?,'%') ";

answered Feb 22 '13 at 20:45

Bhaskar Bhatt

1,399
13
19

score 0 · Answer 4 · answered Feb 22 '13 at 20:59

have one question mark per bind variable. For example prepare(SELECT item_title FROM item WHERE item_title LIKE ? and name2 like ? and ...) The nice thing about prepared statements is that you don't need to worry about quoting the variables.

Then bind all parameters like bind_param("ss...", $param1, $param2, ....);

PHP Binding a Wildcard

4 Answers4

Linked

Related