mysql - Mysqli transaction query over normal php mysqli query

I'm trying to use a mysql transaction query over php mysqli query like this:

$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()->query($sql);

If I get the $sql value after it and copy-paste as sql command into php my admin, it runs nice and it does what it's supposed to do but, when I run this through php it does not work and I can't figure out why.

I know I could use php native transaction, setting it auto_commit(FALSE) and adding the different queries on it to finaly commit it, but in this case I would like to use a mysql command "as is" and I can't find why it's not working properly.

Anyone see the fault or can tell me why it does not work?

Thanks!

Answer

As @raymond-nijland pointed out, PHP client does not support multiple SQL statements separated with semicolon.

But this is half-true.
You can use 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);

Or, as you wrote, using a php transaction, which will takes you to the same result:

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

Hope it helps!