Search/Querying a mysql database with a keyword with partial match being OK

Question

public function search($search)
{
    global $pdo;
    $query = $pdo->prepare('SELECT * FROM items WHERE item_name = ?');
    $query->bindValue(1, $search);
    $query->execute();
    return $query->fetchAll();
}

with that function if I called it, and say $search was cookie.

and inside my database I have

chocolate chip cookie
oatmeal cookie
cookie

it would only return cookie So how could I fix my query so it returns all the item_names that are a partial match or contain cookie also? Thanks.

score 3 · Answer 1 · edited Jul 10 '13 at 02:51

3

You would use like:

where item_name like '%cookie%'

If you want to look for a full word match:

where concat(' ', item_name, ' ') like '% cookie %'

edited Jul 10 '13 at 02:51

Jason McCreary

71,546
23
135
174

answered Jul 10 '13 at 02:49

Gordon Linoff

1,242,037
58
646
786

1

could I do `WHERE item_name LIKE %?%` or how would that work if I'm binding it? – Zera42 Jul 10 '13 at 02:53
That should work, although you need the single quotes around the pattern. – Gordon Linoff Jul 10 '13 at 02:54
`$pdo->prepare("SELECT * FROM items WHERE item_name LIKE '%?%'");` would be good? – Zera42 Jul 10 '13 at 02:55

score 3 · Accepted Answer · edited May 23 '17 at 12:29

3

For completeness, or as an alternative to LIKE, you could use LOCATE() or INSTR():

SELECT * FROM table WHERE LOCATE('cookie', item_name);

Note: If you're curious about performance, here's some old benchmarks.

edited May 23 '17 at 12:29

Community

1
1

answered Jul 10 '13 at 02:56

Jason McCreary

71,546
23
135
174

If you need word boundaries, you can use `CONCAT()` as shown in **Gordon Linoff**'s answer. – Jason McCreary Jul 10 '13 at 12:15
Oh my gosh, this is so great. I've been looking for something like this all morning. – Chelsea Aug 31 '14 at 13:26

Search/Querying a mysql database with a keyword with partial match being OK

2 Answers2