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?
Best Answer
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)
Since 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 :-)
We 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.
It 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))BEGINLOCK 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_IdFROM 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.
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 ) );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', '[email protected]')";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;}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.
<?phpif(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();