Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1209
  • Last Modified:

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

0
MariusGM
Asked:
MariusGM
  • 2
1 Solution
 
Beverley PortlockCommented:
Well, this

$updateSQL = sprintf("UPDATE sample SET sample_details=%s WHERE sample_ID=%s", $valuesSets);

won't work because you have two substitution parameters (the %s bits) and one value - $valuesSets

You life would be a lot easier if you just moved the update inside the loop

        $updateSQL = "";
        for ($i=0; $i < count($var1); $i++) {
                if(!empty($var2[$i])) {
                    $updateSQL .= "UPDATE sample
                                        SET sample_details='".GetSQLValueString($var1[$i], "text")."'
                                        WHERE sample_ID='".GetSQLValueString($var2[$i], "int")."'; ";
                }
        }


        ....


        mysql_select_db($database_threeeightohsix, $threeeightohsix);
        $Result1 = mysql_query($updateSQL, $threeeightohsix) or die(mysql_error());
       
0
 
MariusGMAuthor Commented:
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...
0
 
Beverley PortlockCommented:
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';
";
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now