mysqli error only on subsequent calls to same function - 'there is no next result set.'

Question

I am iterating rows of a csv file.

On row 1, I call stored procedure (import_extended_data_sp) and it succeeds.

On row 2, the call fails with :

Strict Standards mysqli::next_result(): There is no next result set.

However, with the call being exactly the same as the first, I am struggling to see why ?

I have now hard coded test values as parameters, and checked that the Sproc has no issue with the same values being given twice.

It still fails on the second call !?

Am wondering if there is some nuance of mysqli, where I need to clear or reset something before making the second call ?

<?php include("cnn.php");?>    
<?php include("fn_db.php");?>

# ... get csv file (skipped for brevity) #

while($row = fgetcsv($file_data))
{
    $line = array_combine($head, $row);
       
    # This call works on every loop - no issues
    $id = placemark_to_db($mysqli,$v_header,$line['id_placemark'],$line['name'],$line['swim_type'],$line['latitude'],$line['longitude'],$line['description']);
        
    # This next line only succeeds on first call, but fails on next while loop
    $x = xtended_to_db($mysqli,'99','[{"xtra":"oo"}]');
} 

** fn_db.php >> xtended_to_db**

function xtended_to_db($cn,$id,$jsonarray){
    # procedure returns a rowcount in output parameter

    $cn->multi_query( "CALL import_extended_data_sp($id,'$jsonarray',@out);select @out as _out");
    $cn->next_result();
    $rs=$cn->store_result();
    $ret = $rs->fetch_object()->_out;
    $rs->free(); 
    return $ret;
}

cnn.php

<?php
$mysqli = new mysqli("xxx.xxx.xxx.xxx","mydb","pass","user");
// Check connection
if ($mysqli -> connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
  exit();
}
?>

Answer 1

The best way to fix this error is to avoid multi_query() altogether. While it might sound like a reasonable use case with stored procedures, the truth is this function is mostly useless and very dangerous. You can achieve the same result using the normal way with prepared statements.

function xtended_to_db(mysqli $cn, $id, $jsonarray) {
    $stmt = $cn->prepare('CALL import_extended_data_sp(?,?,@out)');
    $stmt->bind_param('ss', $id, $jsonarray);
    $stmt->execute();

    $stmt = $cn->prepare('select @out as _out');
    $stmt->execute();
    $rs = $stmt->get_result();
    return $rs->fetch_object()->_out;
}

If you are stuborn and you want to keep on using multi_query() then you need to be more careful with how you fetch results. This function is extremely difficult to get right. I am not going to show you how to fix multi_query() as I consider it too dangerous with variable input.

One last note, you really should think about getting rid of stored procedures. They are cumbersome and offer pretty much no benefit. There definitely is a better way to achieve what you want rather than calling stored procedure from PHP, but without seeing its contents I can't give you better advice.