Skip to content
Advertisement

MySQL said 1064 You have an error in your SQL syntax

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.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement