php - How to update stock table after making a sale -
php - How to update stock table after making a sale -
i'm creating inventory management scheme , can't figure out how link inventory table sales table products in inventory table updated when sale made. i'm using mysql
here's sold.php, when sell product page save record in sale table want update manuf table qtyleft row.
<?php require_once('../connections/bidco.php'); ?> <?php mysql_select_db($database_bidco, $bidco); $query_rssaletype = "select * saletype order type asc"; $rssaletype = mysql_query($query_rssaletype, $bidco) or die(mysql_error()); $row_rssaletype = mysql_fetch_assoc($rssaletype); $totalrows_rssaletype = mysql_num_rows($rssaletype); mysql_select_db($database_bidco, $bidco); $query_rscustomercategory = "select * buyertype order type asc"; $rscustomercategory = mysql_query($query_rscustomercategory, $bidco) or die(mysql_error()); $row_rscustomercategory = mysql_fetch_assoc($rscustomercategory); $totalrows_rscustomercategory = mysql_num_rows($rscustomercategory); mysql_select_db('invmgt', mysql_connect('localhost','root','dream2014')) or die(mysql_error()); ?> <?php //start session session_start(); //unset variables stored in session unset($_session['sess_id']); unset($_session['sess_username']); unset($_session['sess_name']); ?> <?php if (isset ($_post ['submit'])) { $da=date("y-m-d"); $itemname=$_post['itemname']; $itemcode=$_post['itemcode']; $itemtype=$_post['itemtype']; $price=$_post['unitprice']; $quantity=$_post['quantity']; $ttype=$_post['select2']; $ccat=$_post['select']; $idate=date("y-m-d"); mysql_query("insert sold_goods (itemname, itemcode, itemtype, unitprice, quantity, transactiontype, customercategory, date) values ('$itemname', '$itemcode', '$itemtype', '$unitprice', '$quantity', '$ttype', '$ccat', '$idate')"); header("location:sold.php"); } ?> <!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> <title>untitled document</title> </head> <body> <table width="711" height="27" border="0"> <tr> <td width="216"><a href="../manufactured_goods/manuf.php">manufactured goods</a> </td> <td width="152"><a href="sold.php">sold goods</a> </td> <td width="148"><a href="../client_details/client.php">client details </a></td> <td width="167"><a href="../users_accounts/users.php">user accounts </a></td> <td width="167"><a href="../login/menu.php">home </a></td> <td width="167"><div align="right"><a href="../login/logout.php">logout </a></div></td> </tr> </table> <p> </p> <form id="form9" name="form9" method="post" action=""> <table width="828" height="572" border="1" align="center"> <tr> <td height="55" colspan="3"><div align="center"><strong>sold goods </strong></div></td> </tr> <tr> <td height="33" colspan="2">user</td> <td height="33"><?php date_default_timezone_set('africa/nairobi'); echo "time " . date("h:i:sa"); ?></td> </tr> <tr> <td height="14">customer code <div align="center"></div></td> <td height="14">customer category <select name="select2"> <?php { ?> <option value="<?php echo $row_rscustomercategory['type']?>"><?php echo $row_rscustomercategory['type']?></option> <?php } while ($row_rscustomercategory = mysql_fetch_assoc($rscustomercategory)); $rows = mysql_num_rows($rscustomercategory); if($rows > 0) { mysql_data_seek($rscustomercategory, 0); $row_rscustomercategory = mysql_fetch_assoc($rscustomercategory); } ?> </select></td> <td height="14"><?php echo "date of transaction " . date("y/m/d")?></td> </tr> <tr> <td height="15"> </td> <td height="15"> </td> <td height="15"> </td> </tr> <tr> <td height="32" colspan="2"><div align="center"><strong>item details </strong></div></td> <td width="258" rowspan="3"><div align="center"></div> <div align="center"></div></td> </tr> <tr> <td width="227" height="23"><div align="right">item name </div></td> <td width="321"><input name="itemname" type="text" id=itemname /></td> </tr> <tr> <td><div align="right">item code </div></td> <td><input name="itemcode" type="text" id="itemcode" /></td> </tr> <tr> <td><div align="right">item type </div></td> <td><input name="itemtype" type="text" id="itemtype" /></td> <td rowspan="3"><div align="center"></div></td> </tr> <tr> <td height="23"><div align="right">unit cost </div></td> <td><input name="unitprice" type="text" id=price /></td> </tr> <tr> <td height="32"><div align="right">quantity </div></td> <td><input name="quantity" type="text" id=qty /></td> </tr> <tr> <td height="42" colspan="2"><div align="center"><strong>sales</strong></div></td> <td><div align="center"></div> <div align="center"></div></td> </tr> <tr> <td><div align="right">gross</div></td> <td> </td> <td><div align="center"></div></td> </tr> <tr> <td><div align="right">type of sale </div></td> <td><select name="select"> <?php { ?> <option value="<?php echo $row_rssaletype['type']?>"><?php echo $row_rssaletype['type']?></option> <?php } while ($row_rssaletype = mysql_fetch_assoc($rssaletype)); $rows = mysql_num_rows($rssaletype); if($rows > 0) { mysql_data_seek($rssaletype, 0); $row_rssaletype = mysql_fetch_assoc($rssaletype); } ?> </select></td> <td> </td> <td> </td> </tr> <tr> <td height="28" colspan="2"><div align="center"><strong>deductions</strong></div></td> <td> </td> </tr> <tr> <td height="36"><div align="right">discount</div></td> <td height="36"> </td> <td><div align="center"> <input type="submit" name="submit" value="add new" /> </div></td> </tr> <tr> <td height="36"><div align="right">v.a.t</div></td> <td height="36"> </td> <td> </td> </tr> <tr> <td height="36"><div align="right">net</div></td> <td height="36"> </td> <td rowspan="2"><div align="center"> <input type="reset" name="submit5" value="cancel" /> </div></td> </tr> <tr> <td height="30" colspan="2"><div align="center"> <label><strong>calculate</strong></label> </div></td> </tr> </table> </form> <p> </p> <p> </p> </body> </html> <?php mysql_free_result($rssaletype); mysql_free_result($rscustomercategory); ?> this manuf.php adding new stock
<?php require_once('../connections/bidco.php'); ?> <?php if (isset ($_post ['submit'])) { $da=date("y-m-d"); $itemname=$_post['itemname']; $itemcode=$_post['itemcode']; $itemtype=$_post['itemtype']; $unitprice=$_post['unitprice']; $quantity=$_post['quantity']; $idate=date("y-m-d"); mysql_query("insert manuf (itemname, itemcode, itemtype, price, qtyleft, date) values ('$itemname', '$itemcode', '$itemtype', '$unitprice', '$quantity', '$idate')"); header("location:manuf.php"); } ?> ?><!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> <title>untitled document</title> </head> <body> <p> </p> <table width="711" height="27" border="0" align="center"> <tr> <td width="216"><a href="inventory.php">inventory</a></td> <td width="152"><a href="../sold_goods/sold.php">sold goods</a> </td> <td width="148"><a href="../client_details/client.php">client details </a></td> <td width="167"><a href="../users_accounts/users.php">user accounts</a> </td> <td width="167"><a href="../login/menu.php">home</a> </td> </tr> </table> <p> </p> <form id="form1" name="form1" method="post" action=""> <table width="743" height="282" border="1" align="center"> <tr> <td colspan="3"><div align="center"><strong>add new product </strong></div></td> </tr> <tr> <td colspan="2">username </td> <td width="265"><?php date_default_timezone_set('africa/nairobi'); echo "time " . date("h:i:sa"); ?></td> </tr> <tr> <td width="180"><div align="right">item name </div></td> <td width="276"><input name="itemname" type="text" id="itemname" /></td> <td> </td> </tr> <tr> <td><div align="right">item code </div></td> <td><input name="itemcode" type="text" id="itemcode" /></td> <td><div align="center"></div></td> </tr> <tr> <td><div align="right">type</div></td> <td><input name="itemtype" type="text" id="itemtype" /></td> <td><div align="center"></div></td> </tr> <tr> <td><div align="right">unit cost </div></td> <td><input name="unitprice" type="text" id="unitprice" /></td> <td><div align="center"></div></td> </tr> <tr> <td><div align="right">quantity</div></td> <td><input name="quantity" type="text" id="quantity" /></td> <td><div align="center"> <input type="submit" name="submit" value="add stock" /> </div></td> </tr> <tr> <td><div align="right">date</div></td> <td><input name="idate" type="varchar" id="idate" /></td> <td><div align="center"> <input type="reset" name="submit5" value="cancel" /> </div></td> </tr> </table> <input type="hidden" name="mm_insert" value="form1"> </form> <div align="center"></div> <div align="center"></div> <p> </p> </body> </html>
after inserting sold table, update manf table well:
mysql_query("insert sold_goods (itemname, itemcode, itemtype, unitprice, quantity, transactiontype, customercategory, date) values ('$itemname', '$itemcode', '$itemtype', '$unitprice', '$quantity', '$ttype', '$ccat', '$idate')"); mysql_query("update manuf set qtyleft = qtyleft - $quantity itemcode = '$itemcode'" ); php mysql
Comments
Post a Comment