CODEWITHSUNDEEP
phpmysql
Tomas
Tomas

Reputation: 3204

MySQL - How to select rows where value is in array?

Ok, normally I know you would do something like this if you knew the array values (1,2,3 in this case):

SELECT * WHERE id IN (1,2,3)

But I don't know the array value, I just know the value I want to find is 'stored' in the array:

SELECT * WHERE 3 IN (ids) // Where 'ids' is an array of values 1,2,3

Which doesn't work. Is there another way to do this?

Upvotes: 51

Views: 156182

Answers (5)

Samar
Samar

Reputation: 1955

WHERE JSON_CONTAINS(column_name, '"STRING"', '$') -- $ -> searching top level of JSON array

This should work generally for strings

Upvotes: 0

Aunik Rahman
Aunik Rahman

Reputation: 121

If the array element is not integer you can use something like below :

$skus  = array('LDRES10','LDRES12','LDRES11');   //sample data

if(!empty($skus)){     
    $sql = "SELECT * FROM `products` WHERE `prodCode` IN ('" . implode("','", $skus) . "') "      
}

Upvotes: 12

doug
doug

Reputation: 197

If you use the FIND_IN_SET function:

FIND_IN_SET(a, columnname) yields all the records that have "a" in them, alone or with others

AND

FIND_IN_SET(columnname, a) yields only the records that have "a" in them alone, NOT the ones with the others

So if record1 is (a,b,c) and record2 is (a)

FIND_IN_SET(columnname, a) yields only record2 whereas FIND_IN_SET(a, columnname) yields both records.

Upvotes: 8

VoteyDisciple
VoteyDisciple

Reputation: 37803

By the time the query gets to SQL you have to have already expanded the list. The easy way of doing this, if you're using IDs from some internal, trusted data source, where you can be 100% certain they're integers (e.g., if you selected them from your database earlier) is this:

$sql = 'SELECT * WHERE id IN (' . implode(',', $ids) . ')';

If your data are coming from the user, though, you'll need to ensure you're getting only integer values, perhaps most easily like so:

$sql = 'SELECT * WHERE id IN (' . implode(',', array_map('intval', $ids)) . ')';

Upvotes: 24

OMG Ponies
OMG Ponies

Reputation: 332571

Use the FIND_IN_SET function:

SELECT t.*
  FROM YOUR_TABLE t
 WHERE FIND_IN_SET(3, t.ids) > 0

Upvotes: 95

Related Questions