Mysqli Multi Query - Sort Results

Question

i want to save every result from an sql statement in a differen array. I tried:

        $sql = "SELECT * FROM `link_server` WHERE `in_use` = false;";
        $sql .= "SELECT * FROM `link_queue` WHERE `active` = false;";

        if ($db->multi_query($sql))
        {
            do
            {
                // Erstes Abfrageergebnis ausgeben
                if ($result = $db->store_result())
                {
                    // Abfrageergebnis ausgeben
                    while ($server_fetch = $result->fetch_array())
                    {
                        $server[] = $server_fetch;
                    }

                    $result->close();

                }
                // Trenner fuer Zweites Abfrageergebnis
                if ($db->more_results())
                {
                    echo "<hr />test";
                    $queue[] = $server_fetch;
                }
            } while ($db->next_result());
        echo "Servers:";
        print_r($server);
        echo "Queue:";
        print_r($queue);
        }

The result from the first statement should be saved in the array $server, and the second should be saved in the array $queue.

The above example stores the complete return (from both statements) in the first array ($server). The second array is empty.

How can i solve that?

Thanks for your help!

why are you doing multiple queries at once? that seems needlessly complicated? — Landon, Oct 22 '12 at 23:02
because its faster and easier to overview.. ;) But thats not the question ;) — user1766080, Oct 22 '12 at 23:33
I doubt the time savings in doing it that way is substantial in anyway. i would highly recommend breaking it out into two separate sections and then testing each one for accuracy separately. — Landon, Oct 22 '12 at 23:35
Let your database application do the hard work. It's optimized to sort and return data. — digitlworld, Oct 23 '12 at 00:02

score 1 · Accepted Answer · answered Oct 23 '12 at 03:00

$db->more_results() is a way of flagging that the end of the results of a query have been reached, not to get the next set of results. You can use it to tell your loop to start loading the next array - for example by setting a flag.

if ($result = $mysqli->store_result()) {
    while ($server_fetch = $result->fetch_row()) {
        if (!$second) {
            $server[] = $server_fetch;
        } else {
            $queue[] = $server_fetch;
        }
    }
    $result->close();
}
/* next set of results */
if ($mysqli->more_results()) {
    $second = true;
}

Alternatively, you could use a variable variable like so:

/* set up variables  */
$server = array();
$queue = array();
$active = 'server';

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store result set */
        if ($result = $mysqli->store_result()) {
            while ($server_fetch = $result->fetch_row()) {
                ${$active}[] = $server_fetch;
            }
            $result->close();
        }
        /* next set of results */
        if ($mysqli->more_results()) {
            $active = 'queue';
        }
    } while ($mysqli->next_result());
}

Although I have to concur with the commentors, this adds a lot of effort and additional code complexity to an otherwise simple pair of requests.

Okay thanks for all answers.. I think you are right.. Its just more code than i thought and just more complicated.. So i just put it in 2 seperate Querys and its done. Thanks! — user1766080, Oct 23 '12 at 09:24

Mysqli Multi Query - Sort Results

1 Answers1