Solved

PHP SQL Update Loop

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Thoughout my experience working on eCommerce web applications I have seen applications succumbing to increased user demand and throughput. With increased loads the response times started to spike, which leads to user frustration and lost sales. I ha…
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to count occurrences of each item in an array.

864 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

24 Experts available now in Live!

Get 1:1 Help Now