Update in the stock table

1

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>
    
asked by anonymous 29.12.2017 / 19:00

1 answer

0

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

After the INSERT in the RegEntradaNutricia table:

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

After the INSERT in the RegSaidaNutricia table:

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

Important:

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

    
29.12.2017 / 19:24