Unlock the Web: Building Your First PHP & MySQL Login System

Welcome, aspiring web developer! If you've ever wondered how websites remember you, how they greet you by name, or how they keep certain areas exclusive to members, you're about to uncover one of the foundational secrets: user authentication. Building a login and registration system is a rite of passage for PHP developers. It’s not just a technical exercise; it's about creating personalized and secure web experiences.

In this comprehensive tutorial, we'll guide you step-by-step through building a simple yet functional login and registration system using PHP and MySQL. This project is incredibly valuable because it touches upon core PHP skills that are essential in real-world web development: handling form data, managing user sessions, interacting with databases, and basic security practices. What you learn here will be a stepping stone, allowing you to expand your applications with more advanced features like password resets, two-factor authentication, and much more. Let's embark on this journey to empower your web applications!

Tools You'll Need:

• PHP: The scripting language that will power our backend logic.
• MySQL: Our database of choice for storing user information.
• Web Server (Apache or Nginx): To serve our PHP files.
• phpMyAdmin (Optional but Recommended): A web-based tool to easily manage your MySQL database.
• XAMPP/MAMP/WAMP or Docker: These packages provide an easy way to set up your local development environment with all the necessary tools. We'll touch upon XAMPP.
• A Code Editor: Such as VS Code, Sublime Text, or PhpStorm.

1. Setting Up Your Development Environment

Before we write a single line of PHP, we need a cozy place for our code to live and run. A local development environment mimics a live web server on your own computer, allowing you to build and test your applications privately.

Using XAMPP (Cross-Platform, Apache, MariaDB, PHP, Perl)

XAMPP is a popular, free, and easy-to-install Apache distribution containing MariaDB (a community-developed fork of MySQL), PHP, and Perl. It's available for Windows, macOS, and Linux.

1. Download XAMPP: Visit the Apache Friends website and download the installer for your operating system.
2. Install XAMPP: Run the installer and follow the on-screen instructions. You can generally stick with the default components.
3. Start Apache and MySQL: Open the XAMPP Control Panel. Start the Apache and MySQL modules. You should see their status turn green.
4. Access phpMyAdmin: Open your web browser and navigate to http://localhost/phpmyadmin. This is where we'll create our database.

Note: For those comfortable with containerization, Docker offers a more isolated and configurable environment. You can find numerous pre-built PHP and MySQL images on Docker Hub.

Project Folder Structure

Once XAMPP (or your chosen stack) is running, you'll need a place to store your project files. If you're using XAMPP, this will typically be in the htdocs subdirectory within your XAMPP installation folder (e.g., C:\xampp\htdocs\ on Windows or /Applications/XAMPP/htdocs/ on macOS).

Create a new folder named phplogin (or any name you prefer) inside htdocs. Our project structure will look something like this initially, and we'll add files as we go:

phplogin/
  ├── register.php
  ├── login.php
  ├── dashboard.php
  ├── logout.php
  ├── config.php       (We'll create this for database connection)
  └── assets/          (Optional: for CSS, JS, images)
    └── style.css

2. Creating the Database and User Table

With our environment ready, it's time to set up the database where our user information will reside. We'll use phpMyAdmin for this, which you can access via http://localhost/phpmyadmin.

Step 1: Create the Database

1. In phpMyAdmin, click on the "Databases" tab.
2. Under "Create database," enter the name auth_demo.
3. Choose a collation, such as utf8mb4_general_ci (good for supporting a wide range of characters), and click "Create."

Step 2: Create the users Table

Now, select the auth_demo database from the left-hand sidebar. You'll be prompted to create a new table.

• Table Name: users
• Number of columns: 4

Click "Go." Now, define the columns as follows:

1. id:
   • Type: INT
   • Length/Values: 11
   • Index: PRIMARY
   • A_I (Auto Increment): Check this box. This will make id our unique primary key that automatically increments.
2. username:
   • Type: VARCHAR
   • Length/Values: 50
   • Attributes: (Optional) Set as UNIQUE if you want usernames to be unique.
3. email:
   • Type: VARCHAR
   • Length/Values: 100
   • Attributes: Set as UNIQUE to ensure emails are unique.
4. password:
   • Type: VARCHAR
   • Length/Values: 255 (Passwords will be hashed, and hashes can be long).

Click "Save." Your users table is now ready to store registrations!

3. Building the Registration Page (register.php)

This is where users will sign up for our application. We'll create an HTML form and then a PHP script to process the submitted data.

Create a file named register.php in your phplogin directory.

The HTML Form

Let's start with the basic HTML structure for the registration form.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Register</title>
    <!-- Link to Bootstrap or your custom CSS here -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <style>
        body{ font: 14px sans-serif; display: flex; align-items: center; justify-content: center; min-height: 100vh; background-color: #f7f7f7;}
        .wrapper{ width: 360px; padding: 20px; background: #fff; border-radius: 5px; box-shadow: 0 0 10px rgba(0,0,0,0.1); }
        .help-block { color: red; font-size: 0.9em; } /* Basic error styling */
    </style>
</head>
<body>
    <div class="wrapper">
        <h2>Sign Up</h2>
        <p>Please fill this form to create an account.</p>
        <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
            <!-- PHP variables for errors will be added here -->
            <div class="form-group">
                <label>Username</label>
                <input type="text" name="username" class="form-control" value="">
                <!-- Error span will be added here -->
            </div>    
            <div class="form-group">
                <label>Email</label>
                <input type="email" name="email" class="form-control" value="">
                 <!-- Error span will be added here -->
            </div>
            <div class="form-group">
                <label>Password</label>
                <input type="password" name="password" class="form-control" value="">
                 <!-- Error span will be added here -->
            </div>
            <div class="form-group">
                <label>Confirm Password</label>
                <input type="password" name="confirm_password" class="form-control" value="">
                 <!-- Error span will be added here -->
            </div>
            <div class="form-group">
                <input type="submit" class="btn btn-primary" value="Submit">
                <input type="reset" class="btn btn-secondary ml-2" value="Reset">
            </div>
            <p>Already have an account? <a href="login.php">Login here</a>.</p>
        </form>
    </div>    
</body>
</html>

Notice action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>". This submits the form data back to the same page (register.php) for processing. htmlspecialchars() is used to prevent XSS attacks.

The PHP Logic (at the top of register.php)

Now, let's add the PHP code to handle form submission, validate input, hash the password, and insert the user into the database. First, we need a database connection. Create a file named config.php:

<?php
/* Database credentials. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root'); // Your MySQL username
define('DB_PASSWORD', ''); // Your MySQL root password, if set
define('DB_NAME', 'auth_demo');

/* Attempt to connect to MySQL database */
$link = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);

// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>

Now, at the very beginning of your register.php file (before any HTML), add the following PHP block:

<?php
// Include config file
require_once "config.php";

// Define variables and initialize with empty values
$username = $email = $password = $confirm_password = "";
$username_err = $email_err = $password_err = $confirm_password_err = "";
$registration_success_msg = ""; // For success message

// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){

    // Validate username
    if(empty(trim($_POST["username"]))){
        $username_err = "Please enter a username.";
    } elseif(!preg_match('/^[a-zA-Z0-9_]+$/', trim($_POST["username"]))){
        $username_err = "Username can only contain letters, numbers, and underscores.";
    } else {
        $sql = "SELECT id FROM users WHERE username = ?";
        if($stmt = mysqli_prepare($link, $sql)){
            mysqli_stmt_bind_param($stmt, "s", $param_username);
            $param_username = trim($_POST["username"]);
            if(mysqli_stmt_execute($stmt)){
                mysqli_stmt_store_result($stmt);
                if(mysqli_stmt_num_rows($stmt) == 1){
                    $username_err = "This username is already taken.";
                } else{
                    $username = trim($_POST["username"]);
                }
            } else{
                echo "Oops! Something went wrong. Please try again later.";
            }
            mysqli_stmt_close($stmt);
        }
    }

    // Validate email
    if(empty(trim($_POST["email"]))){
        $email_err = "Please enter an email.";
    } elseif(!filter_var(trim($_POST["email"]), FILTER_VALIDATE_EMAIL)){
        $email_err = "Please enter a valid email address.";
    } else {
        $sql = "SELECT id FROM users WHERE email = ?";
        if($stmt = mysqli_prepare($link, $sql)){
            mysqli_stmt_bind_param($stmt, "s", $param_email);
            $param_email = trim($_POST["email"]);
            if(mysqli_stmt_execute($stmt)){
                mysqli_stmt_store_result($stmt);
                if(mysqli_stmt_num_rows($stmt) == 1){
                    $email_err = "This email is already registered.";
                } else {
                    $email = trim($_POST["email"]);
                }
            } else {
                echo "Oops! Something went wrong. Please try again later.";
            }
            mysqli_stmt_close($stmt);
        }
    }

    // Validate password
    if(empty(trim($_POST["password"]))){
        $password_err = "Please enter a password.";     
    } elseif(strlen(trim($_POST["password"])) < 6){
        $password_err = "Password must have at least 6 characters.";
    } else{
        $password = trim($_POST["password"]);
    }

    // Validate confirm password
    if(empty(trim($_POST["confirm_password"]))){
        $confirm_password_err = "Please confirm password.";     
    } else{
        $confirm_password = trim($_POST["confirm_password"]);
        if(empty($password_err) && ($password != $confirm_password)){
            $confirm_password_err = "Password did not match.";
        }
    }

    // Check input errors before inserting in database
    if(empty($username_err) && empty($email_err) && empty($password_err) && empty($confirm_password_err)){
        $sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
        if($stmt = mysqli_prepare($link, $sql)){
            mysqli_stmt_bind_param($stmt, "sss", $param_username, $param_email, $param_password);
            $param_username = $username;
            $param_email = $email;
            $param_password = password_hash($password, PASSWORD_DEFAULT); // Creates a password hash

            if(mysqli_stmt_execute($stmt)){
                // Set success message and clear form fields
                $registration_success_msg = "Registration successful! You can now <a href='login.php'>login</a>.";
                $username = $email = $password = $confirm_password = ""; // Clear fields
                // Or redirect: header("location: login.php"); exit;
            } else{
                echo "Something went wrong. Please try again later.";
            }
            mysqli_stmt_close($stmt);
        }
    }
    mysqli_close($link);
}
?>

To display the error messages and success message, you'll need to modify the HTML form. Add the success message display, for example, above the <h2>Sign Up</h2>:

<!-- At the top of the .wrapper div in register.php -->
<?php 
if(!empty($registration_success_msg)){
    echo '<div class="alert alert-success">' . $registration_success_msg . '</div>';
}        
?>

For the username field and its error:

<div class="form-group <?php echo (!empty($username_err)) ? 'has-error' : ''; ?>">
    <label>Username</label>
    <input type="text" name="username" class="form-control" value="<?php echo htmlspecialchars($username); ?>">
    <span class="help-block"><?php echo $username_err; ?></span>
</div>

Apply similar changes for email, password, and confirm password fields, using htmlspecialchars($email) for the email value, but not for password values (they should not be pre-filled for security).

Key points in the PHP script:

• require_once "config.php";: Includes our database connection settings.
• Input Validation: Checks for empty fields, valid email format, username constraints, and password length.
• Duplicate Check: Queries the database to ensure the username or email isn't already taken. This uses prepared statements to prevent SQL injection.
• Password Hashing: password_hash($password, PASSWORD_DEFAULT) is crucial. NEVER store plain text passwords. PASSWORD_DEFAULT uses the current best algorithm (BCrypt by default).
• Prepared Statements: mysqli_prepare(), mysqli_stmt_bind_param(), and mysqli_stmt_execute() are used for database interactions to prevent SQL injection vulnerabilities.

4. Crafting the Login Page (login.php)

The login page will allow registered users to access their accounts. It involves an HTML form and PHP logic to verify credentials.

Create a file named login.php in your phplogin directory.

The HTML Form

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Login</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <style>
        body{ font: 14px sans-serif; display: flex; align-items: center; justify-content: center; min-height: 100vh; background-color: #f7f7f7;}
        .wrapper{ width: 360px; padding: 20px; background: #fff; border-radius: 5px; box-shadow: 0 0 10px rgba(0,0,0,0.1); }
        .help-block { color: red; font-size: 0.9em; } /* Basic error styling */
    </style>
</head>
<body>
    <div class="wrapper">
        <h2>Login</h2>
        <p>Please fill in your credentials to login.</p>
        <!-- PHP logic for login errors will be added here -->
        <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
            <div class="form-group">
                <label>Email</label>
                <input type="email" name="email" class="form-control" value="">
                <!-- Error span -->
            </div>    
            <div class="form-group">
                <label>Password</label>
                <input type="password" name="password" class="form-control">
                <!-- Error span -->
            </div>
            <div class="form-group">
                <input type="submit" class="btn btn-primary" value="Login">
            </div>
            <p>Don't have an account? <a href="register.php">Sign up now</a>.</p>
        </form>
    </div>    
</body>
</html>

The PHP Logic (at the top of login.php)

Add this PHP code at the beginning of login.php:

<?php
// Initialize the session
session_start();

// Check if the user is already logged in, if yes then redirect him to dashboard page
if(isset($_SESSION["loggedin"]) && $_SESSION["loggedin"] === true){
    header("location: dashboard.php");
    exit;
}

// Include config file
require_once "config.php";

// Define variables and initialize with empty values
$email = $password = "";
$email_err = $password_err = $login_err = "";

// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){

    if(empty(trim($_POST["email"]))){
        $email_err = "Please enter email.";
    } else{
        $email = trim($_POST["email"]);
    }

    if(empty(trim($_POST["password"]))){
        $password_err = "Please enter your password.";
    } else{
        $password = trim($_POST["password"]);
    }

    if(empty($email_err) && empty($password_err)){
        $sql = "SELECT id, username, email, password FROM users WHERE email = ?";
        if($stmt = mysqli_prepare($link, $sql)){
            mysqli_stmt_bind_param($stmt, "s", $param_email);
            $param_email = $email;

            if(mysqli_stmt_execute($stmt)){
                mysqli_stmt_store_result($stmt);
                if(mysqli_stmt_num_rows($stmt) == 1){                    
                    mysqli_stmt_bind_result($stmt, $id, $username, $db_email, $hashed_password);
                    if(mysqli_stmt_fetch($stmt)){
                        if(password_verify($password, $hashed_password)){
                            // Password is correct, so start a new session
                            // session_start(); // Already started at the top

                            $_SESSION["loggedin"] = true;
                            $_SESSION["id"] = $id;
                            $_SESSION["username"] = $username;
                            $_SESSION["email"] = $db_email;                           

                            header("location: dashboard.php");
                        } else{
                            $login_err = "Invalid email or password.";
                        }
                    }
                } else{
                    $login_err = "Invalid email or password.";
                }
            } else{
                echo "Oops! Something went wrong. Please try again later.";
            }
            mysqli_stmt_close($stmt);
        }
    }
    mysqli_close($link);
}
?>

To display errors on the form, for instance, before the <form> tag, you could add:

<!-- Inside .wrapper, before the <form> tag in login.php -->
<?php 
if(!empty($login_err)){
    echo '<div class="alert alert-danger">' . $login_err . '</div>';
}        
?>

And for individual field errors (e.g., email):

<div class="form-group <?php echo (!empty($email_err)) ? 'has-error' : ''; ?>">
    <label>Email</label>
    <input type="email" name="email" class="form-control" value="<?php echo htmlspecialchars($email); ?>">
    <span class="help-block"><?php echo $email_err; ?></span>
</div>
<div class="form-group <?php echo (!empty($password_err)) ? 'has-error' : ''; ?>">
    <label>Password</label>
    <input type="password" name="password" class="form-control">
    <span class="help-block"><?php echo $password_err; ?></span>
</div>

Key points in the PHP script:

• session_start();: This must be called at the very beginning of any script that uses sessions.
• Redirect if Already Logged In: Checks if $_SESSION["loggedin"] is true and redirects to the dashboard.
• Credential Validation: Fetches the user record by email.
• password_verify($password, $hashed_password): This is crucial. It securely compares the submitted password against the stored hash.
• Session Variables: Upon successful login, important user data (like ID, username, and login status) is stored in $_SESSION variables.
• Redirection: header("location: dashboard.php"); sends the user to their dashboard.

5. The User Dashboard (dashboard.php)

This is a protected page only accessible to logged-in users. It will display a welcome message and a logout link.

Create a file named dashboard.php.

<?php
// Initialize the session
session_start();

// Check if the user is logged in, if not then redirect him to login page
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
    header("location: login.php");
    exit;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Dashboard</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <style>
        body{ font: 14px sans-serif; text-align: center; padding-top: 50px; background-color: #f7f7f7; }
        .page-header { margin-bottom: 30px;}
        .btn { margin: 5px; }
    </style>
</head>
<body>
    <div class="page-header">
        <h1>Hi, <b><?php echo htmlspecialchars($_SESSION["username"]); ?></b>. Welcome to our site.</h1>
    </div>
    <p>
        <!-- <a href="reset-password.php" class="btn btn-warning">Reset Your Password</a> -->
        <a href="logout.php" class="btn btn-danger">Sign Out of Your Account</a>
    </p>
</body>
</html>

Key points:

• Session Check: The PHP block at the top ensures that if a user is not logged in ($_SESSION["loggedin"] is not set or not true), they are redirected to login.php.
• Displaying User Information: htmlspecialchars($_SESSION["username"]) safely displays the logged-in user's username.

6. Implementing Logout Logic (logout.php)

Logging out is straightforward: we need to destroy the session data.

Create a file named logout.php.

<?php
// Initialize the session
session_start();

// Unset all of the session variables
$_SESSION = array();

// Destroy the session.
session_destroy();

// Redirect to login page
header("location: login.php");
exit;
?>

Explanation:

• session_start();: Required to access session functions.
• $_SESSION = array();: Clears all session variables.
• session_destroy();: Completely destroys the session.
• header("location: login.php");: Redirects the user back to the login page.

7. Basic Security Tips: Beyond the Basics

While our system incorporates password hashing and prepared statements (for SQL injection prevention), web security is a vast field. Here are a few more crucial considerations:

SQL Injection Prevention (Recap)

We've used prepared statements (with mysqli_prepare, mysqli_stmt_bind_param, and mysqli_stmt_execute). This is the most effective way to prevent SQL injection because it separates the SQL query structure from the user-supplied data. The database treats the data purely as data, not as executable code.

Cross-Site Scripting (XSS)

XSS occurs when malicious scripts are injected into otherwise benign and trusted websites. We've used htmlspecialchars() when displaying user-supplied data (like usernames in the dashboard) to mitigate this. Always sanitize output!

<?php echo htmlspecialchars($user_input); ?>

Cross-Site Request Forgery (CSRF)

CSRF tricks a victim into submitting a malicious request. A common defense is using CSRF tokens: unique, secret, unpredictable values generated by the server and transmitted to the client. When the client submits a form, this token is sent back and verified by the server.

Implementing CSRF tokens involves:

1. Generating a token and storing it in the session.
2. Including the token as a hidden field in your forms.
3. When the form is submitted, verifying that the submitted token matches the one in the session.

Password Strength & Policies

• Encourage or enforce strong passwords (mix of uppercase, lowercase, numbers, symbols; minimum length).
• Consider implementing a password strength meter on the registration page.
• Offer (or require) password resets periodically.

Session Security

• Regenerate Session ID: After login and other critical state changes (like password change), regenerate the session ID using session_regenerate_id(true); to prevent session fixation.
• HTTPS: Always use HTTPS on production sites to encrypt data in transit, including session cookies.
• HttpOnly Cookies: Configure session cookies to be HttpOnly to prevent client-side scripts from accessing them. You can set this in php.ini or using session_set_cookie_params().

8. Final Touches: Styling and Flash Messages

Bootstrap Styling

We've included the Bootstrap CSS link in our HTML head sections. Bootstrap provides pre-styled components that make your forms and pages look clean and professional with minimal effort. Classes like form-control, btn, btn-primary, alert, alert-danger, etc., come from Bootstrap.

You can create a custom style.css file in an assets/css/ directory and link it in your HTML files if you want to further customize the appearance.

Flash Messages (e.g., "Registered Successfully")

Flash messages are messages stored in the session to be displayed on the next page load, and then cleared. This is useful for things like "Registration successful!" after redirecting to the login page.

Example implementation:

In register.php, after successful registration and before redirecting (if you choose to redirect instead of showing the message on the same page):

// ... (after successful insert in register.php)
if(mysqli_stmt_execute($stmt)){
    // If redirecting:
    session_start(); // Ensure session is started if not already
    $_SESSION['flash_message'] = "Registration successful! Please login.";
    $_SESSION['flash_message_type'] = "success"; // For styling (e.g., alert-success)
    header("location: login.php");
    exit;
} 
// ...

In login.php (or a common header file), display the flash message if it exists. Make sure session_start(); is at the very top of login.php.

<?php
// Ensure session_start(); is at the very top of login.php

if(isset($_SESSION['flash_message'])){
    $message = $_SESSION['flash_message'];
    $type = isset($_SESSION['flash_message_type']) ? $_SESSION['flash_message_type'] : 'info'; // Default to 'info'
    echo "<div class='alert alert-{$type}'>" . htmlspecialchars($message) . "</div>";

    // Clear the flash message after displaying
    unset($_SESSION['flash_message']);
    unset($_SESSION['flash_message_type']);
}
?>

You would place this PHP snippet early in the <body> of login.php (e.g., inside the .wrapper div, before the login form), or wherever you want the message to appear. The $type variable can be used to apply different Bootstrap alert classes (e.g., alert-success, alert-danger, alert-info).

Next Steps & Bonus Ideas

Congratulations! You've built a foundational login and registration system. But the journey doesn't end here. Here are some ways you can expand and improve your application:

• Email Confirmation: Send a confirmation email with a unique link when a user registers. The account is only activated after clicking the link. This verifies email ownership.
• "Remember Me" Functionality: Allow users to stay logged in for an extended period using persistent cookies (securely implemented, of course!).
• Password Reset: Implement a "Forgot Password" feature that allows users to reset their password via an email link.
• Social Logins: Integrate with OAuth providers like Google, Facebook, or GitHub to allow users to sign in with their existing social media accounts.
• Two-Factor Authentication (2FA): Add an extra layer of security by requiring a second form of verification (e.g., a code from an authenticator app) in addition to the password.
• User Roles and Permissions: Extend the system to support different user roles (e.g., admin, editor, subscriber) with varying levels of access.
• Profile Pages: Allow users to view and edit their profile information.
• More Robust Input Validation: Implement more comprehensive server-side and client-side validation.

Building upon this simple system will solidify your PHP and MySQL skills and open doors to creating more complex and feature-rich web applications. Keep coding, keep learning, and keep exploring!