How to bind a parameter inside of a LIKE clause with percentages in mysqli?

Question

I'm using mysqli, and I have this query:

$q = "SELECT col1, col2 FROM `Something` WHERE col2 LIKE '%?%'";

The same query works fine if I query it outside of PHP with a word in place of ?. Within PHP, I can't do this. The error I'm getting is:

mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement[..]

The query is outputting fine in var_dump(), as is the parameter itself.

Here's the bind_param():

$stmt->bind_param("s", $param);

What am I doing wrong?

@JimL ...I can't believe how simple that was, and that I hadn't thought of it, lol. That's perfect. If you'd like to post it as an answer, it will be accepted. — Mark Buffalo, Jan 17 '16 at 20:55
I'm sure it's a duplicate, hopefully someone can tag it - for some weird reason I had trouble finding a duplicate — JimL, Jan 17 '16 at 20:58
I was not able to find a duplicate, hence the post. Suggestions were for PDO, and the answers didn't work with mysqli. — Mark Buffalo, Jan 17 '16 at 20:59

score 11 · Accepted Answer · answered Jan 17 '16 at 21:01

11

It's just a matter of placement of the signs

$q = "SELECT col1, col2 FROM `Something` WHERE col2 LIKE ?";

$stmt->bind_param("s", '%' . $param . '%');

I know this has thrown off a lot of people, you're not the first :)

answered Jan 17 '16 at 21:01

JimL

Note that if your PHP configuration is set to throw an error when passing parameters by value when references are expected (which I think should be recommended), then you need to create a temporary variable and pass it as shown by Justin in [his answer](https://stackoverflow.com/a/52898138/1652488) because bind_param expects references. – jytou Mar 18 '23 at 13:10

score 3 · Answer 2 · edited Aug 27 '21 at 20:40

3

To get this working I had to do this (similar to the correct answer)...

$param = '%' . $param . '%'

$q = "SELECT col1, col2 FROM `Something` WHERE col2 LIKE ?";

$stmt->bind_param("s", $param );

edited Aug 27 '21 at 20:40

Dharman

answered Oct 19 '18 at 18:31

This works just as well as the answer by JimL – Nerdi.org Apr 01 '21 at 15:55
@Nerdi.org In fact, storing in a temporary variable is necessary because bind_param() expects references to be passed, not values - it even throws an error in some versions/configurations. – jytou Mar 18 '23 at 13:05

2 Answers2