Mysqli transaction query over normal php mysqli query

Question

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!

By default PHP mysql's clients does not support multiple SQL statements separated with semicon `;`. So you need to use multiple query calls. Besides you should also read about preventing SQL injections https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — Raymond Nijland, Jul 10 '18 at 13:36
Have you tried using [multi_query](http://php.net/manual/en/mysqli.multi-query.php) instead of query? — Sloan Thrasher, Jul 10 '18 at 13:39

score 1 · Accepted Answer · answered Jul 10 '18 at 13:44

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!

Thanks, let me take a try on it! :) – jDoe Jul 10 '18 at 13:45 — jDoe, Jul 10 '18 at 13:45
It worked like charm, thanks a lot! – jDoe Jul 10 '18 at 13:45 — jDoe, Jul 10 '18 at 13:45

Mysqli transaction query over normal php mysqli query

1 Answers1