php: duplicate when inserting and updating 2 tables

[peanut82]

Hi, I'm trying to insert and update 2 tables, purchase and stock. The idea is when the user inserts from form the data is inserted into both tables, but when the user inserts an item that already exist in stock database it only updates the quantity so there's no duplicate and inserts it into purchase table (to be recorded as a purchase). The current code i wrote below is still give duplicate on stock table. I don't really understand the logic for such action. Help is much appreciated, thanks!

here's the form code snippet:

<form name="purchaseform" method="post" onsubmit="return validateForm()" action="submitpurchaseadmin.php">
<table>
    <tr>
        <td>Form number</td>
        <td><input type="text" name="no"></td>
    </tr>
    <tr>
        <td>Item</td>
        <td>
        <select name="item">
        <option value="item 1">Item 1</option>
        <option value="item 2">Item 2</option>
        </select>
        </td>
    </tr>
    <tr>
        <td>Quantity</td>
        <td><input type="text" name="qty"></td>
    </tr>
    <tr>
        <td>Date</td>
        <td><input type="text" name="date" value="<?php echo date("d-m-Y"); ?>"></td>
    </tr>
    <tr>
        <td colspan="2" align="center"><input type="submit" name="submit" value="Save"></td>
    </tr>
</table>
</form>
 

and here's the submit code snippet:

<?php
include("connect.php");
 
$host="localhost";
$user="root";
$pass="";
$db_name="proyek";
$tbl_name="purchase";
 
mysql_connect("$host", "$user", "$pass")or die("Cannot connect to SQL."); 
mysql_select_db($db_name);
 
$no=$_POST['no'];
$item=$_POST['item'];
$qty=$_POST['qty'];
$date=$_POST['date'];
 
$query=("SELECT * FROM stock WHERE item='$item'");
$result=mysql_query($query);
$row=mysql_num_rows($result);
 
mysql_query("INSERT into return (no, item, qty, date) VALUES ('$no', '$item', '$qty', '$date')");
 
if($row==0)
{
    mysql_query("INSERT into purchase (item, qty, date) VALUES ('$item', '$qty', '$date')");
    mysql_query("INSERT into stock (item, qty) VALUES ('$item', '$qty')");
}
if($type==$row[2])
{
    mysql_query("INSERT into purchase (item, qty, date) VALUES ('$item', '$qty', '$date')");
    mysql_query("UPDATE stock SET qty=qty+'$qty' WHERE item='$item'");
}
else
{
    mysql_query("INSERT into purchase (item, qty, date) VALUES ('$item', '$qty', '$date')");
    mysql_query("INSERT into stock (item, qty) VALUES ('$item', '$qty')");
}
header("location:purchasehistory.php");
?>
 

[Dormilich]

what you want is INSERT … ON DUPLICATE KEY UPDATE …