Generating UPDATE in loop, how to deal with pesky comma - SOLVED

["Daevid Vincent" <daevid@xxxxxxxxxx>]

Well, how about that. Using PHP, it just get's better each time! I found
this little snippet and adjusted accordingly...

http://us2.php.net/manual/en/function.implode.php

while(list($column, $value) = each($FOO))
{ 
	$upd[] = " $column = '$value'"; //this will loop until sql is built 
} 
$SQL = "UPDATE table SET ".implode(',',$upd)." WHERE blah = 3";
//now do it 
$result = mysql_query($SQL,$db);

Daevid Vincent
http://daevid.com
  

> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@xxxxxxxxxx] 
> Sent: Wednesday, November 26, 2003 4:54 PM
> To: mysql@xxxxxxxxxxxxxxx
> Subject: Generating UPDATE in loop, how to deal with pesky comma
> 
> I'm generating a dynamic UPDATE command in a loop. The 
> problem is the stupid
> comma.
> 
> Given this general idea, how do I handle the pesky comma. 
> It's either in the
> front and in the way, or on the trailing end and in the way...
> 
> I either end up with something like this:
> mysql> UPDATE contact_table SET , contact_fname = 'Helen', 
> contact_phone =
> '(132) 316-1972' WHERE contact_id = '9999999999' LIMIT 1;
> 
> Or this:
> mysql> UPDATE contact_table SET contact_fname = 'Helen', 
> contact_phone =
> '(132) 316-1972', WHERE contact_id = '9999999999' LIMIT 1;
> 
> Depending on where I place my comma.
> 
> Is there some 'place holder' command, like "1=1" or something 
> that I could
> use like so:
> 	$SQL = "UPDATE ".$table." SET 1=1 ";
> 
> So that the , will work properly?
> 
> I tried a few things, but all give syntax errors...
> 
> mysql> UPDATE contact_table SET 1=1, contact_fname = 'Helen', 
> contact_lname
> = 'Wadel', contact_phone = '(132) 316-1972', contact_address1 = 'Main
> Street', contact_address2 = 'Apartment 23', contact_city = 'Anyplace',
> contact_state = 'HI' WHERE contact_id = '9999999999' LIMIT 1;
> 
> 
> --------- snip ------------
> 
> 
> 	$SQL = "UPDATE ".$table." SET ";
> 		
> 	foreach($FIELDS as $field)
> 	{
> 		switch($field['type'])
> 		{
> 			case "name":
> 						$SQL .= ",
> ".$field['column']." =
> '".$DATA[$field['type']][mt_rand(0,count($DATA[$field['type']]
> )-1)]."'";
> 						break;
> 			
> 			case "phone_fax":
> 						$SQL .= sprintf(",
> ".$field['column']." = '(%03u) %03u-%04u'", mt_rand(000,999),
> mt_rand(000,999), mt_rand(0000,9999));
> 						break;
> 			case "ipaddr":
> 						$SQL .= sprintf(",
> ".$field['column']." = '%03u.%03u.%03u.%03u'", mt_rand(000,999),
> mt_rand(000,999), mt_rand(000,999), mt_rand(000,999));
> 						break;
> 		}
> 
> 	} //foreach field
> 
> 	$SQL .= " WHERE ".$pk_column." = '".$id."' LIMIT 1";

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php