Stored procedure – would like to read OUTPUT parameter and select-resultset

I have the following stored procedure:

CREATE DEFINER=`CNX`@`%` PROCEDURE `sp_Facturatie_OpenstaandeBetalingen_Get`(OUT spResult varchar(200))
BEGIN

DECLARE exit handler for SQLEXCEPTION
 BEGIN
  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
   @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  SET spResult = left(CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text),200);
 END;

SET spResult = 'TRUE';


/*SELECT @spResult;*/

SELECT *
FROM   Web_tblAfspraakBetalingen AB
       LEFT OUTER JOIN Web_tblBetaalStatus BS
                    ON AB.betalingbetaalstatusid = BS.betaalstatusid
       LEFT OUTER JOIN Web_tblBetaalMethode BM
                    ON AB.betalingbetaalmethodeid = BM.betaalmethodeid
WHERE  BS.betaalstatusomschrijving <> 'Betaald'
ORDER  BY betalingid ASC;

END

I would like to call this stored procedeure in PHP with MySqli in PHP, in Procedural style. Tried a lot of diffrent code in PHP. Can someone advice how I can do this best?

Code I tried:

$link = mysqli_connect($host, $gebruiker, $wachtwoord, $database)  or die("Query fail: " . mysqli_error());
$call = mysqli_prepare($link, 'sp_Facturatie_OpenstaandeBetalingen_Get(@ResultText)');
mysqli_stmt_execute($call);
$select = mysqli_query($link, 'SELECT @ResultText');
$result = mysqli_fetch_assoc($select);
$ResultText = $result['@ResultText'];

if ($ResultText === 'TRUE')
{
    echo $ResultText;
    die();
}

if ($result)
{
    $rowcount = mysqli_num_rows($result);
    echo $rowcount;
    while ($row = mysqli_fetch_array($result))
    {
        //BetalingID, BetalingAfspraakID, BetalingDatum, BetalingBedrag, BetalingBetaalMethodeID, BetalingBetaalStatusID, BetalingCreatedOn, BetalingUpdatedOn, BetaalStatusID, BetaalStatusOmschrijving, BetaalMethodeID, BetaalMethodeOmschrijving, id
        $BetalingAfspraakID = $row["BetalingAfspraakID"];
        $BetalingDatum = $row["BetalingDatum"];
        $BetalingBedrag = $row["BetalingBedrag"];
        $BetaalStatusOmschrijving = $row["BetaalStatusOmschrijving"];
        $BetaalMethodeOmschrijving = $row["BetaalMethodeOmschrijving"];

        echo "<tr>";
        echo "<td>";
        echo "<b>Afspraak</b> " . $BetalingAfspraakID . " op " . $BetalingDatum . "</br>";
        echo "Bedrag: " . $BetalingBedrag . " EUR per " . $BetaalMethodeOmschrijving . "</br>";
        echo "Status: " . $BetaalStatusOmschrijving;
        echo "</td>";
        echo "<td>";
        ?>
        <button type="button" class="ButtonStyle" onmousedown="toggleOverlay('OverlayPageAfspraakBetalingen',<?php echo $BetalingAfspraakID ?>)">Bewerken</button>
        <?php
        echo "</td>";
        echo "</tr>";
    }           
    // free result set
    mysqli_free_result($result);
}

enter image description here

enter image description here

Answer

I would like to share the final result on how I have overcome the issue and found a solution.

MySQL stored procedeure:

CREATE DEFINER=`CN20100686`@`%` PROCEDURE `sp_Facturatie_OpenstaandeBetalingen_Get`()
BEGIN

Declare spResult nvarchar(200);

DECLARE exit handler for SQLEXCEPTION /*declared an exception handler, which the result is put into a select*/
 BEGIN
   GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
   @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  SET spResult = left(CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text),200);
  SELECT spResult;

 END;

SELECT  AB.BetalingAfspraakID,
        AB.BetalingDatum,
        Round(AB.BetalingBedrag,2) BetalingBedrag,
        BS.BetaalStatusOmschrijving,
        BM.BetaalMethodeOmschrijving,
FROM   Web_tblAfspraakBetalingen AB
       LEFT OUTER JOIN Web_tblBetaalStatus BS
                    ON AB.betalingbetaalstatusid = BS.betaalstatusid
       LEFT OUTER JOIN Web_tblBetaalMethode BM
                    ON AB.betalingbetaalmethodeid = BM.betaalmethodeid
WHERE  BS.betaalstatusomschrijving <> 'Betaald'
ORDER  BY betalingid ASC;

END

in PHP I’ve done the following:

• Check if the resultset came back with zero rows – meaning stored procedeure was executed correctly, but no results were found, else
• Check if the resultset has a field called ‘spResult’. If so, read and populate result on screen, else
• Handle resultset as you wish.

Sample in PHP:

$sproc = 'sp_Facturatie_OpenstaandeBetalingen_Get()';
$result = mysqli_query($link,"CALL " . $sproc) or die("mysqli_query failure: " . mysqli_error());
$rowcount=mysqli_num_rows($result);

    if ($rowcount == 0) {echo "No records found.";}
    if ($result)
    {
        $rowcounter = 'first';
        while ($row = mysqli_fetch_array($result))
        {
            if (substr($row["spResult"],0,5) == 'ERROR')
            {
                echo "Error occured on executing " . $sproc . ": " . $row["spResult"];
                mysqli_free_result($result);
                exit();
            }
            if ($rowcounter == 'first')
            {
                //printing here my table header
                echo "
                     <b>List</b>
                     </br>
                     <table border=1 style='min-width:100%'>
                     <tr>
                        <td width='80%'>
                          Payments:
                       </td>
                       <td width='20%'>
                       </td>
                     </tr>
                     Count open payments: " . $rowcount;
                $rowcounter = 'next';
            }
            $BetalingAfspraakID = $row["BetalingAfspraakID"];
            $BetalingDatum = $row["BetalingDatum"];
            $BetalingBedrag = $row["BetalingBedrag"];
            $BetaalStatusOmschrijving = $row["BetaalStatusOmschrijving"];
            $BetaalMethodeOmschrijving = $row["BetaalMethodeOmschrijving"];

                //display results as you wish
                echo "<tr>";
                echo "<td>";
                echo "<b>Afspraak</b> " . $BetalingAfspraakID . "</br>";
                echo "Amount: " . $BetalingBedrag . "</br>";
                echo "Status: " . $BetaalStatusOmschrijving;
                echo "</td>";
                echo "</tr>";
        }

        // free result set
        mysqli_free_result($result);
    }
    else
    {
        echo "Error occured!" . mysqli_error($link);
        exit();
    }

I hope someone finds this answer usefull; of course open for further improvements 🙂