Member Avatar for TonyG_cyprus

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'><br></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');
}

?>
  • 3 Contributors
  • 3 Replies
  • 163 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for smantscheff

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.

Member Avatar for TonyG_cyprus

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.

Member Avatar for urtrivedi

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.