I need to search field value in LIKE pattern of sql statement

Question

My table looks like this

Id  | name   
---------------
1   |  clinker
2   |  gypsum

Now I need to prepare a search based on the 'name' field. here is the select statement

select * from table where name like '%$keyword%'

Now, suppose the keyword is 'clinker 1', and I want first row of my table as result set. How can I accomplish that?

`select * from table where name like '%$keyword%' OR '$keyword' LIKE CONCAT(name,'%')` — JimmyB, Sep 01 '15 at 09:53
that's no clear, how can you match the row with ID 1 if the keyword is "clinker 1"?? — , Sep 01 '15 at 09:54
possible duplicate of [php mysqli prepared statement LIKE](http://stackoverflow.com/questions/18527659/php-mysqli-prepared-statement-like) — Your Common Sense, Sep 01 '15 at 09:54
that 1 in keyword is not Id, it can be any thing like 'clinker a' or ' 3 clinker' or '_ clinker _' — Gaurav Singh, Sep 01 '15 at 09:57
There is no easy way to accomplish what you want using the MySQL `LIKE` operator. I have seen this question asked before on Stack Overflow though I cannot remember where. — Tim Biegeleisen, Sep 01 '15 at 09:58

score -1 · Answer 1 · answered Sep 01 '15 at 10:05

You'll need to split the $keyword and expand your query to match any word in the $keyword list:

$keywordList = explode(' ', $keyword);
$sql = 'select * from table where ';
while (!empty($keywordList)) {
    $likeKeyword = array_shift($keywordList);
    $sql .= "keyword like '%{$likeKeyword}%'";
    if (!empty($keywordList))
        $sql .= " or ";
}
//...run your query here

So if the $keyword is clinker 1, the resulting query would be:

select * from table where keyword like '%clinker%' or keyword like '%1%'

JimmyB · Accepted Answer · 2015-09-01T10:19:36.437

-1

Not quite sure about MySQL, but LIKE should work with any character expression on either or both sides. That includes both columns and literals. Hence, the LIKE can be used the other way around to find the inverse matches:

SELECT *
FROM table
WHERE name LIKE '%$keyword%'
OR '$keyword' LIKE CONCAT('%', name, '%')

edited Sep 01 '15 at 10:19

answered Sep 01 '15 at 10:11

JimmyB

12,101
2
28
44

I need to search field value in LIKE pattern of sql statement

2 Answers2