Search code examples
mysqlselectinsertauto-increment

Do SELECT on the last INSERT in MySQL or INSERT data into 2 tables which have a common column that is auto generated by MySQL

I have a table named invisible with 3 fields (user_id , username, password), and another table named user_info with fields (user_id , first_name and last_name). user_id from user_info has foreign key constraint on user_id from invisible and this id is auto generated by MySQL using auto increment. I want to insert data about user into these two tables. As of now what i had in mind was to insert a random unique username using INSERT

$username=bin2hex(openssl_random_pseudo_bytes(10));
$password=bin2hex(openssl_random_pseudo_bytes(10));
$query="INSERT INTO invisible SET username='$username', password='$password'";
mysqli_query($query);

And then do

mysqli_query("INSERT INTO user_info SET user_id=(SELECT user_id FROM invisible WHERE username=$username AND password=$password) , first_name=$first_name, last_name=$lastname");

This ,however, i think is a bad way of doing it, since the username and password need not be unique.

So my question is, is there a way to select the user_id from last insert without having to deal with username and password, or better yet, some way to do the whole thing in a better way, like maybe in a single step?

Solution

  • use mysqli::$insert_id:

    $username=bin2hex(openssl_random_pseudo_bytes(10));
$password=bin2hex(openssl_random_pseudo_bytes(10));
$query="INSERT INTO invisible SET username='$username', password='$password'";
mysqli_query($query);

$inserted_user_id = mysqli::$insert_id;

mysqli_query("INSERT INTO user_info SET user_id=$inserted_user_id, first_name=$first_name, last_name=$lastname");