Hướng dẫn last insert id mysql

Get ID of The Last Inserted Record

If we perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field, we can get the ID of the last inserted/updated record immediately.

Nội dung chính Show

Get ID of The Last Inserted Record
Example (MySQLi Object-oriented)
Example (MySQLi Procedural)
Example (PDO)
Description
Return Values

In the table "MyGuests", the "id" column is an AUTO_INCREMENT field:

CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

The following examples are equal to the examples from the previous page (PHP Insert Data Into MySQL), except that we have added one single line of code to retrieve the ID of the last inserted record. We also echo the last inserted ID:

Example (MySQLi Object-oriented)

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '')";

if ($conn->query($sql) === TRUE) {
$last_id = $conn->insert_id;
echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
echo "Error: " . $sql . "
" . $conn->error;
}

$conn->close();
?>

Example (MySQLi Procedural)

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '')";

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

mysqli_close($conn);
?>

Example (PDO)

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '')";
// use exec() because no results are returned
$conn->exec($sql);
  $last_id = $conn->lastInsertId();
  echo "New record created successfully. Last inserted ID is: " . $last_id;
} catch(PDOException $e) {
  echo $sql . "
" . $e->getMessage();
}

$conn = null;
?>

(PHP 4, PHP 5)

mysql_insert_id — Get the ID generated in the last query

Description

mysql_insert_id(resource $link_identifier = NULL): int

Parameters

link_identifier

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() had been called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

Return Values

The ID generated for an AUTO_INCREMENT column by the previous query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or false if no MySQL connection was established.

Examples

Example #1 mysql_insert_id() example

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password'); if (!$link) { die('Could not connect: ' . mysql_error()); } mysql_select_db('mydb');mysql_query("INSERT INTO mytable (product) values ('kossu')"); printf("Last inserted record has id %d\n", mysql_insert_id()); ?>

Notes

Caution

mysql_insert_id() will convert the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). If your AUTO_INCREMENT column has a column type of BIGINT (64 bits) the conversion may result in an incorrect value. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query. For more information about PHP's maximum integer values, please see the integer documentation.

Note:
Because mysql_insert_id() acts on the last performed query, be sure to call mysql_insert_id() immediately after the query that generates the value.

Note:
The value of the MySQL SQL function LAST_INSERT_ID() always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries.

function insert_join($catid, $disc_id) { // insert a new item into the database $conn = db_connect(); // insert new item $demande = "insert into categories_disc values ('', '".$catid."', '".$disc_id."')"; $resultat = $conn->query($demande); if (!$resultat) { return false; } else { return $conn->insert_id; // function will now return the ID instead of true. }

}

?>

Then, on the other side, let us call this function as follows :
$cat_id = insert_join($catid, $disc_id);
if($cat_id !== false) {
            echo 
"Category stuff was added to the database as follows : 
";
        echo "
ID de la category : ".$cat_id."
";        }?>

foros (_AT_) anthalia.com ¶

15 years ago

Forget about using MAX to get the last inserted id. Race conditions like other users inserting between your SELECT MAX(.. and your INSERT may render your id unusable.

The WAY to get the id is by using mysql_insert_id() or the mysql SQL function LAST_INSERT_ID().

Take care, if using mysql_insert_id() you should provide the resource returned by the mysql_connect, not the resultset returned by mysql_query.

elinor dot hurst at REMOVETHIS dot gmail dot com ¶

14 years ago

I don't get all the fuss around this.

I read: "The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients."

See: http://dev.mysql.com/doc/refman/5.0/es/mysql-insert-id.html

I can't really see what's inaccurate about that.

"In the case of a multiple-row INSERT statement, mysql_insert_id() returns the first automatically generated AUTO_INCREMENT value; if no such value is generated, it returns the last last explicit value inserted into the AUTO_INCREMENT column."

I must be missing something here but why would you insert multiple rows and then only handle the last one with some favoured behaviour? You could just as well insert them one at a time and then handle each row separately with the latest id.

I can't see what's wrong with that.

However I can see what's wrong with simply using max(my_table.id_column) because of the concurrent access issues this would imply.

Anonymous ¶

16 years ago

Take care of setting an empty value for the AUTO_INCREMENT Field else you never get a value except zero returned from mysq_insert_id() ....

Ciao Ephraim

hoangvu4000 at gmail dot com ¶

9 years ago

How to get ID of the last updated row in MySQL?

75 down vote I've found an answer to this problem :)

by Pomyk

SET @update_id := 0; UPDATE some_table SET row = 'value', id = (SELECT @update_id := id) WHERE some_other_row = 'blah' LIMIT 1; SELECT @update_id; EDIT by aefxx

This technique can be further expanded to retrieve the ID of every row affected by an update statement:

SET @uids := null; UPDATE footable SET foo = 'bar' WHERE fooid > 5 AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) ); SELECT @uids; This will return a string with all the IDs concatenated by a colon.

(questions: 1388025 form stackoverflow)

dhiraj dot webdeveloper at gmail dot com ¶

4 years ago

MySQLi Procedural //--------------------------------------------- $last_id = mysqli_insert_id($conn);

//--------------------------------------------- $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB";// Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); }$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', '')";

if (

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

vksgeneric at hotmail dot com ¶

22 years ago

You can't do an INSERT DELAYED and expect to get anything but zero, for it runs in a separate thread, and mysql_insert_id() is tied to the current thread. Vlad

heiligkind at yahoo dot de ¶

16 years ago

If you insert a data row by using the ON DUPLICATE KEY UPDATE clause in an INSERT-statement, the mysql_insert_id() function will return not the same results as if you directly use LAST_INSERT_ID() in MySQL.

See the following example:

// insert a datarow, primary key is auto_increment // value is a unique key $query = "INSERT INTO test (value) VALUES ('test')"; mysql_query( $query );

echo 'LAST_INSERT_ID: ', mysql_query( "SELECT LAST_INSERT_ID()" ), ' mysql_insert_id: ', mysql_insert_id();

?>

This will print:

LAST_INSERT_ID: 1 mysql_insert_id: 1

In this case the function returns the same as the MySQL-Statement. But see the insert on an existing key:

$query = "INSERT INTO test (value) VALUES ('test') ON DUPLICATE KEY UPDATE value = 'test2'"; mysql_query( $query );

echo 'LAST_INSERT_ID: ', mysql_query( "SELECT LAST_INSERT_ID()" ), ' mysql_insert_id: ', mysql_insert_id();

?>

This will print:

LAST_INSERT_ID: 2 mysql_insert_id: 1

By using the ON DUPLICATE KEY UPDATE clause, only the old datarow will be modified, if the INSERT statement causes a duplicate entry, but the LAST_INSERT_ID() function returns the next auto_increment value for the primary key, which is by the way not set as the next auto_increment value in the database.

The mysql_insert_id() function returns the primary key of the old (and changed) data row. For me this is the right operation method, because the LAST_INSERT_ID() function returns a value which is not referenced to a data row at all.

Greets from Munich.

heiligkind

louis at intoplay dot com ¶

15 years ago

If mysql_insert_id() returns 0 or null, check your auto increment field is not being set by your sql query, also if you have multiple db connections like I did, the solution is to create a seperate db connection for this query.

Steve Bond ¶

18 years ago

If you use this function after doing an INSERT ... SELECT to insert multiple rows at once, you get the autonumber ID of the *first* row added by the INSERT.

e.g. if there are 4 records in table 'init' that have column 'type' = 2 I want to add these 4 records to table 'game' Table game has an autonumber column 'game_id' that is currently at 32.

If I do this query:

INSERT INTO game (type, players, rounds) SELECT type, players, rounds FROM init WHERE type = 2

Then mysql_insert_id() will return 33, not 36.

Hướng dẫn last insert id mysql

Get ID of The Last Inserted Record

Example (MySQLi Object-oriented)

Example (MySQLi Procedural)

Example (PDO)

Description

Parameters

Return Values

Examples

Notes

See Also

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội