[RESOLVED] Updating multiple rows with one submit button

NiallThistle

Hi everyone,

I'm currently trying to update one field for multiple rows in an SQL table using one Submit button. Is this possible? If so, does anyone have a sample line of code that would do the job?

At present, this is my table that I have displaying the editable "Percentage" field. The idea is that when the percentage changes are made, the amount claimed field will update itself on refresh.

echo"
<form action=\"edit.php?action=amend_variation\" name=\"frmEdit\" method=\"post\">
<table width=\"650\">
<tr><td width=\"50\" bgcolor=\"#CCCCCC\" valign=\"top\"><b>VO#</b></td><td width=\"200\" bgcolor=\"#CCCCCC\" valign=\"top\"><b>Details</b></td><td width=\"100\" bgcolor=\"#CCCCCC\" valign=\"top\"><b>Inst By</b></td><td bgcolor=\"#CCCCCC\" valign=\"top\" align=\"center\"><b>Amount (£)</b></td><td bgcolor=\"#CCCCCC\" valign=\"top\"align=\"center\"><b>Claimed (%)</b></td><td bgcolor=\"#CCCCCC\" valign=\"top\" align=\"center\"><b>Claimed (£)</b></td></tr>
		";

	$get_variations = mysql_query("
	SELECT V.variation_num as num,
	V.variation_detail as detail,
	V.variation_ins as ins,
	V.variation_id as vid,
	V.variation_percent as percent,
	V.variation_cost as cost,
	(V.variation_percent / 100) * V.variation_cost as outstanding
	FROM val_contracts C, val_variations V
	WHERE C.contract_id = '".$_GET["con"]."'
	AND C.contract_id = V.variation_con
	ORDER by V.variation_num ASC
	",$objConnect);

	while($data = mysql_fetch_array($get_variations))
	{
	echo"
	<tr><td valign=\"top\">$data[num]</td><td valign=\"top\">$data[detail]</td><td valign=\"top\">$data[ins]</td><td align=\"center\" valign=\"top\">";

		$cost = number_format($data['cost'],2);

	echo"$cost</td><td align=\"center\" valign=\"top\">
	<input type=\"text\" name=\"percent\" size=\"5\" value=\"$data[percent]\">%</td><td align=\"center\" valign=\"top\">
	<input type=\"hidden\" name=\"vid\" value=\"$data[vid]\">";

		$outstanding = number_format($data['outstanding'],2);

	echo"$outstanding</td></tr>";
	}

	echo"</table><br><input type=\"submit\" name=\"submit\" value=\"Submit\"></form>";

What I want to know is, when that Submit button is clicked, how do I ensure that each of the percent fields are updated at the same time?

I've added a screenshot to show what the form looks like. Any help would be greatly appreciated.

johanafm

Change your html form element names from "percent" and "vid" to "percent[]" and "vid[]" and you will get arrays of input data in $_POST. Iterate over that performing one update per iteration. You might also want to switch to using mysqli instead of mysql, since mysqli lets you used prepared statements, which means your database would only have to parse the query once and thereafter you pass different input data to it rather than resending the entire query each time.

NiallThistle

Forgive my ignorance on this, but once I add the [] to the original form, what changes should I make to my update code?

At present I have:

$strSQL = "UPDATE val_variations SET ";
$strSQL .="variation_percent = '".$_POST["percent"]."' ";
$strSQL .="WHERE variation_id = '".$_POST["vid"]."' ";
$objQuery = mysql_query($strSQL);

if($objQuery)
{
echo "Save completed.";
}
else
{
echo "Error Save [".$strSQL."]";
}
mysql_close($objConnect);

Or do you have an example of what the update query would then look like when I was posting the data to the db?

johanafm

When in doubt, always use var_dump($POST) or print_r($POST) to inspect what it contains. The answer will most likely be obvious once you do so, but if it isn't after you try that, feel free to ask again.

When inspecting data directly in the browser, wrap such output in a pre element for a readable format.

printf('<pre>&#37;s</pre>', print_r($_POST,1));

NiallThistle

Gave this is a go, but still a way off. Think I need some help with the syntax here. First time using foreach and first time really using variables. :queasy:

foreach($_POST['vid'] as $key=>$val) {   

$query = "UPDATE val_variations SET variations_percent = ".$_POST['percent'][$key]."";  

$result = mysql_query($query) or  die(mysql_error());  

} 

echo "Worked?";

johanafm

foreach($_POST['vid'] as $key=>$val)
{
	# You have a syntax error, the double "" at the end of the line
	# which in this case is a good thing, otherwise you'd have updated all rows each on
	# each iteration, thus making all table rows end up with the value of the last
	# percent array element, since you forgot
	#	WHERE vid=$val
	$query = "UPDATE val_variations SET variations_percent = ".$_POST['percent'][$key]."";  

$result = mysql_query($query) or  die(mysql_error());  

}

NiallThistle

Got it, thank you very much!

	
foreach($_POST['vid'] as $key=>$val) 
{ 
mysql_query("UPDATE val_variations SET variation_percent = ".$_POST['percent'][$key]." WHERE variation_id = $val") or die(mysql_error());      

}

johanafm

Great! But like I said in my first post, consider using mysqli

# Need to connect using mysqli to use it
$db = new mysqli($host, $user, $pass, $dbschema);


# Create your prepared statement once
$stmt = $db->prepare('UPDATE val_variations SET variation_percent = :percent WHERE variation_id = :id');

# Execute the prepared statement multiple times with different values
foreach ($_POST['vid'] as $k => $v)
{
	$stmt->execute(array('percent' => $_POST['percent'][$k], 'id' => $v));
}