Why are parenthesis required for calculated 'like' clause in prepared statement?

Question

I thought that I'd add a clause similar to the first line of the following to a prepared statement in PHP to run in MySQL:

$sql .= " where ? like '%'+name+'%' ";

if ($stmt = mysqli_prepare($con, $sql)) {    
//irrelevant code omitted here.
} else {
    echo("Error description: " . mysqli_error($con));
}

where name is a column in the table and ? is a parameter to be bound.

I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+name+'%' at line 1

If I replace ? with 'fred' and run it in MySQL workbench it will run. Similarly it works if I just remove the concatenation so that it starts $sql .= " where ? like name ";

However if I just change it like so:

? like ('%'+name+'%')

then it runs. Anyone know why please? I ask because I feel that there's something to understand here and I'm not getting it. In particular I'd like to know if there are other scenarios where I'll have to add arbitrary parentheses when using prepared statements?

Also, read up on `LIKE` http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like - You shouldn't have `+` signs in there. — Funk Forty Niner, Oct 04 '14 at 20:40

score 3 · Accepted Answer · answered Oct 04 '14 at 20:22

3

Use CONCAT(str1,str2,...) to concatenate:

$sql .= " where ? like CONCAT('%', name, '%')";

+ - is addition operator for numbers in MySQL, see: http://dev.mysql.com/doc/refman/5.7/en/arithmetic-functions.html#operator_plus

answered Oct 04 '14 at 20:22

Rimas

5,904
2
26
38

1

I can't see why this was upvoted. You can't bind columns. `WHERE ?` is invalid. OP: *"If I replace `?` with `'fred'` and run it in MySQL workbench it will run."* – Funk Forty Niner Oct 04 '14 at 20:33
1

@Fred-ii- you are not right. `WHERE ? LIKE samething` is valid. To `?` OP is binding string value, not column name. See LIKE documentation: http://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html#operator_like – Rimas Oct 04 '14 at 21:01
@Fred-ii- did you read [LIKE documenation](http://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html#operator_like)? In documentation: `expr LIKE pat` - `expr` can be not only column name, but also any string value. Also look at example: `SELECT 'abc' LIKE 'ABC';` - did you see any column name in this example? – Rimas Oct 04 '14 at 21:43
I'm well aware of that (LIKE). I am talking about `WHERE ?` – Funk Forty Niner Oct 04 '14 at 21:52
@Fred-ii- `... WHERE ? LIKE pat` is valid and actually works. Here's test: http://phpfiddle.org/lite/code/7zkk-iv7a – Rimas Oct 06 '14 at 08:56
Well that is the most bizarre thing I've ever seen. I've always been told and seen it countless times that using a question mark for table or column could not be used. I guess I stand corrected then. I will +1 your answer and OP should accept it. Thanks for setting it up, *cheers*. – Funk Forty Niner Oct 06 '14 at 14:00

score -1 · Answer 2 · edited Oct 04 '14 at 20:30

-1

Actually i thing it should be like this:

$sql .= " where ? like '%name%'";

edited Oct 04 '14 at 20:30

Funk Forty Niner

74,450
15
68
141

answered Oct 04 '14 at 20:21

c0utinh0

179
1
8

This will compare against the literal value 'name' instead of the value in the name column. – miken32 Oct 04 '14 at 20:26
FYI: Doing `where ?` is invalid. You can't bind tables and/or columns. You can do `where $variable` though. – Funk Forty Niner Oct 04 '14 at 20:31

Why are parenthesis required for calculated 'like' clause in prepared statement?

2 Answers2