http://mustafaturan.net/tr/mysql5_procedured_function_calls2_for_membership.html
## Coder Mustafa Turan ##
## http://mustafaturan.net/ ##
## Delimiters $$ ##
## members(m_ID, m_name, m_email, m_pass, m_acode, m_status, m_datetime) ##
DROP PROCEDURE NewMember$$
CREATE PROCEDURE NewMember(# yeni kullanıcı #
IN N_m_name VARCHAR(14),
IN N_m_email VARCHAR(55),
IN N_m_pass CHAR(32),
IN N_m_acode CHAR(6),
OUT N_m_err TINYINT
)
BEGIN
DECLARE name_exist TINYINT;
DECLARE email_exist TINYINT;
SET name_exist = 0;
SET email_exist = 0;
# get m_ID to name_exist #
SELECT count(m_ID) INTO name_exist FROM members WHERE m_name = N_m_name LIMIT 0,1;
# get m_ID to email_exist #
SELECT count(m_ID) INTO email_exist FROM members WHERE m_email = N_m_email LIMIT 0,1;
IF name_exist > 0 THEN
SET N_m_err = 1;
ELSE IF email_exist > 0 THEN
SET N_m_err = 2;
ELSE
INSERT INTO members (m_ID, m_name, m_email, m_pass, m_acode, m_status, m_datetime) VALUES (NULL, N_m_name, N_m_email, N_m_pass, N_m_acode, 0, NOW())
SET N_m_err = 0;
END IF;
END$$
DROP PROCEDURE ActivateMember$$
CREATE PROCEDURE ActivateMember(# kullanıcıyı aktive etmek #
IN N_m_email VARCHAR(55),
IN N_m_acode CHAR(6),
OUT N_m_err TINYINT
)
BEGIN
DECLARE already_active TINYINT;
DECLARE wrong_code TINYINT;
SET already_active = 0;
SET wrong_code = 0;
# get active status #
SELECT count(m_ID) INTO already_active FROM members WHERE m_email = N_m_email AND m_acode = N_m_acode AND m_status = 1 LIMIT 0,1;
# get code status #
SELECT count(m_ID) INTO wrong_code FROM members WHERE m_email = N_m_email AND m_acode = N_m_acode LIMIT 0,1;
IF already_active > 0 THEN
SET N_m_err = 3;
ELSE IF wrong_code <= 0 THEN
SET N_m_err = 4;
ELSE
UPDATE members SET m_status = 1 WHERE m_email = N_m_email AND m_acode = N_m_acode LIMIT 1;
END IF;
END$$
DROP PROCEDURE ChangePassword$$
CREATE PROCEDURE ChangePassword( # şifre değiştirme #
IN N_m_email VARCHAR(55),
IN N_m_pass CHAR(32),
IN O_m_pass CHAR(32),
OUT N_m_err TINYINT
)
BEGIN
DECLARE wrong_pass TINYINT;
SET wrong_pass = 0;
# get code status #
SELECT count(m_ID) INTO wrong_pass FROM members WHERE m_email = N_m_email AND m_pass = O_m_pass LIMIT 0,1;
IF wrong_pass 0 THEN
# DELETE * FROM files WHERE m_ID = O_m_ID; #
# DELETE * FROM members WHERE m_ID = O_m_ID LIMIT 1; #
UPDATE MEMBERS SET m_status = 0 WHERE m_ID = O_m_ID LIMIT 1;
SET N_m_err = 5;
ELSE
SET N_m_err = 6;
END$$
$N_m_err[0] = ‘Please check your email’;
$N_m_err[1] = ‘Already in use.’;
$N_m_err[2] = ‘E-mail in use.’;
$N_m_err[3] = ‘Already active user.’;
$N_m_err[4] = ‘Invalid activation code!’;
$N_m_err[5] = ‘User and its db files are deleted.’;
$N_m_err[6] = ‘User not found.’;
$N_m_err[7] = ‘Old password is wrong.’;