Solved

PHP SQL Update Loop

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
This article discusses four methods for overlaying images in a container on a web page
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 …
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.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now