MySQL IN() question

Question

I have a numerical field called category_id in my table. I want to do something like this.

$ids=implode(',',$id_array);
$sql="SELECT * FROM myTbl WHERE IN(category_id,'$ids')";

Which should output something like:

SELECT * FROM myTbl WHERE IN(category_id,'1,2,3,4,5,6');

Is this possible and am I using the right syntax for this? Is there a better way to do this?

Answer 1

Almost, but not quite - here is one way it could work

$ids="'".implode("','",$id_array)."'";
$sql="SELECT * FROM myTbl WHERE category_id IN($ids)";

Which should output something like:

SELECT * FROM myTbl WHERE category_id IN('1', '2', '3', '4', '5', '6');

Note that since the field is numeric, the quotes are unnecessary but it's a useful code idiom building lists of quoted strings. In your case you could simplify to

$ids=implode(',',$id_array);

(I'm assuming the array isn't empty, the array elements are santized etc for clarity!)

Answer 2

Yes, syntax for mysql is this:

SELECT * FROM myTbl WHERE category_id IN(1,2,3,4,5,6);

(because the values are ints, they don't need quotes around. If they were strings, they would each need enclosing in their own quotes - category_id IN ( 'a', 'b')

Answer 3

From where do you get the id array? If it's from the database you should consider doing it all there:

SELECT * FROM myTbl WHERE c_id IN (SELECT c_id FROM yourTable WHERE ...);