Useful database helper class to generate CRUD statements using PHP and Mysql

For my AngularJS applications I use PHP as my server side language which mainly interacts with the MySQL or Oracle database. I need to created a database helper class using PHP to collect all recurring database operations into a single class, so that we can easily use it whenever we need to select, insert, update or delete record from database.
This database helper class generate the CRUD statements dynamically and always output the response in a standard format. Hence reducing the recurring tasks load on programmer. This also makes bug tracking task easy as everything stores in a central location.
**I thought about sharing my work. I hope you all like it.**Features:
- Well tested code.
- Secured against SQL injections by using PHP Data Objects(PDO) and Prepared Statements.
- Always returns objects which you can directly use to inform the user.
- Includes proper error handling and information broadcast.
The Framework:
Mainly in SQL database we use the following 4 operations to manage our data (DML Operations)
C – Create
R – Read
U – Update
D – Delete
R – Read: The following is a simple read or select operation in SQL
select * from customers;
Now we will generate similar select statement and return the selected data using the followin helper function
function select($table, $where){ | |
try{ | |
$a = array(); | |
$w = ""; | |
foreach ($where as $key => $value) { | |
$w .= " and " .$key. " like :".$key; | |
$a[":".$key] = $value; | |
} | |
$stmt = $this->db->prepare("select * from ".$table." where 1=1 ". $w); | |
$stmt->execute($a); | |
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC); | |
if(count($rows)<=0){ | |
$response["status"] = "warning"; | |
$response["message"] = "No data found."; | |
}else{ | |
$response["status"] = "success"; | |
$response["message"] = "Data selected from database"; | |
} | |
$response["data"] = $rows; | |
}catch(PDOException $e){ | |
$response["status"] = "error"; | |
$response["message"] = 'Select Failed: ' .$e->getMessage(); | |
$response["data"] = null; | |
} | |
return $response; | |
} |
C – Create : The following is a simple create or insert operation in SQL
insert into customers(name,email) values('Angular Code', 'admin@angularcode.com');
After this operation we need to return the caller function the proper data with any associated success or error message. To handle those recurring tasks these CRUD modules will be helpful for you. That’s why i named this databaseHelper(dbHelper) class.
For insert operation we need the table name, the values to insert and mandatory columns
function insert($table, $columnsArray, $requiredColumnsArray) { | |
$this->verifyRequiredParams($columnsArray, $requiredColumnsArray); | |
try{ | |
$a = array(); | |
$c = ""; | |
$v = ""; | |
foreach ($columnsArray as $key => $value) { | |
$c .= $key. ", "; | |
$v .= ":".$key. ", "; | |
$a[":".$key] = $value; | |
} | |
$c = rtrim($c,', '); | |
$v = rtrim($v,', '); | |
$stmt = $this->db->prepare("INSERT INTO $table($c) VALUES($v)"); | |
$stmt->execute($a); | |
$affected_rows = $stmt->rowCount(); | |
$response["status"] = "success"; | |
$response["message"] = $affected_rows." row inserted into database"; | |
}catch(PDOException $e){ | |
$response["status"] = "error"; | |
$response["message"] = 'Insert Failed: ' .$e->getMessage(); | |
} | |
return $response; | |
} |
U – Update : Following is a simple update operation in SQL where we update email of the user having id 170
update customers set email = 'info@angularcode.com' where id = 170
For update operation we need the table name, the columns names and the corresponding values to update, the condition on which the update will occure on a record
function update($table, $columnsArray, $where, $requiredColumnsArray){ | |
$this->verifyRequiredParams($columnsArray, $requiredColumnsArray); | |
try{ | |
$a = array(); | |
$w = ""; | |
$c = ""; | |
foreach ($where as $key => $value) { | |
$w .= " and " .$key. " = :".$key; | |
$a[":".$key] = $value; | |
} | |
foreach ($columnsArray as $key => $value) { | |
$c .= $key. " = :".$key.", "; | |
$a[":".$key] = $value; | |
} | |
$c = rtrim($c,", "); | |
$stmt = $this->db->prepare("UPDATE $table SET $c WHERE 1=1 ".$w); | |
$stmt->execute($a); | |
$affected_rows = $stmt->rowCount(); | |
if($affected_rows<=0){ | |
$response["status"] = "warning"; | |
$response["message"] = "No row updated"; | |
}else{ | |
$response["status"] = "success"; | |
$response["message"] = $affected_rows." row(s) updated in database"; | |
} | |
}catch(PDOException $e){ | |
$response["status"] = "error"; | |
$response["message"] = "Update Failed: " .$e->getMessage(); | |
} | |
return $response; | |
} |
D – Delete: Following is a simple delete operation in SQL
delete from customer where id='170'
For delete operation we need the table name and the condition on which the delete will occure. In this case the where clause or condition of delete is mandatory otherwise all records will be deleted from database. Hence we will run the delete script only if atleast one condition is specified.
function delete($table, $where){ | |
if(count($where)<=0){ | |
$response["status"] = "warning"; | |
$response["message"] = "Delete Failed: At least one condition is required"; | |
}else{ | |
try{ | |
$a = array(); | |
$w = ""; | |
foreach ($where as $key => $value) { | |
$w .= " and " .$key. " = :".$key; | |
$a[":".$key] = $value; | |
} | |
$stmt = $this->db->prepare("DELETE FROM $table WHERE 1=1 ".$w); | |
$stmt->execute($a); | |
$affected_rows = $stmt->rowCount(); | |
if($affected_rows<=0){ | |
$response["status"] = "warning"; | |
$response["message"] = "No row deleted"; | |
}else{ | |
$response["status"] = "success"; | |
$response["message"] = $affected_rows." row(s) deleted from database"; | |
} | |
}catch(PDOException $e){ | |
$response["status"] = "error"; | |
$response["message"] = 'Delete Failed: ' .$e->getMessage(); | |
} | |
} | |
return $response; | |
} |
For any application, data is always essential. So to protect and validate our data we have to sanitise it both at the client side(for speed and instant help to the user) and server side(for better security). Here comes the module verifyRequiredParams();
function verifyRequiredParams($inArray, $requiredColumns) { | |
$error = false; | |
$errorColumns = ""; | |
foreach ($requiredColumns as $field) { | |
if (!isset($inArray[$field]) || strlen(trim($inArray[$field])) <= 0) { | |
$error = true; | |
$errorColumns .= $field . ', '; | |
} | |
} | |
if ($error) { | |
$response = array(); | |
$response["status"] = "error"; | |
$response["message"] = 'Required field(s) ' . rtrim($errorColumns, ', ') . ' is missing or empty'; | |
print_r($response); | |
exit; | |
} | |
} |
How to use:
Download the zip file from the download link provided above. Import the customer_php.sql file into your database
Add your database settings to the file “config.php”
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');
define('DB_NAME', 'angularcode_auth');
Following is the template of how to use it
select(table name, where clause as associative array)
insert(table name, data as associative array, mandatory column names as array)
update(table name, column names as associative array, where clause as associative array, mandatory columns as array)
delete(table name, where clause as array)
Here are example methods
$rows = $db->select('customers_php',array());
$rows = $db->select('customers_php',array('id'=>171));
$rows = $db->insert('customers_php',array('name' => 'Ipsita Sahoo', 'email'=>'ipi@angularcode.com'), array('name', 'email'));
$rows = $db->update('customers_php',array('name' => 'Ipsita Sahoo', 'email'=>'email'),array('id'=>'170'), array('name', 'email'));
$rows = $db->delete('customers_php', array('name' => 'Ipsita Sahoo', 'id'=>'227'));
Next tutorial i will create a simple AngularJS application and show you the power of these database helper functions used in a real world app.

Most Popular Posts
- ShopNx - The assistant manager for influencers
- Frontendfun marketplace for software projects
- Complete steps to configure elasticsearch on Ubuntu
- Configure Vultr for Nodejs Deployment
- Appointment Booking Microservice using Javascript Fullstack
- 100+ most effective ways to promote a new blog for free
- Steps to Configure Digital Ocean Droplet for Nodejs Application Deployment
- Appointment Booking using Angularjs, Nodejs, Mongodb
- Send email with PDF attachment using PHP
- Simple task manager application using Angularjs PHP Mysql
- Steps to Configure Amazon EC2 for Nodejs app deployment
- Inventory Manager Using Angularjs Mysql Php
- User authentication using Angularjs, PHP, Mysql
- Demo of a simple CRUD Restful php service used with Angularjs and Mysql
- Simple file upload example using Angularjs
- Generate PDF using PHP from Mysql database
- Creating REST API using Nodejs and consuming in Angularjs
- Simple project demonstrates how to send email using Nodejs
- Voting system similar to stackoverflow using Angularjs PHP and Mysql
- Angularjs datagrid paging, sorting, filter using PHP and Mysql
- Useful database helper class to generate CRUD statements using PHP and Mysql
- Online Shopping Mega Menu using Angularjs, PHP, Mysql
- How to create a facebook style autocomplete using Angularjs
- Steps configuring PHP Cron Jobs - Godaddy
- How to change Mysql password
- A simple Angularjs web app that converts text to url format
- Creating SWAP file on Linux