Cannot Figure Out How to Run a MySQLi_Multi_Query and Use the Results from the Last Query

Cannot figure out how to run a mysqli_multi_query and use the results from the last query

Okay after some fiddling around, trial and error and taking reference from another post that I came across in a Google search I've managed to solve my problem!

Here's the new code:

<?php

    $link = mysqli_connect("server", "user", "pass", "db");

    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    $agentsquery = "CREATE TEMPORARY TABLE LeaderBoard (
        `agent_name` varchar(20) NOT NULL,
        `job_number` int(5) NOT NULL,
        `job_value` decimal(3,1) NOT NULL,
        `points_value` decimal(8,2) NOT NULL
    );";
    $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`, `job_number`, `job_value`, `points_value`) SELECT agent_name, job_number, job_value, points_value FROM jobs WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
    $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`) SELECT DISTINCT agent_name FROM apps WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
    $agentsquery .= "SELECT agent_name, SUM(job_value), SUM(points_value) FROM leaderboard GROUP BY agent_name ORDER BY SUM(points_value) DESC";

    mysqli_multi_query($link, $agentsquery) or die("MySQL Error: " . mysqli_error($link) . "<hr>\nQuery: $agentsquery");
    mysqli_next_result($link);
    mysqli_next_result($link);
    mysqli_next_result($link);

    if ($result = mysqli_store_result($link)) {
        $i = 0;
        while ($row = mysqli_fetch_array($result)){
            $number_of_apps = getAgentAppsWeek($row['agent_name'],$weeknum,$current_year);
            $i++;
?>

            <tr class="tr<?php echo ($i & 1) ?>">
                <td style="font-weight: bold;"><?php echo $row['agent_name'] ?></td>
                <td><?php echo $row['SUM(job_value)'] ?></td>
                <td><?php echo $row['SUM(points_value)'] ?></td>
                <td><?php echo $number_of_apps; ?></td>
            </tr>

<?php

        }
    }
?>

after sticking mysqli_next_result in there multiple times for each query it magically worked! yay! I understand why it works, because i'm telling it to skip to the next result 3 times, so it skips to the result for query #4 which is the one i want to use.

Seems a bit clunky to me though, there should just be a command for something like mysqli_last_result($link) or something if you ask me...

Thanks for the help rik and f00, I got there eventually :)

mysqli_multi_query - Commands out of sync; you can't run this command now

I just found the answer in the PHP manual:

WATCH OUT: if you mix $mysqli->multi_query and $mysqli->query, the
latter(s) won't be executed!

BAD CODE:

$mysqli->multi_query(" Many SQL queries ; "); // OK
$mysqli->query(" SQL statement #1 ; ") // not executed!
$mysqli->query(" SQL statement #2 ; ") // not executed!
$mysqli->query(" SQL statement #3 ; ") // not executed!
$mysqli->query(" SQL statement #4 ; ") // not executed!

The only way to do this correctly is:

WORKING CODE:

$mysqli->multi_query(" Many SQL queries ; "); // OK
while ($mysqli->next_result()) {;} // flush multi_queries
$mysqli->query(" SQL statement #1 ; ") // now executed!
$mysqli->query(" SQL statement #2 ; ") // now executed!
$mysqli->query(" SQL statement #3 ; ") // now executed!
$mysqli->query(" SQL statement #4 ; ") // now executed!

I just insert this code after mysqli_multi_query():

while(mysqli_next_result($connect)){;}

How to loop through mysqli_multi_query() to find out what queries succeeded?

how do I loop through the results to know which queries succeeded and
which failed?

int mysqli_stmt_affected_rows ( mysqli_stmt $stmt )
and
bool mysqli_next_result ( mysqli $link ) are the 2 functions you're looking for.

<?php
    $mysqli = new mysqli("localhost", "my_user", "my_password", "world");

    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    $query  = "SELECT CURRENT_USER();";
    $query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

    /* execute multi query */
    if ($mysqli->multi_query($query)) {
        do {
            /* store first result set */
            if ($result = $mysqli->store_result()) {
                while ($row = $result->fetch_row()) {
                    printf("%s\n", $row[0]);
                }
                $result->free();
            }
            /* print divider */
            if ($mysqli->more_results()) {
                printf("-----------------\n");
            }
        } while ($mysqli->next_result());
    }

    /* close connection */
    $mysqli->close();
    ?>

From the documentation.

If you wan to use procedural style, check the example in the documentation. You just have to use mysqli_more_results or $mysqli->next_result() to switch between various queries.

Get last result from mysqli_multi_query

I finally got it. All I had to do is to store results in every while loop iteration.
Here's how it looks now.

$sql = "CALL someProcedure('qr_code', @out); ";
$sql .= "SELECT @out AS `out`;";

if($conn->multi_query($sql)) {
    while ($conn->more_results()) {
        $rs = $conn->store_result();
        $conn->next_result();
    }
    $rs = $conn->store_result();
    $row = $rs->fetch_assoc();
    $odp = $row['out'];

    if (!empty($rs)) {
        $response['success'] = 1;
        $response['message'] = $odp;

        echo json_encode($response);
        $rs->free();

    } else {
        $response['success'] = 0;
        $response['message'] = mysqli_error($conn);

        echo json_encode($response);    
    } 

What may be the cause to my php code does not run after mysqli_multi_query is called?

Yes it's mostly something what you guess.
if you mix mysqli_multi_query and mysqli_query, the latter(s) won't be executed!

problamatic code:

$mysqli->multi_query(" Many SQL queries ; "); //works
$mysqli->query(" SQL statement #1 ; ") // not works!

The only way to do this correctly is:

$mysqli->multi_query(" Many SQL queries ; "); // works
while ($mysqli->next_result()) {;} // flush multi_queries
$mysqli->query(" SQL statement #1 ; ") // works!

You just need to use/flush results to make the rest of php code to work.

---

Related Topics