Newbie: Multiple update help needed

josie

Hi,

I have a table with ID, PartNo, Maufacturer and Quantity.
I have about 20 records.
I am trying to get all 'Quantity' fields updated at once using the page below ubt I can't get it working properly. It will only update the last field. Any help anyone can give me would be appreciated. It is driving me around the bend!!

<?php
include("../include/config.php"); 

function connect_db() {
		global $db_host, $db_name, $db_user, $db_pw, $dbcnx;

	$msg =  "<p>The mySQL database database is temporarily unavailable. ";
	$msg = "Please contact the site's administrator.</p>";

	$dbcnx = @mysql_connect($db_host, $db_user, $db_pw);

	if (!$dbcnx) {
		echo($msg . mysql_error());
		}
		mysql_select_db($db_name) or die("Unable to connect to the database");
		}

connect_db();
?>
<html>
<head>
	<title>Quantities Admin</title>
</head>
<body bgcolor="#FFFFFF">

<?php

if($submit == "Change") {
	for($i = 1; $i <= $check; $i++){
		$sql = "UPDATE ryzex_buyandsell SET Quantity='$Quantity' WHERE ID = '$ID'";
	}
	if (@mysql_query($sql)) {
		echo("<p>The item(s) have been edited</p>");
	} else {
		echo("<p>Error editing item: " .
		mysql_error() . "</p>");
	}
}

$sql = "SELECT ID, PartNo, Manufacturer, Quantity from ryzex_buyandsell ORDER BY Manufacturer";
$items = mysql_query($sql);
$t = mysql_num_rows($items);
if (!$items) {
	echo("<P>" . $err_msg . "<BR>Error: " . mysql_error());
	exit();
}
?>

<form action="<?=$PHP_SELF?>" method="POST">
<table width="600" cellspacing="0" cellpadding="0" border="0">

<?php

while ($item = mysql_fetch_array($items)) {
	$item_id = $item["ID"];
	$item_part = $item["PartNo"];
	$item_manu = $item["Manufacturer"];
	$total_quantity = $item["Quantity"];


	echo("<tr><td align=\"center\">" . $item_manu . "</td><td align=\"center\">" . $item_part . "</td><td><input type=\"text\" name=\"Quantity\" value=\"".$total_quantity."\" size=\"8\" maxlength=\"100\"></td></tr>");
	echo("<input type=\"hidden\" name=\"ID\" value=\"" . $item_id . "\">");
}
echo("<input type=\"hidden\" name=\"check\" value=\"" . $t . "\">");
echo("<tr><td colspan=\"3\"><INPUT TYPE=SUBMIT NAME=\"submit\" VALUE=\"Change\">&nbsp;&nbsp;<INPUT TYPE=RESET NAME=\"clear\" VALUE=\"Reset\"></td></tr>");
echo("</table>");

?>

</body>
</html>

edwardsbc

Have you looked at the HTML generated by this page? I think the problem would be obvious to you then. This will generate (if you have 20 records) 20 sets of input fields all named Quantity. You need to make that an array. Something like this:

Or better yet

This way you loop though this array (once submitted) and generate the correct SQL UPDATE statement.

BTW, if column ID and Quantity are non-text type fields, you shouldn't use single quotes in the SQL statement:

$sql = "UPDATE ryzex_buyandsell SET Quantity=$Quantity WHERE ID = $ID";

swallace

Hi,

If I understand you correctly...

If you look inside your loop, it loops round $check($t) times, but the SQL statment is only updating the one record where the ID = $ID.

You will need to use the counter in the loop to change the ID so that ALL 20 or so records are updated

Good Luck...

josie

Thanks for your help guys.
I'm really not getting it though 🙁
I don't understand how to use the array in the sql statement properly.
How do I use the counter to change the ID (btw the ID's are not consecutive).
If someone could elaborate I would be eternally grateful.
I'm very new to this and thought I was doing quite well - doh!

Josie

chinni_77

hello,

use something like this:

$cnt = count($HTTP_POST_VARS['id']);

for($cntr=0;$cntr<$cnt;$cntr++) {

and dont forget to add in your form fields something like:

HTH

josie

But then what would the sql statement be?
Sorry to be a pain but I am very new to this and I'm trying my hardest!

Josie

chinni_77

$update_sql = "update TBL_NAME SET ".
"FIELD_1 = '{$HTTP_POST_VARS['formfieldname'][$cntr]}', ".
and so on...

"where FLD_ID = {$HTTP_POST_VARS['id'][$cntr]}";

HTH

josie

I have tried what you said but it doesn't work. What am I doing wrong below?

<?php
include("../include/config.php"); 

function connect_db() {
		global $db_host, $db_name, $db_user, $db_pw, $dbcnx;

	$msg =  "<p>The mySQL database database is temporarily unavailable. ";
	$msg = "Please contact the site's administrator.</p>";

	$dbcnx = @mysql_connect($db_host, $db_user, $db_pw);

	if (!$dbcnx) {
		echo($msg . mysql_error());
		}
		mysql_select_db($db_name) or die("Unable to connect to the database");
		}

connect_db();
?>
<html>
<head>
	<title>Quantities Admin</title>
</head>
<body bgcolor="#FFFFFF">

<?php

if($submit == "Change") {
	$cnt = count($HTTP_POST_VARS['Quantity']);
	for($i = 0; $i < $cnt; $i++){
		$sql = "UPDATE ryzex_buyandsell SET Quantity='{$HTTP_POST_VARS['Quantity'][$i]}' WHERE ID = {$HTTP_POST_VARS['Quantity'][$i]}";
	}
	if (@mysql_query($sql)) {
		echo("<p>The item(s) have been edited</p>");
	} else {
		echo("<p>Error editing item: " .
		mysql_error() . "</p>");
	}
}

$sql = "SELECT ID, PartNo, Manufacturer, Quantity from ryzex_buyandsell ORDER BY Manufacturer";
$items = mysql_query($sql);
$t = mysql_num_rows($items);
if (!$items) {
	echo("<P>" . $err_msg . "<BR>Error: " . mysql_error());
	exit();
}
?>

<form action="<?=$PHP_SELF?>" method="POST">
<table width="600" cellspacing="0" cellpadding="0" border="0">

<?php
	while ($item = mysql_fetch_array($items)) {
		$item_id = $item["ID"];
		$item_part = $item["PartNo"];
		$item_manu = $item["Manufacturer"];
		$total_quantity = $item["Quantity"];

	echo("<tr><td align=\"center\">" . $item_manu . "</td><td align=\"center\">" . $item_part . "</td><td><input type=\"text\" name=\"Quantity[" . $item_id . "]\" value=\"".$total_quantity."\" size=\"8\" maxlength=\"100\"></td></tr>");
}
echo("<input type=\"hidden\" name=\"check\" value=\"" . $t . "\">");
echo("<tr><td colspan=\"3\"><INPUT TYPE=SUBMIT NAME=\"submit\" VALUE=\"Change\">&nbsp;&nbsp;<INPUT TYPE=RESET NAME=\"clear\" VALUE=\"Reset\"></td></tr>");
echo("</table>");

?>

</body>
</html>

I really appreciate your help with this.

Josie

chinni_77

well, when you are updating multiple records, you should also store your old values and then compare them with your new values.so, you need something like this before your while st:

$counter=0;
$old_values=null;

and after your form fields,

<?php

$old_values.=' <input type="hidden" name="id['.$counter.']" value="'.$row->ID.'">
';
$counter++;
}
?>
</table>
<?=$old_values?>
Here comes your submit button.

As i said you before, each of your form fields should have this $counter.

HTH

swallace

$sql = "UPDATE ryzex_buyandsell SET Quantity='{$HTTP_POST_VARS['Quantity'][$i]}' WHERE ID = {$HTTP_POST_VARS['Quantity'][$i]}";

Should you be trying to set the quantity to this value where the ID is the same value???

josie

No! Exactly!
I haven't got a scooby about what to do or how I'm doing it.
How do I get the ID number into the loop each time?
Why do I need to store the old values and compare them?
I'm going mad. (knew I shouldn't have started this :eek: )

Josie

swallace

Well, in the first piece of code you posted you had a ID variable

echo("<input type=\"hidden\" name=\"ID\" value=\"" . $item_id . "\">");

That is what chinni_77 was explaining to use:

<input type="hidden" name="id['.$counter.']" value="'.$row->ID.'">

This way when you're doing your query the where statement should be using the {$HTTP_POST_VARS['ID'][$i]}

I think this is what needs to be done, but I'm sure chinni_77 has a better grasp about what is needed...

good luck

chinni_77

what is the error you are getting and where?I mean error in updating the records or what?

josie

OK. This is where I've got to.

<?php

if($submit == "Change") {
	$cnt = count($HTTP_POST_VARS['Quantity']);
	for($i = 0; $i < $cnt; $i++){
		$sql = "UPDATE ryzex_buyandsell SET Quantity='{$HTTP_POST_VARS['Quantity'][$i]}' WHERE ID = {$HTTP_POST_VARS['ID'][$i]}";
	}
	if (@mysql_query($sql)) {
		echo("<p>The item(s) have been edited</p>");
	} else {
		echo("<p>Error editing item: " .
		mysql_error() . "</p>");
	}
}

$sql = "SELECT ID, PartNo, Manufacturer, Quantity from ryzex_buyandsell ORDER BY Manufacturer";
$items = mysql_query($sql);
if (!$items) {
	echo("<P>" . $err_msg . "<BR>Error: " . mysql_error());
	exit();
}
?>

<form action="<?=$PHP_SELF?>" method="POST">
<table width="600" cellspacing="0" cellpadding="0" border="0">

<?php
	while ($item = mysql_fetch_array($items)) {
		$item_id = $item["ID"];
		$item_part = $item["PartNo"];
		$item_manu = $item["Manufacturer"];
		$total_quantity = $item["Quantity"];

	echo("<tr><td align=\"center\">" . $item_manu . "</td>".
	"<td align=\"center\">" . $item_part . "</td>".
	"<td><input type=\"text\" name=\"Quantity[" . $item_id . "]\" value=\"".$total_quantity."\" size=\"8\" maxlength=\"100\"></td></tr>".
	"<input type=\"hidden\" name=\"ID\" value=\"" . $item_id . "\">");
}
echo("<tr><td colspan=\"3\"><INPUT TYPE=SUBMIT NAME=\"submit\" VALUE=\"Change\">&nbsp;&nbsp;<INPUT TYPE=RESET NAME=\"clear\" VALUE=\"Reset\"></td></tr>");
echo("</table>");

?>

If someone could take this and amend this for me I'm sure I would then understand what you are talking about. I seem to understand things better when I can put it all in context.

I would be eternally grateful.
My head hurts - I need to lie down 🙁

Josie

chinni_77

hmm...ok!
I'll work on it later today and let u know if i have something working.

swallace

A thing I noticed to help you and chinni_77 is that

"<input type=\"hidden\" name=\"ID\" value=\"" . $item_id . "\">");

This isn't an array and then in the update statement you are using it as an array...

Sorry I can't help more, but I'm stuck at a couple of things of my own here 😕

josie

You have just been elevated to God-like status.
I can't tell you how much I appreciate your help with this.

Thanks a million

Josie

josie

OK. I've now got this (we're getting there!)

<?php

if($submit == "Change") {
	$cnt = count($HTTP_POST_VARS['Quantity']);
	for($i = 0; $i <= $cnt; $i++){
		$sql = "UPDATE ryzex_buyandsell SET Quantity='{$HTTP_POST_VARS['Quantity'][$i]}' WHERE ID = {$HTTP_POST_VARS['ID'][$i]}";
	}
	if (@mysql_query($sql)) {
		echo("<p>The item(s) have been edited</p>");
	} else {
		echo("<p>Error editing item: " .
		mysql_error() . "</p>");
	}
}

$sql = "SELECT ID, PartNo, Manufacturer, Quantity from ryzex_buyandsell ORDER BY Manufacturer";
$items = mysql_query($sql);
if (!$items) {
	echo("<P>" . $err_msg . "<BR>Error: " . mysql_error());
	exit();
}
?>

<form action="<?=$PHP_SELF?>" method="POST">
<table width="600" cellspacing="0" cellpadding="0" border="0">

<?php
	$i = 0;
	while ($item = mysql_fetch_array($items)) {
		$item_id = $item["ID"];
		$item_part = $item["PartNo"];
		$item_manu = $item["Manufacturer"];
		$total_quantity = $item["Quantity"];
		$i++;

	echo("<tr><td align=\"center\">" . $item_manu . "</td>".
	"<td align=\"center\">" . $item_part . "</td>".
	"<td><input type=\"text\" name=\"Quantity[" . $i . "]\" value=\"".$total_quantity."\" size=\"8\" maxlength=\"100\"></td></tr>".
	"<input type=\"hidden\" name=\"ID[" . $i . "]\" value=\"" . $item_id . "\">");
}
echo("<tr><td colspan=\"3\"><INPUT TYPE=SUBMIT NAME=\"submit\" VALUE=\"Change\">&nbsp;&nbsp;<INPUT TYPE=RESET NAME=\"clear\" VALUE=\"Reset\"></td></tr>");
echo("</table>");

?>

If I alter all of the fields in the outputted form and click submit, it says that it has edited the items but only the last one has been altered. If I change the '$sql = "Update...' into 'echo("Update ...' it prints out the looped sql statement and everything looks correct but it only changes the last record. ???

Josie

chinni_77

josie,

gimmi your table structure and sample data.

can i pm you?

swallace

You guys probably have this sorted out...but if not...

it's only going to update the last record because the actual sql statement is being run outside of the loop. You would need to have it inside the loop if you wish to update the records 20 times or whatever.