Solved

PHP SQL Update Loop

Posted on 2008-06-23
3
1,193 Views
Last Modified: 2013-12-12
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
Comment
Question by:MariusGM
  • 2
3 Comments
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 500 total points
ID: 21847284
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
 

Author Comment

by:MariusGM
ID: 21847568
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 21847833
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question