Creating a simple MySQL Login Script

I don't even know why, but at some point when learning PHP, you just get that itch to build your own stuff from scratch. Maybe you want a simple news script instead of wrestling with WordPress, or a custom mini forum instead of a bloated phpBB setup. Back when I learned to code, almost everyone eventually built their own custom CMS just for the fun of it.

As soon as you start building your own system, you hit the obvious next step: you need a login page. Sure, you could technically hack together a basic login using flat text files or hardcoded sessions without touching a database, but using a proper MySQL database is way more secure, much easier to manage, and actually scales when more people sign up. In this guide, we'll build a simple registration and login script from scratch the right way!

When connecting PHP to MySQL, there are a few ways to do it. The two main database options today are PDO and mysqli. We're using PDO here because it handles security smoothly and keeps the code super clean.

Quick note: If you're building this from scratch, you don't need to worry about the difference. But if you ever try mixing this code with another tutorial that uses mysqli, keep in mind they use totally different syntax and won't work together directly!

First, we need a place to save user details. Open phpMyAdmin, select your database, click the SQL tab, and run this query:

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Here is what each column does:

• id: Gives each new account an automatic ID number.
• username: The public display name. This isn't unique, so multiple users can share the same name.
• email: Used to log in. The UNIQUE setting stops anyone from registering the same email twice.
• password: The hashed password will be saved here.
• created_at: Automatically records when the account was registered.
• ENGINE=InnoDB & CHARSET=utf8mb4: Standard settings for modern character support, including emojis.

Create a file named database.php. This file will connect your PHP scripts to your MySQL database safely using PDO.

<?php
$dbConfig = [
    'host'     => 'localhost',
    'name'     => 'yourdatabasename',
    'user'     => 'yourdatabaseuser',
    'password' => 'yourpassword',
    'charset'  => 'utf8mb4'
];

// Validates config
if (empty($dbConfig['host']) || empty($dbConfig['name']) || empty($dbConfig['user'])) {
    exit('Database host, name, and user cannot be empty.');
}

try {
    $pdo = new PDO(
        "mysql:host={$dbConfig['host']};dbname={$dbConfig['name']};charset={$dbConfig['charset']}",
        $dbConfig['user'],
        $dbConfig['password'],
        [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES   => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );

    // Test the connection
    $pdo->query('SELECT 1');

} catch (PDOException $e) {
    die('Database connection failed.');
}

return $pdo;

The most important part of a login script is probably the registration form, right? So let's start with that.

Create a file named register.php and paste the following code. This page will display a form for users to sign up and process their input safely.

I added some basic validations for you, e.g. the username has a minimum and maximum length and only allows letters, numbers and underscores, the script checks if the email address is a valid one, it compares the passwords and checks the password length.

You don't have to keep all those checks, but they're recommended for safety. Just saying!

<?php
// Database connection file
$pdo = include 'database.php';

// Stores success and error messages
$messages = [];

// Processes the register form
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $username = trim($_POST['username'] ?? '');
    $email = trim($_POST['email'] ?? '');
    $password = $_POST['password'] ?? '';
    $confirmPassword = $_POST['confirm_password'] ?? '';

    // Validate username
    if (!$username || !preg_match('/^[a-zA-Z0-9_]{3,20}$/', $username)) {
        $messages[] = ['type' => 'error', 'text' => 'Username must be 3-20 characters and contain only letters, numbers, and underscores.'];
    }

    // Validate email
    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $messages[] = ['type' => 'error', 'text' => 'Invalid email address.'];
    }

    // Validate password length
    if (!$password || strlen($password) < 6) {
        $messages[] = ['type' => 'error', 'text' => 'Password must be at least 6 characters.'];
    }

    // Confirm password match
    if ($password !== $confirmPassword) {
        $messages[] = ['type' => 'error', 'text' => 'Passwords do not match.'];
    }

    // Check if there are any error messages before we proceed with registration
    $hasErrors = false;
    foreach ($messages as $message) {
        if ($message['type'] === 'error') {
            $hasErrors = true;
            break;
        }
    }

    // Proceed with registration if there are no errors
    if (!$hasErrors) {
        $query = $pdo->prepare('SELECT id FROM users WHERE email = :email');
        $query->execute([':email' => $email]);

        if ($query->fetch()) {
            $messages[] = ['type' => 'error', 'text' => 'Email already taken.'];
        } else {
            $hashedPassword = password_hash($password, PASSWORD_DEFAULT);

            $query = $pdo->prepare('INSERT INTO users (username, email, password) VALUES (:username, :email, :password)');
            $query->execute([
                ':username' => $username,
                ':email' => $email,
                ':password' => $hashedPassword,
            ]);

            $messages[] = ['type' => 'success', 'text' => 'Registration successful! You can now log in.'];
        }
    }
}
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Register</title>
    <style>
        body { font-family: Arial, sans-serif; max-width: 500px; margin: 50px auto; }
        input, button { display: block; margin: 5px 0; padding: 10px; width: 100%; }
        .error { color: red; }
        .success { color: green; }
    </style>
</head>

<body>

    <h1>Register</h1>

    <?php if ($messages): ?>
        <?php foreach ($messages as $message): ?>
            <p class="<?= htmlspecialchars($message['type']) ?>">
                <?= htmlspecialchars($message['text']) ?>
            </p>
        <?php endforeach; ?>
    <?php endif; ?>

    <form method="post">
        <input type="text" name="username" placeholder="Username" value="<?= htmlspecialchars($_POST['username'] ?? '') ?>" required>
        <input type="email" name="email" placeholder="Email" value="<?= htmlspecialchars($_POST['email'] ?? '') ?>" required>
        <input type="password" name="password" placeholder="Password (min 6 chars)" minlength="6" required>
        <input type="password" name="confirm_password" placeholder="Confirm Password" minlength="6" required>
        
        <button type="submit">Register</button>
        
        <p>Already registered? <a href="login.php">Log in</a></p>
    </form>

</body>
</html>

Next, we'll create the login page. Create a file named login.php. This page will allow users to log in securely.

It also comes with basic checks to verify whether a user exists, which prevents error messages. However, you could still enhance the functionality further. We're creating a simple login script, though! 👀

<?php
// Start session to enable login functionality
session_start(); 

// Database connection file
$pdo = include 'database.php';

// Stores success and error messages
$messages = [];

// Processes the login form
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $email = trim($_POST['email'] ?? '');
    $password = $_POST['password'] ?? '';

    // Check if email and password are provided
    if (!$email || !$password) {
        $messages[] = ['type' => 'error', 'text' => 'Please enter email and password.'];
    } else {
        // Fetch user data by email
        $query = $pdo->prepare('SELECT id, username, password FROM users WHERE email = :email');
        $query->execute([':email' => $email]);
        $user = $query->fetch();

        // Verify user exists and password matches
        if (!$user || !password_verify($password, $user['password'])) {
            $messages[] = ['type' => 'error', 'text' => 'Invalid email or password.'];
        } else {
            // Prevent session fixation attacks
            session_regenerate_id(true); 

            // Store user ID in session
            $_SESSION['user_id'] = $user['id']; 

            // Redirect to dashboard after successful login
            header('Location: dashboard.php');
            exit;
        }
    }
}
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Login</title>
    <style>
        body { font-family: Arial, sans-serif; max-width: 500px; margin: 50px auto; }
        input, button { display: block; margin: 5px 0; padding: 10px; width: 100%; }
        .error { color: red; }
        .success { color: green; }
    </style>
</head>

<body>

    <h1>Login</h1>

    <?php if ($messages): ?>
        <?php foreach ($messages as $message): ?>
            <p class="<?= htmlspecialchars($message['type']) ?>">
                <?= htmlspecialchars($message['text']) ?>
            </p>
        <?php endforeach; ?>
    <?php endif; ?>

    <form method="post">
        <input type="email" name="email" placeholder="Email" value="<?= htmlspecialchars($_POST['email'] ?? '') ?>" required>
        <input type="password" name="password" placeholder="Password" required>
        
        <button type="submit">Login</button>
        
        <p>Not registered? <a href="register.php">Sign up</a></p>
    </form>

</body>
</html>

And theoretically you're done here! You can now protect any file with the login. Let's create a simple dashboard.php which will act as a simple admin area. For example, you could create a news system and link to the add_news.php and manage_news.php here.

<?php
// Starts a session to make the login protection work
session_start();

// Redirect to login if not logged in
if (empty($_SESSION['user_id'])) {
    header('Location: login.php');
    exit;
}
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Dashboard</title>
</head>

<body>

    <h1>Dashboard</h1>
    <p>You are logged in!</p>
    <p><a href="logout.php">Logout</a></p>

</body>
</html>

Our login script is basic and has no advanced features like a "Remember me" option (yet?). Users will be logged out automatically when they close their browser, as PHP sessions end with the browser session. For completeness, we'll still provide a logout.php to allow manual logout.

Create logout.php and paste the following code:

<?php
session_start();
session_unset();
session_destroy();
header('Location: login.php');
exit;

That's it! You now have a simple login system to protect your scripts.

Later, you can build on this foundation by adding features like a "Remember me" checkbox or a basic role system (e.g., "admin" and "member"). But we'll cover this on another day, in another tutorial, maybe. 😅