PHP/MySQL insert row then get 'id'

Question

The 'id' field of my table auto increases when I insert a row. I want to insert a row and then get that ID.

I would do it just as I said it, but is there a way I can do it without worrying about the time between inserting the row and getting the id?

I know I can query the database for the row that matches the information that was entered, but there is a high change there will be duplicates, with the only difference being the id.

Answer 1

The MySQL function LAST_INSERT_ID() does just what you need: it retrieves the id that was inserted during this session. So it is safe to use, even if there are other processes (other people calling the exact same script, for example) inserting values into the same table.

The PHP function mysql_insert_id() does the same as calling SELECT LAST_INSERT_ID() with mysql_query().

Answer 2

I found an answer in the above link http://php.net/manual/en/function.mysql-insert-id.php

The answer is:

mysql_query("INSERT INTO tablename (columnname) values ('$value')");        
echo $Id=mysql_insert_id();

Answer 3

As to PHP's website, mysql_insert_id is now deprecated and we must use PDO. To do this with PDO, proceed as following:

$db = new PDO('mysql:dbname=database;host=localhost', 'user', 'pass');
$statement = $db->prepare('INSERT INTO people(name, city) VALUES(:name, :city)');
$statement->execute( array(':name' => 'Bob', ':city' => 'Montreal') );

echo $db->lastInsertId();

Answer 4

Try this... it worked for me!

$sql = "INSERT INTO tablename (row_name) VALUES('$row_value')";
    if (mysqli_query($conn, $sql)) {
    $last_id = mysqli_insert_id($conn);
    $msg1 = "New record created successfully. Last inserted ID is: " . $last_id;
} else {
    $msg_error = "Error: " . $sql . "<br>" . mysqli_error($conn);
    }