Menu

Automate SQL Query – PHP Function – PDO Method

Here is the detailed explanation of Automation of CRUD using the PDO method in core PHP, In PDO method there is a lots of syntax difference than previous versions of database drivers in PHP.

Also we may say it is OOPS based approach to manage database. From this page you may learn : –

  • Creating PDO Object
  • Direct Query execution for fetching data
  • Automated Insert SQL Query using PDO
  • Automated Update SQL Query using PDO
  • Automated Delete SQL Query using PDO
  • Automated Select/View SQL Query using PDO

These are the 4 basic database transaction which are required in development of any application/website. By automating our SQL queries while doing general transactions of CRUD we can reduce our development time and cost which directly effects the budget of and profit in a project.

Automated SQL Queries demonstration using PDO Method :-

Step 1 : First of all we define database credentials and create a function for getting a PDO Object

<?php
// Code by KharePHP.com
// Defining Database Credentials
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS','');
define('DB_NAME','dbname');

// Declaring a function which returns a PDO Object 
function getPDOObject() {
 $dsn = 'mysql:host='.DB_SERVER.';dbname='.DB_NAME.';charset=utf8mb4';
 $user = DB_USER;
 $pass = DB_PASS;
 $pdo = new PDO($dsn, $user, $pass);
 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
 $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
return $pdo;
 }      

Step 2 : Then we create a function that will execute the sql queries to fetch the data from database

<?php

function sqlfetch($query)
 {
     $row=array();
     $pdo=getPDOObject();
     $sql=$pdo->query($query);
     $datas = $sql->fetchAll(PDO::FETCH_ASSOC);
     foreach($datas as $data)
     $row[]=$data;
     return $row;
 }

Step 3 : Now we are declaring a function which will automatically create a Insert SQL Query on the basis of fields present in desired table and the array keys present in posted data array. It will match them both on its own and then associate them accordingly

<?php

/*
    Code by KharePHP.com
    Insert data into the database
    @param string name of the table
    @param array the data for inserting into the table
 */
 function insert($table,$data){
 $pdo=getPDOObject();
 $sql="SHOW COLUMNS FROM ".$table."";
 $columns_query= sqlfetch($sql);
 foreach($columns_query as $coloumn_data)  
 $column_name[]=$coloumn_data['Field'];
 if(!empty($data) && is_array($data)){
     $columns = '';
     $values  = '';
     $i = 0;
     if(!array_key_exists('created',$data)){
         $data['created'] = date("Y-m-d H:i:s");
     }
     if(!array_key_exists('modified',$data)){
         $data['modified'] = date("Y-m-d H:i:s");
     }
     $actual_data=array();
     foreach($data as $key=>$val)
     {
         if(in_array($key,$column_name))
         {
             $actual_data[$key]=$val;
         }
     }
     $columnString = implode(',', array_keys($actual_data));
     $valueString = ":".implode(',:', array_keys($actual_data));
     $sql = "INSERT INTO ".$table." (".$columnString.") VALUES (".$valueString.")";
     $query = $pdo->prepare($sql);
     foreach($actual_data as $key=>$val){
         $val = htmlspecialchars(strip_tags($val));
         $query->bindValue(":".$key, $val);
     }
     $insert = $query->execute();
     if($insert){
         $data['id'] = $pdo->lastInsertId();
         return $data;
     }
     else{
         return false;
     }
 }
 else{
     return false;
 }
 }

Step 4 : Now another function to fetch the data although we already have a function (sqlfetch) which can be used to fetch data from database but that requires to write whole query by self, So here is the automated one

<?php
/*  Code by KharePHP.com
     * Returns rows from the database based on the conditions
     * @param string name of the table
     * @param array select, where, order_by, limit and return_type conditions
     */
function getRows($table,$conditions = array()){
	$pdo=getPDOObject();
	$sql = 'SELECT ';
	$sql .= array_key_exists("select",$conditions)?$conditions['select']:'*';
	$sql .= ' FROM '.$table;
	if(array_key_exists("where",$conditions)){
		$sql .= ' WHERE ';
		$i = 0;
		foreach($conditions['where'] as $key => $value){
			$pre = ($i > 0)?' AND ':'';
			$sql .= $pre.$key." = '".$value."'";
			$i++;
		}
	}
	
	if(array_key_exists("order_by",$conditions)){
		$sql .= ' ORDER BY '.$conditions['order_by']; 
	}
	
	if(array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
		$sql .= ' LIMIT '.$conditions['start'].','.$conditions['limit']; 
	}elseif(!array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
		$sql .= ' LIMIT '.$conditions['limit']; 
	}
	
	$query = $pdo->prepare($sql);
	$query->execute();
	
	if(array_key_exists("return_type",$conditions) && $conditions['return_type'] != 'all'){
		switch($conditions['return_type']){
			case 'count':
				$data = $query->rowCount();
				break;
			case 'single':
				$data = $query->fetch(PDO::FETCH_ASSOC);
				break;
			default:
				$data = '';
		}
	}else{
		if($query->rowCount() > 0){
			$data = $query->fetchAll(PDO::FETCH_ASSOC);
		}
	}
	return !empty($data)?$data:false;
}
Tags: , , , ,

2 thoughts on “Automate SQL Query – PHP Function – PDO Method”

  1. domestic removals says:

    I’m getting your info, good topic.

    I needs to spend some time learning more or understanding more.
    Thanks for wonderful information I was looking for this info for my
    mission.

  2. Leica ScanStation P16 Brochure says:

    This is very interesting, You are a very skilled blogger.
    I’ve joined your feed and look forward to seeking more of
    your great post. Also, I’ve shared your site in my social networks!

Leave a Reply

Your email address will not be published. Required fields are marked *