Sergey M. Sergey M. - 7 months ago 43
SQL Question

Insert the mysqli_insert_id inside separate table using PHP

I'd like to submit new registered user with AUTO_INCREMENT id inside table1 and at the same time insert mysqli_insert_id or LAST_INSERT_ID what ever you want to call it inside table2. I can echo id out, but I need it inside my table2. How can I accomplish that? Please help!

Here is my code for reference:

<?php

if(isset($_POST['submit'])) {

require 'connect.php';

$sql = "INSERT INTO table1 (username, password)
VALUES ('".$_POST["username"]."','".$_POST["password"]."')";

if (mysqli_query($con, $sql)) {
$last_id = mysqli_insert_id($con);
echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($con);
}
mysqli_close($con);
}

?>

Answer

Use the variable from the last inserted id from the (successful) query to INSERT into the other table.

This is a rough sketch as we don't know what the db schema is for your second table and is quite unclear, so you will need to do (fill in) the rest.

if (mysqli_query($con, $sql)) {
   $last_id = mysqli_insert_id($con);
     echo "New record created successfully. Last inserted ID is: " . $last_id;

     $sql = mysqli_query($con, "INSERT INTO table2 (col) VALUES ('$last_id')")
            or die(mysqli_error($con));
}

Passwords

I also noticed that you may be storing passwords in plain text. This is not recommended.

Use one of the following:

Other links:

Important sidenote about column length:

If and when you do decide to use password_hash() or the compatibility pack (if PHP < 5.5) https://github.com/ircmaxell/password_compat/, it is important to note that if your present password column's length is anything lower than 60, it will need to be changed to that (or higher). The manual suggests a length of 255.

You will need to ALTER your column's length and start over with a new hash in order for it to take effect. Otherwise, MySQL will fail silently.

Comments