INSERT INTO & LAST_INSERT_ID inside Query inside function

Question

Background: This is the first time I've posted a question here, but I have learned a lot from reading others' questions and answers and I really appreciate all of your help! I have spent several hours researching this problem and can't find a solution that works for me. The example below is from the MySQL website and thus far, seems to be the most reliable.

Goal: Ultimately, I want to link the user id from the registration form (foo for now) database to other databases (foo2 for now). I THINK I understand the INSERT INTO and the LAST_INSERT_ID and have tried many example I've read here. Assuming my code for that is correct (which it might not be), I think my question is regarding the syntax and code outside of that area (the entire function createRows). Do I need two $query or just the one? Many examples I've seen don't show the query parts or code beyond just the INSERT INTO statements and that's what I need to see.

Problem: I run this code and don't get any errors. However, data is only going into table foo2, completely skipping/bypassing table foo, which remains completely empty.

function createRows(){
    if (isset($_POST['submit'])) {
        global $connection;
        $text = $_POST['text'];

        $query = "INSERT INTO foo (id,text) VALUES(NULL,'$text')";
        $query = "INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'$text')";  

        $result = mysqli_query($connection, $query);
        if (!$result) {
            die('Query FAILED' . mysqli_error($connection));
        } else {
            echo "Success! New record created.";
        }
    }
}

You're not executing the first query. – Rajdeep Paul Feb 14 '16 at 17:47 — Rajdeep Paul, Feb 14 '16 at 17:47

score 1 · Answer 1 · edited May 23 '17 at 11:59

Change your code with this :

function createRows(){
    if (isset($_POST['submit'])) {
        global $connection;
        $text = $_POST['text'];

        $query = "INSERT INTO foo (id,text) VALUES(NULL,'$text')";

        $result = mysqli_query($connection, $query);
        if (!$result) {
            die('Query FAILED' . mysqli_error($connection));
        } else {
            $last = mysqli_insert_id($connection);
            $query1 = "INSERT INTO foo2 (id,text) VALUES($last,'$text')"; 
            mysqli_query($connection, $query1); 
            echo "Success! New record created.";
        }
    }
}

Also i'm confused with your first query.Why are you inserting null value in you id field? If your id is primary key then it should not be null.Also if you've assigned auto increment to it then you dont need to insert null values in it. In this case your first query will be :

"INSERT INTO foo (text) VALUES('$text')";

Side note : Your query is unsafe. Read this How can I prevent SQL injection in PHP?.

score 0 · Answer 2 · answered Feb 14 '16 at 17:52

$query = "INSERT INTO foo (id,text) VALUES(NULL,'$text')";
$query = "INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'$text')";

Your second query is overwriting the first query. Either execute them one at a time or put them in separate variables (example blow).

$queryOne = "INSERT INTO foo (id,text) VALUES(NULL,'$text')";
$queryTwo = "INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'$text')";

INSERT INTO & LAST_INSERT_ID inside Query inside function

2 Answers2