In this guide, we will walk through creating a simple yet robust PHP login and registration system integrated with a MySQL database. This setup is ideal for websites or applications that require user authentication. We’ll cover creating the database, setting up registration, implementing login, and displaying user details upon successful login.
Prerequisites
Before we dive in, make sure you have the following installed:
- A local server environment (e.g., XAMPP, WAMP, or MAMP) or Live PHP Server (Recommended)
- MySQL database
- Basic knowledge of PHP and MySQL
For a simpler login system without a database, check out our previous blog post: Building a Simple No-Database Login System Using PHP
Why Database Login is Secure
Database login systems offer a higher level of security compared to file-based or no-database login systems. Here are some reasons why:
- Data Encryption: Passwords can be securely hashed and stored in the database, making it difficult for attackers to retrieve plain-text passwords even if they gain access to the database.
- SQL Injection Prevention: Using prepared statements with parameterized queries ensures that user input is properly escaped, preventing SQL injection attacks that could otherwise compromise the system.
- Scalability: Database login systems can handle a large number of users and provide efficient query processing, making them suitable for applications with high user loads.
- Centralized Management: User data is stored in a centralized location, making it easier to manage and maintain user accounts, enforce policies, and implement security measures.
- Audit Trails: Databases can maintain logs of user activities, providing valuable information for monitoring, auditing, and detecting suspicious activities.
Step 1: Create the Database and Users Table
First, we need to create a database and a table to store user details. Open your MySQL database management tool (like phpMyAdmin) and run the following SQL commands:
CREATE DATABASE user_login_system;
USE user_login_system;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(15) NOT NULL,
status ENUM('Active', 'Inactive') DEFAULT 'Active',
registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step 2: Connect to the MySQL Database in PHP
Create a file named db.php to handle database connections. This file will be included in other PHP files to establish a connection.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "user_login_system";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
Step 3: Create the Registration Form
Create a file named registration.php to handle user registration. This form will collect user details and store them in the database using prepared statements for security.
<?php
include 'db.php';
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$username = $_POST['username'];
$password = password_hash($_POST['password'], PASSWORD_DEFAULT);
$email = $_POST['email'];
$phone = $_POST['phone'];
$stmt = $conn->prepare("INSERT INTO users (username, password, email, phone) VALUES (?, ?, ?, ?)");
$stmt->bind_param("ssss", $username, $password, $email, $phone);
if ($stmt->execute()) {
echo "New record created successfully";
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
$conn->close();
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Register</title>
</head>
<body>
<h1>Register</h1>
<form method="POST" action="">
Username: <input type="text" name="username" required><br>
Password: <input type="password" name="password" required><br>
Email: <input type="email" name="email" required><br>
Phone: <input type="text" name="phone" required><br>
<input type="submit" value="Register">
</form>
</body>
</html>
Step 4: Create the Login Form
Create a file named login.php to handle user login. This form will validate user credentials and redirect to the welcome page upon successful login.
<?php
session_start();
include 'db.php';
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$username = $_POST['username'];
$password = $_POST['password'];
$stmt = $conn->prepare("SELECT password FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();
$stmt->bind_result($hashed_password);
$stmt->fetch();
if ($hashed_password && password_verify($password, $hashed_password)) {
$_SESSION['username'] = $username;
header("Location: welcome.php");
exit();
} else {
echo "Invalid username or password!";
}
$stmt->close();
$conn->close();
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Login</title>
</head>
<body>
<h1>Login</h1>
<form method="POST" action="">
Username: <input type="text" name="username" required><br>
Password: <input type="password" name="password" required><br>
<input type="submit" value="Login">
</form>
</body>
</html>
Step 5: Create the Welcome Page
Create a file named welcome.php to display a list of registered users and a welcome message to the logged-in user.
<?php
session_start();
if (!isset($_SESSION['username'])) {
header("Location: login.php");
exit();
}
include 'db.php';
$sql = "SELECT id, username, email, phone, status, registration_date FROM users";
$result = $conn->query($sql);
?>
<!DOCTYPE html>
<html>
<head>
<title>Welcome</title>
</head>
<body>
<h1>Welcome, <?php echo $_SESSION['username']; ?>!</h1>
<h2>List of Registered Users:</h2>
<table border="1">
<tr>
<th>ID</th>
<th>Username</th>
<th>Email</th>
<th>Phone</th>
<th>Status</th>
<th>Registration Date</th>
</tr>
<?php
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['username'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" . $row['phone'] . "</td>";
echo "<td>" . $row['status'] . "</td>";
echo "<td>" . $row['registration_date'] . "</td>";
echo "</tr>";
}
} else {
echo "<tr><td colspan='6'>No users found</td></tr>";
}
$conn->close();
?>
</table>
<a href="logout.php">Logout</a>
</body>
</html>
Step 6: Add a Logout Option
Create a file named logout.php to log out the user and end the session.
<?php
session_start();
session_destroy();
header("Location: login.php");
exit();
?>
Why We should use Prepared Statements?
Prepared statements are a feature of many database management systems, including MySQL, that help protect your database from SQL injection attacks. SQL injection is a technique used by attackers to manipulate your SQL queries by inserting malicious code. Prepared statements separate SQL code from user input, ensuring that user-provided data is treated as data and not executable code. Here are some key benefits of using prepared statements:
- Security: Prepared statements prevent SQL injection by properly escaping user input.
- Performance: Prepared statements can be parsed and compiled once, then executed multiple times with different parameters, which can improve performance.
- Readability: They make your SQL queries cleaner and easier to read by separating the query logic from the data.
Conclusion
Congratulations! You have successfully created a simple PHP login and registration system with MySQL database integration. This basic setup can be expanded and customized according to your requirements. Always remember to follow best practices for security and code maintenance.
If you have any questions or comments, feel free to leave them below. Happy coding!
Leave a Reply