CODEWITHSUNDEEP
phpmysqlsql
Zera42
Zera42

Reputation: 2692

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

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.

Upvotes: 0

Views: 1052

Answers (2)

Jason McCreary
Jason McCreary

Reputation: 73031

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.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271161

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 %'

Upvotes: 3

Related Questions