PHP/MySQL Insert Row Then Get 'Id'

PHP/MySQL insert row then get 'id'

$link = mysqli_connect('127.0.0.1', 'my_user', 'my_pass', 'my_db');
mysqli_query($link, "INSERT INTO mytable (1, 2, 3, 'blah')");
$id = mysqli_insert_id($link);

See mysqli_insert_id().

Whatever you do, don't insert and then do a "SELECT MAX(id) FROM mytable". Like you say, it's a race condition and there's no need. mysqli_insert_id() already has this functionality.

---

Another way would be to run both queries in one go, and using MySQL's LAST_INSERT_ID() method, where both tables get modified at once (and PHP does not need any ID), like:

mysqli_query($link, "INSERT INTO my_user_table ...;
  INSERT INTO my_other_table (`user_id`) VALUES (LAST_INSERT_ID())");

Note that Each connection keeps track of ID separately (so, conflicts are prevented already).

Get the new record primary key ID from MySQL insert query?

You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Eg:

INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

This will get you back the PRIMARY KEY value of the last row that you inserted:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.

How can i get ID when insert into a table

Do this on your else statement:

else {
  echo mysql_insert_id();
}

Though I do not recommend it because MySQL extension is deprecated in newer versions of PHP. Generally to issue queries to MySQL programmers use the MySQLi or the PDO extension of PHP. Try doing it using MySQLi if you can. It's equivalent is the mysqli_insert_id() function.

PHP/mySQL Insert then get id by calling a stored procedure

If you're using a stored procedure then you have no real access to what's going on in that procedure unless the procedure itself passes the information out. You can, therefore, define an OUT parameter to the procedure and return the last_insert_id() value in that:

Procedure

delimiter $$
drop procedure if exists p$$
CREATE PROCEDURE p(OUT InsertId int) 
BEGIN
insert test.Numbers (Numbers) values (3);
select last_insert_id() into InsertId ;

END$$
delimiter ;

Call with a mysqli query

call p(@InsertId);

Then retrieve the result with another mysqli query

select @InsertId as id;

Note - the variables live for the life of the connection so unless you close the connection you can retrieve the value of @InsertId whenever it's convenient.

Thanks to Patchrick for adding the PHP niceties:

$acct = mysql_query("call p(@InsertId);select @InsertId as id;");
$row = mysql_fetch_array($acct);
echo $row["id"]; 

How to get the id of a row i've just inserted php/mysql

That mostly depends on the interface you're using to access MySQL.

If you're using PDO (recommended), you'd use PDO::lastInsertId(): http://us.php.net/manual/en/pdo.lastinsertid.php

If you're using MySQL, you'd use mysql_insert_id() http://php.net/manual/en/function.mysql-insert-id.php

If you're using MySQLi, you'd use mysqli_insert_id(): http://us.php.net/manual/en/mysqli.insert-id.php

Just simply call whichever function is appropriate right after your INSERT completes.

It's important to note that you likely do not want to fetch id out of the database using SELECT because if you have multiple writers, you may get the id that some other thread inserted. At best, you'll operate on the wrong data, and at worst, you could have serious security issues and/or corruption.

Get id of last inserted row in PHP

Frapi Database extends from PDO, so you would use this:

$lastId = $db->lastInsertId();

See also: PDO::lastInsertId()

---

Related Topics