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
Thanks for reading.