Register users and login through store procedure mysql

1. Signup a user through store procedure.

2. "mysecret" here you can use your key. password will be encrypted using AES

3. You can call store procedure like below

CALL`AddCustomerDetails`('David Warnor','david@gmail.com','12','male','2377.34','34343.343');
CREATE PROCEDURE `AddCustomerDetails`(
 IN `vCustomerName` VARCHAR(50),
 IN `vEmail` VARCHAR(50),
 IN `vPassword` VARCHAR(50),
 IN `eGender` VARCHAR(10),
 IN `dLatitude` FLOAT,
 IN `dLongitude` FLOAT
)
BEGIN
 DECLARE ResultCount INT default 0;
 DECLARE skey VARCHAR(15) default 'mysecret';
 DECLARE eStatus INT default 1;
 DECLARE iCustomerId INT;

 SET @ResultCount = (SELECT COUNT(iCustomerId) FROM table_name WHERE vEmail = vEmail);
 IF (@ResultCount > 0) THEN
 SET @iCustomerId = 0;
 SELECT @iCustomerId;
 ELSE
 INSERT INTO table_name
 (vCustomerName
 ,vEmail
 ,vPassword
 ,eGender
 ,dLatitude
 ,dLongitude
 ,eStatus
 ,dCreatedDate
 )
 VALUES
 (vCustomerName
 ,vEmail
 ,AES_ENCRYPT(vPassword,skey)
 ,eGender
 ,dLatitude
 ,dLongitude
 ,eStatus
 ,NOW()
 );
 SET @iCustomerId = LAST_INSERT_ID();
 SELECT @iCustomerId as CustomerId;
 END IF;
END

1. Now above will add a entry to your mysql table let create a store procedure which will validate and check credentials.

ALL `CheckLogin`('david@gmail.com', '123123', '0');
CREATE PROCEDURE `CheckLogin`(
 IN `Email` VARCHAR(255),
 IN `Psw` VARCHAR(255),
 IN `UserType` INT(11)
)
BEGIN
 DECLARE ResultCount INT;
 DECLARE ResultMessage varchar(15);
 DECLARE skey VARCHAR(15) default 'secrets';
 IF (UserType = 0) THEN
 SET @ResultCount = (SELECT COUNT(C.iCustomerId) FROM customerdetails C 
 WHERE C.vEmail = Email AND C.vPassword = AES_ENCRYPT(Psw,skey) AND C.eStatus = 1);
  IF (@ResultCount > 0) THEN #For Customer
  SELECT * from customerdetails C WHERE C.vEmail = Email AND C.vPassword = AES_ENCRYPT(Psw,skey) 
  AND C.eStatus = 1;
  ELSE
 SET @ResultMessage='User not found.';
  SELECT @ResultMessage;
  END IF;
 ELSE
 SET @ResultCount = (SELECT COUNT(V.iVendorId) FROM vendor V WHERE V.vEmail = Email 
 AND V.vPassword = AES_ENCRYPT(Psw,skey) AND V.eStatus = 1);
 IF (@ResultCount > 0) THEN
  SELECT * from vendor V WHERE V.vEmail = Email AND V.vPassword = AES_ENCRYPT(Psw,skey) 
  AND V.eStatus = 1;
 ELSE
 SET @ResultMessage='vendor not found.';
  SELECT @ResultMessage;
 END IF;
 END IF;
END