CODEWITHSUNDEEP
phpmysql
Mohammad Intsar
Mohammad Intsar

Reputation: 463

How to use IN statement in sql when both of side have multiple values

I have a problem. I want to know how to use IN statement (in a query) when both sides have multiple values - I am writing a query :

SELECT name FROM math_Details WHERE cric_ids in (1,2,3,4,8);

In this query problem is in cric_ids column there is also multiple values seprated through (,comma); like (5,9,1,10,2)

Please help me?

Upvotes: 2

Views: 310

Answers (2)

jcho360
jcho360

Reputation: 3759

oh man, your design is so mess up that what you are asking is almost impossible, what I understand is that you have:

    Select name from math_Details where cric_ids in (1,2,3,4,8);

and the column looks like this:

| cric_ids |
-------------
|1,2,3,5,4 |
|5,8,6,12,3|
|78,6,2,4,6|
|5,7,8,9,1 |

and you want to be able to make search by digits....

the like wont help you because the result is a string or varchar, so your possible options could be (beside that you need to use a 'like' for each option)

critics like '1' //wont work at least you only have 1 in your cric_ids
critics like '%1' // will work only if 1 is the last value
critics like '1%' //will work only if 1 is the first vaue
critics like '%1%' // will give you all values with 1, like 1, 10, 11, 111, 19, etc...

I think you have 2 options,

  1. make a full query and with PHP functions get the values (you can convert it to into an array and get the values), it will be SO SLOW!.....
  2. Redesign your database (recommended) to fix that and futures conflicts.

Please take a look to database entity relationship model and normalization.

Upvotes: 0

eggyal
eggyal

Reputation: 125865

You could use MySQL's FIND_IN_SET() function repeatedly:

SELECT name
FROM   math_Details
WHERE  FIND_IN_SET(1, cric_ids) OR FIND_IN_SET(2, cric_ids)
    OR FIND_IN_SET(3, cric_ids) OR FIND_IN_SET(4, cric_ids)
    OR FIND_IN_SET(8, cric_ids)

Such a statement could be built dynamically in PHP. For example, using prepared statements in PDO:

$set = [1,2,3,4,8];
$sql = 'SELECT name FROM math_Details WHERE FALSE'
     . str_repeat(' OR FIND_IN_SET(?, cric_ids)', count($set));

$qry = $dbh->prepare($sql);
$qry->execute($set)

However, as others have said, this is an indication of poor database design. You should consider normalising your data structure so that instead of a cric_ids field, you have a table relating records in math_Details with each cric.

Upvotes: 1

Related Questions