Solved

PHP SQL Update Loop

Posted on 2008-06-23
3
1,196 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
reverse engineer .sql from php files 11 34
Accessing specific element - DOM PHP HTML 8 34
PHP 5.6 and 7.x 4 20
Php logic to add to date card 9 19
Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design.  These principles do not stand alone; there is interplay among them.  And they are not laws, merely princ…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

839 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