PHP trying to simply a repetitive script involving rowsets

I’m trying to simplify a repetitive script that I’m doing in PHP. I’ve looked at a few loop options but since it involves rowsets being returned from a MySQL stored procedure it’s not acting properly. I’m doing this same script about 15 times to return all the data. Ultimately I’m looking to pass back a json_encode array to the ajax calling it. The results I keep getting when trying to put it in a loop is a 500 error or a poorly constructed array.

    $stmt->execute();
    $values = array();
    
    $stmt->execute();
    $values = array();
    $rowCount = $stmt->rowCount();
    
    if ($rowCount > 0) {
       $row = $stmt->fetchAll(PDO::FETCH_NUM);
       
       $values = array();
       
       foreach ($row as $rowvalue) {
           $values[] = array($rowvalue[0], $rowvalue[1], $rowvalue[2], $rowvalue[3], $rowvalue[4], $rowvalue[5]);
       }
       
       $stmt -> nextRowset();
       $row = $stmt->fetchAll();
       
       foreach ($row as $rowvalue) {
           $values[] = array($rowvalue[0], $rowvalue[1], $rowvalue[2], $rowvalue[3], $rowvalue[4], $rowvalue[5]);
       }
       
       $stmt -> nextRowset();
       $row = $stmt->fetchAll();
       
       foreach ($row as $rowvalue) {
           $values[] = array($rowvalue[0], $rowvalue[1], $rowvalue[2], $rowvalue[3], $rowvalue[4], $rowvalue[5]);
       }
       ...
       echo json_encode($values);
    }      

Updated to use the code example below:

    $sql = 'CALL fo_SELECT_Operations_Detail(?,?,?)';
    $facility = $_POST['facility'];
    $startweek = $_POST['startweek'];
    $endweek = $_POST['endweek'];
    
    $sql->bindParam(1, $facility, PDO::PARAM_STR);
    $sql->bindParam(2, $startweek, PDO::PARAM_STR);
    $sql->bindParam(3, $endweek, PDO::PARAM_STR);
    $stmt = $conn->query($sql);
    
    $values = array();
    
    do {
        $rows = $stmt->fetchAll(PDO::FETCH_NUM);
        foreach ($rows as $r) {
           $values[] = array($r[0], $r[1], $r[2], $r[3], $r[4], $r[5]);
       }
    
    } while ($stmt->nextRowset());
    
    // all processed so now send JSON
    echo json_encode($values);

Answer

We all forget that SP’s which create rowsets also create an annoying empty one as well that has to be Next’d over, but not actually unloaded. The && $stmt->columnCount() looks after getting nextRowset() called but not actually attempting to process it in any way.

$sql = 'CALL fo_SELECT_Operations_Detail(?,?,?)';
$stmt->prepare($sql);
    
$stmt->bindParam(1, $_POST['facility'], PDO::PARAM_STR);
$stmt->bindParam(2, $_POST['startweek'], PDO::PARAM_STR);
$stmt->bindParam(3, $_POST['endweek'], PDO::PARAM_STR);

$stmt = $conn->execute();

$values = array();

do {
    $rows = $stmt->fetchAll(PDO::FETCH_NUM);
    foreach ($rows as $r) {
       $values[] = [$r[0], $r[1], $r[2], $r[3], $r[4], $r[5]];
   }

} while ($stmt->nextRowset()  && $stmt->columnCount());

// all processed so now send JSON
echo json_encode($values);