List:General Discussion« Previous MessageNext Message »
From:Swati K Date:November 27 2003 6:17am
Subject:RE: Generating UPDATE in loop, how to deal with pesky comma - SOL VED
View as plain text  
this is in which version of MYSQL..
One cant write procedures and functions in MYSQL 4.0.12...

Regards
Swati kalia
http://www.mecklai.com
where risk meets its match 
***************************************


-----Original Message-----
From: Daevid Vincent [mailto:daevid@stripped]
Sent: Thursday, November 27, 2003 6:41 AM
To: mysql@stripped
Subject: RE: Generating UPDATE in loop, how to deal with pesky comma -
SOLVED


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 LIMIT 5";
//now do it 
$result = mysql_query($SQL,$db);

Daevid Vincent
http://daevid.com
  

> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped] 
> Sent: Wednesday, November 26, 2003 4:54 PM
> To: mysql@stripped
> 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";
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
Thread
Generating UPDATE in loop, how to deal with pesky commaDaevid Vincent27 Nov
  • RE: Generating UPDATE in loop, how to deal with pesky comma - SOLVEDDaevid Vincent27 Nov
RE: Generating UPDATE in loop, how to deal with pesky comma - SOL VEDSwati K27 Nov