What is the proper way to perform multiple insert queries in multiple table in a relational mysql database?

Question

I have a scenario, that how will I ensure if all of my insert queries works properly. I am using PHP for the backend coding and mysql for my database. Below I am describing my problem briefly.

I have two tables. One table is for login_details and other table is for personal_details. My table structure is here.

Table login_details here user_id is the unique primary key and sl_num is auto incremental:

th{
  padding: 3px;
  border: 1px solid black;
}

td{
  padding: 3px;
  border: 1px solid black;
}

<table>
   <thead>
      <th>sl_num</th>
      <th>user_id</th>
      <th>email</th>
      <th>password</th>
   </thead>
   <tbody>
      <tr>
        <td>1</td>
        <td>123</td>
        <td>daniel@gmail.com</td>
        <td>5567</td>
      </tr>
      <tr>
        <td>2</td>
        <td>246</td>
        <td>lucy@yahoo.com</td>
        <td>9908</td>
      </tr>
   </tbody>
</table>

and my second table is personal_details. Here also the user_id is the primary key and this key also maintain relation among the two tables and sl_num is auto incremental:

th{
  padding: 3px;
  border: 1px solid black;
}

td{
  padding: 3px;
  border: 1px solid black;
}

<table>
   <thead>
      <th>sl_num</th>
      <th>user_id</th>
      <th>name</th>
      <th>address</th>
      <th>age</th>
   </thead>
   <tbody>
      <tr>
        <td>1</td>
        <td>123</td>
        <td>Daniel</td>
        <td>San Francisco</td>
        <td>23</td>
      </tr>
      <tr>
        <td>2</td>
        <td>246</td>
        <td>Lucy</td>
        <td>London</td>
        <td>27</td>
      </tr>
   </tbody>
</table>

And, I use this code to insert values in these tables.

<?php
   $user_id = uniqid();
   $email = $_POST['email'];
   $password = $_POST['password'];
   $name = $_POST['name'];
   $address = $_POST['address'];
   $age = $_POST['age'];

   $con = mysqli_connect('localhost', 'username', 'password', 
                         'user_db');

   $query = "INSERT INTO `login_details` (`user_id`, `email`, 
             `password`) VALUES ('$user_id', '$email', '$password')";
   mysqli_query($con,$query);

   $query2 = "INSERT INTO `personal_details` (`user_id`, `name`, 
             `address`, `age`) VALUES ('$user_id', '$name', '$address', 
             '$age')";
   mysqli_query($con,$query);

   echo 'User created successfully';?>

But a wired problem happened once. A third user name 'John Doe' trying to create his account. The details of the personal_details table inserted successfully but the for some reasons the details of the login_details is not inserted. So, the user 'John Doe' has personal information on the table but as he doesn't have any login information so, he can't login.

So, what is the best solution of these kinds of multi insert situation. How can I prevent this situation. It means, if one row inserted into login_details then the system has to ensure that the relative row is also inserted into personal_details table. If two are inserted successfully then the transaction happened or if one row inserted and other not then the system will automatically discarded the inserted row and show a fail message.

Did you check your PHP error log to clear up the *for some reason*? Also, run `SHOW CREATE TABLE ` so we can see internal structures. You seem to conflate primary and foreign keys: *user_id is the primary key and this key also maintain relation among the two tables*. Finally, why create an autonumber in MySQL but a `uniqid()` in PHP? — Parfait, Nov 16 '17 at 19:23
I am agree with AndySavage and Strawberry. But @Parfait I am not clear what you have said about the structure of the database. Shouldn't I keep the autonumber field? — SAAN07, Nov 17 '17 at 20:13
I am suggesting the reverse that PHP's `$user_id = uniqid();` is not needed since MySQL's autonumber *is* the unique identifier. — Parfait, Nov 17 '17 at 23:27
Not sure what you mean by *guessable* but primary key IDs should never be seen and certainly not managed in application code. — Parfait, Nov 26 '17 at 16:21

score 0 · Accepted Answer · answered Nov 16 '17 at 19:04

The reason the query failed is probably because you're not escaping any of the variables in an SQL context. That means that if there's a ' somewhere in one of the strings, the SQL statement becomes invalid. This is known as an SQL injection attack and can quickly leave your application vulnerable to attacks.

A common way of mitigating these attacks is to use a prepared statement instead.

That would at least allow you to cancel the second query if the first one fails. But what if the second one fails (for some reason when you've used a proper .. this shouldn't really happen if you've escaped your variables properly, but disks can run full, etc.).

You can wrap both statements in an SQL transaction, and then either commit or roll back the transaction if any of the queries fail. This assumes that your table / database type in MySQL supports transactions and that auto commit is disabled.

You can start a transaction with mysqli by calling mysqli_begin_transaction($con).

You can then commit or rollback back the transaction by calling either mysqli_commit or mysqli_rollback.

To detect if the previous statement failed, you can call mysqli_error.

Thank you MatsLindh for your suggestion. I think this will solve my problem. — SAAN07, Nov 17 '17 at 20:09

What is the proper way to perform multiple insert queries in multiple table in a relational mysql database?

1 Answers1