Frontaccounting common class for Insert, Update, Delete, Get SQL Functions |
X

Congrats, You are Subscribed to Receive Updates.

Frontaccounting common class for Insert, Update, Delete, Get SQL Functions


Frontaccounting common class for Insert, Update, Delete, Get SQL Functions. FrontAccounting has awesome functionality to manage all your financial transaction. but for development and customization.It has very less support. While working with custom modules. I feel to create an Common functions for the Database operations such as  Inert, Update, Select and Delete. So I wrote the following functions which are also available in my github page for download. And also I Am feeling this one can help the FrontAccounting Developers to perform Add, edit, delete, and get details from database by creating simple queries.  For complex queries. They have to go with Custom Function.

Insert :

For Insert the below one will help you.

function kv_insert($table_name, $data){
    $sql0 = "INSERT INTO ".TB_PREF.$table_name."(";
    $sql1 = " VALUES (";
    foreach($data as $key=>$value){
        $sql0 .= $key.",";
		if(is_array($value)) { 
			if($value[1] == 'date')				
				$sql1 .=  db_escape(date2sql($value[0])).",";
			if($value[1] == 'float')
				$sql1 .= $value.",";
		}else 
			$sql1 .= db_escape($value).",";
    }
    $sql0 = substr($sql0, 0, -1).")";
    $sql1 = substr($sql1, 0, -1).")";
	//display_notification($sql0.$sql1);
    db_query($sql0.$sql1, "Could not insert data to table {$table_name}");
	return  db_insert_id();
}

Here the function `kv_insert` accepts two parameters. first one the table name and second one is an array of datas which is combination of column name and its value.

Update:

You can do the updates , by using the same above code. we will write a different function for better understanding.

function kv_update($table_name, $primary_key ,$data ){
    $sql0 = "UPDATE ".TB_PREF.$table_name." SET ";
    foreach($data as $key=>$value){
		if(is_array($value)) { 
			if($value[1] == 'date')				
				$sql0 .= $key." = ". db_escape(date2sql($value[0])).",";
			if($value[1] == 'float')
				$sql0 .= $key." = ". $value.",";
		}else 
			$sql0 .= $key." = ".db_escape($value).",";
    }
    $sql0 = substr($sql0, 0, -1);
    $sql0 .= " where ".$primary_key[0]." = ".$primary_key[1];
	return  db_query($sql0, "Could not update data on table {$table_name}");	 
}

 Get:

Getting data’s from mysql has Four different possibilities. So our function will be changed like this.

  • Getall Data
  • Getrow of Data
  • Get Single Value
  • Get With Joining Tables

Let’s see each one and its functions.

Getall:

The word itself we can understand, we will get all the results from the table. so this is common function to get all the columns and rows by applying filter condition in it.

function kv_get_all($table_name, $conditions = null){
    $sql0 = "SELECT * FROM ".TB_PREF.$table_name." WHERE 1=1";
    if($conditions != null) {
		foreach($conditions as $key=>$value){
			$sql0 .= " AND {$key} = ${value}";
		}
    }
    $result = db_query($sql0, "Could not get data from {$table_name}");
    $data = array();
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    return $data;
}

 GetRow of Data:

Getting a row of data, which is more useful for the editing a row of data’s. Let’s see the function.

function kv_get_row($table_name, $conditions = null){
    $sql0 = "SELECT * FROM ".TB_PREF.$table_name." WHERE 1=1";
    if($conditions != null) {
		foreach($conditions as $key=>$value){
			$sql0 .= " AND {$key} = ${value}";
		}
    }
	$sql0 .= ' LIMIT 1'; 
    $result = db_query($sql0, "Could not get data from {$table_name}");
	$data = db_fetch($result);
    
    return $data;
}

Get Single Value:

Getting single result is good for some name retrieval and few other operations. We may need to edit only one field sometimes

function kv_get_single_value($tablename, $column_single, $conditions=null){
	$sql0 = "SELECT ".$column_single." FROM ".TB_PREF.$tablename." WHERE 1=1";
    foreach($conditions as $key=>$value){
        $sql0 .= " AND {$key} = ${value}";
    }
    return db_get_single_value($sql0, "Could not get data from {$tablename}");
}

Get With Joining Tables :

You can join two tables by using the function , you can use, join and inner join for now. Here is the code.

function kv_get_results_join($main_table, $joins, $columns=array(), $conditions=null){
    $sql0 = "SELECT ";
    if(count($columns)>0){
        foreach ($columns as $value) {
            $sql0 .= $value.",";
        }
        $sql0 = substr($sql0, 0, -1);
    }else{
        $sql0 .= " *";
    }
    $sql0 .= " FROM ".TB_PREF."{$main_table} ";
    foreach ($joins as $value) {
        if(isset($value['join'])){
            $sql0 .= " {$value['join']} JOIN ".TB_PREF.$value['table_name'].' ON '.$value['conditions'];
        }else{
            $sql0 .= " INNER JOIN ".TB_PREF.$value['table_name'].' ON '.$value['conditions'];
        }
    }
    $sql0 .= " WHERE 1=1 ";
    foreach($conditions as $key=>$value){
        $sql0 .= " AND {$key} = ${value}";
    }
    //echo $sql0;
    $result = db_query($sql0, "Could not get data!");
    $data = array();
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    return $data;
}

And these are all the Get filtering options. I hope I will expand this function more in future.

Delete:

You may need to delete a column or row or some part , you can do this by using following function.

function kv_delete($table_name, $conditions){
    $sql0 = "DELETE FROM ".TB_PREF.$table_name." WHERE 1=1";
    foreach ($conditions as $key=>$value) {
        $sql0 .= " AND ".$key."=".$value;
    }
    $result = db_query($sql0, "Could not delete data from {$table_name}");
    return $result;
}

So with all these functions you get it here.

<?php 
//-----------------------------------------------------------------------------------------------

// Fuctions : Database Simple query writer

// Author: Kvvaradha

// Website : http;//kvcodes.com

// Read More: https://www.kvcodes.com/2015/12/frontaccounting-common-class-for-insert-update-delete-get-sql-functions/

//-----------------------------------------------------------------------------------------------

function kv_insert($table_name, $data){
    $sql0 = "INSERT INTO ".TB_PREF.$table_name."(";
    $sql1 = " VALUES (";
    foreach($data as $key=>$value){
        $sql0 .= $key.",";
		if(is_array($value)) { 
			if($value[1] == 'date')				
				$sql1 .=  db_escape(date2sql($value[0])).",";
			if($value[1] == 'float')
				$sql1 .= $value.",";
		}else 
			$sql1 .= db_escape($value).",";
    }
    $sql0 = substr($sql0, 0, -1).")";
    $sql1 = substr($sql1, 0, -1).")";
	//display_notification($sql0.$sql1);
    db_query($sql0.$sql1, "Could not insert data to table {$table_name}");
	return  db_insert_id();
}
function kv_update($table_name, $primary_key ,$data ){
    $sql0 = "UPDATE ".TB_PREF.$table_name." SET ";
    foreach($data as $key=>$value){
		if(is_array($value)) { 
			if($value[1] == 'date')				
				$sql0 .= $key." = ". db_escape(date2sql($value[0])).",";
			if($value[1] == 'float')
				$sql0 .= $key." = ". $value.",";
		}else 
			$sql0 .= $key." = ".db_escape($value).",";
    }
    $sql0 = substr($sql0, 0, -1);
    $sql0 .= " where ".$primary_key[0]." = ".$primary_key[1];
	return  db_query($sql0, "Could not update data on table {$table_name}");	 
}
function kv_delete($table_name, $conditions){
    $sql0 = "DELETE FROM ".TB_PREF.$table_name." WHERE 1=1";
    foreach ($conditions as $key=>$value) {
        $sql0 .= " AND ".$key."=".$value;
    }
    $result = db_query($sql0, "Could not delete data from {$table_name}");
    return $result;
}
function kv_get_results_join($main_table, $joins, $columns=array(), $conditions=null){
    $sql0 = "SELECT ";
    if(count($columns)>0){
        foreach ($columns as $value) {
            $sql0 .= $value.",";
        }
        $sql0 = substr($sql0, 0, -1);
    }else{
        $sql0 .= " *";
    }
    $sql0 .= " FROM ".TB_PREF."{$main_table} ";
    foreach ($joins as $value) {
        if(isset($value['join'])){
            $sql0 .= " {$value['join']} JOIN ".TB_PREF.$value['table_name'].' ON '.$value['conditions'];
        }else{
            $sql0 .= " INNER JOIN ".TB_PREF.$value['table_name'].' ON '.$value['conditions'];
        }
    }
    $sql0 .= " WHERE 1=1 ";
    foreach($conditions as $key=>$value){
        $sql0 .= " AND {$key} = ${value}";
    }
    //echo $sql0;
    $result = db_query($sql0, "Could not get data!");
    $data = array();
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    return $data;
}
function kv_get_all($table_name, $conditions = null){
    $sql0 = "SELECT * FROM ".TB_PREF.$table_name." WHERE 1=1";
    if($conditions != null) {
		foreach($conditions as $key=>$value){
			$sql0 .= " AND {$key} = ${value}";
		}
    }
    $result = db_query($sql0, "Could not get data from {$table_name}");
    $data = array();
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    return $data;
}

function kv_get_row($table_name, $conditions = null){
    $sql0 = "SELECT * FROM ".TB_PREF.$table_name." WHERE 1=1";
    if($conditions != null) {
		foreach($conditions as $key=>$value){
			$sql0 .= " AND {$key} = ${value}";
		}
    }
	$sql0 .= ' LIMIT 1'; 
    $result = db_query($sql0, "Could not get data from {$table_name}");
	$data = db_fetch($result);
    
    return $data;
}

function kv_get_filter($table_name, $columns= array(), $conditions=null){
    $sql0 = "SELECT ";
    if(count($columns)>0){
        foreach ($columns as $value) {
            $sql0 .= $value.",";
        }
    }else{
        $sql0 .= " * ";
    }
    // remove the last character ','
    $sql0 = substr($sql0, 0, -1);
    $sql0 .= " FROM ".TB_PREF.$table_name." WHERE 1=1";
    foreach($conditions as $key=>$value){
        $sql0 .= " AND {$key} = ${value}";
    }
    $result = db_query($sql0, "Could not get data from {$table_name}");
    $data = array();
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    return $data;
}

function kv_get_single_value($tablename, $column_single, $conditions=null){
	$sql0 = "SELECT ".$column_single." FROM ".TB_PREF.$tablename." WHERE 1=1";
    foreach($conditions as $key=>$value){
        $sql0 .= " AND {$key} = ${value}";
    }
    return db_get_single_value($sql0, "Could not get data from {$tablename}");
}
?>

Save this file and put it on your `Root-of-FA/includes/db/`. Than hook this file on `connect_db.inc`.After that, you can use it simply anywhere in your code. Else, you can copy paste the functions straightaway on the `connect_db.inc` .

commenter

About Varadharaj V

The founder of Kvcodes, Varadharaj V is an ERP Analyst and a Web developer specializing in WordPress(WP), WP Theme development, WP Plugin development, Frontaccounting(FA), Sales, Purchases, Inventory, Ledgers, Payroll & HRM, CRM, FA Core Customization, PHP and Data Analyst. Database Management Advance Level

2 comments

  1. commenter

    hey,

    can you please guide me about this i am trying to get data i mean single value from database like the value of TOTAL ASSETS from database, how can i do that?

    please help me

Reply to Jacksii Cancel reply

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

*

Current ye@r *

Menu

Sidebar