MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

PHP Binding a Wildcard

with 8 comments

Somebody asked me about how you bind a parameter in mysqli when a multiple character wildcard needs to be next to the variable value. They’d found that it worked when creating a SQL statement by concatenation (string gluing), like this:

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

However, they tried to bind the variable following the same pattern, and found that it failed. They used the following type of syntax:

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

It raised the following 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 #

The reason is the parser, it expects variables to be independent tokens in the SQL string. You can’t bind a string into the shell of an apostrophe delimited string. You could naturally, make an assignment to the local PHP variable before binding it to the variable, like this:

$title = '%'.$title.'%';

It’s actually a better idea to concatenate the multiple character wildcard operator inside the SQL statement. The correct syntax requires that you use the CONCAT() function. You could reset to use piped concatenation but generally you should avoid that on the MySQL platform (see this post for an explanation of SQL concatenation on Oracle, MySQL, and SQL Server).

This is the required statement when using a MySQL database:

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

Written by maclochlainn

February 21st, 2010 at 1:13 am

Posted in LAMP,MAMP,MySQL,PHP

8 Responses to 'PHP Binding a Wildcard'

Subscribe to comments with RSS or TrackBack to 'PHP Binding a Wildcard'.

  1. Sometimes one just doesn’t see the clear and simple of the solution. Thanks for the quick and fresh tip :)

    Omarcs

    21 Jul 10 at 12:11 pm

  2. Great! Thanks!

    Constantin

    31 Jul 10 at 7:28 am

  3. Thanks , i’m stuck in this sql query , finally it’s ok thank’s to you man thanks a lot

    lee

    4 Sep 10 at 11:08 am

  4. $sql = “SELECT item_title FROM item WHERE item_title LIKE CONCAT(‘%’,?,’%’)”;

    It works….Thanks

    NoIdea

    3 Mar 11 at 6:54 am

  5. […] here’s one that works using a static query […]

    PHP/MySQL Query

    14 Jul 12 at 11:59 pm

  6. […] Well, here’s one that works using a static query. If you want to use a prepared statement, check this earlier post. […]

  7. Thanks. I was having trouble getting the syntax right with a variable on the LIKE ‘%”.$title.”%'”. You seem to be the only one that uses variables :-), or at least gives an example of one.

    Fred Dearman

    28 Jun 13 at 4:02 am

Leave a Reply