Page 1 of 1

Call procedure from PHP doesn't work for me, please help!!!

Posted: Thu May 25, 2006 3:55 am
by gnel2000
Weirdan | Please use

Code: Select all

,

Code: Select all

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:

Code: Select all

$db = mysqli_connect('localhost', 'root', '');
  if (mysqli_connect_errno())
  {
     print 'Error: Could not connect to database. Please try again later.';
     exit;
  } else {
  	echo "no error";
  }

  mysqli_select_db($db, 'testing');
  
  //$result3 = mysqli_multi_query($db, "call select_emp()") or die(mysqli_error($db));
  
  if($result = mysqli_multi_query($db, "call select_emp()")) {  
	   while ($row = mysqli_fetch_array($result,MYSQLI_NUM)) {
	   	echo $row[0];
	   }	  
	   mysqli_free_result($result);
	   echo 'good';
  } else {
  	echo 'sucks';
  }
From command like, i can see the result with: Call select_emp(), but not at browser.
I still don't understand whats wrong with my coding!

Is there any one have any idea about this?


Weirdan | Please use

Code: Select all

,

Code: Select all

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]

mysqli

Posted: Fri May 26, 2006 7:20 pm
by tr0gd0rr
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.

http://php.net/mysqli_multi_query

Posted: Sat May 27, 2006 2:50 am
by raghavan20
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.

Code: Select all

$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));
}

Posted: Sun May 28, 2006 10:44 pm
by gnel2000
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.

Code: Select all

$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?

Posted: Mon May 29, 2006 12:38 am
by RobertGonzalez
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.

Code: Select all

<?php
$sQuery="CALL SomeSP('params')";
if(!mysqli_multi_query($sqlLink,$sQuery)) {
  // your error handler
}
$sqlResult=mysqli_store_result($sqlLink);

if(mysqli_more_results($this->sqlLink))//Catch 'OK'/'ERR'
  while(mysqli_next_result($this->sqlLink));
?>
You will have to rewrite/expand this a bit for more usability of course, but it's just an example.
So to expand on your query, maybe try this...

Code: Select all

<?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.

Posted: Mon May 29, 2006 1:47 am
by gnel2000
Its finally working now. I just added the mysql_store_result() in my original code, then i got the result i want.

I have referring to an article and that article is somehow misleading me. :evil:

Thanks a lot Everah, really appreciate your hardwork.
Thanks again.