51

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?

Improve this question
6
  • In what programming language are you writing the query? Commented Oct 27, 2010 at 20:01
  • I'm using PHP and MySQL. Commented Oct 27, 2010 at 20:03
  • it's just text for now, that's another part I guess I'm not sure if it needs to be varchar, text or what have you. Commented Oct 27, 2010 at 20:04
  • Have you tried to define a SubQuery to return the ids instead of your ids array as you called!! Commented Oct 27, 2010 at 20:05
  • 3
    Wait, I may have misunderstood. Is ids another field in your database somehow? Or is it a PHP variable? If it's a PHP variable, is it an array, or a string? Commented Oct 27, 2010 at 20:05

5 Answers 5

Reset to default
95

Use the FIND_IN_SET function:

SELECT t.*
  FROM YOUR_TABLE t
 WHERE FIND_IN_SET(3, t.ids) > 0
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

You sir, are a star. I searched high and low for it and knew it had to be there. My instincts told me they should have used CONTAINS for this, but what do I know.
@Tomas: Only MySQL has that function - any other database, you'd have to build something yourself.
I don't plan on switching... but that's definitely good to know.
@OMGPonies you are my hero!
@OMGPonies It it what I was looking for. Thanks a lot. +1
25

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)) . ')';
Improve this answer

4 Comments

+1 for array_map('intval', ...). You could also use mysql_real_escape_string() instead of intval(), which would leave the integer validation to MySQL, but would require extra logic to quote the array elements.
Thanks, but as I stated, I know the id and didn't need to search for rows based on a known set. The other way around as answered below.
i believe the second example should be: $sql = 'SELECT * WHERE id IN (' . implode(',', array_map('intval', $ids)) . ')';
Yes, it should! I've fixed that.
12

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) . "') "      
}
Improve this answer

1 Comment

Is it posible to append the query LIMIT value with it's offset ?
8

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.

Improve this answer

Comments

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

This should work generally for strings

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.