Registration PHP File for Conventional Web Development with MySQL
Conventional Web Development with PHP and MySQL – Part 6
Forward: In this part of the series, we look at the PHP script that responds to the registration of a member.
By: Chrysanthus Date Published: 2 Aug 2012
Introduction
The name of the script is, register.php. In this project, the script is in the home directory. The script connects to the database as the root: The script attempts to create the user account. If it does not succeed, it informs that user (student) that the user name already exists – precisely it tells him that he should change the user name and try again. If this step is successful, it goes on to give the new user privileges, in some tables of the Bookshop database. After that it selects the Bookshop database and inserts a row of the user’s credentials in the Members table.
We now look at the different code segments of the PHP script.
The First Code Segment
This is the first code segment of the script:
<?php
$username = $_POST['username'];
$password = $_POST['password'];
$firstname = $_POST['firstname'];
$middlename = $_POST['middlename'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$phone = $_POST['tel'];
$university = $_POST['university'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
$country = $_POST['country'];
$continue = "Yes";
It starts with the opening PHP tag. Next you have the $_POST variables for the user name, password and user credentials. Remember, $_POST is used to receive dataset sent with the POST method, by an HTML Form. After that you have the variable, $continue. This variable is initialized with "Yes". If the script fails to connect, it will set this variable to "No", and that will prevent its lower part from executing. If the script fails to create a user, it also sets this variable to "No", to prevent its lower part from executing. You will have more explanation on this as you read the code samples below.
This is the second code segment:
$link = mysql_connect('localhost', 'root', 'psswrd');
if (!$link)
{
echo "<body><h3>There is no connection with the server.</h3></body>";
$continue = "NO";
}
else
{
This segment attempts to connect to the database server (the database server is different from the web server). If it fails, it sends an error message to the user and sets the value of the $continue variable to NO. If it succeeds, it executes all the code in its else block. Apart from the a few statements, the rest of the script code is in this else block.
The Third Code Segment
This is the third code segment:
$createUserStr = "create user \"$username\"@\"localhost\" identified by \"$password\"";
if (!mysql_query($createUserStr, $link))
{
echo "<body><h3>Sorry! User Account could not be created! Change user name and try again.</h3></body>";
$continue = "NO";
}
else
{//if the user has been created, then this segment will likely be executed
MySQL 5 can have only one user with a particular name. However, it can have different users with the same password. This code segment attempts to create a user. If is fails it sends a feedback to the user, telling him to change his user name and try again. If it succeeds, its else block is executed. Its else block is the following segment explained:
This is the fourth code segment:
else
{//if the user has been created, then this segment will likely be executed
$grantStr1 = "grant INSERT, SELECT on Bookshop.Members to \"$username\"@\"localhost\"";
$grantStr2 = "grant SELECT on Bookshop.Products to \"$username\"@\"localhost\"";
$grantStr3 = "grant INSERT, SELECT on Bookshop.Sales to \"$username\"@\"localhost\"";
$grantStr4 = "grant INSERT on Bookshop.SaleItems to \"$username\"@\"localhost\"";
mysql_query($grantStr1, $link);
mysql_query($grantStr2, $link);
mysql_query($grantStr3, $link);
mysql_query($grantStr4, $link);
mysql_select_db('Bookshop', $link);
$credentialsStr = "insert into Members (username, email, phone, firstname, middlename, lastname, university, address, city, state, country) values (\"$username\", \"$email\", \"$phone\", \"$firstname\", \"$middlename\", \"$lastname\", \"$university\", \"$address\", \"$city\", \"$state\", \"$country\")";
mysql_query($credentialsStr, $link); #if the user was succesfully created then this correct syntax line should work.
}
}
This segment gives the new user certain privileges to certain tables in the Bookshop database. Then it adds a row of the new user’s credentials to the Members table in the Bookshop database. The segment has the ending, }, of its else block and the previous (link) else block.
The Fifth Code Segment
This is the fifth Code Segment:
#success feedback to user
if ($continue == "Yes")
{
echo "<body><h3>congratulations! Your user account has been created. You can now login and make good use of the site.</h3></body>";
}
mysql_close($link);
?>
Before the “mysql_close($link);” line in the code, you can add the following code segment that will send an email to the user (student) informing him of his registration:
if ($continue == "Yes")
{
$to = $email;
$subject = "Registration at University Bookshop";
$message = "Congratulations!\r\nYou are now a registered member at the University Bookshop.\r\n\r\nYour user name is: $username.\r\nYour password is: $password.\r\n\r\nThanks\r\nJohn Snith";
$additional_headers = "From: John Smith <info@bookshop.com>";
if (mail($to, $subject, $message, $additional_headers))
{
header('Location: sent.htm');
}
else
{
header('Location: error.htm');
}
}
This code segment would replace the previous one above.
It prepares an email and sends to the user indicating to the user, the acceptance of his registration and his user name and password. If the email is sent (accepted for delivery) then the “sent.htm” file will be sent to the user’s browser. This file will not display the user name and password. It will simply say that the registration is OK and that an email has been sent to him for further instructions. If the email cannot be sent, the file, "error.htm" will be sent to the user’s browser indicating that an error occurred.
Normally, the email will be sent. If the email is not sent, it means something is wrong with your coding or the recipient’s ($to) email address does not exist. At the limit, it would mean that the server is not in a developed country, and it is being used below its specifications.
Note: you can join the pieces of code in this tutorial to form the whole PHP file.
The TRIGGER Privilege
In a commercial project, the registered user will need to have the TRIGGER privilege for some tables. Triggers are used to automatically update certain tables. For example, if 3 of a particular item are bought, the number 3 has to be subtracted from the corresponding quantityAvailable cell in the products table. However, to keep things simple, I do not address triggers in this series. I did not even consider the quantityAvailable column of the products table when the values of the products tables were inserted. For the sake of pedagogy and to keep things simple, just allow the situation like that in this series (project).
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT