LAST_INSERT_ID() MySQL

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?

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 }

For no InnoDB solution: you can use a procedure don'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); 
END

And you can use it...

SET @myid;
CALL myproc( @myid, 1, "my title" );
SELECT @myid;

Instead of this LAST_INSERT_ID() try to use this one

mysqli_insert_id(connection)

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', '[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;
    }

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