Using SELECT resultset to run UPDATE query with MySQL Stored Procedures

I'm trying to understand MySQL Stored Procedures, I want to check if a users login credentials are valid and if so, update the users online status:

-- DROP PROCEDURE IF EXISTS checkUser;
DELIMITER //
CREATE PROCEDURE checkUser(IN in_email VARCHAR(80), IN in_password VARCHAR(50))
BEGIN
    SELECT id, name F开发者_如何学PythonROM users WHERE email = in_email AND password = in_password LIMIT 1;
    -- If result is 1, UPDATE users SET online = 1 WHERE id = "result_id";
END //
DELIMITER ;

How Can I make this if-statement based on the resultsets number of rows == 1 or id IS NOT NULL?

DELIMITER //
CREATE PROCEDURE checkUser(IN in_email VARCHAR(80), IN in_password VARCHAR(50))
BEGIN
    DECLARE tempId INT DEFAULT 0;
    DECLARE tempName VARCHAR(50) DEFAULT NULL;
    DECLARE done INT DEFAULT 0;

    DECLARE cur CURSOR FOR 
        SELECT id, name FROM users WHERE email = in_email AND password = in_password;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    REPEAT
        FETCH cur INTO tempId, tempName;
        UPDATE users SET online = 1 WHERE id = tempId;
    UNTIL done  = 1 END REPEAT;
    CLOSE cur;

    SELECT tempName;
END //
DELIMITER ;

NB: I have not tested this. It's possible that MySQL doesn't like UPDATE against a table it currently has a cursor open for.

PS: You should reconsider how you're storing passwords.

Re comment about RETURN vs. OUT vs. result set:

RETURN is used only in stored functions, not stored procedures. Stored functions are used when you want to call the routine within another SQL expression.

SELECT LCASE( checkUserFunc(?, ?) );

You can use an OUT parameter, but you have to declare a user variable first to pass as that parameter. And then you have to select that user variable to get its value anyway.

SET @outparam = null;
CALL checkUser(?, ?, @outparam);
SELECT @outparam;

When returning result sets from a stored procedure, it's easiest to use a SELECT query.

Use:

UPDATE USERS
   SET online = 1
 WHERE EXISTS(SELECT NULL
                FROM USERS t
               WHERE t.email = IN_EMAIL
                 AND t.password = IN_PASSWORD
                 AND t.id = id)
   AND id = 'result_id'

Why do you have LIMIT 1 on your SELECT? Do you really expect an email and password to be in the db more than once?

You could try an if statement if you have an result which returns 1 i looked at yor code, it seems nothing returns a true so you have to refactor it, as above omg wrote thats realy true why do you have an limit 1 in your select query where only one emailadress can exisst? something like this

update users set if(result==1,online=1,online=0) where email=emailadress