Creating CRUD Operation using PHP OOP Approach and SQLite3 Database Tutorial

Submitted by oretnom23 on Saturday, October 8, 2022 - 16:14.

Introduction

In this tutorial, you will learn how to create a CRUD (Create, Read, Update, and Delete) Operation using PHP OOP Approach and SQLite3 Database. This tutorial aims to provide the IT/CS students or those who are new to PHP Language a reference to learn with for learning and enhancing their programming capabilities. Here, I will show you the basic way to write a CRUD Operation program in PHP and SQLite. Snippets and sample source codes are also available or provided in this tutorial.

What is PHP?

PHP stands for Hypertext Preprocessor language. It is an open-source server-side programming language. A lot of developers or programmers use this programming language for creating websites with dynamic content. In addition, it is a general-purpose language that you can employ to create a variety of tasks, such as Graphical User Interfaces (GUIs).

What is SQLite3?

You can quickly build and utilize a database with the small using the free SQLite3 database. Despite not having all the features of a full-featured database, SQLite3 supports a surprising amount of the SQL standard and is the best option for both developers who require a straightforward database engine to integrate into their applications and those just learning SQL. Because of this, SQLite has gained enormous popularity among mobile app developers.

Getting Started

Before we start the coding part of this tutorial, please make sure to download any virtual server software such as XAMPP/WAMP to run our PHP Scripts on your local machine. After the successful installation, enable the sqlite3 library in your php.ini file. Lastly, make sure to run your Apache/Nginx or any equivalent of your download virtual server. In XAMPP/WAMP, open the software's Control Panel and start the Apache.

Creating the Database Class

The following script is a PHP file. It is written using the OOP (Object-Orient Programming) Approach in PHP which contains the database class. The class extends the SQLite3 Library and contains the connection and query objects. In my case, I save the file as db-connect.php.

  1. <?php
  2. if(!is_dir('./db'))
  3.     mkdir('./db');
  4. if(!defined('db_file')) define('db_file','./db/dummy_db.db');
  5. function my_udf_md5($string) {
  6.     return md5($string);
  7. }
  8.  
  9. Class Database extends SQLite3{
  10.     private $allowed_field;
  11.     function __construct(){
  12.          $this->open(db_file);
  13.          $this->exec("PRAGMA foreign_keys = ON;");
  14.          $this->exec("CREATE TABLE IF NOT EXISTS `member_list` (
  15.             `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  16.             `fullname` INTEGER NOT NULL,
  17.             `email` TEXT NOT NULL,
  18.             `contact` TEXT NOT NULL,
  19.             `address` TEXT NOT NULL,
  20.             `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  21.             `updated_at` TIMESTAMP DEFAULT NULL
  22.         )"); 
  23.         $this->allowed_field = ['ID', 'fullname', 'email', 'contact', 'address'];
  24.         $this->exec("CREATE TRIGGER IF NOT EXISTS member_updated_at AFTER UPDATE on `member_list`
  25.         BEGIN
  26.             UPDATE `member_list` SET `updated_at` = CURRENT_TIMESTAMP where ID = ID;
  27.         END
  28.         ");
  29.     }
  30.     function __destruct(){
  31.          $this->close();
  32.     }
  33.     function sanitize_string($string = ""){
  34.         if(!is_numeric($string)){
  35.             $string = addslashes($this->escapeString($string));
  36.         }
  37.         return $string;
  38.     }
  39.     public function insert($data = []){
  40.         if(!is_array($data)){
  41.             return ['status' => 'failed', "error" => "Data must be an array."];
  42.         }else{
  43.             if(count($data) <= 0)
  44.             return ['status' => 'failed', "error" => "Data is empty"];
  45.  
  46.             $fields = "";
  47.             $values = "";
  48.             foreach($data as $k => $v){
  49.                 if(in_array($k, $this->allowed_field) && $k != "ID"){
  50.                     $v = $this->sanitize_string($v);
  51.                     if(!empty($fields)) $fields .= ", ";
  52.                     $fields .= "`{$k}`";
  53.                     if(!empty($values)) $values .= ", ";
  54.                     $values .= "'{$v}'";
  55.                 }
  56.             }
  57.             if(empty($fields))
  58.             return ['status' => 'failed', "error" => "Given data fields are not allowed"];
  59.  
  60.             $sql = "INSERT INTO `member_list` ({$fields}) VALUES ({$values})";
  61.             $save = $this->query($sql);
  62.             if($save){
  63.                 return ['status' => 'success'];
  64.             }else{
  65.                 return ['status' => 'failed', "error" => $this->lastErrorMsg()];
  66.             }
  67.         }
  68.     }
  69.  
  70.     public function get_results(){
  71.         $sql = "SELECT * FROM `member_list` order by ID asc";
  72.         $query = $this->query($sql);
  73.         $data = [];
  74.         while($row = $query->fetchArray()){
  75.             $data[] = $row;
  76.         }
  77.         return ['num_rows' => count($data), 'data' => $data];
  78.     }
  79.     public function get_single_by_id($id){
  80.         $id = $this->sanitize_string($id);
  81.         $sql = "SELECT * FROM `member_list` where `ID` = '{$id}'";
  82.         $query = $this->query($sql);
  83.         return $query->fetchArray();
  84.     }
  85.  
  86.     public function update($data=[]){
  87.         if(!is_array($data)){
  88.             return ['status' => 'failed', "error" => "Data must be an array."];
  89.         }else{
  90.             if(count($data) <= 0)
  91.             return ['status' => 'failed', "error" => "Data is empty"];
  92.  
  93.             $update_data = "";
  94.             foreach($data as $k => $v){
  95.                 if(in_array($k, $this->allowed_field) && $k != "ID"){
  96.                     $v = $this->sanitize_string($v);
  97.                     if(!empty($update_data)) $update_data .= ", ";
  98.                     $update_data .= "`{$k}`= '{$v}'";
  99.                 }
  100.             }
  101.             if(empty($update_data))
  102.             return ['status' => 'failed', "error" => "Given data fields are not allowed"];
  103.             $id = $this->sanitize_string($data['id']);
  104.             $sql = "UPDATE `member_list` set {$update_data} where ID = '{$id}'";
  105.             $save = $this->query($sql);
  106.             if($save){
  107.                 return ['status' => 'success'];
  108.             }else{
  109.                 return ['status' => 'failed', "error" => $this->lastErrorMsg()];
  110.             }
  111.         }
  112.     }
  113.     public function delete($id=""){
  114.         if(empty($id))
  115.         return ['status' => 'failed', "error" => "ID is required."];
  116.         if(!is_numeric($id))
  117.         return ['status' => 'failed', "error" => "ID cannot be a string."];
  118.         $sql = "DELETE FROM `member_list` where `ID` = '{$id}'";
  119.         $delete = $this->query($sql);
  120.         if($delete){
  121.             return ['status' => 'success'];
  122.         }else{
  123.             return ['status' => 'failed', "error" => $this->lastErrorMsg()];
  124.         }
  125.  
  126.     }
  127. }
  128. ?>

Creating the APIs

The following script is also a PHP file that holds the "Actions Class" and contains all the functions or objects needed for this sample CRUD Application. This class extends the Database Class to execute queries. I save this file as functions.php.

  1. <?php
  2. session_start();
  3. require_once('db-connect.php');
  4.  
  5. Class Actions extends Database{
  6.     function __construct(){
  7.         parent::__construct();
  8.     }
  9.     function __destruct(){
  10.         parent::__destruct();
  11.     }
  12.  
  13.     public function insert_member(){
  14.         $insert = $this->insert($_POST);
  15.         if($insert['status'] == 'success'){
  16.             $_SESSION['success_msg'] = "New member has been added successfully.";
  17.             header("location: ./");
  18.             exit;
  19.         }elseif($insert['status'] =='failed' && isset($insert['error'])){
  20.             $_SESSION['form_error_msg'] = $insert['error'];
  21.         }else{
  22.             $_SESSION['form_error_msg'] = "An error occurred while inserting data due to unknown reason";
  23.         }
  24.         $data = [];
  25.         foreach($_POST as $k => $v){
  26.             $data[$k] = $v;
  27.         }
  28.     }
  29.     public function member_list(){
  30.         return $this->get_results();
  31.     } 
  32.  
  33.     public function get_member_by_id($id){
  34.         return $this->get_single_by_id($id);
  35.     }
  36.  
  37.     public function update_member(){
  38.         $update = $this->update($_POST);
  39.         if($update['status'] == 'success'){
  40.             $_SESSION['success_msg'] = "Member Details has been updated successfully.";
  41.             header("location: ./");
  42.             exit;
  43.         }elseif($update['status'] == 'failed' && isset($update['error'])){
  44.             $_SESSION['form_error_msg'] = $update['error'];
  45.         }else{
  46.             $_SESSION['form_error_msg'] = "An error occurred while updating data due to unknown reason";
  47.         }
  48.         $data = [];
  49.         foreach($_POST as $k => $v){
  50.             $data[$k] = $v;
  51.         }
  52.     }
  53.  
  54.     public function delete_member($id){
  55.         $delete = $this->delete($id);
  56.         if($delete['status'] == 'success'){
  57.             $_SESSION['success_msg'] = "Member has been deleted successfully.";
  58.             header("location: ./");
  59.             exit;
  60.         }elseif($delete['status'] == 'failed' && isset($delete['error'])){
  61.             $_SESSION['error_msg'] = $delete['error'];
  62.         }else{
  63.             $_SESSION['error_msg'] = "An error occurred while deleting data due to unknown reason";
  64.         }
  65.     }
  66. }
  67.  
  68. $action = new Actions();
  69. ?>

Creating the Interface and Form

The following file script consists of HTML and PHP scripts. It has the code for the elements needed for the form and the page interface of the sample site. It uses CDN for the external libraries for better user design such as the Bootstrap Framework. You will need to have an internet connection to run the page design properly or as intended. I save this file as index.php.

  1. <?php
  2. require_once("functions.php");
  3. if($_SERVER['REQUEST_METHOD'] == "POST"){
  4.     if(empty($_POST['id'])){
  5.        $action->insert_member(); 
  6.     }else{
  7.        $action->update_member(); 
  8.  
  9.     }
  10. }
  11. if(isset($_GET['action'])){
  12.     switch($_GET['action']){
  13.         case 'edit':
  14.             $data = $action->get_member_by_id($_GET['id']);
  15.             break;
  16.         case 'delete':
  17.             $data = $action->delete_member($_GET['id']);
  18.             break;
  19.     }
  20. }
  21. ?>
  22. <!DOCTYPE html>
  23. <html lang="en">
  24. <head>
  25.     <meta charset="UTF-8">
  26.     <meta http-equiv="X-UA-Compatible" content="IE=edge">
  27.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  28.     <title>CRUD using PHP and SQLite</title>
  29.     <!-- Fontawsome -->
  30.     <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css" integrity="sha512-xh6O/CkQoPOWDdYTDqeRdPCVd1SpvCA9XXcUnZS2FmJNp1coAFzvtCN9BmamE+4aHK8yyUHUSCcJHgXloTyT2A==" crossorigin="anonymous" referrerpolicy="no-referrer" />
  31.     <script src="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/js/all.min.js" integrity="sha512-naukR7I+Nk6gp7p5TMA4ycgfxaZBJ7MO5iC3Fp6ySQyKFHOGfpkSZkYVWV5R7u7cfAicxanwYQ5D1e17EfJcMA==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
  32.     <!-- Bootstrap CSS -->
  33.     <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" integrity="sha384-Zenh87qX5JnK2Jl0vWa8Ck2rdkQ2Bzep5IDxbcnCeuOxjzrPF/et3URy9Bv1WTRi" crossorigin="anonymous">
  34.     <!-- jQuery -->
  35.     <script src="https://code.jquery.com/jquery-3.6.1.min.js" integrity="sha256-o88AwQnZB+VDvE9tvIXrMQaPlFFSUTR+nldQm1LuPXQ=" crossorigin="anonymous"></script>
  36.     <!-- Bootstrap Bundle JS -->
  37.     <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-OERcA2EqjJCMA+/3y+gxIOqMEjwtxJY7qPCqsdltbNJuaOe923+mo//f6V8Qbsw3" crossorigin="anonymous"></script>
  38.     <style>
  39.         html,body{
  40.             height:100%;
  41.             width:100%;
  42.         }
  43.  
  44.     </style>
  45. </head>
  46. <body>
  47.     <main>
  48.         <nav class="navbar navbar-expand-lg navbar-dark bg-primary bg-gradient">
  49.             <div class="container">
  50.                 <a class="navbar-brand" href="#">
  51.                 CRUD using PHP and SQLite
  52.                 </a>
  53.                 <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
  54.                 <span class="navbar-toggler-icon"></span>
  55.                 </button>
  56.                 <div class="collapse navbar-collapse" id="navbarNav">
  57.                     <ul class="navbar-nav">
  58.                         <li class="nav-item">
  59.                             <a class="nav-link" aria-current="page" href="./">Home</a>
  60.                         </li>
  61.                     </ul>
  62.                 </div>
  63.                 <div>
  64.                     <a href="https://sourcecodester.com" class="text-decoration-none text-light fw-bold">SourceCodester</a>
  65.                 </div>
  66.             </div>
  67.         </nav>
  68.         <div class="container my-3 py-2">
  69.             <div class="row">
  70.                 <div class="col-lg-5 col-md-6 col-sm-12">
  71.                     <div class="card rounded-0 shadow">
  72.                         <div class="card-header">
  73.                             <div class="card-title"><b>Form Panel</b></div>
  74.                         </div>
  75.                         <div class="card-body rounded-0">
  76.                             <div class="container-fluid">
  77.                                 <form id="sample-form" action="" method="POST">
  78.                                     <?php if(isset($_SESSION['form_error_msg'])): ?>
  79.                                         <div class="alert alert-danger rounded-0">
  80.                                             <?= $_SESSION['form_error_msg'] ?>
  81.                                         </div>
  82.                                     <?php unset($_SESSION['form_error_msg']); ?>
  83.                                     <?php endif; ?>
  84.                                     <input type="hidden" name="id" value="<?= isset($data['ID']) ? $data['ID'] : '' ?>">
  85.                                     <div class="mb-3">
  86.                                         <label for="fullname" class="control-label fw-bold">Fullname</label>
  87.                                         <input type="text" class="form-control rounded-0" id="fullname" name="fullname" value="<?= isset($data['fullname']) ? $data['fullname'] : '' ?>" required="required">
  88.                                     </div>
  89.                                     <div class="mb-3">
  90.                                         <label for="email" class="control-label fw-bold">Email</label>
  91.                                         <input type="email" class="form-control rounded-0" id="email" name="email" value="<?= isset($data['email']) ? $data['email'] : '' ?>" required="required">
  92.                                     </div>
  93.                                     <div class="mb-3">
  94.                                         <label for="contact" class="control-label fw-bold">Contact</label>
  95.                                         <input type="text" class="form-control rounded-0" id="contact" name="contact" value="<?= isset($data['contact']) ? $data['contact'] : '' ?>" required="required">
  96.                                     </div>
  97.                                     <div class="mb-3">
  98.                                         <label for="address" class="control-label fw-bold">Address</label>
  99.                                         <textarea rows="3" class="form-control rounded-0" id="address" name="address" required="required"><?= isset($data['address']) ? $data['address'] : '' ?></textarea>
  100.                                     </div>
  101.                                 </form>
  102.                             </div>
  103.                         </div>
  104.                         <div class="card-header rounded-0 py-2">
  105.                             <div class="d-flex justify-content-center">
  106.                                 <button class="btn btn-primary btn-sm rounded-0 mx-1" form="sample-form"><i class="fa fa-save"></i> Save</button>
  107.                                 <button class="btn btn-sm rounded-0 btn-default border border-dark mx-1" id="reset-form" type="button"><i class="fa fa-times"></i> Cancel</button>
  108.                             </div>
  109.                         </div>
  110.                     </div>
  111.                 </div>
  112.                 <div class="col-lg-7 col-md-6 col-sm-12">
  113.                     <?php if(isset($_SESSION['success_msg'])): ?>
  114.                         <div class="alert alert-success rounded-0">
  115.                             <?= $_SESSION['success_msg'] ?>
  116.                         </div>
  117.                     <?php unset($_SESSION['success_msg']); ?>
  118.                     <?php endif; ?>
  119.                     <?php if(isset($_SESSION['error_msg'])): ?>
  120.                         <div class="alert alert-danger rounded-0">
  121.                             <?= $_SESSION['error_msg'] ?>
  122.                         </div>
  123.                     <?php unset($_SESSION['error_msg']); ?>
  124.                     <?php endif; ?>
  125.  
  126.                     <div class="card shadow rounded-0">
  127.                         <div class="card-header">
  128.                             <div class="card-title"><b>Member Lists</b></div>
  129.                         </div>
  130.                         <div class="card-body">
  131.                             <div class="container-fluid">
  132.                                 <table class="table table-bordered table-hover table-striped">
  133.                                     <colgroup>
  134.                                         <col width="5%">
  135.                                         <col width="20%">
  136.                                         <col width="15%">
  137.                                         <col width="15%">
  138.                                         <col width="30%">
  139.                                         <col width="15%">
  140.                                     </colgroup>
  141.                                     <thead>
  142.                                         <tr class="bg-primary text-light">
  143.                                             <th class="text-center">#</th>
  144.                                             <th class="text-center">Name</th>
  145.                                             <th class="text-center">Email</th>
  146.                                             <th class="text-center">Contact</th>
  147.                                             <th class="text-center">Address</th>
  148.                                             <th class="text-center">Action</th>
  149.                                         </tr>
  150.                                     </thead>
  151.                                     <tbody>
  152.                                         <?php
  153.                                         $members = $action->member_list();
  154.                                         ?>
  155.                                         <?php if($members['num_rows'] > 0): ?>
  156.                                             <?php foreach($members['data'] as $row): ?>
  157.                                                 <tr>
  158.                                                     <th class="text-center"><?= $row['ID'] ?></th>
  159.                                                     <td class="p-1"><?= $row['fullname'] ?></td>
  160.                                                     <td class="p-1"><?= $row['email'] ?></td>
  161.                                                     <td class="p-1"><?= $row['contact'] ?></td>
  162.                                                     <td class="p-1"><?= $row['address'] ?></td>
  163.                                                     <td class="p-1 text-center">
  164.                                                         <a href="./?action=edit&id=<?= $row['ID'] ?>" class="btn btn-sm btn-outline-primary"><i class="fa fa-edit" title="Edit Member"></i></a>
  165.                                                         <a href="./?action=delete&id=<?= $row['ID'] ?>" class="btn btn-sm btn-outline-danger" onclick="if(confirm(`Are you sure to delete this member?`) === false) event.preventDefault();"><i class="fa fa-trash" title="Delete Member"></i></a>
  166.                                                     </td>
  167.                                                 </tr>
  168.                                             <?php endforeach; ?>
  169.                                         <?php else: ?>
  170.                                             <tr>
  171.                                                 <th class="text-center p-1" colspan="6">No result</th>
  172.                                             </tr>
  173.                                         <?php endif; ?>
  174.                                     </tbody>
  175.                                 </table>
  176.                             </div>
  177.                         </div>
  178.                     </div>
  179.                 </div>
  180.             </div>
  181.         </div>
  182.     </main>
  183. </body>
  184. <script>
  185.     $(document).ready(function(){
  186.         $('#reset-form').click(function(e){
  187.             e.preventDefault()
  188.             if($("[name='id']").val() !== ""){
  189.                 location.replace("./")
  190.             }
  191.             $('#sample-form')[0].reset()
  192.         })
  193.     })
  194. </script>
  195. </html>

Snapshot

Here's the sample snapshot of this sample web application with CRUD functionalities.

PHP CRUD APP using SQLite3 Database

DEMO VIDEO

That's it! You can now test this sample web application on your local machine that demonstrates the CRUD Operation using PHP OOP Approach and SQLite3 Database. You can also download this sample application's complete source code on this website for free. The download button is located below this article. Feel free to download and modify it.

That's the end of this tutorial. I hope this will help you with what you are looking for and that you'll find this useful for your current and future PHP Projects.

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

Happy Coding :)

Tags

Add new comment

About text formats
  • 704 views