mysql stored procedures using php

Question

I have a stored procedure:

delimiter //

create procedure userlogin(in eml varchar(50))

begin

  select * from users 

  where email = eml;

end//

delimiter ;

And the php:

$db = new mysqli("localhost","root","","houseDB");

$eml = "[email protected]";

$sql = $db->query("CALL userlogin('$eml')");

$result = $sql->fetch_array();

The error that I get from the browser when I run the php script:

Fatal error: Call to a member function fetch_array() on a non-object...

I am using phpmyadmin version 3.2.4 and mysql client version 5.1.41.

a1ex07 · Accepted Answer · 2010-04-20 21:32:51Z

2

You have to use mysqli_multi_query, not query. Check http://us.php.net/manual/en/mysqli.multi-query.php , they have a good example

answered Apr 20, 2010 at 21:32

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

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

2 Comments

Pekka Over a year ago

Why does calling a stored procedure need mysqli_multi_query? He isn't creating it, he's just calling it, isn't he?

a1ex07 Over a year ago

But it returns values. If it didn't, it would work as it is (actually, it's working, but in order to access result set from procedure we need to use multi_query).

Pekka · Accepted Answer · 2010-04-20 21:30:25Z

0

mysqli::query returns false if the query fails (instead of returning a result object or true). You need to test whether the result actually is an object:

$sql = $db->query("CALL userlogin('$eml')");

if (is_object($sql)) 
$result = $sql->fetch_array();
else
printf("Error: %s\n", $sql->error);

You will probably get an error message explaining why calling the stored procedure didn*t work out.

answered Apr 20, 2010 at 21:30

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

2 Comments

maid450 Over a year ago

$sql->error will also fail because false has no 'error' attribute. In case of false you can echo mysql_error(); to debug the error

Pekka Over a year ago

@maid450 this is mysqli, not mysql.

Collectives™ on Stack Overflow

mysql stored procedures using php

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related