[Phpwm] mysql in() lists

[David Goodwin]

Phil Beynon wrote :
> Hi all,
> 
> Is there a more efficient way of making in() list sql other than
> concatenating them together?
> 
> Currently I'm doing this;
> 

$sql = "select user_name from imageasset where user_name like '%DUMMY_IMAGE.jpg'";
$cleanup = mysql_query($sql); 
if(!$cleanup){
	echo("<p>Error in select cleanup dummy image E66 : " . mysql_error() . "</p>"); 
	exit();
}
if(mysql_num_rows($cleanup) >= 1){
	while($row = mysql_fetch_array($cleanup,MYSQL_ASSOC)){
		$in_name = $in_name .  $row["user_name"] . "','";
	}
	$in_name = substr_replace($in_name,"",(strlen($in_name)-3));
}

(Sorry for reindenting it, but I found it hard to read)

> 
> which then results in a nice little string along the lines of;
> 
> username1.jpg','username2.jpg','username3.jpg','....usernamex.jpg','
> 
> The substr_replace just trims the last ',' off the end.
> The % in the sql is because there is a random precursor sequence added to
> the filenames.
> 
> This then gets used as;
> 
> $sql = "select user_name from Type_B_Image_Tracking where user_name like
> '%DUMMY_IMAGE.jpg' and user_name not in('$in_name')";

Why can't you do :

SELECT user_name FROM Type_B_Image_Tracking where 
	user_name like '%DUMMY_IMAGE.jpg' and 
	user_name not in (
		select user_name from imageasset where user_name like '%DUMMY_IMAGE.jpg') 


??

You could remove the "," from the end of the string in a better manner
too, perhaps something like :


$ending = "";
while($something) {
	$foo = $ending . " blah blah blah";
	$ending = ",";
}

Technically your code is vulnerable to sql injection where you
concatenate stuff together (i.e. the file names in the db could contain
"'").

(I've obviously not tried the SQL, and have little understanding of what
you're trying to do.... so I could be talking/writing rubbish)

David.
-- 
David Goodwin 

[ david at codepoets dot co dot uk ]
[ http://www.codepoets.co.uk       ]