asked on

PHP SQL Update Loop

Hello.

I have a form which shows records in a table matching a foreign key. I would like the user to be able to update descriptions of each record and submit in one update rather updating each record individually.

I have tried to adapt a looped Insert statement that works for me elsewhere but having trouble. It might be completely the wrong approach, I'm not sure. Any advise is much appreciated.

$editFormAction = $_SERVER['PHP_SELF'];
	if (isset($_SERVER['QUERY_STRING'])) {
	  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
	}
 
	if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "update_details")) {
		$var1 = $HTTP_POST_VARS['sample_details'];
		$var2 = $HTTP_POST_VARS['sample_ID'];
				
		$updateGoTo = "confirm_desc.php?job_ID=" . $_GET['job_ID']; 
	if (isset($_SERVER['QUERY_STRING'])) {
		$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
		// $updateGoTo .= $_SERVER['QUERY_STRING'];
	}
	
	for ($i=0; $i < count($var1); $i++) {
		if(!empty($var2[$i])) {
		$valuesSets.="(".GetSQLValueString($var1[$i], "text").",".GetSQLValueString($var2[$i], "int")."),";
		}
	}
	$valuesSets=substr($valuesSets,0,-1); // to remove last comma
	$updateSQL = sprintf("UPDATE sample SET sample_details=%s WHERE sample_ID=%s", $valuesSets);
		
	print "<script language=\"JavaScript\">";
	print "window.location = '$updateGoTo'";
	print "</script>";
 
  mysql_select_db($database_threeeightohsix, $threeeightohsix);
  $Result1 = mysql_query($updateSQL, $threeeightohsix) or die(mysql_error());
 
}

Open in new window

ASKER CERTIFIED SOLUTION

Beverley Portlock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MariusGM

ASKER

I knew it was those %s bits!! Thank you bportlock. Didn't know the technical name for them. Substitution parameters, now I am able to identify them.

I had attempted, unsuccessfully, to put the query inside the loop, but kept thinking I HAD to use substitution parameters. If only I knew that I could put the array parameters (correct?) directly in the SQL...

Beverley Portlock

YOu can have multiple SQL statements in a single string. The trick is to use semi-colons to separate them like so.....

$string = "
update aTable set aField = 'aValue';
update aTable set aField = 'another Value';
update aTable set aField = 'a thrid Value';
";