Creating a simple MySQL Login Script
This tutorial will guide you through creating a simple user registration and login system using PHP and PDO. ✨
1. Create the database table
First, create a table named users in your MySQL database. This table will store your users' information.
To create the users table, log into your hosting control panel (cPanel, Plesk, etc.), open phpMyAdmin, select your database, go to the SQL tab, and paste this command:
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2. Create your PDO database connection file
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;3. Create the registration form and script
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>4. Create the login form and script
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>5. Create a Protected Page
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>6. Create a Logout Script
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. 😅