Tutorials, PHP & MySQL

In this tutorial I will explain how to create a user registration script along with validation.

When ever you collect data from users you always have to validate all the data NEVER trust anything from a user!

To store the member info into a database table you need to create the table first here is the structure of the table you can create this in phpmyadmin or script it yourself<

// create table
$sql = 'CREATE TABLE members (
memberID INT (4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
email VARCHAR(255) NOT NULL,
)';

if (@mysql_query($sql)) {
echo 'members table successfully created!';
} else {
  exit('Error creating members: ' . mysql_error());
}

To start declare your using php then first thing were going to do is create a function that will count and print out all errors that arise from the form validation.

First you use declare your using a function with the name function then supply the name of the function in this case were calling our function errors were also passing a variable to our function called $error this will contain an array of errors if there has been any.

Next we check to see if there are any errors with an if statement saying if error is not empty then execute the code. then we make a new variable called $i with a value of 0 then we start a while loop which goes like this while $i is less then the number of errors keep looping through them till there are no more errors.

Inside the loop We've setup the actual error that will be printed $error[$i] this is an array the $i represents the error number which will increase on each loop till there are no errors We've also put the error inside a paragraph and a span class so we can style the error with css this is optional then we set $i to increment using ++ (increment) which causes the loop to loop through till then value of $i is greater then the number of errors.

This function at present will not do anything its just been setup and will be called later down the script where it will start checking for errors in this example I've put the function in the same script to make it easy to see but in a functioning environment it would be in a separate file along with all your other function.

//Connect to database

// db properties
$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'database name';

// make a connection to mysql here
@$conn = mysql_connect ($dbhost, $dbuser, $dbpass);
@$conn = mysql_select_db ($dbname);

if(!$conn){
die( "Sorry! There seems to be a problem connecting to our database. Please give us a few minutes to remedy the problem. Thank you.");
}

// function for generating errors
function errors($error){
if (!empty($error))
{
$i = 0;
while ($i < count($error)){
echo "<p><span class="warning">".$error[$i]."</span></p>n";
$i ++;}
}// close if empty errors
} // close function

Now we check to see if the form has been submitted using if isset $_POST['submit']

//This code runs if the form has been submitted
if (isset($_POST['submit']))
{

Now we know the form has been submitted we need to check to see if the form has actually got any data or it has been filled in right.

First we trim any white space from the data using the trim function then post the data to make it available to the script. Now we have the data for the first feild we need to check the length of the data using an if statement.

We do this with the function strlen (string length) in the first example we saying if the string is less then 3 characters then an error is created. The error is put in an error array for our errors function to process later in the script. We then do the same for the maximum characters allowed in this case 20 and if there were more then 20 characters an error would be created. This could have been done in a single if statement but for clarity we've done it another way.

The process is repeated for all the fields.

// check feilds are not empty
$firstName = trim($_POST['firstName']);
if (strlen($firstName) < 3) {
$error[] = 'First Name Must be between 3 and 20 charactors.';
}

if (strlen($firstName) > 20) {
$error[] = 'First Name Must be between 3 and 20 charactors.';
}

// check fields are not empty
$lastName = trim($_POST['lastName']);
if (strlen($lastName) < 3) {
$error[] = 'Last Name Must be between 3 and 20 characters.';
}

if (strlen($lastName) > 20) {
$error[] = 'Last Name Must be between 3 and 20 characters.';
}

// check fields are not empty
$username = trim($_POST['username']);

if (strlen($username) < 3) {
$error[] = 'username Must be between 3 and 20 characters.';
}

if (strlen($username) > 20) {
$error[] = 'username Must be between 3 and 20 characters.';
}

// checks if the username is in use

if (!get_magic_quotes_gpc()) {
$_POST[] = addslashes($_POST['username']);
}

We want all username to be unique so when a user registers to the site we need to check to see if the requested username is taken. We do this by first posting the username to a variable called $usercheck then an sql statement is made which will select the username from the users table where username is $usercheck (thre username from their form) or die meaning the query failed and will show an error why it failed using mysql_error()

Then the data is passed to another variable and counted using mysql_num_rows then an if statement is performed which says if check is not equal to 0 then the username is already taken and an error is created.

$usercheck = $_POST['username'];
$check = mysql_query("SELECT username FROM users WHERE username = '$usercheck'")or die(mysql_error());

$check2 = mysql_num_rows($check);
//if the name exists it gives an error
if ($check2 != 0) {
$error[] = 'Sorry, the username <b>'.$_POST['username'].'</b> is already in use.';
}

// check fields are not empty
$password = trim($_POST['password']);
if (strlen($password) < 5) {
$error[] = 'password Must be between 5 and 20 characters.';
}

if (strlen($password) > 20) {
$error[] = 'password Must be between 5 and 20 characters.';
}

// check fields are not empty
$password2 = trim($_POST['password2']);

if (strlen($password2) < 5) {
$error[] = 'confirm password Must be between 5 and 20 characters.';
}

if (strlen($password2) > 20) {
$error[] = 'confirm password Must be between 5 and 20 characters.';
}

We also want to make sure the user knows the password they entered so to check this we make sure they type it in twice, so now we check to make sure both passwords match using an if statement saying if password is not equal to password2 then an error is created.

// this makes sure both passwords entered match
if ($_POST['password'] != $_POST['password2']) {
$error[] = 'Your passwords did not match.';
}

We then make sure the email is of a valid format using a regular expression which makes sure the email has to have some text then an @ symbol then some more text then a period then more text such as email@yourdomain.com

// check for valid email address
$email = $_POST['email'];
$pattern = '/^[^@]+@[^srn'";,@%]+$/';
if (!preg_match($pattern, trim($email))) {
$error[] = 'Please enter a valid email address';
}

// checks if the email is in use
if (!get_magic_quotes_gpc()) {
$_POST[] = addslashes($_POST['email']);
}

We then check to see if the email is in use as we don't want more then 1 user with the same email address so you can reset passwords in there email. we check the email the same way we checked the username so we add the email address to a variable then run a statement to get the email addresses from email where $emailcheck and if there is a match then a error is created as the email address is in use.

$emailcheck = $_POST['email'];
$emailcheck1 = mysql_query("SELECT email FROM members WHERE email = '$emailcheck'")or die(mysql_error());
$emailcheck2 = mysql_num_rows($emailcheck1);

//if the name exists it gives an error
if ($emailcheck2 != 0) {
$error[] = 'Sorry, the email address <b>'.$_POST['email'].'</b> is already in use, Please choose another email address.';
}

Now all validation has been done we check to see if there has been an error. If there has been an error the script scripts the following code and prints all errors.

// if validation is okay then carry on
if (!$error ) {
// get data from form

We now get all the data from the form and add them to variables

$firstName = $_POST['firstName'];
$lastName = $_POST['lastName'];
$username = $_POST['username'];
$password = $_POST['password'];
$email = $_POST['email'];

We add some formatting to the firstname and lastname. We want a capital letter for the first name then lower case for all other letters we do this by adding the username to a function ucfirst which means uppercase first letters then strtolower which means string to lowercase then the variable and add it all to the variable firstname then do the same for lastname. 

// convert name to capital first letter then lowercase
$firstName = ucfirst(strtolower($firstName));
$lastName = ucfirst(strtolower($lastName));

We want to escape data if magic quotes if disabled on the server so we use an if statement which says if not magic quotes gpc then add slashes to all escapable data.

if(!get_magic_quotes_gpc())
{
$firstName = addslashes($firstName);
$lastName = addslashes($lastName);
$username = addslashes($username);
$password = addslashes($password);
$email = addslashes($email);
}

Another security check is mysql_real_escape_string which will escape all data to prevent a mysql injection attack 

// escape any harmful code and prevent sql injection
$firstName = mysql_real_escape_string($firstName);
$lastName = mysql_real_escape_string($lastName);
$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);
$email = mysql_real_escape_string($email);

We also want to remove all code that may exist in the data this is a little over the top as the previous code would have escaped it but we don't want any escaped code showing so we use strip_tags to remove all code from the data. 

// removal all code from data
$firstName = strip_tags($firstName);
$lastName = strip_tags($lastName);
$username = strip_tags($username);
$password = strip_tags($password);
$email = strip_tags($email);

Now we format the data to capitalize first letters then have the rest lowercase. 

//convert to capital first letter and the rest lowercase
$firstName = ucwords(strtolower($firstName));
$lastName = ucwords(strtolower($lastName));
$username = ucwords(strtolower($username));
$email = strtolower($email);

Now all data is safe and ready to be put in the mysql database. First we create a variable with a sql statement telling mysql to insert into user table and the column names then the values for password we use md5('$password') which will encrypt the password to a 32 random characters and numbers. then a result varible is created which will query the database and either insert the data or fail and print a message. 

// now we insert it into the database
$insert1 = "INSERT INTO users (firstName, lastName, username, password, email) VALUES ('$firstName', '$lastName', '$username', md5('$password'), '$email')";
$result1 = mysql_query($insert1) or die('Error : ' . mysql_error());

Now the user is registered we want to send them an email with their login details in case they forget them.

First we create a variable that has their email address in then a variable for the subject of the email and another variable which will contain the email content. We also create variables for additional headers to make the email address from and reply to fields available.

change the site@emaildomain.com address to your email address you want to use for this site.

Now the email is ready to be sent we use the function mail() which includes all the variables to be sent in the email.

//send email
$to = "$email";
$subject = "Registration Information";
$body = "Hi $firstName $lastName, nn Welcome to sitename nn Here is your account information please keep this as you may need this at a later stage. nnYour username is $username nn your password is $password nn Regards Site Admin nn";
$additionalheaders = "From: <site@emaildomain.com>rn";
$additionalheaders .= "Replt-To: site@emaildomain.com";
if(mail($to, $subject, $body, $additionalheaders)){}

Were also going to send an email to the site admin to let them know a new user has been registered which is the same as what we've just created but we hand code the email address the email will be sent to.

//send email to admin
$to = "youremail@domain.com";
$subject = "New member at sitetitle";
$body = "Hello adminnn There is a new member just registered to sitetitle here are there details.nn Name: $firstName $lastName nn Username: $username nn Email: $emailnn Regards Site Admin nn";
$additionalheaders = "From: <youremail@domain.com>rn";
$additionalheaders .= "Replt-To: youremail@domain.com";
if(mail($to, $subject, $body, $additionalheaders)){}

We then show the user the results

echo "<h2>Member Registration</h2>";
echo "<p>Thank you, <b>$username</b> you have registered you may now Login.</p>";

We then close the brackets for validation and if form has been sent.

} // end validation
} // end if posted

We then call the errors function to display any errors that may have been created. When calling the function we pass the error array to count all errors.

//display any errors
errors($error);

Now we show the form for the user to fill in the first part is tells html to expect a form using form then the action is set to.

<?php $_SERVER['PHP_SELF'];?>

Which send the page to the same page it's already on using the post method so the data is send behind the scenes.

Then we create a label for each field along with an input for the user to type a single line in.

For all input fields we have:

<?php if(isset($error)) {echo "value='field name'";} ?>

Which tells php that if an error is set to keep the inputted data in the value field. if we had a textarea instead of using a value we just use the variable data like.

<textarea><?php echo $name;?></textarea>
<form action="<?php $_SERVER['PHP_SELF'];?>" method="post">
<legend>Member Registration</legend>
<p><label>First Name:</label><input name="firstName" type="text" maxlength="20" <?php if(isset($error)) {echo "value='$firstName'";} ?> /></p>
<p><label>Last Name:</label><input name="lastName" type="text" maxlength="20" <?php if(isset($error)) {echo "value='$lastName'";} ?> /></p>
<p><label>Username:</label><input name="username" type="text" maxlength="20" <?php if(isset($error)) {echo "value='$username'";} ?> /></p>
<p><label>Password:</label><input name="password" type="password" maxlength="20" /></p>
<p><label>Confirm Password:</label><input name="password2" type="password" maxlength="20" /></p>
<p><label>Email:</label><input name="email" type="text" maxlength="255" <?php if(isset($error)) {echo "value='$email'";} ?> /></p>
<p><input type="submit" name="submit" value="Register"></p>
</form>

<p>Here's the full script: </p>

<?php
// function for generating errors
function errors($error){
if (!empty($error))
{
  $i = 0;
  while ($i < count($error)){
    echo "<p><span class="warning">".$error[$i]."</span></p>n";
  $i ++;}
}// close if empty errors
} // close function

//This code runs if the form has been submitted
if (isset($_POST['submit']))
{

// check feilds are not empty
$firstName = trim($_POST['firstName']);
if (strlen($firstName) < 3) {
  $error[] = 'First Name Must be between 3 and 20 charactors.';
}

if (strlen($firstName) > 20) {
  $error[] = 'First Name Must be between 3 and 20 charactors.';
}

// check fields are not empty
$lastName = trim($_POST['lastName']);

if (strlen($lastName) < 3) {
  $error[] = 'Last Name Must be between 3 and 20 characters.';
}

if (strlen($lastName) > 20) {
  $error[] = 'Last Name Must be between 3 and 20 characters.';
}

// check fields are not empty
$username = trim($_POST['username']);

if (strlen($username) < 3) {
  $error[] = 'username Must be between 3 and 20 characters.';
}

if (strlen($username) > 20) {
  $error[] = 'username Must be between 3 and 20 characters.';
}

// checks if the username is in use
if (!get_magic_quotes_gpc()) {
  $_POST[] = addslashes($_POST['username']);
}

$usercheck = $_POST['username'];
$check = mysql_query("SELECT username FROM users WHERE username = '$usercheck'")or die(mysql_error());
$check2 = mysql_num_rows($check);

//if the name exists it gives an error
if ($check2 != 0) {
  $error[] = 'Sorry, the username <b>'.$_POST['username'].'</b> is already in use.';
}

// check fields are not empty
$password = trim($_POST['password']);
if (strlen($password) < 5) {
  $error[] = 'password Must be between 5 and 20 characters.';
}

if (strlen($password) > 20) {
  $error[] = 'password Must be between 5 and 20 characters.';
}

// check fields are not empty
$password2 = trim($_POST['password2']);
if (strlen($password2) < 5) {
  $error[] = 'confirm password Must be between 5 and 20 characters.';
}

if (strlen($password2) > 20) {
  $error[] = 'confirm password Must be between 5 and 20 characters.';
}

// this makes sure both passwords entered match
if ($_POST['password'] != $_POST['password2']) {
  $error[] = 'Your passwords did not match.';
}

// check for valid email address
$email = $_POST['email'];
$pattern = '/^[^@]+@[^srn'";,@%]+$/';
if (!preg_match($pattern, trim($email))) {
  $error[] = 'Please enter a valid email address';
}

// checks if the email is in use
if (!get_magic_quotes_gpc()) {
  $_POST[] = addslashes($_POST['email']);
}

$emailcheck = $_POST['email'];
$emailcheck1 = mysql_query("SELECT email FROM members WHERE email = '$emailcheck'") or die(mysql_error());
$emailcheck2 = mysql_num_rows($emailcheck1);

//if the name exists it gives an error
if ($emailcheck2 != 0) {
 $error[] = 'Sorry, the email address <b>'.$_POST['email'].'</b> is already in use, Please choose another email address.';
}

// if validation is okay then carry on
if (!$error ) {

// get data from form
//We now get all the data from the form and add them to variables

$firstName = $_POST['firstName'];
$lastName = $_POST['lastName'];
$username = $_POST['username'];
$password = $_POST['password'];
$email = $_POST['email'];

// convert name to capital first letter then lowercase
$firstName = ucfirst(strtolower($firstName));
$lastName = ucfirst(strtolower($lastName));

if(!get_magic_quotes_gpc())
{
$firstName = addslashes($firstName);
$lastName = addslashes($lastName);
$username = addslashes($username);
$password = addslashes($password);
$email = addslashes($email);
}

// escape any harmful code and prevent sql injection
$firstName = mysql_real_escape_string($firstName);
$lastName = mysql_real_escape_string($lastName);
$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);
$email = mysql_real_escape_string($email);

// removal all code from data
$firstName = strip_tags($firstName);
$lastName = strip_tags($lastName);
$username = strip_tags($username);
$password = strip_tags($password);
$email = strip_tags($email);

//convert to capital first letter and the rest lowercase
$firstName = ucwords(strtolower($firstName));
$lastName = ucwords(strtolower($lastName));
$username = ucwords(strtolower($username));

$email = strtolower($email);

// now we insert it into the database
$insert1 = "INSERT INTO users (firstName, lastName, username, password, email) VALUES ('$firstName', '$lastName', '$username', md5('$password'), '$email')";
$result1 = mysql_query($insert1) or die('Error : ' . mysql_error());

//send email
$to = "$email";
$subject = "Registration Information";
$body = "Hi $firstName $lastName, nn Welcome to sitename nn Here is your account information please keep this as you may need this at a later stage. nnYour username is $username nn your password is $passwordnn Regards Site Admin nn";
$additionalheaders = "From: <site@emaildomain.com>rn";
$additionalheaders .= "Reply-To: site@emaildomain.com";
if(mail($to, $subject, $body, $additionalheaders)){}

//send email to admin
$to = "youremail@domain.com";
$subject = "New member at sitetitle";
$body = "Hello adminnn There is a new member just registered to sitetitle here are there details.nn Name: $firstName $lastName nn Username":

$username nn Email: $emailnn Regards Site Admin nn";
$additionalheaders = "From: <youremail@domain.com>rn";
$additionalheaders .= "Reply-To: youremail@domain.com";

if(mail($to, $subject, $body, $additionalheaders)){}

echo "<h2>Member Registration</h2>";
echo "<p>Thank you, <b>$username</b> you have registered you may now Login.</p>";


} // end validation
} // end if posted

//display any errors
errors($error);
?>

<form action="<?php $_SERVER['PHP_SELF'];?>" method="post">
<legend>Member Registration</legend>
<p><label>First Name:</label><input name="firstName" type="text" maxlength="20" <?php if(isset($error)) {echo "value='$firstName'";} ?> /></p>
<p><label>Last Name:</label><input name="lastName" type="text" maxlength="20" <?php if(isset($error)) {echo "value='$lastName'";} ?> /></p>
<p><label>Username:</label><input name="username" type="text" maxlength="20" <?php if(isset($error)) {echo "value='$username'";} ?> /></p>
<p><label>Password:</label><input name="password" type="password" maxlength="20" /></p>
<p><label>Confirm Password:</label><input name="password2" type="password" maxlength="20" /></p>
<p><label>Email:</label><input name="email" type="text" maxlength="255" <?php if(isset($error)) {echo "value='$email'";} ?> /></p>
<p><input type="submit" name="submit" value="Register"></p>
</form>

That's it you now have a fully working registration script with validation enjoy!