mysql LAST_INSERT_ID not working through php

Question

I tried two ways for reaching it this job:

multi_query:

$sql = "START TRANSACTION; INSERT INTO songs (title, disco, deleted) VALUES ('".$titol."', '".$codi."', '0'); SET @last_id = LAST_INSERT_ID(); INSERT INTO lyrics (`lyricsOri`, `lyricsTra`, `song`, `deleted`) VALUES ('".$lyricsO."', '".$lyricsT."', @last_id, 0); COMMIT;";

connection()->multi_query($sql);

and transaction:

    connection()->begin_transaction(MYSQLI_TRANS_START_READ_ONLY);
    connection()->query("START TRANSACTION;");
    connection()->query("INSERT INTO songs (title, disco, deleted) VALUES ('".$titol."', '".$codi."', '0');");
    connection()->query("SET @last_id = LAST_INSERT_ID();");
    connection()->query("INSERT INTO lyrics (`lyricsOri`, `lyricsTra`, `song`, `deleted`) VALUES ('".$lyricsO."', '".$lyricsT."', @last_id, 0);");
    connection()->query("COMMIT;");
    connection()->commit();
    connection()->close();

All the records are recorded well except song column on lyrics table, which takes NULL value.

Can anyone help me with this?

Thanks!

Answer 1

This multiple queries are executed one by one after each one, so LAST_NSERT_ID() is applying against nothing.

You can use a mix of this two techniques to reach what you need:

        connection()->begin_transaction(MYSQLI_TRANS_START_READ_ONLY);
        connection()->query("START TRANSACTION;");
        connection()->multi_query("INSERT INTO songs (title, disco, deleted) VALUES ('".$titol."', '".$codi."', '0');SET @last_id = LAST_INSERT_ID();INSERT INTO lyrics (`lyricsOri`, `lyricsTra`, `song`, `deleted`) VALUES ('".$lyricsO."', '".$lyricsT."', @last_id, 0);");
        connection()->query("COMMIT;");
        connection()->commit();
        connection()->close();

Now, the var is declared and used on the same query execution flow so it will work fine.

Cheers!