Is there a way to insert data to multiple tables? php mysql

Question

I've been trying to insert data into multiple tables from my web page into the data base, and I can't really figure it out!

I have this page where a user can make a new order and first he needs to take the Clients details:

name, age, email etc.

and also his car details:

the car model and the year of fabrication

In my database I have 2 tables that are linked 1 - Clients(Client_id (PK), name, email, id_Car(fK) and the second one is Cars (id_Car, model, year_of_fabrication).

My primary keys are Auto_Incremented so I don't have to pass values.

The issue, I think, is at my Foreign Key because from the website I don't pass any value to the Clients table but I don't really know how insert data into clients and cars table at the same time.

This is my form for the order.

<form action="include/comanda.inc.php">
    <div class="comanda"> 
        <h2 id="titlu">Detaliile clientului:</h2>

        <label for="client">Numele clientului: </label>
        <input type="text" name="client">

        <label for="client">CNP: </label>
        <input type="text" name="cnp">

        <label for="client">Sex: </label>
        <select name="sex" id="sex">
            <option value="M">M</option>
            <option value="F">F</option>
        </select>

        <label for="client">Email: </label>
        <input type="text" name="email">

        <label for="client">Sector: </label>
        <select name="sector" id="sectorCl">
            <option value="1">Sector 1</option>
            <option value="2">Sector 2</option>
            <option value="3">Sector 3</option>
            <option value="4">Sector 4</option>
            <option value="5">Sector 5</option>
            <option value="6">Sector 6</option>
        </select>

    
        <label for="client">Marca masina: </label>
        <input type="text" name="masina">

        <!-- DROPDOWN LIST CU ANII DE LA 2020 LA 2000-->
        <label for="client">Anul Fabricatiei: </label>
        <select name="an_fabricatie" id="an_masina">
        <option value="2020">2020</option>
        <option value="2019">2019</option>
        <option value="2018">2018</option>
        <option value="2017">2017</option>
        <option value="2016">2016</option>
        <option value="2015">2015</option>
        <option value="2014">2014</option>
        <option value="2013">2013</option>
        <option value="2012">2012</option>
        <option value="2011">2011</option>
        <option value="2010">2010</option>
        </select>

And this is my script for the order:

<?php 
if(isset($_POST["creaza_comanda"])){
    require 'dbh.inc.php';

    $nume = $_POST["client"];
    $cnp = $_POST["cnp"];
    $sex = $_POST["sex"];
    $email = $_POST["email"];
    $sector = $_POST["sector"];
    $masina = $_POST["masina"];
    $an_fabricatie = $_POST["an_fabricatie"];
    $angajat = $_POST["nume-angajat"];
    $den_furnizor = $_POST["den_furnizor"];
    $oras_furnizor = $_POST["oras_furnizor"];
    $piesa = $_POST["productName"];
    $pret = $_POST["price"];

    if(!preg_match("/^[a-zA-Z ]*$/", $nume)){
        header("Location: ../comanda_noua.php?eroare");
        exit();
    } else if(!preg_match("/^[0-9]*$/",$cnp)){
        header("Location: ../comanda_noua.php?msg2");
        exit();
    } else if (!filter_var($email, FILTER_VALIDATE_EMAIL)){
        header("Location: ../comanda_noua.php?msg2");
        exit();
    } else {
        
       $sql = "INSERT INTO masini (marca, an_fabricatie) VALUES ('$masina', $an_fabricatie);";
       
       if(mysqli_query($conn,$sql)){
           header("Location: ../comanda_noua.php?succes");
           exit();
       } else{
            echo "Error ". $sql . ":-" . mysqli_error($conn);
       }
       mysqli_close($conn);
    }
    header("Location: ../comanda_noua.php");
    exit;
}

Your script is wide open to [SQL Injection](https://stackoverflow.com/questions/601300/what-is-sql-injection) attacks. You should be using prepared statements. — esqew, Jun 22 '20 at 14:53
Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) You should consider using [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenated values — RiggsFolly, Jun 22 '20 at 14:54
`else if` (with a space) is javascript style. `elseif` (without space) is php style. Please see the php documentation regarding the functionality of `ctype_digit()` instead of `preg_match("/^[0-9]*$/"`. Never show `mysqli_error()` to your users. @Timi — mickmackusa, Jun 22 '20 at 21:45

score -2 · Answer 1 · answered Jun 22 '20 at 15:05

you just have to get the last insert row id from your main table and pass it to the derived table as foreign key. when you insert data into Cars table using DOM object then you need to write this line just after this:

$sql = "INSERT INTO masini (marca, an_fabricatie) VALUES ('$masina', $an_fabricatie);"; 
if(mysqli_query($conn,$sql)){
     $last_id = $conn->insert_id;     // this will gives you last inserted id
    // just after this you can add data to another table
    $sql1 = "INSERT INTO Clients (<column_name>) VALUES ('<values>');";  // change column name and values here;
    if(mysqli_query($conn,$sql1)){
       // after all data insert add your next logic code here.or simply redirect it.
    }
}

Or you can also get ID by using this :

 $last_id = mysqli_insert_id($conn);

try this, I think this will help you.

This answer is unstable/insecure because it is not using prepared statements with bound parameters. This advice should not be implemented by the OP nor researchers. — mickmackusa, Jun 22 '20 at 21:43

Is there a way to insert data to multiple tables? php mysql

1 Answers1