CODEWITHSUNDEEP
phpmysql
Lian Diktea
Lian Diktea

Reputation: 13

Proper way to manipulate database

My entry form I have an inventory database with tables like aluminium, iron etc... Each table contains a subcategory of items like aluminium_pala, iron_1.5inch and so on. The entry code is like this:

include("dbConnect.php");
$orderNo = $_POST["number"];

if(isset($_POST["mat1"])&&$_POST["mat1"]!=NULL)
{
    $mat1 = $_POST["mat1"];
    $selmat1 = $_POST["selmat1"];
    $amtmat1 = $_POST["amtmat1"];

    $query = "INSERT INTO $mat1 ($selmat1,orderNo) VALUES (-$amtmat1,$orderNo);";
    if(!($result = $mysqli->query($query)))
         print "<div class='error'>insertion failed. Check your data</div>";
}
if(isset($_POST["mat2"])&&$_POST["mat2"]!=NULL)
{
    $mat2 = $_POST["mat2"];
    $selmat2 = $_POST["selmat2"];
    $amtmat2 = $_POST["amtmat2"];

    $query = "INSERT INTO $mat2 ($selmat2,orderNo) VALUES (-$amtmat1,$orderNo);";
    if(!($result = $mysqli->query($query)))
         print "<div class='error'>insertion failed. Check your data</div>";
}... and it goes on till mat11

I am trying to collect each similar table (mat1, mat2..) and their corresponding item (selmat1, selmat2...) and bunch the all in one query. That is, instead of going 

INSERT INTO al_openable (zPala,orderNo) VALUES (23,14); 
INSERT INTO al_openable (outer,orderNo) VALUES (50,14);

I am trying to execute it like 

INSERT INTO al_openable (zPala,outer,orderNo) VALUES (23,50,14);

I need this to avoid duplicate foreign key entry(for $orderNo). One idea I've been considering is to use UPDATE if the order number is pre-existing. Do you guys think this is a good idea? And if so, what will be the best way to execute it? If not, how would a more experienced programmer solve this conundrum?

Upvotes: 0

Views: 40

Answers (1)

Nidhin David
Nidhin David

Reputation: 2474

I think this question is related to your query: Multiple Updates in MySQL

You may use ON DUPLICATE KEY UPDATE in combination with INSERT statement.

Upvotes: 1

Related Questions