[Phpwm] mysql in() lists

[Dave Holmes]

Alternative get rid of the loop altogether and use the implode command!

$csv_string = implode(",",$array); 

Let the underlying C code take the strain :) 

-----Original Message-----
From: phpwm-bounces at mailman.lug.org.uk
[mailto:phpwm-bounces at mailman.lug.org.uk] On Behalf Of David Goodwin
Sent: 02 October 2006 14:40
To: West Midlands PHP User Group
Subject: Re: [Phpwm] mysql in() lists

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       ]

_______________________________________________
Phpwm mailing list
Phpwm at mailman.lug.org.uk
https://mailman.lug.org.uk/mailman/listinfo/phpwm
Wiki: http://wiki.phpwm.org