[Galaxy_Stranger]

I was working on "change my password" functionality in my web application. Everything's working fine except for a little bug. The way I've written the stored procedure, you pass it the users existing password, the new password and their user id. The update changes the password if the existing password matches with what's already in the table. If they don't match, it doesn't update the password.

My problem is that when you put in an existing password that doesn't match the table value, the stored procedure still WORKS, so there's no error thrown. Basically, "update user_password where user_id = user_id AND existing_password = table_value ;".

As you can see, the update either occurs or it doesn't. There is no error if the update does not happen.

What's the best way to go about handling this?

TIA.

[Martyr2]

There is ROW_COUNT() which you can use to determine if any rows were updated. Of course if the answer is zero then you know it was unsuccessful.

Hopefully this is what you were looking for.

[Galaxy_Stranger]

Thanks for the reply, Martyr.

Yeah, ROW_COUNT() looks like the way to go. But I'm having problems grabbing the return string value. Here's my procedure:

CREATE DEFINER=`myUser`@`localhost` PROCEDURE `USER_CHANGE_MY_PASSWORD`(IN currentUserId VARCHAR(45), IN currentPassword VARCHAR(45), IN newPassword VARCHAR(45), OUT outcomeString VARCHAR(45))
BEGIN
    /*  Update the table:   */
    UPDATE USER_AUTH
    SET USER_PASSWORD = newPassword
    WHERE USER_ID = currentUserId
    AND USER_PASSWORD = currentPassword ;

    /* If a row got updated, return true, if not, return false so the code can handle it:   */
    IF(ROW_COUNT() > 0) THEN
        SET outcomeString = "THE RECORD WAS UPDATED!" ;
    ELSE
        SET outcomeString = "THERE WAS NO RECORD THAT MET THE CRITERIA TO BE UPDATED." ;
    END IF ;
END

This compiles and, given good inputs, updates the users password. Here is my procedure call:

$sql = "CALL USER_CHANGE_MY_PASSWORD('$currentUserId',password('$currentPassword'),password('$newPassword'), @outcomeString)" ;

And then I have a try/catch: if(!($result = mysql_query($sql))), etc...

But I'm having problems grabbing the return value. I presume it has something to do with bind_param(). All of the examples I see are using a lot of different techniques that I'm not up to yet. Can you point me in the right direction?

This post has been edited by Galaxy_Stranger: 05 July 2012 - 08:38 PM