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

Facebook
Reddit
Twitter
Whatsapp
Cover image

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.

Subscribe my updates via Email