MYSQL. Failed to insert data to different tables

Question

I am building a .php to insert data to two different tables. I already used mysqli_multi_query() function and mysqli_insert_id(), however, only the submitted data only appear in one of the table. I already stuck in this hurdle for a few days, can anyone please help me find out what's wrong with my code.

Here are the 2 tables:

Table patients: patient_id(PK)(AI), name, age, email,mobile.....

Table emergency_contact: id(PK)(AI), patient_id(FK), name, relationship, mobile

    <?php
session_start();
?>
<?php

$pname=$_POST['pname'];
$pid=$_POST['pid'];
$hkid=$_POST['hkid'];
$dob=$_POST['dob'];
$age=$_POST['age'];
$gender=$_POST['gender'];
$mobile=$_POST['mobile'];
$email=$_POST['email'];
$address=$_POST['address'];
$ename=$_POST['ename'];
$relationship=$_POST['relationship'];
$emobile=$_POST['emobile'];

$con = new mysqli("localhost","root","2013700910","2013700910");

$last_id = $con->insert_id;

if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}  
 echo "Connected successfully";

$sql="INSERT INTO patients (name, hkid, date_of_birth, age, gender, mobile, email, address) 
                            VALUES('$pname', '$hkid' ,'$dob' ,'$age', '$gender', '$mobile', '$email', '$address');
      INSERT INTO products (name, relationship, mobile, patient_id) 
                            VALUES('$ename', '$relationship', '$emobile', '$last_id')";

if ($con->multi_query($sql) === TRUE) {

    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $con->error;
}

$con->close();

?>

(P.S. *only '$pname', '$hkid' ,'$dob' ,'$age', '$gender', '$mobile', '$email', '$address'are successfully inserted into the database. )

You can't do that in mysql. Instead you could use 2 statements or create a `stored procedure` for the task. — Professor Abronsius, Nov 09 '15 at 15:43
Also, your code is vulnerable to injection - you should look at [preparing your statements](http://php.net/manual/en/pdo.prepared-statements.php). — Ben, Nov 09 '15 at 15:44
RTFM: http://php.net/manual/en/mysqli.multi-query.php "Returns false if the **FIRSTSTATEMENT FAILED**. To retrieve subsequent errors from other statements you have to call mysqli_next_result() first". — Marc B, Nov 09 '15 at 15:47
@RamRaider: false, that's why there's the multi_query function. but OP's problem is precisely the reason it shouldn't be used - it's much harder to figure out which of the multiple statements failed, and whatever minor efficiency gains you get from doing only "one" query call are outweighed by the hassles it causes. — Marc B, Nov 09 '15 at 15:48
Is it just me, or did you write that your second table is 'emergency_contact', but in the query your second statement tries to insert data to 'products' table... — Ron Dadon, Nov 09 '15 at 15:49
@MarcB - to be fair I have to agree and to my shame I must admit I didn't see the use of `multi_query`. — Professor Abronsius, Nov 09 '15 at 15:54
Write it as 2 seperate queries, ESPECIALLY as you are trying to use the `$id` of the newly created `Patient Row` and store it in the `emergency_contact` table as `patient_id`. Then move the `$last_id = $con->insert_id;` to after the first INSERT and BEFORE the second INSERT. Currently its complete useless where it is. — RiggsFolly, Nov 09 '15 at 15:57
`multi_query` + User input in SQL = bad things coming. Use parameterized queries. http://php.net/manual/en/mysqli.quickstart.prepared-statements.php and/or http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — chris85, Nov 09 '15 at 16:02
Thanks all for the help. I should work on the injection problem now. I find — Rex Chau, Nov 10 '15 at 13:22

score 1 · Accepted Answer · answered Nov 09 '15 at 16:11

I have not started the code but I think you have to separate the queries. You can do something like this:

        $sql="INSERT INTO patients (name, hkid, date_of_birth, age, gender, mobile, email, address) 
    VALUES('$pname', '$hkid' ,'$dob' ,'$age', '$gender', '$mobile', '$email', '$address')";
        if (!$con->query($sql)) {
            echo "insert failed, error: ", $com->error;
        } else {
            $last_id = $con->insert_id;
            $sql="INSERT INTO products (name, relationship, mobile, patient_id) 
VALUES('$ename', '$relationship', '$emobile', '$last_id')";
            $con->query($sql);
            ...
        }

PS: I am sorry it is not formatted well.

Thank you for your code demonstration. I got it working now. — Rex Chau, Nov 10 '15 at 12:55

score 0 · Answer 2 · answered Nov 09 '15 at 16:02

You have many problems, but this is the cause:

 $last_id = $con->insert_id;

You cannot generate an insert ID BEFORE the insert has been performed. MySQL (and) PHP cannot time travel.

You need to do something more like:

query("INSERT ... parent record stuff ...");
$id = last_insert_id();
query("INSERT .... child record stuff .... ($id)");

multi_query() should be avoided when you're doing parent/child inserts because of this. You COULD do this all in a single call:

INSERT ... ; INSERT ... VALUES(last_insert_id(), ...)

But since insert_id() only ever returns the ID of the last-performed insert, there would be NO way to insert multiple child records. e.g. this next query would be totally invalid:

INSERT ... parent ...; INSERT child #1 (last_insert_id()); INSERT child #2 (last_insert_id());

The #2 child would actually be insert the ID generated for the #1 child, NOT the original parent record.

And as mentioned above, it gets difficult to figure out WHICH query in the chain failed. it's far easier to do individual query() calls, and check for errors after each, plus retrieve any metadata (e.g. last_insert_id()).

score 0 · Answer 3 · answered Nov 10 '15 at 13:34

Thanks all for the help. I should work on the injection problem now. I found the following code which I can use to avoid the injection problem.

<pre>
    $stmt = $dbh->prepare("INSERT INTO Customers (CustomerName,Address,City) 
    VALUES (:nam, :add, :cit)");
    $stmt->bindParam(':nam', $txtNam);
    $stmt->bindParam(':add', $txtAdd);
    $stmt->bindParam(':cit', $txtCit);
    $stmt->execute();
<code>

Can anyone explain how should I modified my code? Should I use $stmt->bindParam(':pnam', $txtpname); to replace the $pname=$_POST['pname'];

score 0 · Answer 4 · edited May 23 '17 at 11:44

If you decide to continue with mysqli like it is in the first post you need to use mysqli_real_escape_string. Here is a link to the function in php.net Here is an example:

$con = new mysqli("localhost","root","2013700910","2013700910");

$pname=$con->real_escape_string($_POST['pname']);
$pid=$con->real_escape_string($_POST['pid']);
$hkid=$con->real_escape_string($_POST['hkid']);
$dob=$con->real_escape_string($_POST['dob']);
$age=$con->real_escape_string($_POST['age']);
$gender=$con->real_escape_string($_POST['gender']);
$mobile=$con->real_escape_string($_POST['mobile']);
$email=$con->real_escape_string($_POST['email']);
$address=$con->real_escape_string($_POST['address']);
$ename=$con->real_escape_string($_POST['ename']);
$relationship=$con->real_escape_string($_POST['relationship']);
$emobile=$con->real_escape_string($_POST['emobile']);
.....

If you decide to rewrite you code using PDO, like it is in you second post, prepare - execute is the right way to do it and it automatically sanitize your values. Here is a good discussion about this.

MYSQL. Failed to insert data to different tables

4 Answers4