User Signup and Signin with PHP and MySQL


Authentication system is the most important part for the developers to implement in a web application. It is necessary if you want to provide limited access of your application for anonymous user. If you want to create a web application with secure admin area or a user signup and signin with php and mysql this post will help you.

Here are the steps to create a simple application that allow user to signup and signin and view secure page.

Database : First we need to create a database for example simpledb now create a table users that has two fields username and password with auto incremented field id.

CREATE TABLE `simpledb`.`users` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 20 ) NOT NULL ,
`password` VARCHAR( 64 ) NOT NULL
);

Now for database queries we will use PDO(PHP Data Object). If you are not familiar with PDO I recommend to read this before go ahead.

function connectDb()
{
  global $pdo;

  try {
    $pdo = new PDO('mysql:host=localhost;dbname=simpledb', 'root', '');
  } catch (PDOException $e) {
    die('MySQL connection fail! ' . $e->getMessage());
  }
}

connectDb function will establish the database connection.

Signup: Now we will create a signup page for new users to create their account.  To create a signup form use following HTML code and place it in signup.php file.

<!doctype html>
<html>
<head>
  <meta charset="utf-8">
  <title>Sign Up</title>
</head>
<body>
  <h1>Create Account</h1>
  <p>Already have an account? <a href="signin.php">Login</a></p>

  <!-- to display error or success message -->
  <?php if (!empty($message)): ?>
    <p><?php echo $message?></p>
  <?php endif ?>

  <!-- signup form -->
  <form action="" method="post">
    <input type="text" name="username" placeholder="username">
    <input type="password" name="password" placeholder="password">

    <input type="submit" value="Sign Up">
  </form>
</body>
</html>

The create_user function will add user information to users table.

function create_user($username, $password) {
  # check if username is already exists
  if (username_exists($username))
    return false;

  # add new user
  global $pdo;
  $stmt = $pdo->prepare('
    INSERT INTO users
    (username, password)
    values (:username, :password)');

  $stmt->execute( array(':username' => $username, ':password' => md5($password)) );

  if ($pdo->lastInsertId())
    return true;
  else
    return false;
}

To call create_user function you need to add following code above signup.php file.

$message = "";
if(!empty($_POST["username"]) && !empty($_POST["password"])){
	if(create_user($_POST["username"], $_POST["password"])){
		$message = "User account created successfully.";
	} else {
		$message = "Username already exists.";
	}
}

Sign In: Now we are going to create a sign in form. this form is almost similar to sign up form.  You need to add this code to signin.php file.

<!doctype html>
<html>
<head>
  <meta charset="utf-8">
  <title>Sign In</title>
</head>
<body>
  <h1>Sign In</h1>

  <!-- to display error or success message -->
  <?php if (!empty($message)): ?>
    <p><?php echo $message?></p>
  <?php endif ?>

  <form action="" method="post">
    <input type="text" name="username" placeholder="username">
    <input type="password" name="password" placeholder="password">

    <input type="submit" value="Sign In">
  </form>

</body>
</html>

The login function will check user input into the database if entered username and password found in database it sets the session variable and returns true.

function login($username, $password){
  global $pdo;

  $stmt = $pdo->prepare('
    SELECT id, username
    FROM users
    WHERE username = :username AND password = :password
    LIMIT 1');

  $stmt->execute(array(':username' => $username, 'password' => md5($password)));

  if ($data = $stmt->fetch( PDO::FETCH_OBJ )) {
    # set session
    $_SESSION['username'] = $data->username;
    return true;
  } else {
    return false;
  }
}

To call login function you need to add below code on top of the signin.php file.

$message = "";
if(!empty($_POST["username"]) && !empty($_POST["password"])){
	if(login($_POST["username"], $_POST["password"])){
		$message = "You are logged in successfully.";
	} else {
		$message = "Username and password not matched";
	}
}

This is a sample login system to understand how user signup and signin with php and mysql works. You need to add a proper validation methods with user inputs.

Download Code