Insert data in two separate tables

Question

I am fiddling with some code to pass time, and I am unsure if I'm doing this the right way. I would really appreciate suggestions if possible of better ways of doing this.

I took a query and added a union to make essentially the same query but with a variable that has text attached to it such as 'IN'.

below you will see what i have tried, I did various things but i fear I'm wasting my time since surely there are a better way of making this work.

<?php 
require_once("connection/link.php");
 if(
     (isset($_POST['id'])&& $_POST['id'] !='') && 
 (isset($_POST['upc'])&& $_POST['upc'] !='') && 
 (isset($_POST['category'])&& $_POST['category'] !='') &&
 (isset($_POST['state'])&& $_POST['state'] !='') &&
 (isset($_POST['quantity'])&& $_POST['quantity'] !='')
    )
    { 
$id = $link->real_escape_string($_POST['id']);
$upc = $link->real_escape_string($_POST['upc']);
$category = $link->real_escape_string($_POST['category']);
$quantity = $link->real_escape_string($_POST['quantity']);
$state = "IN";
$query = mysqli_query($link, "SELECT * FROM products WHERE id='".$id."'");

    if (!$query)
    {
        die('Error: ' . mysqli_error($link));
    }
if(mysqli_num_rows($query) > 0){

    echo "Product code already exists";
    } else
    {
$sql="INSERT INTO products (id, upc, category, quantity) 
        VALUES ('".$id."', '".$upc."', '".$category."', '".$quantity."')
            UNION
        INSERT INTO transactions (id, upc, category, quantity, state) 
        VALUES ('".$id."', '".$upc."', '".$category."', '".$quantity."', '".$state."')";
if(!$result = $link->query($sql)){
die('There was an error running the query [' . $link->error . ']');
    }   
else
    {
echo "Product was added successfully!";
        }
    }
}
?>

This would create a historic log of all products added and whether they are IN or OUT. Mind you this is just the IN part of this whole ordeal.

You have to do two separate queries. You can only use `UNION` to combine `SELECT` queries, not anything else. — Barmar, Oct 24 '19 at 19:28
You should also learn to use prepared statements and parameters rather than concatenating variables into SQL. — Barmar, Oct 24 '19 at 19:29
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Oct 24 '19 at 19:36
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Oct 24 '19 at 19:36
You should create two separate prepared statements and execute them one after the other. — Dharman, Oct 24 '19 at 19:38
So this is what i am doing now except im not doing prepared statements as i dont know how yet. If i have two queries can't i just run them both like this ? ```if(!$result = $link->query($sql, $sql1)){ ``` — ZerkYoAGS, Oct 24 '19 at 19:43

score 2 · Accepted Answer · answered Oct 24 '19 at 19:42

You have to do two separate queries. Put them into a transaction to ensure that they're done atomically.

You should also use prepared statements to prevent SQL-injection.

You don't need a variable for IN, you can just hard-code it into the SQL.

if(!empty($_POST['id']) && !empty($_POST['upc']) && !empty($_POST['category']) && !empty($_POST['state']) && !empty($_POST['quantity'])) {
    $prod_stmt = $link->prepare("INSERT INTO products (id, upc, category, quantity, state) 
        VALUES (?, ?, ?, ?)");
    $prod_stmt->bind_param("issi", $_POST['id'], $_POST['upc'], $_POST['category'], $_POST['quantity']);
    $trans_stmt = $link->prepare("INSERT INTO transactions (id, upc, category, quantity, state) 
        VALUES (?, ?, ?, ?, 'IN')");
    $trans_stmt->bind_param("issi", $_POST['id'], $_POST['upc'], $_POST['category'], $_POST['quantity']);
    $link->begin_transaction();
    $prod_stmt->execute();
    $trans_stmt->execute();
    $link->commit();
}

You can also use !empty(variable) instead of checking both isset(variable) and variable != '' (note that this simplification isn't appropriate for variables where 0 or null are valid values, since that's considered empty).

Thanks a lot for that @Barmar, I tried it but, it has not worked although it gave no errors. I added values such as ```VALUES ('".$id."', '".$upc."', '".$category."', '".$quantity."')");``` & ```VALUES ('".$id."', '".$upc."', '".$category."', '".$quantity."', 'IN')");``` The code is intact other than that. Any idea why ? heres a screenshot of my file. [link](https://i.gyazo.com/9650c7904248a958edb43425f07fbbd0.png) — ZerkYoAGS, Oct 24 '19 at 19:54
Add error checking to the code, see https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments — Barmar, Oct 24 '19 at 20:03
So i Added ```mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);``` before my mysqli connection but still i dont get any errors. — ZerkYoAGS, Oct 24 '19 at 20:15

Insert data in two separate tables

1 Answers1