Update mysql table with posted array

How to update multiple rows in a table

[upshire]

Hi
I could do with some help I have spent near enough a week trying to sort this out for myself, but to no avail.
What I want to do is to present a form to the user through which he/she can update stock levels in the relevant table acc_stock_items".I am also giving them the ability to increase the amount of input rows on the form as required.

I have a form on which I have a list box,and two input text boxes:
lst_product[]
txt_quantity[]
txt_cost[]

Also on this form I have a jquery plug in "relcopy" which can add another line as required.
I suspect that I will need an array to put this data in to the table, so I have made it my business to research aray's this week. This is what I have come up with so far.

My form looks like this:

<form name="form1" method="post" action="phpcode/update_stock_level.php">
 <table width="80%" border="0" class="btn-large">
  <tr>
  <th scope="col">Stock Item:</th>
  <th scope="col">Qnty Received:</th>
  <th scope="col">Item Cost:</th>
  <th scope="col">&nbsp;</th>
  <th scope="col">&nbsp;</th>
  </tr>
  <tr class="phone">
  <td><select name="lst_product[]" class="controls" id="product_id">
   <option><?php prod_list()?></option>
  </select></td>
  <td><input name="txt_quantity[]" type="text" class="controls" id="quantity" value=""></td>
  <td><input name="txt_item_cost[]" type="text" class="controls" id="item_cost" value=""></td>
  <td>&nbsp;</td>
  <td>&nbsp;</td>
  </tr>
 </table>
 <p><a href="#" class="copy" rel=".phone">Add stock line</a></p>
 <div class="span7"><p>
  <input name="updatestock" type="submit" class="btn-primary" id="button" value="Submit">
  <span class="textarea">
  <input name="hidden_company_id" type="hidden" id="hidden_company_id" value="<?php echo $_SESSION['sess_company_id']?>" />
  </span></p>
 </div>
 
 </form>

when I post my form, using

echo '<pre>';
print_r($_POST); echo '</pre>';

on the receiving page I get an array like this:

Array
(
 [lst_product] => Array
  (
   [0] => 1
  )

 [txt_quantity] => Array
  (
   [] => 33
  )

 [txt_item_cost] => Array
  (
   [0] => 44
  )

 [updatestock] => Submit
 [hidden_company_id] => 5
)

this example is showing just one product being posted, I'm trying to get things working with one update before I scale up.

this is my code on the update page:

<?php session_start();?>
<?php require_once('../Connections/connpay.php'); ?>
<?php require_once('../functions/global_functions.php'); ?>
<?php require_once('../functions/account_functions.php'); ?> 
<?php
//$company_id = ($_POST['hidden_company_id']);
 ?>
 <?php 
 //Lets connect to the database
 if(mysqli_connect_errno()){
 exit("Failed to connect to the database".mysqli_connect_error());}
 ?>
 <?php
 foreach ($_POST['lst_product'] as $row=>$id)
 $productid = $id;
 $newquantity = ($_POST['txt_quantity'][$row]);
 $query = "UPDATE acc_stock_items SET stock_level = '$newquantity' WHERE product_id = '$productid'";
 if($conn->query($query)===TRUE){
echo $company_id;
//Header("Location:../view_stock_items.php");
echo "all good";
echo "quantity is ".$newquantity;
}
else {
echo "Error:".$conn->error;
}
 ?>
 
 <?php 
//$update_stock = array(
//$prodid => $_POST['lst_product'],
//$quantity => $_POST['txt_quantity'],
//$cost => $_POST['txt_item_cost']);


echo '<pre>';
print_r($_POST); echo '</pre>';

echo print_r($_POST); 
?>

Anything I try will not update a single stock level, I've tried echoing the variable "$newquantity" and that comes up blank.
I know I have to assign a variable to a key and a value in the row and then loop through the array and update each one in the table, but so far this concept has eluded me. Any and all help would be greatly appreciated.
David

[skoff]

first thing you're missing a "{" on your foreach line
second i dont think it's necessary to put () on your $newquantity line

also i was wondering, will you always have the same number of textbox for your quantity and you product, if not you will face some errors.-

[upshire]

Hi Skoff
Thank's for that, I managed to sort it out during the week myself, and as it happens, the missing"{" after the for each was the issue.I appreciate that you took the time to have a look and make a sensible comment.
David