PHP Classes

PHP Datatable AJAX JSON Server: Generate JSON data from database queries

Recommend this page to a friend!
  Info   View files Example   View files View files (3)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not yet rated by the usersTotal: 90 This week: 1All time: 9,926 This week: 560Up
Version License PHP version Categories
datatable_server 1.0Custom (specified...5PHP 5, Databases, AJAX, Global
Description 

Author

This package can generate JSON data from database queries.

It provides a function that takes a configuration array and executes SQL queries on specific tables using the CodeIgniter framework.

The function returns a JSON string with the database query results suitable for being used by AJAX JavaScript code on the browser to display the query results without reloading the page.

Picture of Shabeer ALi M
  Performance   Level  
Name: Shabeer ALi M <contact>
Classes: 4 packages by
Country: India India
Age: ???
All time rank: 3645247 in India India
Week rank: 106 Up8 in India India Up
Innovation award
Innovation award
Nominee: 2x

Example

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
 * CodeIgniter
 *
 * An open source application development framework for PHP 5.1.6 or newer
 *
 * @package CodeIgniter
 * @author ExpressionEngine Dev Team
 * @copyright Copyright (c) 2008 - 2014, EllisLab, Inc.
 * @license http://codeigniter.com/user_guide/license.html
 * @link http://codeigniter.com
 * @since Version 1.0
 * @filesource
 */

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

/**
 * CodeIgniter Datatable Helpers
 *
 * @package CodeIgniter
 * @subpackage Helpers
 * @category Helpers
 * @author Shabeer Ali M
 * @link https://github.com/shabeer-ali-m/datatable_server
 */

// ------------------------------------------------------------------------
if ( ! function_exists('dt_get_json'))
{
   
/**
     * Datatable server is php code that you can create json easily for ajax datatable.
     * git : https://github.com/shabeer-ali-m/datatable_server
     *
     *
     *
     * $config=array(
     *
     * Coloums you need to select form database. [Array()]
     * 'aColumns' => array( 'id','name','email','mobile'),
     *
     * Index coloum of the table. [String]
     * 'sIndexColumn' => 'id',
     *
     * Table name [String]
      * You can also gave join statment
      * ex : 'sTable' =>"order left join invoice on invoiceid=orderid" ,
     * 'sTable' =>"address-book" ,
     *
     * If you have any conditional statement you can add this. [String][optional]
     * 'sCondition'=>'name like "%a"',
     *
     * Output needed in each coloum. [Array(Array('type'=>value))]
     * Usage
     * text => Normal Text values
     * var => Variable Name.The variable name will replaced with the variable value.
     * The variable that you are selecting should be added in aColoums.
     * html => You can directly gave html. If you want to use variable in between you can use {{variable_name}}.
     * eval => You can call a function that you have written.You can use the arguments as your variable that you selected in aColoums.
     * 'aColumns_out' => array(
     * array('var'=>'id'),
     * array('var'=>'name',
     * array('html'=>'<a href="mailto:{{email}}">{{email}}</a>'),
     * array('eval'=>'your_function($mobile)'),
     * ),
     * );
     *
     * @access public
     * @param array()
     * @return json JSON data for datatable
     */
   
function dt_get_json($config)
    {
       
/* creating instance */
       
$CI =& get_instance();

       
/* loading database */
       
$CI->load->database();

       
/* Array of database columns which should be read and sent back to DataTables. Use a space where
         * you want to insert a non-database field (for example a counter or static image)
        */
       
$aColumns = $config['aColumns'];

       
/* Indexed column (used for fast and accurate table cardinality) */
       
$sIndexColumn = $config['sIndexColumn'];

       
/* DB table to use */
       
$sTable = $config['sTable'];

       
/* Query condition */
       
$sCondition=isset($config['sCondition'])?$config['sCondition']:'';

       
/*
        * Paging
        */
       
$sLimit = "";
        if ( isset(
$_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
        {
           
$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
               
intval( $_GET['iDisplayLength'] );
        }


       
/*
        * Ordering
        */
       
$sOrder = "";
        if ( isset(
$_GET['iSortCol_0'] ) )
        {
           
$sOrder = "ORDER BY ";
            for (
$i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
            {
                if (
$_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
                {
                   
$sOrder .= "`".$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."` ".
                        (
$_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
                }
            }
           
           
$sOrder = substr_replace( $sOrder, "", -2 );
            if (
$sOrder == "ORDER BY" )
            {
               
$sOrder = "";
            }
        }

       
/*
         * Filtering
         * NOTE this does not match the built-in DataTables filtering which does it
         * word by word on any field. It's possible to do here, but concerned about efficiency
         * on very large tables, and MySQL's regex functionality is very limited
        */
       
$sWhere = "";
        if ( isset(
$_GET['sSearch']) && $_GET['sSearch'] != "" )
        {
           
$sWhere = "WHERE (";
            for (
$i=0 ; $i<count($aColumns) ; $i++ )
            {
               
$sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
            }
           
$sWhere = substr_replace( $sWhere, "", -3 );
           
$sWhere .= ')';
        }

       
/* Individual column filtering */
       
for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( isset(
$_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
            {
                if (
$sWhere == "" )
                {
                   
$sWhere = "WHERE ";
                }
                else
                {
                   
$sWhere .= " AND ";
                }
               
$sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
            }
        }
       
        if(
$sWhere==""&&$sCondition!="")
           
$sWhere.='where ('.$sCondition.') ';
        else if(
$sCondition!="")
           
$sWhere.='and ('.$sCondition.') ';

       
/*
         * SQL queries
         * Get data to display
        */
       
$sQuery = "
            SELECT SQL_CALC_FOUND_ROWS `"
.str_replace(" , ", " ", implode("`, `", $aColumns))."`
            FROM
$sTable
           
$sWhere
           
$sOrder
           
$sLimit
            "
;

       
/* fetching result from database */
       
$rResult = $CI->db->query($sQuery);
       
$sQuery = "
            SELECT COUNT(`"
.$sIndexColumn."`) as 'count'
            FROM
$sTable $sWhere
        "
;
       
$rResultTotal = $CI->db->query($sQuery);
       
$rResultTotal = $rResultTotal->row_array();
       
$iTotal = $rResultTotal["count"];
       
$iFilteredTotal=$iTotal;
       
       
/*
         * Output
         */
       
$output = array(
           
"sEcho" => intval($_GET['sEcho']),
           
"iTotalRecords" => $iTotal,
           
"iTotalDisplayRecords" => $iFilteredTotal,
           
"aaData" => array()
        );

        foreach (
$rResult->result_array() as $aRow )
        {
               
$row = array();
               
extract($aRow);

                foreach(
$config['aColumns_out'] as $aColumnsrow)
                {
                   
$col="";
                    foreach(
$aColumnsrow as $key=>$value)
                    {
                        if(
$key=='text')
                           
$col.=$value;
                        else if(
$key=='var')
                           
$col.=$aRow[$value];
                        else if(
$key=='html')
                        {
                            if(
preg_match_all('/\{{(.*?)\}}/',$value,$match)){
                                foreach(
$match[1] as $v=>$k)
                                   
$match[1][$v]=$aRow[$k];
                                       
$col.=str_replace($match[0],$match[1],$value);
                            }
                            else
                               
$col.$value;
                        }
                        else if(
$key=='eval')
                            eval(
"\$col = ".$value.";");
                        else
                           
$col.='Invalid type!';
                    }
                   
$row[] = $col;
                }
               
               
$output['aaData'][] = $row;
        }
                       
           
            return
json_encode( $output );

    }
}

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



/* End of file datatable_helper.php */
/* Location: ./application/helpers/datatable_helper.php */


Details

Datatable Server

Datatable server .

- Easy to use - Codeignator plug-in available.

Datatable server is php code that you can create json easily for [ajax datatable].

> The overriding design goal for Datatable Server PHP function is to create json easily with less configuration for datatable.

Version

1.0.1

Codeigniter Usage

Installation

You need to add [codeigniter/datatable_server_helper.php] in Codeigniter helpers folder: (yourproject/application/helpers/)

Usage

Call the function in your controller

//your custom function to call inside json render
function your_function($mobile){
    return $mobile;
}

$this->load->helper('datatable_server');
$config=array(
    // Coloums you need to select form database. [Array()]
    'aColumns' => array( 'id','name','email','mobile'),
	 
    //Index coloum of the table.	[String]
    'sIndexColumn' => 'id', 
	 
    // Table name 	[String]
 	// You can also gave join statement
 	// ex : 'sTable' =>"order left join invoice on invoiceid=orderid" ,
    'sTable' =>"address-book" , 	
	
    //If you have any conditional statement you can add like this. [String][optional]
    //'sCondition'=>'name like "%a"',	
		
	//Output needed in each coloum. [Array(Array('type'=>value))]
	// Usage
	// text	=>	Normal Text values
	// var =>  Variable Name.The variable name will replaced with the variable value.
	// The variable that you are selecting should be added in aColoums.
	// html =>  You can directly gave html. If you want to use variable in between you can use {{variable name}}.
	 // eval =>  You can call a function that you have written.You can use the arguments as your variable that you selected in aColoums.
     'aColumns_out' => array( 
         array('var'=>'id'),
         array('var'=>'name',
         array('html'=>'<a href="mailto:{{email}}">{{email}}</a>'),
         array('eval'=>'your_function($mobile)'),
        ),
     );	
echo dt_get_json($config);

License

MIT [codeigniter/datatable_server_helper.php]:https://github.com/shabeer-ali-m/datatable_server/blob/master/codeigniter/datatable_server_helper.php [ajax datatable]:http://www.datatables.net/examples/data_sources/server_side.html


  Files folder image Files  
File Role Description
Files folder imagecodeigniter (1 file)
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files  /  codeigniter  
File Role Description
  Accessible without login Plain text file datatable_server_helper.php Example Example script

 Version Control Unique User Downloads Download Rankings  
 100%
Total:90
This week:1
All time:9,926
This week:560Up