and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Hi All,
i'm new to stored procedure, i have go thru a lot of article and testing on their example.
I manage to create and call procedure from the command like. But my only problem is
when calling procedure from PHP.
Here is the coding:
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
I'm new to mysqli as well, but the php manual indicates that mysqli_multi_query returns a boolean and mysqli_store_result returns the result handle you are looking for.
Normally, a multi_query can return multiple results. We have to normally see results available, if available store result and then use it. Keep looping the same procedure until you find there is no more result to be picked up.
The procedure of reading results is just as same as running two or more select statement using a multi_query().
Also remember to free result because this might sometimes lead to locking problems as well.
$query = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";
/* execute multi query */
if (mysqli_multi_query($link, $query)) {
do {
/* store first result set */
if ($result = mysqli_store_result($link)) {
while ($row = mysqli_fetch_row($result)) {
printf("%s\n", $row[0]);
}
mysqli_free_result($result);
}
/* print divider */
if (mysqli_more_results($link)) {
printf("-----------------\n");
}
} while (mysqli_next_result($link));
}
raghavan20 wrote:guys, what multi_query returns is not important.
Normally, a multi_query can return multiple results. We have to normally see results available, if available store result and then use it. Keep looping the same procedure until you find there is no more result to be picked up.
The procedure of reading results is just as same as running two or more select statement using a multi_query().
Also remember to free result because this might sometimes lead to locking problems as well.
$query = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";
/* execute multi query */
if (mysqli_multi_query($link, $query)) {
do {
/* store first result set */
if ($result = mysqli_store_result($link)) {
while ($row = mysqli_fetch_row($result)) {
printf("%s\n", $row[0]);
}
mysqli_free_result($result);
}
/* print divider */
if (mysqli_more_results($link)) {
printf("-----------------\n");
}
} while (mysqli_next_result($link));
}
well, i have no problem with the about code.
My problem is, i have created the stored procedure in mysql. but when i call the stored procedure from PHP, it somehow cannot display the data fetched into the array. In fact, i dont even know whether it have fetch correctly or not. As you can see in my coding, i just fetch the result as normal like how I did with mysql_fetch_array. Is this any special or different way to fetch the result set when using mysqli_fetch?
After a nasty, exhaustive search, I think I may have found something related to your problem on the PHP web site. It is a user supplied omment in the manual section for mysqli_query().
info at ff dot net wrote:Calling Stored Procedures
Beeners' note/example will not work. Use mysqli_multi_query() to call a Stored Procedure. SP's have a second result-set which contains the status: 'OK' or 'ERR'. Using mysqli_query will not work, as there are multiple results.
<?php
$db = mysqli_connect('localhost', 'root', '');
if (mysqli_connect_errno())
{
die('Error: Could not connect to database. Please try again later: ' . mysqli_error());
} else {
echo "no error";
}
mysqli_select_db($db, 'testing');
$sQuery="CALL select_emp()";
if(!mysqli_multi_query($db,$sQuery)) {
echo 'There was a problem: ' . mysqli_error();
}
$sqlResult=mysqli_store_result($db);
while ($row = mysqli_fetch_array($sqlResult,MYSQLI_NUM)) {
echo $row[0];
}
mysqli_free_result($sqlResult);
?>
I have no idea if this will work, or why I went on a personal crusade to figure this out, but I hope it helps or at least sets you in the right direction. Good luck.