CRUD Example using SQL Scripts in PHP and SQLite3

Introduction

Here we will see how to create CRUD example using SQL scripts in PHP and SQLite3. We can easily execute SQL statement in PHP to perform CRUD operations but when you have lots of SQL statements to create tables then you will find easier to execute SQL scripts rather than executing individual SQL statements one by one in PHP programming. You may also need to insert initial data for testing your application and in such case you would want to execute SQL script to insert data rather than executing statements one by one in PHP programming.

When you need to do some quick PoC or you need to use light-weight database then you can use SQLite.

Prerequisites

Configuring PHP 7 and SQLite3 in Windows Environment

Example with Source Code

Creating Project

Create a root project directory sqlite3-crud. This is the folder where we will put our PHP file and SQL scripts to create CRUD example using SQL scripts in PHP and SQLite3.

Creating SQL Scripts

Create Table Script

Create an SQL script called create-table.sql under sqlite3-crud folder with the below source code.

CREATE TABLE IF NOT EXISTS projects (
    project_id   INTEGER PRIMARY KEY,
    project_name TEXT    NOT NULL
);
 
CREATE TABLE IF NOT EXISTS tasks (
    task_id        INTEGER PRIMARY KEY,
    task_name      TEXT    NOT NULL,
    completed      INTEGER NOT NULL,
    start_date     TEXT,
    completed_date TEXT,
    project_id     INTEGER NOT NULL,
    FOREIGN KEY (
        project_id
    )
    REFERENCES projects (project_id) ON UPDATE CASCADE
                                     ON DELETE CASCADE
);

In the above SQL script we have created two tables. I did not create auto-increment fields for Primary Key but if you want you can create on Primary Key.

Insert Data Script

Create a file called insert-data.sql under sqlite3 folder for inserting data into two tables.

INSERT INTO projects (project_id, project_name) VALUES (1, 'Employee Management System'), (2, 'Leave Management System');
INSERT INTO tasks (task_id, task_name, completed, start_date, completed_date, project_id) VALUES (1, 'Create Employee Information', 0, NULL, NULL, 1), (2, 'Update Employee Information', 0, NULL, NULL, 1), (3, 'Create Casual Leaves', 0, NULL, NULL, 2), (4, 'Create Sick Leaves', 0, NULL, NULL, 2);

In the above SQL script, we have inserted two rows for projects table and four rows for tasks table.

As we are creating example for CRUD operations but we have created only two SQL scripts – create table and insert data (create), and for rest of the operations (read, update, delete), we will execute SQL statement directly from the PHP page.

CRUD Operations

Now we will create a PHP file called sqlite3_crud.php and execute above two scripts for creating tables and inserting data into tables. But for rest of the operations we will perform SQL statements from PHP code.

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('projects.db');
      }
   }
   
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo nl2br("Opened database successfully\n\n");
   }
   
   echo nl2br("Creating Tables --- Start\n\n");
   $sql = file_get_contents('create-table.sql');
   $ret = $db->exec($sql);
   if(!$ret) {
      echo $db->lastErrorMsg();
   } else {
      echo nl2br("Tables created successfully\n\n");
   }
   echo nl2br("Creating Tables --- End\n\n");
   
   echo nl2br("Creating Record --- Start\n\n");
   $sql = file_get_contents('insert-data.sql');
   $ret = $db->exec($sql);
   if(!$ret) {
      echo $db->lastErrorMsg();
   } else {
      echo nl2br("Records created successfully\n\n");
   }
   echo nl2br("Creating Record --- End\n\n");
   
   echo nl2br("Reading Records --- Start\n\n");
   $sql = 'SELECT * FROM projects p, tasks t WHERE t.project_id = p.project_id';
   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
		echo nl2br("Project Id: " . $row['project_id'] . "\n");
		echo nl2br("Project Name: " . $row['project_name'] . "\n");
		echo nl2br("Task Id: " . $row['task_id'] . "\n");
		echo nl2br("Task Name: " . $row['task_name'] . "\n");
		echo nl2br("Completed: " . ($row['completed'] != 0 ? 'Yes' : 'No') . "\n");
		echo nl2br("Start Date: " . ($row['start_date'] == NULL ? 'Not started yet' : $row['start_date']) . "\n");
		if($row['completed_date'] != NULL) {
			echo nl2br("Completed Date: " . $row['completed_date'] . "\n");
		}
		echo nl2br("\n");
   }
   echo nl2br("Reading Records --- End\n\n");
   
   echo nl2br("Updating Records --- Start\n\n");
   $sql = "UPDATE projects SET project_name ='Employee Leave Management System' WHERE project_id = 2";
   $ret = $db->exec($sql);
   if(!$ret) {
      echo $db->lastErrorMsg();
   } else {
      echo nl2br($db->changes(), "Record updated successfully\n\n");
   }
   echo nl2br("Updating Records --- End\n\n");
   
   echo nl2br("Reading Records --- Start\n\n");
   $sql = 'SELECT * FROM projects p, tasks t WHERE t.project_id = p.project_id';
   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
		echo nl2br("Project Id: " . $row['project_id'] . "\n");
		echo nl2br("Project Name: " . $row['project_name'] . "\n");
		echo nl2br("Task Id: " . $row['task_id'] . "\n");
		echo nl2br("Task Name: " . $row['task_name'] . "\n");
		echo nl2br("Completed: " . ($row['completed'] != 0 ? 'Yes' : 'No') . "\n");
		echo nl2br("Start Date: " . ($row['start_date'] == NULL ? 'Not started yet' : $row['start_date']) . "\n");
		if($row['completed_date'] != NULL) {
			echo nl2br("Completed Date: " . $row['completed_date'] . "\n");
		}
		echo nl2br("\n");
   }
   echo nl2br("Reading Records --- End\n\n");
   
   echo nl2br("Deleting Records --- Start\n\n");
   $sql = 'DELETE FROM tasks WHERE task_id = 4';
   $ret = $db->exec($sql);
   if(!$ret){
     echo $db->lastErrorMsg();
   } else {
      echo nl2br($db->changes(), " Record deleted successfully\n\n");
   }
   echo nl2br("Deleting Records --- End\n\n");
   
   echo nl2br("Reading Records --- Start\n\n");
   $sql = 'SELECT * FROM projects p, tasks t WHERE t.project_id = p.project_id';
   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
		echo nl2br("Project Id: " . $row['project_id'] . "\n");
		echo nl2br("Project Name: " . $row['project_name'] . "\n");
		echo nl2br("Task Id: " . $row['task_id'] . "\n");
		echo nl2br("Task Name: " . $row['task_name'] . "\n");
		echo nl2br("Completed: " . ($row['completed'] != 0 ? 'Yes' : 'No') . "\n");
		echo nl2br("Start Date: " . ($row['start_date'] == NULL ? 'Not started yet' : $row['start_date']) . "\n");
		if($row['completed_date'] != NULL) {
			echo nl2br("Completed Date: " . $row['completed_date'] . "\n");
		}
		echo nl2br("\n");
   }
   echo nl2br("Reading Records --- End\n\n");
   
   $db->close();
?>

The above PHP file is self-explanatory and you can easily understand if you have PHP and Database knowledge.

Testing the Application

Now if you execute the above php file – sqlite3_crud.php , then you will see the below output on the browser:

Opened database successfully

Creating Tables --- Start

Tables created successfully

Creating Tables --- End

Creating Record --- Start

Records created successfully

Creating Record --- End

Reading Records --- Start

Project Id: 1
Project Name: Employee Management System
Task Id: 1
Task Name: Create Employee Information
Completed: No
Start Date: Not started yet

Project Id: 1
Project Name: Employee Management System
Task Id: 2
Task Name: Update Employee Information
Completed: No
Start Date: Not started yet

Project Id: 2
Project Name: Leave Management System
Task Id: 3
Task Name: Create Casual Leaves
Completed: No
Start Date: Not started yet

Project Id: 2
Project Name: Leave Management System
Task Id: 4
Task Name: Create Sick Leaves
Completed: No
Start Date: Not started yet

Reading Records --- End

Updating Records --- Start

1Updating Records --- End

Reading Records --- Start

Project Id: 1
Project Name: Employee Management System
Task Id: 1
Task Name: Create Employee Information
Completed: No
Start Date: Not started yet

Project Id: 1
Project Name: Employee Management System
Task Id: 2
Task Name: Update Employee Information
Completed: No
Start Date: Not started yet

Project Id: 2
Project Name: Employee Leave Management System
Task Id: 3
Task Name: Create Casual Leaves
Completed: No
Start Date: Not started yet

Project Id: 2
Project Name: Employee Leave Management System
Task Id: 4
Task Name: Create Sick Leaves
Completed: No
Start Date: Not started yet

Reading Records --- End

Deleting Records --- Start

1Deleting Records --- End

Reading Records --- Start

Project Id: 1
Project Name: Employee Management System
Task Id: 1
Task Name: Create Employee Information
Completed: No
Start Date: Not started yet

Project Id: 1
Project Name: Employee Management System
Task Id: 2
Task Name: Update Employee Information
Completed: No
Start Date: Not started yet

Project Id: 2
Project Name: Employee Leave Management System
Task Id: 3
Task Name: Create Casual Leaves
Completed: No
Start Date: Not started yet

Reading Records --- End

So it is clear from the output that we have successfully created an example on CRUD example using SQL scripts in PHP and SQL

Source Code

source code

Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *