MySQLi multi_query wont return more than 1 results

Question

When the result is limited to 1, everything works great.

However, when I have more than 1 results...it doesn't return anything.

$output = $conn->multi_query("CALL `test_discount`('022979', 1101, 1, 'W', 100, @out); SELECT @out AS `discount`;");

if ($output == true){
    while($conn->next_result()){
        $result = $conn->store_result();
            while ($row = $result->fetch_assoc()){
                print_r($row);
                break;
            }                   
    if($conn->more_results() == false) { break; };
    }
}

Am guessing I am doing something wrong?

The `break` statement stops the loop after the first row. Get rid of it. — Barmar, Mar 06 '18 at 06:21
You don't need the `more_results()` check. When you run out of results, `next_result()` will return `false`. — Barmar, Mar 06 '18 at 06:22
@Barmar Thats not the problem. Removed `break`, still doesn't return anything. `more_results()` is also disabled now, and it's not returning anything still. — Nikk, Mar 06 '18 at 06:23
What results are you expecting? `SELECT @out` can only return one value. — Barmar, Mar 06 '18 at 06:25
Returns a discount for a product id. So thats the problem then `@out` can't return more than 1? @Barmar — Nikk, Mar 06 '18 at 06:27
@Barmar BTW, this is what I get if I don't check `more_results()`: `mysqli::next_result(): There is no next result set. Please, call mysqli_more_results()/mysqli::more_results() to check whether to call this function/method ` — Nikk, Mar 06 '18 at 06:28
That's strange. The examples at http://php.net/manual/en/mysqli.multi-query.php only use `more_results()` to know whether to print a divider line, not to check whether to keep looping. — Barmar, Mar 06 '18 at 06:32
If your code is supposed to return many result sets / many rows, then you should work on your SQL first, than starting with PHP — Your Common Sense, Mar 06 '18 at 07:22

Your Common Sense · Accepted Answer · 2018-03-06T09:25:40.867

If SQL above makes any sense, then I would suggest to fetch the data returned by the procedure first, and then select that stray @out variable.

$sql = "CALL `test_discount`('022979', 1101, 1, 'W', 100, @out)";
$res = $conn->multi_query($sql);
do {
    if ($res = $mysqli->store_result()) {
        foreach ($res as $row) {
            print_r($row);
        }
    }
} while ($mysqli->more_results() && $mysqli->next_result());

$out = $conn->query("SELECT @out")->fetch_row[0];

MySQLi multi_query wont return more than 1 results

1 Answers1