Mysql stored procedure : cant run from PHP code

I have below stored procedure to check the user name availability

DELIMITER $$;

DROP PROCEDURE IF EXISTS tv_check_email$$

CREATE PROCEDURE tv_check_email (IN username varchar(50)) BEGIN select USER_ID from tv_user_master where EMAIL=username; END$$

DELIMITER ;$$

when i run this from my mysql front end tool, it is working fine

call tv_check_email('shyju@techies.com')

But when trying to execute from the PHP page, i am getting an error like "PROCEDURE mydatabase.tv_check_email can't return a result set in the given context"

Can any one tell me why it is so ?

I am sure that my PHP version is 5.2.6

Thanks in advance

From stackoverflow

• You need to bind your result into an OUT parameter.

  See the mysql docs on stored procedures

  mysql> delimiter //
  
  mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
      -> BEGIN
      ->   SELECT COUNT(*) INTO param1 FROM t;
      -> END;
      -> //
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> delimiter ;
  
  mysql> CALL simpleproc(@a);
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> SELECT @a;
  +------+
  | @a   |
  +------+
  | 3    |
  

  +------+

  Shyju : thanks cody , it worked

• It looks like if you use the mysqli PHP library you can actually retrieve your result set without having to use an OUT variable and another query to retrieve your value. This article covers the details:

  http://amountaintop.com/php-5-and-mysql-5-stored-procedures-error-and-solution-qcodo

• Cody is not 100% right. You can bind your resulting return columns and return select data from within a stored procedure.

  $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
  
  $stmt = $mysqli->prepare("call tv_check_email(?)");
  $stmt->bind_param('s', "shyju@techies.com");
  $stmt->execute();
  
  $stmt->bind_result($userid);
  
  while ($stmt->fetch()) {
    printf("User ID: %d\n", $userid);
  }
  
  $stmt->close();
  $mysqli->close();
  

  Cody Caughlan : Ah yes, much better. From my understanding, the std. mysql extension doesnt support accessing result sets w/o OUT, but the mysqli does (as your code indicates)? Good to know.