Using the SELECT result set to run an UPDATE query with MySQL stored procedures

Question

I am trying to understand MySQL stored procedures, I want to check if the login credentials are valid, and if so, update the status of online users:

-- DROP PROCEDURE IF EXISTS checkUser; DELIMITER // CREATE PROCEDURE checkUser(IN in_email VARCHAR(80), IN in_password VARCHAR(50)) BEGIN SELECT id, name FROM 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 result set number of lines == 1 or id IS NOT NULL?

Answer 1

 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. MySQL may not like UPDATE against the table in which the cursor is currently open.

PS: You should review how you store passwords .

---

Comment on RETURN vs. OUT vs. result set:

RETURN used only in stored functions, not in stored procedures. Stored functions are used when you want to call a procedure in another SQL statement.

 SELECT LCASE( checkUserFunc(?, ?) ); 

You can use the OUT parameter, but you must first declare a custom variable in order to pass it as that parameter. And then you have to select this custom variable to get its value anyway.

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

When returning result sets from a stored procedure, the easiest way is to use a SELECT query.

Answer 2

Using:

 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 your email address and password to be in db more than once?

Answer 3

You can try the if statement, if you have a result that returns 1, I looked at the yor code, it seems that nothing is returning true, so you need to reorganize it as above, omg wrote that it is really true why you have limit 1 in the selected request, where can there be only one email address? something like that

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