Reading OUT parametter of Stored procedure

[veledrom]

Hi,

How do i check result of out parametter of stored procedure in php?

CREATE PROCEDURE `username`(IN name_in varchar(10), OUT name_out varchar(10))
BEGIN
	SELECT name FROM table WHERE name= name_in;
END;

//This always returns 1 even there is no record in the table.
$query = "CALL username($name, @out)";
$run = mysql_query("SELECT @out");

if (@mysql_num_rows($run) ==0) {
 echo "No record";
} else {
 echo "Yes record";
}

Where is the mistake?
Thanks guys

[digital-ether]

Heres the docs on creating stored procedures:

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

From my tests (on win):

You have to use:

delimiter //

Which will set the delimiter to // instead of the default ;

This will allow you to use ; in your store procedure - it will be treated as a literal.

If you look at the example on the mysql docs:

SELECT COUNT(*) INTO param1 FROM t;

They use "select ... into .. param "

For your purpose, it would be:

SELECT name into name_out FROM table WHERE name= name_in;

In your PHP code you have:

$query = "CALL username($name, @out)";
$run = mysql_query("SELECT @out");

I don't see where the first query is being run, that calls the username procedure.

Should it be:

$query = "CALL username($name, @out)";
$run = mysql_query($query);
$run = mysql_query("SELECT @out");

Also, the reason why you're always getting a result is because mysql will return NULL if you select a variable that does not exist:

eg:

SELECT @test;

Will return:

NULL

The correct way in this case would be to test the value returned, instead of counting the returned rows.

[veledrom]

I check the value but it returns this "Resource id #4" instead of the result in PHP.
Also, i dont know where to put delimiter // in stored procedure. Without any adjustment, It works fine in sql command prompt, returns values.

??

[digital-ether]

I check the value but it returns this "Resource id #4" instead of the result in PHP.
Also, i dont know where to put delimiter // in stored procedure. Without any adjustment, It works fine in sql command prompt, returns values.

??

It looks like you're viewing the reference to the Mysql connection created when you did mysql_connect() or similar.

Could you post the code.

[veledrom]

Stored Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `name`(IN name_in varchar(10), OUT name_out varchar(10))
BEGIN
SELECT username INTO name_out FROM login WHERE username=name_in;
END;

MySQL command: CALL name('EDI', @name_out); SELECT @name_out; Result : EDI. Because it is in table. This works.
--------------------------
PHP code:

$q="CALL name('EDI', @name_out);";
$run=mysql_query($q);
$run=mysql_query("SELECT @name_out;");

echo @mysql_num_rows($run);
echo "<br>".$run;

Result :
1
Resource id #4

Always returns like this even whether or not EDI is in table.

[digital-ether]

Edit: Looks like the stored procedure exists across mysql connections.

Try validating each query after you make it in PHP to see if it worked, and get any error messages.
http://www.php.net/mysql_error

[veledrom]

Long time later Solved.

This line

$run=mysql_query("SELECT @name_out;");

must be like this

$run=mysql_query("SELECT @name_out AS NameOut;");

Then use an array to read the NameOut.