MySqli prepare statement error when used for LIKE

Question

I'm trying to make a prepared statement for a LIKE query using php's mysqli extension. But no matter what I try, I always get this error:

Fatal error: Problem preparing query (SELECT f.*,r.slug FROM `foods` AS f INNER JOIN `resturants` AS r ON f.`rest_id` = r.`rest_id` WHERE f.`name` LIKE CONCAT('%',"f", '%')) 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 ''%',"f", '%')' at line 4 in /path/to/class.mysqli.php on line 462

I have tried the following queries to no avail:
(The $s would be my string to search against.)

SELECT f.*,r.slug FROM `foods` AS f
INNER JOIN `resturants` AS r
ON f.`rest_id` = r.`rest_id`
WHERE f.`name` LIKE '%?%'

SELECT f.*,r.slug FROM `foods` AS f
INNER JOIN `resturants` AS r
ON f.`rest_id` = r.`rest_id`
WHERE f.`name` LIKE CONCAT('%', ?, '%')


SELECT f.*,r.slug FROM `foods` AS f
INNER JOIN `resturants` AS r
ON f.`rest_id` = r.`rest_id`
WHERE f.`name` LIKE CONCAT('%', {$s}, '%')


SELECT f.*,r.slug FROM `foods` AS f
INNER JOIN `resturants` AS r
ON f.`rest_id` = r.`rest_id`
WHERE f.`name` LIKE '%{$s}%'

Even:

sprintf("SELECT f.*,r.slug FROM `foods` AS f
INNER JOIN `resturants` AS r
ON f.`rest_id` = r.`rest_id`
WHERE f.`name` LIKE '%%%s%%'", $s)

Help me please, I'm getting frustrated.

Hm, `CONCAT('%', ?, '%')` should work AFAIK..... And after some testing, it actually DOES work in MySQL (did no check `mysqli` itself). Although... are you using ACTUAL `mysqli`, or mocked? Because the `/path/to/class.mysqli.php` confuses me... — Wrikken, Mar 05 '13 at 17:14
It's wrapper class. But it basically comes down to the mysqli object. — 2hamed, Mar 05 '13 at 17:34
But _is_ it wrapping `mysqii`, or wrapping something else? And.... You probably need to show that class. — Wrikken, Mar 05 '13 at 19:06

PaulP · Accepted Answer · 2013-03-05T18:44:23.877

I would move expression after LIKE to variable:

$param = '%somestring%';

$sql = "SELECT f.*,r.slug FROM `foods` AS f
INNER JOIN `resturants` AS r
ON f.`rest_id` = r.`rest_id`
WHERE f.`name` LIKE ?"

UPDATE:

Maybe this will help

-- test.sql
CREATE TABLE supportContacts (
     id int auto_increment primary key, 
     type varchar(20), 
     details varchar(30)
);

INSERT INTO supportContacts
(type, details)
VALUES
('Email', 'admin@sqlfiddle.com'),
('Twitter', '@sqlfiddle');

<?php
// test.php
$mysqli = new mysqli("localhost", "root", "root", "test");
$sql = 'SELECT type FROM supportContacts WHERE type LIKE ?'; // here is only ?, no %

$stmt = $mysqli->prepare($sql);
$type = 'E%'; // and here you can put % sign
$stmt->bind_param('s', $type);
$stmt->execute();
$stmt->bind_result($result);
$stmt->fetch();
var_dump($result);

Instead LIKE '%?%' (first sql statement) write LIKE ? and in binding params pass your value with '%'. — PaulP, Mar 05 '13 at 17:45

score 0 · Answer 2 · answered Mar 05 '13 at 17:09

0

Your %'s need to be encapsulated in quotes too.

answered Mar 05 '13 at 17:09

Martin

6,632
4
25
28

What do you mean by encapsulated? You mean separately? – 2hamed Mar 05 '13 at 17:37

MySqli prepare statement error when used for LIKE

2 Answers2

Linked