Mysql where clause (search where table column match)

Question

I have 1 table, t1, around 500+ data row, I just show a sample data.

Data as below:

+--------+----------+-------------------+
| id     | Name     | category          |
+--------+----------+-------------------+
| 1      | ABC      | 6,9,25,27         |
+---------------------------------------+

My mysql query like below:

$gcategory = intval($_GET['cat']);
$test = DB::fetch_all("SELECT * FROM t1 WHERE category like '%$gcategory%' ORDER BY id DESC");
foreach($test as $te){
    $list[] = $te;
}

But if $gcategory = '7'; the ABC also will appear in my $list[], but I just want when $gcategory = '6' || $gcategory = '9' || $gcategory = '25' || $gcategory = '27' then ABC only appear in my $list[]? how to fix this?

Thanks.

Ashish Patel · Accepted Answer · 2016-12-26T06:43:47.010

4

Please try like following way, you should use find_in_set php function when you finding from , seperated list of value:

$gcategory = intval($_GET['cat']);
$test = DB::fetch_all("SELECT * FROM t1 WHERE FIND_IN_SET("'.$gcategory.'", category) ORDER BY id DESC");
foreach($test as $te){
    $list[] = $te;
}

edited Dec 26 '16 at 06:43

answered Dec 26 '16 at 06:35

Ashish Patel

1,011
13
27

score 0 · Answer 2 · answered Dec 26 '16 at 06:37

0

Try using MySQL FIND_IN_SET() function

You query will be like this

"SELECT * FROM t1 WHERE FIND_IN_SET($gcategory,category) ORDER BY id DESC"

answered Dec 26 '16 at 06:37

PravinS

2,640
3
21
25

Mysql where clause (search where table column match)

2 Answers2