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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gnel2000
Forum Newbie
Posts: 16
Joined: Tue Jul 26, 2005 10:41 pm

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

Post 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]
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

mysqli

Post 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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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));
}
gnel2000
Forum Newbie
Posts: 16
Joined: Tue Jul 26, 2005 10:41 pm

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
gnel2000
Forum Newbie
Posts: 16
Joined: Tue Jul 26, 2005 10:41 pm

Post 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.
Post Reply