mysqli_insert_id
Функция mysqli_insert_id() возвращает ID, генерируемый запросом к таблице, которая содержит колонку с атрибутом AUTO_INCREMENT. Если последний запрос не был INSERT или UPDATE или в модифицируемой таблице отсутствует колонка с атрибутом AUTO_INCREMENT, данная функция вернет ноль.
Замечание:
Выполнение выражения INSERT или UPDATE, используя функцию LAST_INSERT_ID(), также изменяет значение, возвращаемое функцией mysqli_insert_id().
Список параметров
Только для процедурного стиля: Идентификатор соединения, полученный с помощью mysqli_connect() или mysqli_init()
Возвращаемые значения
Значение поля AUTO_INCREMENT, которое было затронуто предыдущим запросом. Возвращает ноль, если предыдущий запрос не затронул таблицы, сожержащие поле AUTO_INCREMENT.
Замечание:
Если число больше максимального значения int, функция mysqli_insert_id() вернет строку.
Примеры
Пример #1 Пример функции mysqli->insert_id
$mysqli = new mysqli("localhost", "my_user", "my_password", "world"); /* проверка соединения */ if (mysqli_connect_errno()) < printf("Соединение не установлено: %s\n", mysqli_connect_error()); exit(); >$mysqli->query("CREATE TABLE myCity LIKE City"); $query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)"; $mysqli->query($query); printf ("ID новой записи: %d.\n", $mysqli->insert_id); /* удаление таблицы */ $mysqli->query("DROP TABLE myCity"); /* закрытие соединения */ $mysqli->close();$link = mysqli_connect("localhost", "my_user", "my_password", "world"); /* проверка соединения */ if (mysqli_connect_errno()) < printf("Соединение не установлено: %s\n", mysqli_connect_error()); exit(); >mysqli_query($link, "CREATE TABLE myCity LIKE City"); $query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)"; mysqli_query($link, $query); printf ("ID новой записи: %d.\n", mysqli_insert_id($link)); /* удаление таблицы */ mysqli_query($link, "DROP TABLE myCity"); /* закрытие соединения */ mysqli_close($link);Результат выполнения данных примеров:
Описание класса mysqli, примеры использования класса mysqli.
mysqli_insert_id
Функция mysqli_insert_id() возвращает ID, генерируемый запросом к таблице, которая содержит колонку с атрибутом AUTO_INCREMENT. Если последний запрос не был INSERT или UPDATE или в модифицируемой таблице отсутствует колонка с атрибутом AUTO_INCREMENT, данная функция вернет ноль.
Замечание:
Выполнение выражения INSERT или UPDATE, используя функцию LAST_INSERT_ID(), также изменяет значение, возвращаемое функцией mysqli_insert_id() .
Список параметров
Только для процедурного стиля: Идентификатор соединения, полученный с помощью mysqli_connect() или mysqli_init()
Возвращаемые значения
Значение поля AUTO_INCREMENT, которое было затронуто предыдущим запросом. Возвращает ноль, если предыдущий запрос не затронул таблицы, содержащие поле AUTO_INCREMENT.
Замечание:
Если число больше максимального значения int, функция mysqli_insert_id() вернет строку.
Примеры
Пример #1 Пример функции $mysqli->insert_id
$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );
/* проверка соединения */
if ( mysqli_connect_errno ()) printf ( «Соединение не установлено: %s\n» , mysqli_connect_error ());
exit();
>
$mysqli -> query ( «CREATE TABLE myCity LIKE City» );
$query = «INSERT INTO myCity VALUES (NULL, ‘Stuttgart’, ‘DEU’, ‘Stuttgart’, 617000)» ;
$mysqli -> query ( $query );
printf ( «ID новой записи: %d.\n» , $mysqli -> insert_id );
/* удаление таблицы */
$mysqli -> query ( «DROP TABLE myCity» );
/* закрытие соединения */
$mysqli -> close ();
?>
$link = mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );
/* проверка соединения */
if ( mysqli_connect_errno ()) printf ( «Соединение не установлено: %s\n» , mysqli_connect_error ());
exit();
>
mysqli_query ( $link , «CREATE TABLE myCity LIKE City» );
$query = «INSERT INTO myCity VALUES (NULL, ‘Stuttgart’, ‘DEU’, ‘Stuttgart’, 617000)» ;
mysqli_query ( $link , $query );
printf ( «ID новой записи: %d.\n» , mysqli_insert_id ( $link ));
/* удаление таблицы */
mysqli_query ( $link , «DROP TABLE myCity» );
/* закрытие соединения */
mysqli_close ( $link );
?>
Результат выполнения данных примеров:
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 mysqli_insert_id() Function
If you have a table with an AUTO_INCREMENT attribute for a column and, if your last MySQLi function call executes INSERT or UPDATE statement. The mysqli_insert_id() function returns the auto generated id of the last executed query.
Syntax
Parameters
This is an object representing a connection to MySQL Server.
Return Values
PHP mysqli_insert_id() function returns the value of the «Auto Increment» column in the last query In case it is INSERT or, UPDATE operation. If the last executed query is not INSERT or, UPDATE or, if the table doesn’t have any column/field with «AUTO_INCREMENT» attribute, this function returns 0.
PHP Version
This function was first introduced in PHP Version 5 and works works in all the later versions.
Assume we have created a table named Cricketers in the database mydb, where the field ID is PRIMARY KEY and it is AUTO INCREMENTED as −
CREATE TABLE Cricketers( ID INT PRIMARY KEY AUTO_INCREMENT, First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) );
Example
Following example demonstrates the usage of the mysqli_insert_id() function (in procedural style) −
This will produce following result −
Example
In object oriented style the syntax of this function is $con->insert_id(); Following is the example of this function in object oriented style $minus;
query("insert into Cricketers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka')"); //Insert ID $state = $con->insert_id; print("Insert ID: ".$state."\n"); //Inserting a record into the employee table $con -> query("insert into Cricketers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India')"); //Insert ID $state = $con->insert_id; print("Insert ID: ".$state); //Closing the connection $con -> close(); ?> This will produce following result −
Example
Following is another example of the function mysqli_insert_id −
This will produce following result −
Insert ID (select query): 0 Insert ID: (multiple inserts) 6 Insert ID (update query): 0 Insert ID: (table with out auto incremented key) 0
Example
Following example demonstrates the usage of the mysqli_insert_id function with SELECT, UPDATE, INSERT and, DELETE queries −
mysqli_query($connection_mysql,"INSERT INTO Employee (name) VALUES('PHP')"); echo "New record has id: " . mysqli_insert_id($connection_mysql); mysqli_close($connection_mysql); ?> This will produce following result −