$objConn = setDbConn();
$strSQL = "CALL mp_proc_getHomePageProjNews('Chennai')";
if($objConn->multi_query($strSQL))
{
do
{
$l = 0;
/* store first result set */
if ($result = $objConn->use_result())
{
while($row = $result->fetch_row())
{
$arrTempResult[] = $row;
}
$arrResult[] = $arrTempResult;
unset($arrTempResult);
$result->close();
}
if($objConn->more_results())
{
}
} while ($objConn->next_result());
}
Category Archives: Procedure Call
Getting Records from Multiple Result sets in PHP
Table Structure
CREATE TABLE Areas(AreaName VARCHAR(255),
PinCode VARCHAR(255))
INSERT INTO Areas(AreaName, PinCode)
VALUES('Teynampet', '6000018'),
('Ramapuram', '6000089'),
('TNagar', '6000017'),
('Mylapore', '6000014'),
('Gopalapuram', '6000087')
Procedure which returns multiple result sets
DROP PROCEDURE IF EXISTS mp_test1; CREATE PROCEDURE mp_test1() BEGIN SELECT AreaName FROM Areas; SELECT PinCode FROM Areas; END
Procedure Call
CALL mp_test1()
PHP Code To retrieve Records from Multiple Resultsets
$mysqli = new mysqli('localhost', 'root', '', 'test','3306');
$query = "CALL mp_test1()";
$i = 0;
if ($mysqli->multi_query($query))
{
do {
/* store first result set */
if ($result = $mysqli->use_result())
{
$j = 0;
while ($row = $result->fetch_row())
{
$arrResult[$i][$j] = $row[0];
$j++;
}
$result->close();
}
if($mysqli->more_results())
{
$i = $i + 1;
}
} while ($mysqli->next_result());
}
print "";
print_r($arrResult);
Procedure Call from PHP
Mysqli Method
Procedure
DROP PROCEDURE IF EXISTS prSample1;
CREATE PROCEDURE prSample1(OUT Name VARCHAR(255))
BEGIN
SET Name = 'Mugil';
SELECT Name;
END;
Call to Procedure From PHP
$Conn = new mysqli('localhost', 'root', '', 'metroplots');
$strSQL = "CALL prSample1(@Name)";
$stmt = $Conn->prepare($strSQL);
$stmt->execute();
$stmt->bind_result($Name);
while($stmt->fetch())
$strName = $Name;