I have three tables:
1- RegEntradaNutricia with the following fields: InputDate, SupplyDocument, ProductDescription, Quantity, Price
2- RegSaidaNutricia with the following fields: DataSaida, ProductDescription, Quantity.
3- StockNutricia with the following fields: ProdNutricia, Quantity .
I would like to insert the form into the RegEntradaNutricia table, to add the quantity of this form to the quantity of the StockNutricia table to control the stock.
And when I insert from another form into the RegSaidaNutricia table, I would remove the quantity from that form to the quantity of the StockNutricia table.
This is the code and 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>