MySQL stored procedure: can't run from PHP code

Question

I have the 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 works just fine:

call tv_check_email('[email protected]')

But when trying to execute from the PHP page, I get an error like

"PROCEDURE mydatabase.tv_check_email can't return a result set in the given context"

I am sure that my PHP version is 5.2.6.

St. John Johnson · Accepted Answer · 2009-04-09 15:36:12Z

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();

answered Apr 9, 2009 at 15:36

St. John Johnson

6,6607 gold badges37 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cody Caughlan Over a year ago

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.

Cody Caughlan · Accepted Answer · 2009-04-09 05:23:57Z

3

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    |

+------+

answered Apr 9, 2009 at 5:23

Cody Caughlan

32.8k5 gold badges67 silver badges68 bronze badges

Comments

Cody Caughlan · Accepted Answer · 2009-04-09 05:33:10Z

3

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

answered Apr 9, 2009 at 5:33

Cody Caughlan

32.8k5 gold badges67 silver badges68 bronze badges

Collectives™ on Stack Overflow

MySQL stored procedure: can't run from PHP code

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related