Mysql stored procedure : cant run from PHP code

Question

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('[email protected]')

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

Answer 1

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    |

+------+

Answer 2

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

Answer 3

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', "[email protected]");
$stmt->execute();

$stmt->bind_result($userid);

while ($stmt->fetch()) {
  printf("User ID: %d\n", $userid);
}

$stmt->close();
$mysqli->close();