Get last inserted id in php mysql

PHP MySQL Last Inserted ID

In this tutorial you will learn how to retrieve the unique ID of the last inserted row from a MySQL database table using PHP.

How to Get the ID of Last Inserted Row

In the PHP MySQL insert chapter you’ve learnt MySQL automatically generate an unique ID for the AUTO_INCREMENT column each time you insert a new record or row into the table. However, there are certain situations when you need that automatically generated ID to insert it into a second table. In these situations you can use the PHP mysqli_insert_id() function to retrieve the most recently generated ID, as shown in the upcoming example.

For this example we’ll use the same persons table that we’ve created in the PHP MySQL create tables chapter, which has four columns id, first_name, last_name and email, where id is the primary key column and marked with AUTO_INCREMENT flag.

Example

 // Attempt insert query execution $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Ron', 'Weasley', 'ronweasley@mail.com')"; if(mysqli_query($link, $sql)) < // Obtain last inserted id $last_id = mysqli_insert_id($link); echo "Records inserted successfully. Last inserted ID is: " . $last_id; >else < echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); >// Close connection mysqli_close($link); ?>

connect_error); > // Attempt insert query execution $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Ron', 'Weasley', 'ronweasley@mail.com')"; if($mysqli->query($sql) === true)< // Obtain last inserted id $last_id = $mysqli->insert_id; echo "Records inserted successfully. Last inserted ID is: " . $last_id; > else< echo "ERROR: Could not able to execute $sql. " . $mysqli->error; > // Close connection $mysqli->close(); ?>

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ERROR: Could not connect. " . $e->getMessage()); > // Attempt insert query execution try< $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Ron', 'Weasley', 'ronweasley@mail.com')"; $pdo->exec($sql); $last_id = $pdo->lastInsertId(); echo "Records inserted successfully. Last inserted ID is: " . $last_id; > catch(PDOException $e)< die("ERROR: Could not able to execute $sql. " . $e->getMessage()); > // Close connection unset($pdo); ?>

Источник

PHP MySQL Get Last Inserted ID

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.

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’, ‘john@example.com’)»;

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;
>

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’, ‘john@example.com’)»;

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);
>

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’, ‘john@example.com’)»;
// 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();
>

Источник

mysql_insert_id

This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide. Alternatives to this function include:

Description

Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT).

Parameters

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

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.

See Also

User Contributed Notes 12 notes

There’s nothing inherently wrong with using auto-increment fields. There’s also nothing wrong with the main competetive idea, which is for the database to supply a primitive sequence of non-repeating identifiers, typically integers. This is rather like which side of the road you drive on.

The bigger problem is when people don’t understand what they are doing with database access. It’s like driving a car without really knowing the rules of the road. Such people wind up making bad decisions without realizing it, and then, eventually, something breaks.

Databases are complex beasts, and worth taking the time to really understand. Learn about the implications and limitations of different approaches to solving problems. Then, you will be prepared to pick a solution based on what has to work.

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.

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.»

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.

I thought this would be relevant to all the people using mysqli and looking for the ID after INSERT command :

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 . «

---

» ;

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() .

Источник

Последний добавленный идентификатор PHP MySQL

В этом уроке вы узнаете, как получить уникальный идентификатор последней вставленной строки из таблицы базы данных MySQL с помощью PHP.

Как получить идентификатор последней вставленной строки

В уроке о вставка данных в таблицу БД MySQL мы узнали, что MySQL автоматически генерирует уникальный идентификатор для столбца с атрибутом AUTO_INCREMENT каждый раз, когда мы вставляем новую запись или строку в таблицу. Однако бывают ситуации, когда нам нужен автоматически сгенерированный идентификатор, чтобы вставить его во вторую таблицу. В этих ситуациях мы можем использовать функцию PHP mysqli_insert_id() для получения последнего сгенерированного идентификатора.

В следующем примере мы будем использовать ту же таблицу persons, которую мы создали в уроке создание таблиц PHP MySQL, которая имеет четыре столбца id, first_name, last_name и email, где id — столбец первичного ключа с атрибутом AUTO_INCREMENT .

Пример

 // Попытка выполнения запроса вставки $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Ron', 'Weasley', 'ronweasley@mail.com')"; if(mysqli_query($link, $sql)) < // Получить последний вставленный идентификатор $last_id = mysqli_insert_id($link); echo "Записи успешно вставлены. Последний вставленный идентификатор: " . $last_id; >else < echo "Ошибка вставки $sql. " . mysqli_error($link); >// Закрыть соединение mysqli_close($link); ?>

connect_error); > // Попытка выполнения запроса вставки $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Ron', 'Weasley', 'ronweasley@mail.com')"; if($mysqli->query($sql) === true)< // Получить последний вставленный идентификатор $last_id = $mysqli->insert_id; echo "Записи успешно вставлены. Последний вставленный идентификатор: " . $last_id; > else< echo "Ошибка вставки $sql. " . $mysqli->error; > // Закрыть соединение $mysqli->close(); ?>

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("Ошибка подключения. " . $e->getMessage()); > // Попытка выполнения запроса вставки try< $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Ron', 'Weasley', 'ronweasley@mail.com')"; $pdo->exec($sql); $last_id = $pdo->lastInsertId(); echo "Записи успешно вставлены. Последний вставленный идентификатор: " . $last_id; > catch(PDOException $e)< die("Ошибка вставки $sql. " . $e->getMessage()); > // Закрыть соединение unset($pdo); ?>

Источник