[SOLVED] calling mysql stored procedures. multiple arguments.

[etm124]

Hello all,

 

Stumbled across this site as I was searching for my answer. I also searched the forums, and found this thread. I am using mysql 5, centOS.

 

I am doing a simple login/pw validation. My stored procedure is as follows:

| sp_validation |          | 
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_validation`(in uname varchar(25), in pword varchar(25))
begin
select * from user where username=uname and password=pword;
END | 

 

my snipit of code in my php:

 

my connection to the database is fine. I have verified that.

$result=mysqli_query('call sp_validation("edwin","beerbeer")');
$num=mysql_num_rows($result);
echo $num;

 

it seems to be failing at the $result=... part. Here is what happens when I run the stored procedure manually:

mysql> call sp_validation("emiller","beerbeer");
+----------+----------+-------+--------+------------+-------------+------+
| username | password | fname | lname  | last_login | last_logout | ip   |
+----------+----------+-------+--------+------------+-------------+------+
| emiller  | beerbeer | edwin | miller | NULL       | NULL        | NULL |
+----------+----------+-------+--------+------------+-------------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

 

I've seen many articles on the net, but not a lot telling how to pass in multiple arguments AND return a value.

 

Am I returning something incorrectly in my sproc? or is it my php?

 

Thank you for any help.

 

 

 

[etm124]

bump.

[etm124]

this forum moves fast

[etm124]

ok, this is what im now using as a stored procedure to make it a bit easier:

 

procedure sp_validation2(in u varchar(25), out p varchar(25))
begin
select password into p from user where username=u;
END

 

onto my php:

$mysqli = new mysqli("localhost", "root","");
$mysqli->select_db("my_db");
$mysqli->query('call sp_validation2("emiller",@p)');
$result = $mysqli->query('select @p');
echo $result

 

"Object id#2" prints to the screen.

 

when i manually run the stored procedure, it works fine.

 

im pulling my hair out over here.

 

thanks again.

[effigy]

You need to add error checking. An Object id is correct because that's what the result is. You need to use this object to access the rest of the data. See example 1469 here.

[etm124]

you are the man.

 

SOLVED!