Dynamic lists for IN clause...Please?

[heyrad]

So, I want to query my audio database and find all the audio files that contain a specific foreign key. However, I have a list of foreign keys that I want the query to search for.

I wanted to use a statement like this:

// list of foreign keys
$list = array('123','456',789');

// make $list a string
$newlist = implode(",",$list);

$q = sprintf("SELECT * from TableName where ColumnName IN(%s)", $newlist); Keep in mind, the explode function didn't add single qoutes to each element.

I don't seem to be getting the results I want as the IN clause is a bit tempermental.

Anybody have a workaround for dynamically creating lists for the IN clause? REMEMBER... IN CLAUSE LIKES QUOTED STRINGS.

thanks

[bastien]

$q = sprintf("SELECT * from TableName where ColumnName IN($newlist);

[superwormy]

You're code doesn't work becuase you end up with a query like this:

SELECT * FROM whatever WHERE field IN ( 'value1,value2,value3' )

and it SHOUDL look like this:

SELECT * FROM whatever WHERE field IN ('value1','value2','value3' )

[bastien]

$counter=count($list); //count the elements

$sql="SELECT * FROM whatever WHERE field IN "

for ($i=0;$i<$count;$i++){

if ($i==($count-1)){
$sql.=$list[$i];
}else{
$sql.=$list[$i].",";
}
}

couse this would need some adjustment to see if the array only had one element, but it should go

hth

[ahundiak]

The IN clause works fine without quotes as long as your keys are integers which is usually the case.

You might want to take a closer look at where you are getting the id's from and make sure you actually have them.

One thing the IN clause does not like is empty strings so always make sure you have at least one id to check.