Update in stock table

Question

I have three tables:

1- Regentradanutricia with the following fields: Datainput, Codfornecedor, Descricaoprod, Quantity, Price.

2- Regsaidanutricia with the following fields: Date output, Description, Quantity.

3- Stocknutricia with the following fields: Prodnutricia, Quantidade.

I would like to insert from the form into the table Regentradanutricia, to add the quantity of this form to the quantity of the table Stocknutrition to control the stock.

And when to insert another form into the table Regsaidanutricia, to remove the quantity of that form from the quantity in the table Stocknutrition.

This is the code and the form I have:

<?php 
$servername = "xxxxxxxxx";
$username = "xxxxx";
$password = "xxxxxx";
$dbname = "xxxxxxx";

$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset('utf8');

$data = $_POST['DataEntrada'];
$fornecedor = $_POST['CodFornecedor'];
$descricao = $_POST['DescricaoProd']; 
$quantidade = $_POST['Quantidade'];
$preco = $_POST['Preco'];

$sql = "INSERT INTO RegEntradaNutricia (`DataEntrada`,`CodFornecedor`,`DescricaoProd`,`Quantidade`,`Preco`)
VALUES ('$data','$fornecedor','$descricao','$quantidade','$preco')";

if ($conn->query($sql) === TRUE);

$rowCount = $query->num_rows;

$conn->close();
 ?> 

 <form name="form1" method="POST" onsubmit="return form_validation()" >

<h1><center><strong>Entrada de Produtos Nutricia</strong></center></h1></br>

<p><h5><strong>Data Entrada</strong></h5> <input type="date" required="" id="DataEntrada" name="DataEntrada" /><br/></p>
<p><h5><strong>Código Fornecedor</strong></h5> <input type="text" id="CodFornecedor" name="CodFornecedor" required="" size="120" /><br/></p>
<label for=""><h5><strong>Produto</strong></h5></label>
<select name="DescricaoProd">
       <option value="0">Selecione Produto</option>
        <?php
         $servername = "xxxxx";
$username = "xxxxx";
$password = "xxxxx";
$dbname = "xxxxxx";

$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset('utf8'); 
        
         $sql = "SELECT * FROM ProdNutricia ORDER BY ProdNutricia ASC";
         $qr = mysqli_query($conn, $sql);
         while($ln = mysqli_fetch_assoc($qr)){
            echo '<option value="'.$ln['ProdNutricia'].'">'.$ln['ProdNutricia'].'</option>';
         }
      ?>        
    </select>
<p><h5><strong>Quantidade</strong></h5> <input type="text" id="Quantidade" name="Quantidade" required="" size="120" /><br/></p>	
<p><h5><strong>Preço</strong></h5> <input type="text" id="Preco" name="Preco" required="" size="120" /><br/></p>	

<input type="submit" value="Registar"/>
</form>

Answer 1

You can put the UPDATE in your php after the INSERT executed, example:

After the INSERT in the table Regentradanutricia:

$sql = "UPDATE StockNutricia SET Quantidade = Quantidade +" . $quantidade . " WHERE StockNutricia.ProdNutricia =" . $descricao

After the INSERT in the table Regsaidanutricia:

$sql = "UPDATE StockNutricia SET Quantidade = Quantidade -" . $quantidade . " WHERE StockNutricia.ProdNutricia =" . $descricao

Important:

I noticed that the relationship occurs by description, the ideal is that there is a unique form of identification, a IDProduto for example. This involves modeling your bank and would be subject to another question.