Multiple Updates via fetch array (help!)

Stewy

Hey all.
I'm struggling to figure out how to update my mysql db from one page that is generated using a fetch array.

All the data is pulled and displayed from one table on one page, and i need a text box to input more data into each row (kinda like the end of a shopping basket where you can change the number purchased on multiple items). Problem is, all the data is pulled with a single variables using WHERE clauses, and i can't throw a form in there (i think). Am i way off track here? This is a sample of what i'm working with:

 
	$SQL = " SELECT * FROM orders ORDER BY floor ";
	$retid = mysql_db_query($db, $SQL, $cid);
    if (!$retid) { echo( mysql_error()); }
    else {

echo ("<table width='100%' cellspacing='0' cellpadding='0' border='0'>");
echo ("<tr>");
echo ("<td class='FormInnerBorderListElements'>");
echo ("<table border='0' width='100%' cellpadding='3' cellspacing='0'>");
echo ("<tr>");
echo ("<td>&nbsp</td>");
echo ("<td>Floor</td>");
echo ("<td>Date</td>");
echo ("<td>Product</td>");
echo ("<td>On Hand</td>");
echo ("<td>Amount</td>");
echo ("<td>&nbsp</td>");
echo ("</tr>");

     while ($row = mysql_fetch_array($retid)) {
        $session = $row["session"];
		$date = $row["date"];
		$prodnum = $row["prodnum"];
		$floor = $row["floor"];
        $amount = $row["amount"];

        $ITEM = " SELECT * FROM products WHERE UID = '$prodnum' ";
        $result = mysql_db_query($db, $ITEM, $cid);
        while ($row = mysql_fetch_array($result)) {
        $UID = $row["UID"];
        $ItemDescription = $row["ItemDescription"];
        $Stocked = $row["Stocked"];
}

echo ("<tr>");
echo ("<td width='25' align='center' valign='middle'><img src='images/layout_link2.gif'></td>");
echo ("<td width='' align='left' valign='middle'>$floor</td>");
echo ("<td width='' align='center' valign='middle'>$date</td>");
echo ("<td width='' align='center' valign='middle'>$ItemDescription</td>");
echo ("<td width='' align='center' valign='middle'>$Stocked</td>");
echo ("<td width='' align='center' valign='middle'>

[COLOR=DarkRed]// This is the only field that needs to be updated/inserted into the displaying results[/COLOR]
<input type='text' size='2' name='amountordered'>

</td>");
echo ("<td><A HREF=\"index.php?task=processorder&sub=removeitem&floor=$floor&prodnum=$prodnum\"><center>Remove</center></A></td>");
echo ("</td>");
echo ("</tr>");
}
echo ("</table>");
}
?>

All help is totally appreciated!!

coreyp_1

First of all, you could wrap the entire table in <form> tags. Then, you could include a hidden tag with some sort of ID number (perhaps in an array) so you know which fields to update later.
Now, when the submit button is pushed (I assume at the bottom of the table), on the next page access the information using '$POST['amountordered']' The other, hidden fields can also be accessed using the $POST[] variable. If you are unsure what all has been passed, then do a print_r($_POST).
Let us know if it works!

Stewy

Thanks for the help!
Here's where i'm at so far....

To test, i've called from the db 3 records, and am trying to update just the amount field. I've added the array (hopefully right), and it is getting post data np (from debug).

Array ( [floor_array] => Array ( [0] => Floor1 [1] => Floor2 [2] => Floor4 ) [date_array] => Array ( [0] => 2005-10-07 [1] => 2005-10-07 [2] => 2005-10-07 ) [prodnum_array] => Array ( [0] => 1042 [1] => 581 [2] => 343 ) [amountordered_array] => Array ( [0] => 12 [1] => 14 [2] => 16 ) [x] => 51 [y] => 5 )

(the date array is redundant, as i don't need it to UPDATE SET.. i'll remove it later.)

Now, getting the data to UPDATE the orders set is fun.
Using the collected array and putting it straight through (for fun), i get a query result of:

UPDATE orders SET amount='Array' WHERE prodnum='Array'

I'm currently trying to figure out a way to add it all accordingly without serialize. Still researching. Feel free to beat me to the punchline.
Thanks!

Stewy

Hopefully this pans out:

$link = mysql_connect("localhost","root","");
mysql_select_db("inventory",$link);

for ($i=0; $i<count($_POST['prodnum_array']); $i++) {

mysql_query ("UPDATE orders SET amount=\"{$_POST['amountordered_array'][$i]}\" WHERE prodnum = '{$_POST['prodnum_array'][$i]}'");
}

I'l play around with this and see what happens. I see alot of posts regarding people trying to figure this out..... finally think i got it.
Thanks again!