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!

Welcome to Stack Overflow! You haven't shown the definition of your table(s), without which it's hard to give a good answer. See [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](//meta.stackoverflow.com/q/333952) — Toby Speight, Jul 10 '18 at 15:31

score 0 · Accepted Answer · answered Jul 10 '18 at 15:27

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!

@jDoe if that worked, please accept this answer to close your question. — IncredibleHat, Jul 10 '18 at 15:52
sorry, SO does not let me do it since it past some time, just did it! — jDoe, Jul 10 '18 at 16:20

mysql LAST_INSERT_ID not working through php

1 Answers1