multiple update help

Hi,
I have a mysql database for stock records in the pub I work in. The code below displays the list of items and sizes, what I need is to be able to update several rows by subtracting the number in the qty column from the bal column in the database when I hit the go button. I thought I had it but it doesn't work, any suggestions welcome!

dbase

CREATE TABLE IF NOT EXISTS `levels` (
  `itemsize` varchar(55) NOT NULL,
  `item` varchar(55) NOT NULL,
  `size` varchar(5) DEFAULT NULL,
  `code` varchar(10) NOT NULL,
  `bal` varchar(4) NOT NULL,
  `max` varchar(4) NOT NULL,
  `min` varchar(4) NOT NULL,
  `supp` varchar(55) DEFAULT '  ---',
  UNIQUE KEY `ident` (`itemsize`)
)

<?php

mysql_connect("localhost","root","corrupt") or die("cannot connect");
mysql_select_db("stock") or die("cannot select db");
$res=mysql_query("SELECT * FROM levels ORDER BY item ASC ");
echo "<html>
     <head>
     <title>Stock Control</title>
     <link rel='stylesheet' type='text/css' href='style.css'>
     </head>
     <body>";
echo "<div class='top'></div>
  <div class='mid'>
  <table width='800' border='0' class='order' align='center'><form name='update' action='$self' method='post'>
     <tr class='order'>
         <td class='heading' align='center' colspan='8'>Update List</td>
         </tr>
         <tr>
         <td class='sub'>Item</td><td class='sub'>Qty</td><td class='sub'>Max Qty</td><td class='sub'>Min Qty</td>

     </tr><tr><td colspan='10'><hr color='#000000'></td></tr>";
$count=mysql_num_rows($res);
while($row=mysql_fetch_array($res)) {
echo "<tr>";
$item=$row['itemsize'];
$size=$row['size'];
$bal=$row['bal'];
$min=$row['min'];
$max=$row['max'];
$sup=$row['supp'];

echo "<td class='grid'><input type='text' name='part' value='".$item."'></td><td class='grid'><input type='text' name='upd'></td><td class='grid'>".$max."</td><td class='grid'>".$min."</td>";
}

echo "</tr><tr>
    <td colspan='10' align='right'><input type='submit' name='go' value='Go'></form>
 </table>";
echo "</div><div class='bottom'></div>";
echo "</body>";
echo "</html>";
if(isset ($_POST['go'])) {
$new=$_POST['upd'];
$item=$_POST['part'];
for($i=0; $i<$count; $i++) {
$z=mysql_query("UPDATE levels SET bal=bal-'$new[$i]' WHERE itemsize= '$item[$i]'");
$result=mysql_query($z);
}
}
if($result) {
header('location:list.php');
}

?>

Tonygcyprus

Newbie Poster

10 posts since Dec 2009

Reputation Points: 10

Solved Threads: 0

6 Months Ago

I have no clear idea of what you are trying, but this here is definitively wrong:

$z=mysql_query("UPDATE levels SET bal=bal-'$new[$i]' WHERE itemsize= '$item[$i]'");
$result=mysql_query($z);

The result of the mysql_query function is a ressource identifier which you cannot use as input for the same function.

smantscheff

Nearly a Posting Virtuoso

1,233 posts since Oct 2010

Reputation Points: 300

Solved Threads: 254

6 Months Ago

Sorry I didn't explain too clearly.
The html part displays a table which is a list of all items in the database.
The first column consists of a textbox containing the item and size of bottle. the next column consists of a blank textbox. when items are removed from or added to stock I want to be able to use the form to update only the rows where the quantity has changed, without having to search every item individually. hope thats clearer.

Tonygcyprus

Newbie Poster

10 posts since Dec 2009

Reputation Points: 10

Solved Threads: 0

6 Months Ago

One thing is that mysql itself takes care and do not update a row if there is no change.

Still if you want to include it in your code, then when you load your page with database values, then you also add one more hidden field, say original_qty,

Now when your process your data from html form. you compare qty and original_qty column, if both are not matching then only execute update query

urtrivedi

Nearly a Posting Virtuoso

1,306 posts since Dec 2008

Reputation Points: 257

Solved Threads: 270

This question has already been solved

You