Simple PHP Login System with SQLite and PDO

There are three parts in this script. Download the zip file at the end of the tutorial.

Create Table and Insert a Record
Login Page with Bootstrap style
PHP Script for Login Validation Using PDO and SQLite

Login

Note: This login system is intended for learning purposes only. It stores passwords in plain text, which is not secure. In real-world applications, you should implement proper industry standards such as password hashing using functions like password_hash() and password_verify(), and secure your application with HTTPS, input validation, and protection against SQL injection and cross-site scripting (XSS) attacks. Always follow best practices for user authentication and security.

Create SQLite Database and Insert a Record 🔝

The following PHP code creates an SQLite database, defines a table called users with columns userid, password, and email, and inserts a sample record into the table.

PHP Code to Create a Table and Insert a Record

<?php
$my_conn = new PDO('sqlite:'.dirname(__FILE__).'/plus2net_users.db'); // Inside the same directory 

try {    	
   $count=$my_conn->prepare("
CREATE TABLE users (
    userid TEXT NOT NULL,
    password TEXT NOT NULL,
    email TEXT NOT NULL);
");
					  
  if($count->execute()){
  echo "TABLE users created";
  }else{
  echo "Not able to create table users";
  }	

// Insert a sample user record
$count=$my_conn->exec("INSERT INTO users (userid, password, email) 
		VALUES ('user1', 'password123', 'user1@example.com')");
echo "<BR>Number of records added: ".$count;

// Close the connection
$my_conn = null;
}
catch(PDOException $e) 
{
    // Print PDOException message
    echo $e->getMessage();
} 
?>

Explanation:

The script uses PDO to connect to an SQLite database located in the same directory as the PHP file.
A new table called users is created with three fields: userid, password, and email.
It then inserts one user record into the users table with sample data: user1, password123, and user1@example.com.
In case of any errors, a PDOException will catch and display the error message.
The database connection is closed using $my_conn = null after executing the queries.

This file represents a simple login form using Bootstrap for styling. The user will input their User ID and Password, and the form will submit the data to check-login.php for validation.

Login Form Code

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Login</title>
<!-- Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container">
        <div class="row justify-content-center">
            <div class="col-md-4">
                <h2 class="text-center mt-5">Login</h2>
                <form action="check-login.php" method="POST">
                    <div class="form-group mb-3">
                        <label for="userid">User ID</label>
                        <input type="text" name="userid" id="userid" class="form-control" required>
                    </div>
                    <div class="form-group mb-3">
                        <label for="password">Password</label>
                        <input type="password" name="password" id="password" class="form-control" required>
                    </div>
                    <button type="submit" class="btn btn-primary w-100">Login</button>
                </form>
            </div>
        </div>
    </div>
<!-- Bootstrap JS and Popper.js -->
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

Explanation:

The form collects User ID and Password, and submits the data via a POST request to check-login.php.
The form fields are styled using Bootstrap, with classes like form-control and btn-primary to ensure responsive and professional design.
The page includes Bootstrap CSS and JavaScript via CDN for easy integration and responsive design.
The submit button is styled to be full-width using the w-100 class, ensuring it fits the container.

PHP Script for Login Validation Using PDO and SQLite 🔝

This PHP script connects to the SQLite database using PDO, retrieves the form data (User ID and Password), and checks if they match any record in the users table. If a match is found, a session is created, and a welcome message is displayed. If not, a login failure message is shown.

check-login.php : Login Validation Code 🔝

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

try {
    // Connect to SQLite database
    $my_conn = new PDO('sqlite:' . dirname(__FILE__) . '/plus2net_users.db'); // Inside the same directory

    // Get the form data
    $userid = $_POST['userid'];
    $password = $_POST['password'];

    // Prepare the SQL statement to check for the user
    $stmt = $my_conn->prepare("SELECT * FROM users WHERE userid = :userid AND password = :password");

    // Bind parameters
    $stmt->bindParam(':userid', $userid);
    $stmt->bindParam(':password', $password);  // Assuming password is stored as plain text for this example. Use hashing in production.

    $stmt->execute();  // Execute the query

    $user = $stmt->fetch(PDO::FETCH_ASSOC);  // Fetch the result

    // Check if a matching user was found
    if ($user) {
        // User found, create session
        $_SESSION['userid'] = $user['userid'];
        $_SESSION['email'] = $user['email'];

        echo "<h2>Welcome, " . htmlspecialchars($user['userid']) . "!</h2>";
    } else {
        // Invalid credentials
        echo "<h2>Login Failed. Invalid User ID or Password.</h2>";
    }

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Explanation:

We start the session using session_start() to store user information in case of a successful login.
The script connects to the SQLite database using PDO and retrieves the User ID and Password submitted via POST request.
An SQL query is prepared using bindParam to securely bind the user input and check if it matches any record in the users table.
If a matching record is found, the user's User ID and Email are stored in the session, and a welcome message is displayed. Otherwise, a failure message is shown.
Exceptions are handled using try-catch blocks to ensure errors are captured and displayed if the connection or query execution fails.

Podcast on SQLite database management using PHP PDO

Download sample scripts using SQLite with instructions on how to use.

PHP SQLite Insert record MySQL Blob data

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: