Creating an SQLite Custom CRUD Class in PHP Tutorial

In this tutorial, you will learn how to create a Custom CRUD Class of SQLite Queries using PHP Language. This tutorial aims to provide IT/CS students and new programmers with a reference for enhancing their knowledge and programming capabilities using PHP and SQLite3 Database. Here, snippets for achieving the objectives of this tutorial are provided. Also, a working sample source code zip file that demonstrates the usage of the Custom CRUD Class is provided and free to download.

What is the purpose of Custom SQLite CRUD Class in PHP?

The Custom SQLite Class in PHP is a simple PHP object that developers can use for handling database queries and execution in a PHP Project. Since the object is for CRUD (Create, Read, Update, and delete) operations of an application or program, the object contains the methods for simplifying the queries for the said operations. It can help developers to write codes or develop their projects easier and faster.

How to Create an SQLite Custom CRUD Class in PHP?

Well, PHP version 5 or later version comes with SQLite3 class which is an object that interfaces SQLite version 3 Database. You can enable this class or object by enabling the sqlite3 extension on your php.ini file. See the following image for enabling or uncommenting the said extension:

PHP.INI - SQLITE3 Extesion

Next, before we create the custom CRUD class, we must create first the database connection object for the application and the SQLite3. The following snippet is an example of creating the SQLite3 Database Connection in PHP. The database connection object provided below contains also the method that creates the database table.

  1. <?php
  2. class Database extends SQLite3 {
  3. public function __construct($db_file_path=""){
  4. if(empty($db_file_path)){
  5. throw new ErrorException("SQLite DB file is not set.");
  6. }
  7. $this->open($db_file_path);
  8. if($this->lastErrorCode() > 0){
  9. throw new ErrorException("Database Connection failed.");
  10. }
  11. $this->create_post_table();
  12. }
  13.  
  14. public function create_post_table(){
  15. /**
  16.   * Creating a sample post table
  17.   */
  18. $post_tbl = "CREATE TABLE IF NOT EXISTS `posts`
  19. (
  20. `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  21. `title` text NOT NULL,
  22. `meta_description` text NOT NULL,
  23. `content` text NULL DEFAULT NULL,
  24. `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  25. `updated_at` datetime NULL DEFAULT NULL
  26. )
  27. ";
  28. $this->exec($post_tbl);
  29. if($this->lastErrorCode()){
  30. throw new ErrorException($this->lastErrorMsg(), $this->lastErrorCode());
  31. }
  32. /**
  33.   * POST update datetime trigger
  34.   */
  35. $post_dt_trigger = "CREATE TRIGGER IF NOT EXISTS [UpdateAT]
  36. AFTER
  37. UPDATE
  38. ON `posts`
  39. FOR EACH ROW
  40. BEGIN
  41. UPDATE `posts` set `updated_at` = CURRENT_TIMESTAMP where `id`=OLD.`id`;
  42. END
  43. ";
  44. $this->exec($post_dt_trigger);
  45. if($this->lastErrorCode()){
  46. throw new ErrorException($this->lastErrorMsg(), $this->lastErrorCode());
  47. }
  48. }
  49.  
  50. public function __destruct(){
  51. /**
  52.   * destruct the sample class object
  53.   */
  54. $this->close();
  55. }
  56. }
  57. if(!is_dir("./database"))
  58. mkdir("./database");
  59. // $database = new Database();
  60.  

Next, we'll now create the custom CRUD class or object. The object must derive the SQLite3 class or the Custom SQLite3 Database class so we can access the properties of the SQLite for executing the database queries. Check out the snippet below.

  1. <?php
  2. class DBCRUD extends DATABASE{
  3. public function insert($tbl="", $data = []){
  4. if(empty($tbl)){
  5. throw new ErrorException("Insertion Table Name must be specified.");
  6. }
  7. if(empty($data)){
  8. throw new ErrorException("Insertion required column and data must be specified.");
  9. }
  10.  
  11. $insert_columns = [];
  12. $insert_values = [];
  13. foreach($data as $k => $v){
  14. if(!is_numeric($v) && !is_array($data[$k]) && !empty($v)){
  15. $v = $this->escapeString(addslashes($v));
  16. }
  17. $insert_columns[] = $k;
  18. $insert_values[] = $v;
  19. }
  20.  
  21. $tbl_columns = implode("`, `", $insert_columns);
  22. $tbl_values = implode("', '", $insert_values);
  23. $insert_sql = "INSERT INTO `{$tbl}`
  24. (`{$tbl_columns}`)
  25. VALUES
  26. ('{$tbl_values}')
  27. ";
  28. try{
  29. $this->exec($insert_sql);
  30. if(!$this->lastErrorCode()){
  31. $resp['status'] = "success";
  32. $last_id = $this->lastInsertRowid();
  33. $resp['id'] = $last_id;
  34. }else{
  35. throw new ErrorException($this->lastErrorMsg(), $this->lastErrorCode());
  36. }
  37. }catch(Exception $e){
  38. $resp['status'] = "failed";
  39. $resp['error'] = $e->getMessage();
  40.  
  41. }
  42. return $resp;
  43. }
  44. public function update($tbl="", $data = [],$where=[]){
  45. if(empty($tbl)){
  46. throw new ErrorException("Update Table Name must be specified.");
  47. }
  48. if(empty($data)){
  49. throw new ErrorException("Update required column and data must be specified.");
  50. }
  51.  
  52. if(empty($where)){
  53. throw new ErrorException("Update Where Array must be specified.");
  54. }
  55. $update_data= "";
  56. foreach($data as $k => $v){
  57. if(!is_numeric($v) && !is_array($data[$k]) && !empty($v)){
  58. $v = $this->escapeString(addslashes($v));
  59. }
  60. if(!empty($update_data)) $update_data .= ", ";
  61. $update_data .= "`{$k}`='{$v}'";
  62. }
  63. $where_data= "";
  64. foreach($where as $k => $v){
  65. if(!is_numeric($v) && !is_array($where[$k]) && !empty($v)){
  66. $v = $this->escapeString(addslashes($v));
  67. }
  68. $type = "and";
  69. $compare = "=";
  70. $value = $v;
  71. if(is_array($v)){
  72. if(isset($v['value']))
  73. $value = $v['value'];
  74. if(isset($v['type']))
  75. $type = $v['type'];
  76. if(isset($v['compare']))
  77. $compare = $v['compare'];
  78. }
  79. if(!empty($where_data)) $where_data .= " {$type} ";
  80. $where_data .= "`{$k}` {$compare} ".(!empty($value) ? "'{$value}'" : "");
  81. }
  82.  
  83. $update_sql = "UPDATE `{$tbl}` set {$update_data} WHERE {$where_data}";
  84. try{
  85. $this->exec($update_sql);
  86. if(!$this->lastErrorCode()){
  87. $resp['status'] = "success";
  88. }else{
  89. throw new ErrorException($this->lastErrorMsg(), $this->lastErrorCode());
  90. }
  91. }catch(Exception $e){
  92. $resp['status'] = "failed";
  93. $resp['error'] = $e->getMessage();
  94.  
  95. }
  96. return $resp;
  97. }
  98. public function delete($tbl="", $where=[]){
  99. if(empty($tbl)){
  100. throw new ErrorException("Table Name for deleting must be specified.");
  101. }
  102. if(empty($where)){
  103. throw new ErrorException("Where Array for deleting must be specified.");
  104. }
  105. $where_data= "";
  106. foreach($where as $k => $v){
  107. if(!is_numeric($v) && !is_array($where[$k]) && !empty($v)){
  108. $v = $this->escapeString(addslashes($v));
  109. }
  110. $type = "and";
  111. $compare = "=";
  112. $value = $v;
  113. if(is_array($v)){
  114. if(isset($v['value']))
  115. $value = $v['value'];
  116. if(isset($v['type']))
  117. $type = $v['type'];
  118. if(isset($v['compare']))
  119. $compare = $v['compare'];
  120. }
  121. if(!empty($where_data)) $where_data .= " {$type} ";
  122. $where_data .= "`{$k}` {$compare} ".(!empty($value) ? "'{$value}'" : "");
  123. }
  124. $delete_sql = "DELETE FROM `{$tbl}` where {$where_data}";
  125. try{
  126. $this->exec($delete_sql);
  127. if(!$this->lastErrorCode()){
  128. $resp['status'] = "success";
  129. }else{
  130. throw new ErrorException($this->lastErrorMsg(), $this->lastErrorCode());
  131. }
  132. }catch(Exception $e){
  133. $resp['status'] = "failed";
  134. $resp['error'] = $e->getMessage();
  135.  
  136. }
  137. return $resp;
  138. }
  139. public function getSingle($tbl, $where=[], $select = []){
  140. if(empty($tbl)){
  141. throw new ErrorException("Table Name for fetching data must be specified.");
  142. }
  143. if(empty($where)){
  144. throw new ErrorException("Where Array for fetching data must be specified.");
  145. }
  146. $where_data= "";
  147. foreach($where as $k => $v){
  148. if(!is_numeric($v) && !is_array($where[$k]) && !empty($v)){
  149. $v = $this->escapeString(addslashes($v));
  150. }
  151. $type = "and";
  152. $compare = "=";
  153. $value = $v;
  154. if(is_array($v)){
  155. if(isset($v['value']))
  156. $value = $v['value'];
  157. if(isset($v['type']))
  158. $type = $v['type'];
  159. if(isset($v['compare']))
  160. $compare = $v['compare'];
  161. }
  162. if(!empty($where_data)) $where_data .= " {$type} ";
  163. $where_data .= "`{$k}` {$compare} ".(!empty($value) ? "'{$value}'" : "");
  164. }
  165. $select_column = "";
  166. if(!empty($select)){
  167. foreach($select as $k => $v){
  168. if(!is_numeric($v) && !is_array($select[$k]) && !empty($v)){
  169. $v = $this->escapeString(addslashes($v));
  170. }
  171. if(is_array($v) && is_numeric($v) && empty($v))
  172. continue;
  173. if(!empty($select_column)) $select_column .= ", ";
  174. $select_column .= "`{$v}`";
  175. }
  176. }else{
  177. $select_column = "*";
  178. }
  179. $selectSingle_sql = "SELECT {$select_column} FROM `{$tbl}` WHERE {$where_data} ";
  180. // return $selectSingle_sql;
  181. $query = $this->query($selectSingle_sql);
  182. $data = $query->fetchArray(SQLITE3_ASSOC);
  183. return !empty($data) ? $data : [];
  184. }
  185. public function getResults($tbl, $select = [], $where=[], $additional_qry = ""){
  186. if(empty($tbl)){
  187. throw new ErrorException("Table Name for fetching data must be specified.");
  188. }
  189. $where_data= "";
  190. foreach($where as $k => $v){
  191. if(!is_numeric($v) && !is_array($where[$k]) && !empty($v)){
  192. $v = $this->escapeString(addslashes($v));
  193. }
  194. $type = "and";
  195. $compare = "=";
  196. $value = $v;
  197. if(is_array($v)){
  198. if(isset($v['value']))
  199. $value = $v['value'];
  200. if(isset($v['type']))
  201. $type = $v['type'];
  202. if(isset($v['compare']))
  203. $compare = $v['compare'];
  204. }
  205. if(!empty($where_data)) $where_data .= " {$type} ";
  206. $where_data .= "`{$k}` {$compare} ".(!empty($value) ? "'{$value}'" : "");
  207. }
  208. $select_column = "";
  209. if(!empty($select)){
  210. foreach($select as $k => $v){
  211. if(!is_numeric($v) && !is_array($select[$k]) && !empty($v)){
  212. $v = $this->escapeString(addslashes($v));
  213. }
  214. if(is_array($v) && is_numeric($v) && empty($v))
  215. continue;
  216. if(!empty($select_column)) $select_column .= ", ";
  217. $select_column .= "`{$v}`";
  218. }
  219. }else{
  220. $select_column = "*";
  221. }
  222. $select_sql = "SELECT {$select_column} FROM `{$tbl}` ".(!empty($where_data) ? " WHERE {$where_data} " : "");
  223. if(!empty($additional_qry))
  224. $select_sql .= " {$additional_qry}";
  225. // return $select_sql;
  226. $query = $this->query($select_sql);
  227. $data = [];
  228. while($row = $query->fetchArray(SQLITE3_ASSOC)){
  229. $data[] = $row;
  230. }
  231. return !empty($data) ? $data : [];
  232. }
  233. }
  234. $dbcrud = new DBCRUD("./database/test.db");
  235. ?>

In the snippet above, the DBCRUD class represents the custom CRUD class that derives from the Custom SQLite Database Class called Database class. The class contains multiple methods that can be used for inserting, updating, reading/fetching, and deleting data from the database. Using the object methods, executing queries will be easier.

Object Parameters Usage

Table Parameter [$tbl]

This parameter is required for all methods listed on the object. It requires you to enter the database table name you want to manage or where you will execute the database query.

Data Parameter [$data]

This parameter is required in the insert and update method of the object. The parameter contains an array of data to insert or update on the database table. The array key must match the column name on the table and the value as the value.

Where Parameter [$where]

This parameter is required in the update, getSingle, and delete methods but optional in getResults method. It is an array of data for matching the data to manage on the table.

Array Format

  1. <?php
  2. $where = ["id" => 1];
  3. ?>

Or

  1. <?php
  2. $where = array(
  3. "id" => array(
  4. "value"=>1,
  5. "compare" => "="
  6. ),
  7. "title" => array(
  8. "value"=>"%sample%",
  9. "compare" => "LIKE",
  10. "type" => "OR"
  11. )
  12. );
  13. ?>

Select Parameter [$select]

This parameter is an array of table column names to select.

Additional Query Statement Parameter [$additional_qry]

This parameter is a string you wish to add in getResults method query statement such as the "Order By" clause.

Example

I have provided a working application source code zip file on this site. The application is a simple application with CRUD features. It demonstrates the usage of the Custom SQLite3 CRUD Class that I provided above. The download button is located below this article's content.

Application Snapshots

Here are the snapshots of some interfaces of the sample application I have provided.

List Page

PHP CRUD using SQLite3 Database

Add Form Oage

PHP CRUD using SQLite3 Database

Edit Form Page

PHP CRUD using SQLite3 Database

View Page

PHP CRUD using SQLite3 Database

DEMO VIDEO

That's it! I hope this Creation of an SQLite3 Custom CRUD Class in PHP Tutorial will help you with what you are looking for and will be useful for your current and future PHP Projects.

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

Happy Coding =)

Comments

Submitted byoleteacheron Thu, 02/16/2023 - 08:31

Thank you, thank you!

We enjoy your SQLite tutorials and use them in the classroom often.

Please keep them flowing to us:)

Submitted byvaz rwanda (not verified)on Thu, 03/02/2023 - 17:26

very nice app

Add new comment