Jump to content

User Registration with PHP, PDO and MySQL.


Recommended Posts

Today we'll make a simple user registration script. We'll cover the basics of PDO and Form validation. We'll also look at current best practices for hashing passwords. It's assumed that you have basic/intermediate knowledge of PHP, as well as that you have a web server/web host with at least PHP 5.3.7 installed and know how to create a MySQL database (or execute SQL code to a db server).

If you need a web server and PHP (locally), look for WampServer if you're on Windows, MAMP if you use Mac, LAMP for Linux etc. These packages comes with every tool you need to follow through the tutorial, but there are plenty tutorials on how to install and setup these things.

If you don't know PDO; read a tutorial: http://makewebgames.io/showthread.php/44586-How-to-PDO-in-One-Two-Three!?p=301700#post301700

Let's start by the SQL and execute the following in your favorite database management tool (I use MySQL Workbench).


CREATE SCHEMA `pandora` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;

CREATE TABLE `pandora`.`users` (
 `username` VARCHAR(20) NULL,
 `password` VARCHAR(60) NULL,
 `email` VARCHAR(100) NULL,
 `created` TIMESTAMP NULL,
 `confirmed` TINYINT(1) NULL DEFAULT 0,
 PRIMARY KEY (`id`),
 INDEX `username` (`username` ASC));


If you already have a database, you could remove the first line and on third line replace "pandora" your database name (just make sure the collation is utf8) and then execute that SQL instead, in anyhow you've accomplished the task when you have a database and a table called users. The table is fairly simple, we have two columns for user identification username and password, an email address if they wish to reset their password, the created is a TIMESTAMP which contains the TIMESTAMP when the User was created. Finally we have a boolean (a TINYINT(1) is often referred as a boolean) that checks if the user is confirmed or not (meaning that the email has been verified).

Now when we have the database set up, let's get started with the coding. Firstly we need to make a database connection. We'll use PDO, PHP Data Objects that is one of the extensions that replaces the now deprecated (mysql_) API. (mysql_connect, mysql_query, you know?)

Create a file on your web server named config.php.



$config = array(
   'db' => array(
       'dsn' => 'mysql:host=localhost;dbname=pandora;charset=utf8',
       'username' => 'root',
       'password' => 'password',
       'options' => array(),

$db = new PDO(



Line 3-10 is our configuration array, here we would add an configuration items that later can be added throughout


As you can see I use a multidimensional array which is cleaner and easier to work with, every database configuration I would place in the ['db'], and then to be accessed like:


This part is not really essential of the tutorial, you could have 4 variables instead, however a lean way of storing configuring statically.

Line 5 is the fairly interesting line here. This is called the dns. We tell PDO that we soon create that we want to use the MySQL driver, (PDO supports many drivers!), we want to use the database "pandora", the host (localhost, locally) and finally charset utf8. Almost like the ordinary mysql_connect, right?

Line 6-7 should be obvious, your db user and db password. Leave line 8 for now.

Line 13-18 is where we create the $db (PDO object). We pass the config parameters, DNS, username, password and options. And voila, we have our db object work with. Unlike mysql_ API, PDO uses objects.

PDO has different error modes, on the final line we specifically say we want PDO to throw Exceptions if something fuzzy going on, like PDO cannot find the database etc. Now when we have the database connection we can continue by creating the register.php file.



require 'config.php';

function validate_registration($db) {


if (isset($_POST['register'])) {



<!DOCTYPE html>
   <form method="post" action="register.php">
       <input type="text" name="username" value="">
       <input type="password" name="password" value="">
       <input type="email" name="email" value="">
       <input type="submit" name="register" value="Register">


Here we have the very basics we need to get started. Firstly, we include the config.php file we just created earlier. And then we create a function, here we'll validate the form data when the form is processed. Line 11 basically looks like "Hey, have the Register button been pressed (aka form submitted) - if so... let's process the form here...".

So, imagine the form has been submitted. What do we need to check to make sure the submitted data is valid? We need to know:


  • That a username, password and email has been entered.
  • That the username is not already taken.
  • That the email is valid.


We also want:

  • That the username/password has a minimum length.
  • That the username follows a pattern (say only alphanumerical)


Let's add the following code (after function validate_registration($db) {)


    $errors = array();

   // Required fields
   $fields = array('username', 'password', 'email');

   foreach ($fields as $field) {
       if (empty($_POST[$field])) {
           $errors[] = 'Field ' . $field . ' is required';

   //If we already have errors, let's interrupt the validation process
   if ( $errors ) {
       return $errors;

   if (strlen($_POST['username']) < 3) {
       $errors[] = 'Username is too short';
   } else if (preg_match('/[^a-z0-9_-]+/i', $_POST['username'])) {
       $errors[] = 'The username must only contain alphanumerical, dashes and underscores';

   if (strlen($_POST['password']) < 6) {
       $errors[] = 'The password is too short';

   //Make sure the email is valid

   if (filter_var($_POST['email'], FILTER_VALIDATE_EMAIL) === false) {
       $errors[] = 'The email is not valid';

   if ( $errors ) {
       return $errors;

   $_POST['email'] = filter_var($_POST['email'], FILTER_SANITIZE_EMAIL);

   //Make sure the username is not already taken

   $stmt = $db->prepare("SELECT `id` FROM `users` WHERE `username` = ?");

   if ($stmt->fetch()) {
       $errors[] = 'The username is already taken';

   return $errors;


"HOLY ****. That was a lot of code." - Hey, hey, just relax now. Take a big breath and let's go it through.

Firstly we create a

$errors = array();

this array contains any error that may occur, we'll also use this array to check during the validation process wherever to continue or not. Like it makes no sense to make a database connection and check if the username exists or not, if the username in say is already invalid.

Line 3-10 makes sure that the fields that I put in the array (username, password, email) are required and if these aren't present, we tell so.

Line 12-15 makes the first check. If the errors array contains some values, the validation has already failed. So let's interrupt the process by returning the errors array.

Line 19 checks that the username matches a pattern, the username must only contain alphanumerical, underscores and dashes - if not, well, we have an error here sir!


$stmt = $db->prepare("SELECT `id` FROM `users` WHERE `username` = ?");

if ($stmt->fetch()) {
   $errors[] = 'The username is already taken';


Here(s) some more PDO magic. We use a prepared statements (which prevents SQL injection). So first we tell, hey PDO prepare this SQL for us. This creates a PDOStatement that is stored in the $stmt variable. On line two we say, hey PDOStatement execute this and use this data for the first parameter. (Notice the array?, if we want more parameters, we simply add the data in the array!)

On the fourth line we try to the fetch the executed statement, if it succeeds a user is found and the test has failed.

Finally in the validation we return the $errors array(). If it's empty, the validation has succeeded!


Soon, we're going to use a function called password_hash that isn't available as of PHP 5.5. If you have this version or above, you're fine and if not you'll have to follow along and download a backward compatibility library named password_compact which allows you use these functions as of PHP 5.3.7 (hence the requirement for this tutorial). So if you have PHP 5.5 > you can skip this paragraph and if not, follow along.

Let's go to https://github.com/ircmaxell/password_compat, click on "lib" folder and then the password.php file. You'll see a button called "Raw" click on it and the file will be opened as raw. Right click and save the file as password.php on your web root. Now open your config.php file and put


require 'password'.php';

below the db object creation.


Now when we have the validation figured out, let's continue with the registration. The logic is as, if the registration validation passes, let's insert the User in to the database, and if not store some error messages and refresh the page.

Let's add session_start(); in the top of our config.php file.


if (isset($_POST['register'])) {





Let's add:


    $errors = validate_registration($db);

   //If we have errors, let's store them in a session and refresh the page...
   if ($errors) {
       $_SESSION['register_errors'] = $errors;
       header("Location: register.php");

   //Hash the password with bcrypt
   $password = password_hash($_POST['password'], PASSWORD_BCRYPT);

   $stmt = $db->prepare("INSERT INTO `users`(`username`, `password`, `email`, `created`) VALUES(?, ?, ?, NOW())");
   $stmt->execute(array($_POST['username'], $password, $_POST['email']));

   //We can safely assume the user has been registered, if not an exception would be thrown and handled elsewhere...

   header("Location: registered.php");


First we call the validation function and stores the result in the $errors, either an empty array or some horrifying errors. If there are errors, we'll store them in a session, and then we refresh the page (Remember; we just did a POST request, so we're not suppose to show any data in this state, but instead redirect back to register.php, so the request is GET instead, this pattern is known as GET/POST/GET).

If we have no errors, we has the password with the password_hash function. We'll use the PASSWORD_BCRYPT as a second parameter. bcrypt is a best practice when storing passwords in data storages, this is also the reason why the password in the database table is varchar 60, a hashed passwords contains 60 characters long.

Line 13-14 we use yet again another prepared statements, we have 3 fields that requires three parameters (these parameters are known as unnamed parameters). We use prepared statement because it prevents SQL injection. If you remember what I told about the execute array, you now see it in action. We have 3 parameters (three ? in the SQL query) hence we have three data variables to pass to the execute. Because we earlier in config.php set ERRMODE to Exceptions we can safely assume that the registration will pass and if not the error should be handled somewhere else. ( A global exception handler). So let's redirect the registered.php.

Now again, we redirected the User back to register.php if the registration failed. So let's see what we can do about that. Below the ending } of isset($_POST['reg...] add the following:


if (isset($_SESSION['register_errors'])) {
   $errors = $_SESSION['register_errors'];


And in our HTML, below the <body> let's add


    <?php if (isset($errors)): ?>
       <?php foreach ($errors as $error): ?>
       <li><?php echo $error; ?></li>
       <?php endforeach; ?>
   <?php endif; ?>


Now we're ready to try out our result. Here is mine:


What now? Well, once you have a User registered the next step would be to be able to login and have member protected pages. I won't be able to cover it all here, but If you have absolutely no idea where to start, here's some ideas (and maybe I'll write another tutorial about this, if you like too!)


  • Use Sessions to store the id of a user, when logged in. ($_SESSION['user_id'] = $row['id'])
  • Use password_verify (http://se2.php.net/manual/en/function.password-verify.php) to check if the hashed password in the database matches the password a user entered when logging in.
  • Use prepared statements
  • When logging in, you will check if the username exists and retrieve the User (with his password) and then you'll do the above 2 steps.
  • Validation as through as registration is not needed when logging a User in, you only need to check if the user exists and then if the username and password is correct you'll log in the user, no need for checking length of username etc.


What can you do next on this registration page?


  • Add some styling for the forms
  • Add more fields for the registration itself, like firstname, lastname etc.
  • Put the HTML code somewhere else (separate PHP and HTML!)



That's it! Comments, questions, feedback - please!

Edited by john.
  • Like 1
Link to comment
Share on other sites

Nice one. It I see a few things:

for your email check you are using strict comparison operators which is case sensitive and believe the actual outputs are TRUE:FALSE so you don't need to be strict.

Also for the same little snippets you know that they have filter_input since you are filtering an actual input:


And yes they do pretty much the same thing in reality but still :p

Link to comment
Share on other sites

awesome! Can't wait to try

Thank you, let me know if you have any issues following along.


Nice one. It I see a few things:

for your email check you are using strict comparison operators which is case sensitive and believe the actual outputs are TRUE:FALSE so you don't need to be strict.

Also for the same little snippets you know that they have filter_input since you are filtering an actual input:


And yes they do pretty much the same thing in reality but still :p

Thanks for the input. I didn't know about filter_input, until now. I'll have a look at it later.


Comprehensive and detailed. I like that you explain and it's easy to understand. Good job.

Thank you! :)

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...