LAST_INSERT_ID() MySQL
Matthew Harrington 
I have a MySQL question that I think must be quite easy. I need to return the LAST INSERTED ID from table1 when I run the following MySql query:
INSERT INTO table1 (title,userid) VALUES ('test',1);
INSERT INTO table2 (parentid,otherid,userid) VALUES (LAST_INSERT_ID(),4,1);
SELECT LAST_INSERT_ID();As you can understand the current code will just return the LAST INSERT ID of table2 instead of table1, how can I get the id from table1 even if I insert into table2 between?
14 Answers
You could store the last insert id in a variable :
INSERT INTO table1 (title,userid) VALUES ('test', 1);
SET @last_id_in_table1 = LAST_INSERT_ID();
INSERT INTO table2 (parentid,otherid,userid) VALUES (@last_id_in_table1, 4, 1); Or get the max id from table1 (EDIT: Warning. See note in comments from Rob Starling about possible errors from race conditions when using the max id)
INSERT INTO table1 (title,userid) VALUES ('test', 1);
INSERT INTO table2 (parentid,otherid,userid) VALUES (LAST_INSERT_ID(), 4, 1);
SELECT MAX(id) FROM table1; (Warning: as Rob Starling points out in the comments)
11Since you actually stored the previous LAST_INSERT_ID() into the second table, you can get it from there:
INSERT INTO table1 (title,userid) VALUES ('test',1);
INSERT INTO table2 (parentid,otherid,userid) VALUES (LAST_INSERT_ID(),4,1);
SELECT parentid FROM table2 WHERE id = LAST_INSERT_ID();This enables you to insert a row into 2 different tables and creates a reference to both tables too.
START TRANSACTION;
INSERT INTO accounttable(account_username) VALUES('AnAccountName');
INSERT INTO profiletable(profile_account_id) VALUES ((SELECT account_id FROM accounttable WHERE account_username='AnAccountName')); SET @profile_id = LAST_INSERT_ID();
UPDATE accounttable SET `account_profile_id` = @profile_id;
COMMIT;I had the same problem in bash and i'm doing something like this:
mysql -D "dbname" -e "insert into table1 (myvalue) values ('${foo}');"which works fine:-) But
mysql -D "dbname" -e "insert into table1 (myvalue) values ('${foo}');set @last_insert_id = LAST_INSERT_ID();"
mysql -D "dbname" -e "insert into table2 (id_tab1) values (@last_insert_id);"don't work. Because after the first command, the shell will be logged out from mysql and logged in again for the second command, and then the variable @last_insert_id isn't set anymore. My solution is:
lastinsertid=$(mysql -B -N -D "dbname" -e "insert into table1 (myvalue) values ('${foo}');select LAST_INSERT_ID();")
mysql -D "dbname" -e "insert into table2 (id_tab1) values (${lastinsertid});"Maybe someone is searching for a solution an bash :-)
1We only have one person entering records, so I execute the following query immediately following the insert:
$result = $conn->query("SELECT * FROM corex ORDER BY id DESC LIMIT 1");
while ($row = $result->fetch_assoc()) { $id = $row['id'];
}This retrieves the last id from the database.
2It would be possible to save the last_id_in_table1 variable into a php variable to use it later?
With this last_id I need to attach some records in another table with this last_id, so I need:
1) Do an INSERT and get the last_id_in_table1
INSERT into Table1(name) values ("AAA");
SET @last_id_in_table1 = LAST_INSERT_ID();2) For any indeterminated rows in another table, UPDATING these rows with the last_id_insert generated in the insert.
$element = array(some ids)
foreach ($element as $e){ UPDATE Table2 SET column1 = @last_id_in_table1 WHERE id = $e }Instead of this LAST_INSERT_ID()try to use this one
mysqli_insert_id(connection)For no InnoDB solution: you can use a proceduredon't forgot to set the delimiter for storing the procedure with ;
CREATE PROCEDURE myproc(OUT id INT, IN otherid INT, IN title VARCHAR(255))
BEGIN
LOCK TABLES `table1` WRITE;
INSERT INTO `table1` ( `title` ) VALUES ( @title );
SET @id = LAST_INSERT_ID();
UNLOCK TABLES;
INSERT INTO `table2` ( `parentid`, `otherid`, `userid` ) VALUES (@id, @otherid, 1);
ENDAnd you can use it...
SET @myid;
CALL myproc( @myid, 1, "my title" );
SELECT @myid;In trigger BEFORE_INSERT this working for me:
SET @Last_Insrt_Id = (SELECT(AUTO_INCREMENT /*-1*/) /*as Last_Insert_Id*/
FROM information_schema.tables
WHERE table_name = 'tblTableName' AND table_schema = 'schSchemaName');Or in simple select:
SELECT(AUTO_INCREMENT /*-1*/) as Last_Insert_Id
FROM information_schema.tables
WHERE table_name = 'tblTableName' AND table_schema = 'schSchemaName'); If you want, remove the comment /*-1*/ and test in other cases.
For multiple use, I can write a function. It's easy.
We could also use $conn->insert_id; // Create connection
$conn = new mysqli($servername, $username, $password, $dbname); $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 . "<br>" . $conn->error; }For last and second last:
INSERT INTO `t_parent_user`(`u_id`, `p_id`) VALUES ((SELECT MAX(u_id-1) FROM user) ,(SELECT MAX(u_id) FROM user ) );My code does not work for me. Any idea to recover the id of my last insert this is my code I am new developing and I do not know much
I GOT ERROR IN THE QUERY AND I DON'T KNOW HOW TO SEND PRINT IN THE LINE OF $ session-> msg ('s', "Product added successfully. Make cost configuration". LAST_INSERT_ID ());
ALREADY VERIFY AND IT IS CORRECT THE CONNECTION AND THE FIELDS OF THE DATABASE.
<?php if(isset($_POST['add_producto'])){ $req_fields = array( 'nombre', 'categoria', 'proveedor'); validate_fields($req_fields); if(empty($errors)){ $codigobarras = remove_junk($db->escape($_POST['codigobarras'])); $identificador = remove_junk($db->escape($_POST['identificador'])); $nombre = remove_junk($db->escape($_POST['nombre'])); $categoria = (int)$db->escape($_POST['categoria']); $etiquetas = remove_junk($db->escape($_POST['etiquetas'])); $unidadmedida = remove_junk($db->escape($_POST['unidadmedida'])); $proveedor = remove_junk($db->escape($_POST['proveedor'])); $fabricante = remove_junk($db->escape($_POST['idfabricante'])); $maximo = remove_junk($db->escape($_POST['maximo'])); $minimo = remove_junk($db->escape($_POST['minimo'])); $descripcion = remove_junk($db->escape($_POST['descripcion'])); $dias_vencimiento = remove_junk($db->escape($_POST['dias_vencimiento'])); $servicio = "0"; if (isset($_POST['servicio'])){ $servicio =implode($_POST['servicio']); } $numeroserie = "0"; if (isset($_POST['numeroserie'])){ $numeroserie =implode($_POST['numeroserie']); } $ingrediente = "0"; if (isset($_POST['ingrediente'])){ $ingrediente =implode($_POST['ingrediente']); } $date = make_date(); $query = "INSERT INTO productos ("; $query .=" codigo_barras,identificador_producto,nombre,idcategoria,idetiquetas,unidad_medida,idproveedor,idfabricante,max_productos,min_productos,descripcion,dias_vencimiento,servicio,numero_serie,ingrediente,activo"; $query .=") VALUES ("; $query .=" '{$codigobarras}', '{$identificador}', '{$nombre}', '{$categoria}', '{$etiquetas}', '{$unidadmedida}', '{$proveedor}', '{$fabricante}', '{$maximo}', '{$minimo}', '{$descripcion}', '{$dias_vencimiento}', '{$servicio}', '{$numeroserie}', '{$ingrediente}', '1'"; $query .=");"; $query .="SELECT LAST_INSERT_ID();"; if($db->query($query)){ $session->msg('s',"Producto agregado exitosamente. Realizar configuracion de costos" . LAST_INSERT_ID()); redirect('precio_producto.php', false); } else { $session->msg('d',' Lo siento, registro falló.'); redirect('informacion_producto.php', false); } } else{ $session->msg("d", $errors); redirect('informacion_producto.php',false); } }
?>Just to add for Rodrigo post, instead of LAST_INSERT_ID() in query you can use SELECT MAX(id) FROM table1;, but you must use (),
INSERT INTO table1 (title,userid) VALUES ('test', 1)
INSERT INTO table2 (parentid,otherid,userid) VALUES ( (SELECT MAX(id) FROM table1), 4, 1)If you need to have from mysql, after your query, the last auto-incremental id without another query, put in your code:
mysql_insert_id();
