Retrieve return value from MySQL function inside php

Question

Possible Duplicate:
mysql_fetch_array() expects parameter 1 to be resource, boolean given in select

I have the following MySQL Function:

DELIMITER $$
DROP FUNCTION IF EXISTS DoUsernameAndPasswordMatch$$
CREATE FUNCTION DoUsernameAndPasswordMatch(
    UserLogin VARCHAR(20),
    UserPass VARCHAR(20)
)
RETURNS BOOLEAN
BEGIN
DECLARE userCount BOOLEAN;

    SELECT COUNT(*) INTO userCount
    FROM Users_Login 
    WHERE Users_Login.UserLogin = UserLogin AND Users_Login.UserPass = SHA1(UserPass);

    RETURN userCount;    
END$$
DELIMITER ;

I invoke it from PHP like this:

$username_escaped = escapeMySQLParam($_POST["user"]);
$userpass_escaped = escapeMySQLParam($_POST["pass"]);

$mysql_query = "SELECT DoUsernameAndPasswordMatch('$username_escaped', '$userpass_escaped')";
$mysql_results = mysql_query($mysql_query);
$mysql_row = mysql_fetch_row($mysql_results);

But i get the following error:

Warning: mysql_fetch_row() expects parameter 1 to be resource, 
boolean given in D:\xampp\webdesktop\login.php on line 22

I understand the error and tried to fixed it and workaround it with no success. Is it possible to retrieve INTEGER value from MySQL in PHP and how?

Or could you advice me better way to do this.

Thank!

This kind of error happens when your request return null. have you tried your sql request ? — zessx, May 24 '12 at 17:36
Have you tried dumping $mysql_results to see what you're getting? It sounds like maybe it's putting the result of the procedure directly into the variable, rather than returning a SQL resource (the kind of thing you can run mysql_fetch_row on). The other possibility, as samsamX said, is that your query is broken and is returning nothing. — octern, May 24 '12 at 17:37
Yes, I echoed my request string and executed in MySQL IDE, everything was OK and I got result of "1" — iatanasov, May 24 '12 at 17:38
Also tried to dump the $mysql_results value but nothing shown, very strange... — iatanasov, May 24 '12 at 17:40
Tried `$mysql_results = mysql_query($mysql_query) or die( mysql_error() );` to see if that throws any errors? — kmfk, May 24 '12 at 17:54
`$mysql_results = mysql_query($mysql_query) or die(mysql_error());` and check output. There must be an error in your query — Vishal, May 24 '12 at 17:54

score 0 · Answer 1 · answered May 24 '12 at 18:56

Make the following changes to work:

    DELIMITER $$
DROP FUNCTION IF EXISTS DoUsernameAndPasswordMatch$$
CREATE FUNCTION DoUsernameAndPasswordMatch(
    UserLogin VARCHAR(20),
    UserPass VARCHAR(20)
) RETURNS TEXT
BEGIN
DECLARE userCount TEXT;

    SELECT COUNT(*) INTO userCount
    FROM Users_Login 
    WHERE Users_Login.UserLogin = UserLogin AND Users_Login.UserPass = SHA1(UserPass);

    RETURN userCount;    
END$$
DELIMITER ;

Retrieve return value from MySQL function inside php

1 Answers1