HW 4 SP

1 Write SP:

Purpose: Practice creation of stored procedures

Instruction:

Use Users table to create stored procedures for the following information queries:

1. Write a stored procedure to find the earliest registration date by a OUT parameter. (use min())2. Write a stored procedure to find the registration date by a IN user_id parameter and a OUT parameter.

3. Write a stored procedure to add a new user. Provide IN parameters of user_id, first_name, last_name, email, pass and registration_date. 4. Write a stored procedure to delete a user by providing an IN user_id. 5. Write a stored procedure to retrieve all the records from users table? 6. Write a stored procedure to update the email of a user by an IN user_id and IN email parameters. Thing to submit: 1. All the stored procedure statements. 2. A screen shot for the successful execution of each stored procedure. 3. Submit a Word file that contains all the results and screen shot.

2 Create the SP to change the register.php code as :

Replacing

$q = "INSERT INTO users (first_name, last_name, email, pass, registration_date) VALUES ('$fn', '$ln', '$e', SHA1('$p'), NOW() )";

with

$q="CALL register_proc('$fn', '$ln', '$e', '$p')";

Submit the SP in the Word file