0

I need one help. I need to fetch value from table as per multiple ids which are in comma separated string using MySQL. I am explaining my table and query below.

db_basic:

id      special          name

 1       2,3,4,5          Raj


 2      4,2,5,6           Rahul

 3      3,5,6             Rocky

My code is given below.

$special=2;
$qry=mysqli_query($connect,"select * from db_basic where special='".$special."'");

Here I need where special=2 is present inside that comma separated string those value will be fetched. I need only proper query. Please help me.

  • you should Normalize your DB and save special column in another table, so you can achive what you want with sub query, but in your case look on http://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string – Haim Evgi Jan 12 '17 at 10:33
  • 1
    try to use 'LIKE' operator – Bilal Zafar Jan 12 '17 at 10:36
  • Possible duplicate of [MySQL query finding values in a comma separated string](http://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string) – GilZ Jan 12 '17 at 14:18

4 Answers4

0

if you current code expeted to return the first and second line of your db, you can use the LIKE operator in the query like this :

$special=2;
$qry=mysqli_query($connect,"select * from db_basic where special LIKE '%,".$special.",%'");

or LOCATE like :

$special=2;
$qry=mysqli_query($connect,"select * from db_basic where LOCATE(".$special.",special)>0");

And FIND_IN_SET : From the docco here - "This function does not work properly if the first argument contains a comma (“,”) character".

Frankich
  • 842
  • 9
  • 19
  • @ MacBooc : This `LIKE '%,".$special."',%` is throwing error. Here one , is present with %. –  Jan 12 '17 at 10:45
0

use below query

May be work for u

Use LIKE instead of "="

"select * from db_basic where special LIKE '%".$special."%'"

Kaushik Andani
  • 1,252
  • 1
  • 14
  • 22
0

use FIND_IN_SET function for ex. select * from db_basic where FIND_IN_SET(2,special)

Bhavika
  • 40
  • 4
0

Try this once,

select * from db_basic where FIND_IN_SET($special, special);

It should work.