call stored procedure using php

Question

i have written a basic stored procedure using mysql

DELIMITER // 

CREATE PROCEDURE `sp_sel_test`()
BEGIN

    SELECT * FROM category c;

END// 
DELIMITER ;

now i m calling it from php

the php code is:

  <?php
    $txt = $_GET['id'];
    $name = $_GET['name'];
$con = mysql_connect("localhost","four","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("fourthes_a", $con);



//$result = mysql_query("select * from new_c where name like %". $name ."% or c_name like %" . $name . "% order by name asc;");


$result = mysql_query("call sp_sel_test()");
    if ($result === FALSE) {
    die(mysql_error());
}
while($row = mysql_fetch_array($result))
  {
  echo $row['category_id'] . " " . $row['c_name'];
  ?>
  <br />
  <?php
  }

mysql_close($con);
    echo $txt;

?>

now its giving the error

PROCEDURE fourthes_a.sp_sel_test can't return a result set in the given context

The source code doesn't seem to match the error message. Which line is 26? — wallyk, Apr 12 '11 at 21:41
Have you tested it from phpmyadmin, or the mysql command line to see if the stored procedure returns as expected? — profitphp, Apr 12 '11 at 21:41
phpmyAdmin also says PROCEDURE fourthes_a.sp_sel_test can't return a result set in the given context — Pradyut Bhattacharya, Apr 12 '11 at 21:54
Have you checked out [this question](http://stackoverflow.com/questions/1200193/cant-return-a-result-set-in-the-given-context)? Your problem may be caused by a PHP bug and you may need to upgrade to version 5.3. — Gruber, Dec 14 '11 at 12:47

score 5 · Answer 1 · answered Apr 12 '11 at 21:45

mysql_query() returns false when the query fails. You didn't check if your sproc query succeeded, so most likely you're passing that boolean FALSE to the fetch function, which is rightfully complaining.

Rewrite your code like this, as a bare mininum, for proper error handling:

$res = mysql_query('call sp_sel_test()');
if ($res === FALSE) {
    die(mysql_error());
}

Never ever assume a query succeeded. Even if the SQL syntax is perfect, there's far too many other reasons for a query to fail to NOT check if it worked.

with your code its giving the error PROCEDURE fourthes_a.sp_sel_test can't return a result set in the given context — Pradyut Bhattacharya, Apr 12 '11 at 21:53

score 4 · Answer 2 · answered Apr 12 '11 at 22:17

4

You need to set client flags while connecting for using stored procedures with php. Use this:

mysql_connect($this->h,$this->u,$this->p,false,65536);

See MySQL Client Flags for more details. PHP MySQL does not allow you to run multiple statements in single query. To overcome this you must tell PHP to allow such queries by setting CLIENT_MULTI_STATEMENTS flag in your connection.

answered Apr 12 '11 at 22:17

Ashwini Dhekane

2,280
14
19

It should work. If it did not work. try calling mysql_connect again using flag 131072 or 199608. Which will set multiple results flag. – Ashwini Dhekane Apr 12 '11 at 22:38

score 1 · Answer 3 · answered Jun 20 '13 at 16:36

I know last answer was a year ago, but...

CREATE PROCEDURE sp_sel_test(OUT yourscalarvariable INT/TEXT...)

Statements that return a result set cannot be used within a stored function. This includes SELECT statements that do not use INTO to fetch column values into variables, SHOW statements, and other statements such as EXPLAIN. For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET_IN_FUNC). For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can't return a result set in the given context error occurs (ER_SP_BADSELECT).

So, your select shoould be like this:

       SELECT * FROM category **INTO** c;

http://www.cs.duke.edu/csl/docs/mysql-refman/stored-procedures.html

call stored procedure using php

3 Answers3