Creating a Database Connection with SQLite3 using PHP Tutorial

Introduction

In this tutorial, I will show you how to Create a Database Connection with SQLite3 in PHP using the Object-Oriented Programming (OOP) Approach. I will also teach you the basic way of creating a database table in SQLite3 in PHP. The tutorial aims to provide students new to PHP Language a reference to learn about PHP and SQLite3 Integration. Here, snippets and sample scripts will be provided.

What is SQLite3

SQLite3 is a C-Language Library SQL Database Engine that is compact, quick, self-contained, highly reliable, and fully featured. It is built into mobile or smartphones, most computers or laptops, and many other applications. It is one of the most popular database engines in the world.

How to enable SQLite3 in PHP?

SQLite3 already comes with PHP 5.3.0 or later versions which means you don't need to download it. Although you still need to enable the SQLite3 extension in your PHP to be able to use it. To enable it to follow the instruction below.

1. Locate your PHP's php.ini file.
2. Open the file with your preferred text editor such as Notepad++, Sublime text, etc.
3. Locate the SQLite3 extension by searching the line with extension=sqlite3.
4. Remove the semi-colon (;) located before the extension word to uncomment the line or enable the said extension.
5. Make sure to restart your web servers such as Apache or Nginx.

How to create a Database and DB Connection in SQLite3 using PHP OOP?

Here are the following snippets on how to create SQLite Database and connect with it in PHP using the Object-Oriented Programming approach.

Creating the Database Connection Class

SQLite3 Database file will be automatically created if not existed yet upon opening a database connection with the given database file name. Here is the sample snippet to do so.

    <?php
    Class Database extends SQLite3 {
        public function __construct($db_file){
            // Open SQlite DB Connection
            $this->open($db_file);
 
        }
 
        public function __destruct(){
            // Close SQlite DB Connection
            $this->close();
        }
    }

Run the given class with the database path name as the parameter. See the example below

<?php
// Database Directory
$db_dir = "database/";
// Create directory if not exists
if(!is_dir($db_dir))
    mkdir($db_dir);
 
// Database pathname
$db_file = $db_dir."dummy_db.db";
 
// Database Connection
$connection = new Database($db_file);

After that, try to run or browse the PHP Script in your web browser to execute the script. If no error occurred, the database is successfully created at the given database path.

Creating Database Table

Next, try to create a new database table on your newly created database. To do that, you will need to use the SQLites' exec method. using the said method you can execute the Schema of your table. Here are sample snippets that demonstrate the table creation in SQLite Database using PHP.

<?php
$this->exec("CREATE TABLE IF NOT EXISTS `user_table` (
            `first_name` varchar(250) NOT NULL,
            `middle_name` varchar(250) NOT NULL,
            `last_name` varchar(250) NOT NULL,
            `email` varchar(100) NOT NULL,
            `address` TEXT NOT NULL)
        ");

You can put the code along with the __construct() function on your database to run automatically upon the database connection process or in a separate function just like the snippet below.

<?php
 
Class Database extends SQLite3 {
    public function __construct($db_file){
        // Open SQlite DB Connection
        $this->open($db_file);
 
    }
    public function create_dummy_table(){
        $create_table = $this->exec("CREATE TABLE IF NOT EXISTS `user_table` (
            `first_name` varchar(250) NOT NULL,
            `middle_name` varchar(250) NOT NULL,
            `last_name` varchar(250) NOT NULL,
            `email` varchar(100) NOT NULL,
            `address` TEXT NOT NULL)
        ");
        /**
        * Table will be only created if it is not existed yet 
        */
        if($create_table)
            return true;
        else
            return false;
    }
    public function __destruct(){
        // Close SQlite DB Connection
        $this->close();
    }
}
 
 
// Database Directory
$db_dir = "database/";
// Create directory if not exists
if(!is_dir($db_dir))
    mkdir($db_dir);
 
// Database pathname
$db_file = $db_dir."dummy_db.db";
 
// Database Connection
$connection = new Database($db_file);
?>

DEMO VIDEO

That's the end of this tutorial. I hope this tutorial will help you with what you are looking for and enhance your PHP and SQLite3 Integration knowledge. You can also download the source code zip file I created for this tutorial to do some more experiments. The download button is located below this article.

Explore more on this website for Tutorials and Free Source Codes.

Happy Coding :)