[SOLVED] mysql LAST_INSERT_ID not working through php

mysql LAST_INSERT_ID not working through php

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!

Solution

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!