我有以下存儲過程：CREATE DEFINER=`CNX`@`%` PROCEDURE `sp_Facturatie_OpenstaandeBetalingen_Get`(OUT spResult varchar(200))BEGINDECLARE 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.betaalmethodeidWHERE BS.betaalstatusomschrijving <> 'Betaald'ORDER BY betalingid ASC;END我想在 PHP 中使用 PHP 中的 MySqli 以 Procedural 風格調用這個存儲過程。在 PHP 中嘗試了很多不同的代碼。有人可以建議我如何做到最好嗎？
1 回答
茅侃侃
TA貢獻1842條經驗 獲得超21個贊
我想分享一下我如何克服這個問題并找到解決方案的最終結果。
MySQL存儲過程：
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
在 PHP 中，我做了以下事情：
檢查結果集是否返回零行 - 表示存儲過程已正確執行，但未找到結果，否則
檢查結果集是否有一個名為“spResult”的字段。如果是這樣，請在屏幕上讀取并填充結果，否則
根據需要處理結果集。
$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();
}
我希望有人覺得這個答案有用；當然可以進一步改進:)
- 1 回答
- 0 關注
- 148 瀏覽
添加回答
舉報
0/150
提交
取消