I wrote a MYSQL Procedure for my user registration page, I have already written the PHP part to send data from there to MySQL, and it works fine (tried with a dummy data and retrieval). But there is some sort of problem with my handler or transaction I guess
This is my Procedure:
BEGIN DECLARE unamec INT; DECLARE emailc INT; DECLARE m INT; DECLARE msg VARCHAR(100); DECLARE EXIT HANDLER FOR SQLEXCEPTION SET m=1; START TRANSACTION; SET autocommit=0; SELECT COUNT(*) INTO unamec FROM login WHERE uname=`@user`; IF unamec=0 THEN SELECT COUNT(*) INTO emailc FROM login WHERE email=@email; IF emailc=0 THEN INSERT INTO login (uname, hash, email, role) values(@user,MD5(@password),@email,'1'); SET msg='Successfully Registered'; ELSE SET msg='Email Already Exists'; END IF; ELSE SET msg='Username Already Exists'; END IF; COMMIT; END; END; IF m=1 THEN ROLLBACK; SET msg='ERROR'; END IF; SELECT msg as message; END
I always get the error
MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END; IF m=1 THEN ROLLBACK; SET msg='ERROR'; END IF; SELECT msg as message;' at line 23
Is there something obvious I am missing? I have been trying for some time to solve this with almost all the results to show the same error.
Any help will be highly appreciated.
Advertisement
Answer
Ok, I edited my own code and got the required result.
The changes I made was:
BEGIN DECLARE unamec INT; DECLARE emailc INT; DECLARE m INT; DECLARE msg VARCHAR(100); DECLARE EXIT HANDLER FOR SQLEXCEPTION START TRANSACTION; SET autocommit=0; SELECT COUNT(*) INTO unamec FROM login WHERE uname=`@user`; IF unamec=0 THEN SELECT COUNT(*) INTO emailc FROM login WHERE email=@email; IF emailc=0 THEN INSERT INTO login (uname, hash, email, role) values(@user,MD5(@password),@email,'1'); SET msg='Successfully Registered'; ELSE SET msg='Email Already Exists'; END IF; ELSE SET msg='Username Already Exists'; END IF; COMMIT; BEGIN ROLLBACK; SET msg='ERROR'; END; SELECT msg as message; END
If anyone get any other answer, still appreciate it.
Anyways, the above code is now working fine.