How to use mysqli->multi_query()?

Question

Can anyone show me how to use mysqli->multi_query() to execute multiple update/ insert queries on a single connection? Thanks.

I did follow the tutorials in the PHP manual. But I have some issues.

My batch of queries has 5 statements separated by semicolons.

UPDATE scenes set UserID = '11111111' WHERE ID = '031DFDAD92F6F4AB64AF317C06D64089DF119EC2';

INSERT INTO surfaces (ID, Name, SceneID, SurfaceTypeID, Color) VALUES('5F9A7301C2D398C4D1B90BA5AA56A9DED3FAA639', 'front ', '031DFDAD92F6F4AB64AF317C06D64089DF119EC2', 1, 11432044);

INSERT INTO regions (ID, SurfaceID, x, y, width, height, RegionMovieClip) VALUES('864406A2CB30CFBE846ED7B0B08A79BD5605037D', '5F9A7301C2D398C4D1B90BA5AA56A9DED3FAA639', 375, 22, 104, 125, 'asdasdcvxcv');

INSERT INTO surfaces (ID, Name, SceneID, SurfaceTypeID, Color) VALUES('1FCA2131ED1B89206E4E66DBE20D8D09513FF39D', 'floor ', '031DFDAD92F6F4AB64AF317C06D64089DF119EC2', 1, 7318465);

INSERT INTO regions (ID, SurfaceID, x, y, width, height, RegionMovieClip) VALUES('DBD0E85EAEE2685E2AEC590C8CA214C3C5653971', '1FCA2131ED1B89206E4E66DBE20D8D09513FF39D', 272, 288, 114, 89, 'asdasd')

All are executed except for the 3rd query. That's where I'm lost. If there's an sql error inserting the 3rd query, how can I get it? And how the queries after the failed 3rd one is getting executed?

Are the examples in the manual not sufficient? http://php.net/manual/en/mysqli.multi-query.php — Pekka, Dec 26 '10 at 14:37
Here is a simple solution (procedural style) and explanation at a similar question: http://stackoverflow.com/questions/14715889/strict-standards-mysqli-next-result-error-with-mysqli-multi-query/22469722#22469722 — mickmackusa, Mar 09 '15 at 12:52

score 2 · Answer 1 · answered Dec 26 '10 at 19:27

2

The examples don't really show how to handle an error.

Execution will stop after the first failure.

Use mysqli_error() to get error information.

mysqli_use_result() returns false if an error occured - if you count the results and there aren't enough, an error occurred.

answered Dec 26 '10 at 19:27

evan

12,307
7
37
51

This is half right. Access mysqli_error() to get the failure / error message. mysqli_use_result() will be of no use for the provided INSERT and UPDATE queries as they never return a result set. Use mysqli_affected_rows() to measure successfulness. – mickmackusa Mar 09 '15 at 12:50

score 0 · Answer 2 · answered May 07 '13 at 22:02

This is untested and will not handle errors but should run all 5 queries.

$fconn = new mysqli($fdbhost, $fdbuser, $fdbpass, $fdbname) or die  ('Error connecting to mysqli');
$query  = "UPDATE scenes set UserID = '11111111' WHERE ID = '031DFDAD92F6F4AB64AF317C06D64089DF119EC2';";
$query .= "INSERT INTO surfaces (ID, Name, SceneID, SurfaceTypeID, Color) VALUES('5F9A7301C2D398C4D1B90BA5AA56A9DED3FAA639', 'front ', '031DFDAD92F6F4AB64AF317C06D64089DF119EC2', 1, 11432044);";
$query .= "INSERT INTO surfaces (ID, Name, SceneID, SurfaceTypeID, Color) VALUES('1FCA2131ED1B89206E4E66DBE20D8D09513FF39D', 'floor ', '031DFDAD92F6F4AB64AF317C06D64089DF119EC2', 1, 7318465);";
$query .= "INSERT INTO surfaces (ID, Name, SceneID, SurfaceTypeID, Color) VALUES('1FCA2131ED1B89206E4E66DBE20D8D09513FF39D', 'floor ', '031DFDAD92F6F4AB64AF317C06D64089DF119EC2', 1, 7318465);";
$query .= "INSERT INTO regions (ID, SurfaceID, x, y, width, height, RegionMovieClip) VALUES('DBD0E85EAEE2685E2AEC590C8CA214C3C5653971', '1FCA2131ED1B89206E4E66DBE20D8D09513FF39D', 272, 288, 114, 89, 'asdasd')";
if ($fconn->multi_query($query)) {

         if ($result = $fconn->store_result()) {
            //while ($row = $result->fetch_row()) {
                //print_r($row);
            }
            //$result->free();
            }
         if ($fconn->more_results()) {

            while ($fconn->next_result()){ 
            $thisresult = $fconn->store_result();

            print_r($thisresult).'<br />';
            }
            }

}
unset($query);
$fconn->close();

There are issues with this bit of code. The store_result condition statement has too many closing curly brackets; furthermore there will never be any results to store anyhow because all queries are UPDATE/INSERT queries. I cannot recommend anyone to use this untested example. This is just not the right way to go about it. — mickmackusa, Mar 09 '15 at 12:38

score 0 · Answer 3 · answered Oct 06 '15 at 20:37

This is not fool proof, but I have fought and fought with MySQLi and it's band of merry men involved with multi_query and I couldn't get it to play nicely the way I wanted to, or have the flexibility that I needed. I saw several examples where some programmers were simply running explode(';', $sql_statements) which made my eyes bleed with how horribly wrong that can be.

My solution may not work for you, but this worked for me. (no it's not bulletproof either, but does the job for my particular application).

<?php
    $file = file_get_contents('test_multiple_queries.sql');
    $result = preg_split("/;(?=\s*(create|insert|update|alter|show|explain|truncate|drop|delete|replace|start|lock|commit|rollback|set|begin|declare|rename|load|begin|describe|help))/im", $file);
    $result = array_map('trim', $result);

    foreach($result as $sql_query) {

        // Procedural style
        mysqli_query($link, $sql_query);

        // Now you can get errors easily, or affected_rows, or whatever
        //    using much simpler, readable code
        mysqli_error($link);
        mysqli_affected_rows($link);

        // or go crazy with some other stuff
        $words = preg_split("/\s+/", $sql_query);
        switch(strtolower($words[0])) {
            case 'insert':
                // do something nifty like...
                echo 'New ID: '.mysqli_insert_id($link)."\n";
                break;
            case 'drop':
                // obviously run this before the query, simply here for example
                echo 'Hey young (man|lady)! We don\'t drop anything!';
                break;
        }
    }

How to use mysqli->multi_query()?

3 Answers3