Adding custom field jQuery Datatables

Question

I am using jQuery Datatables to populate my data. I am using the server side method which I need coz I am fetching hundred thousand records. However I don't know how to add a custom field. For example

|Action|
________
http://localhost/qms/public/customer/1/edit

Which the 1 should be the ID

Because on the datatables, you only declaring column tables that you need so I don't know how to add a custom one.

I currently have this:

I need to put action column to edit those customers. I am using Laravel 5.1

HTML View:

<table id="CustomerList" class="table table-striped table-bordered" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th colspan="7"> <center>Customer Information<center></th>
            <!-- <th colspan="2"> <center>Actions<center></th> -->
        </tr>
        <tr>
            <th>ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Gender</th>
            <th>Phone Number</th>
            <th>Country</th>
            <th>Postcode</th>
        <!--     <th>Edit</th>
            <th>Delete</th> -->
        </tr>
    </thead>
    <tbody>
    </tbody>
</table>

Ajax:

<script type="text/javascript">
    $(document).ready(function() {
        $.fn.dataTable.ext.legacy.ajax = true;
        $('#CustomerList').DataTable( {
            "processing": true,
            "serverSide": true,
            "ajax": "api/customer/all",
            "paging" : true,
            "searching" : true,
            "ordering" :  true,
        } );
        var tt = new $.fn.dataTable.TableTools( $('#CustomerList').DataTable() );
        $( tt.fnContainer() ).insertBefore('div.dataTables_wrapper');
    });
</script>

Controller:

public function apiGetCustomers()
{
    /*=================================================================*/
    /*
     * Script:    DataTables server-side script for PHP and PostgreSQL
     * Copyright: 2010 - Allan Jardine
     * License:   GPL v2 or BSD (3-point)
     */

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Easy set variables
     */

    /* 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 = array('id', 'firstname', 'lastname', 'gender', 'phone_num', 'country', 'postcode' );

    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "phone_num";

    /* DB table to use */
    $sTable = "customers";

    /* Database connection information */
    $gaSql['user']       = "postgres";
    $gaSql['password']   = "postgres";
    $gaSql['db']         = "qms";
    $gaSql['server']     = "localhost";



    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * If you just want to use the basic configuration for DataTables with PHP server-side, there is
     * no need to edit below this line
     */

    /*
     * DB connection
     */
    $gaSql['link'] = pg_connect(
        " host=".$gaSql['server'].
        " dbname=".$gaSql['db'].
        " user=".$gaSql['user'].
        " password=".$gaSql['password']
    ) or die('Could not connect: ' . pg_last_error());


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


    /*
     * Ordering
     */
    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 assumes that the field that is being searched on is a string typed field (ie. one
     * on which ILIKE can be used). Boolean fields etc will need a modification here.
     */
    $sWhere = "";
    if ( $_GET['sSearch'] != "" )
    {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $_GET['bSearchable_'.$i] == "true" )
            {
                if($aColumns[$i] != 'id') // Exclude ID for filtering
                {
                    $sWhere .= $aColumns[$i]." ILIKE '%".pg_escape_string( $_GET['sSearch'] )."%' OR ";
                }
            }
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ")";
    }

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


    $sQuery = "
        SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
        FROM   $sTable
        $sWhere
        $sOrder
        $sLimit
    ";

    $rResult = pg_query( $gaSql['link'], $sQuery ) or die(pg_last_error());

    $sQuery = "
        SELECT $sIndexColumn
        FROM   $sTable
    ";
    $rResultTotal = pg_query( $gaSql['link'], $sQuery ) or die(pg_last_error());
    $iTotal = pg_num_rows($rResultTotal);
    pg_free_result( $rResultTotal );

    if ( $sWhere != "" )
    {
        $sQuery = "
            SELECT $sIndexColumn
            FROM   $sTable
            $sWhere
        ";
        $rResultFilterTotal = pg_query( $gaSql['link'], $sQuery ) or die(pg_last_error());
        $iFilteredTotal = pg_num_rows($rResultFilterTotal);
        pg_free_result( $rResultFilterTotal );
    }
    else
    {
        $iFilteredTotal = $iTotal;
    }

    /*
     * Output
     */
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );

    while ( $aRow = pg_fetch_array($rResult, null, PGSQL_ASSOC) )
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $aColumns[$i] == "version" )
            {
                /* Special output formatting for 'version' column */
                $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
            }
            else if ( $aColumns[$i] != ' ' )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }

    echo json_encode( $output );

    // Free resultset
    pg_free_result( $rResult );

    // Closing connection
    pg_close( $gaSql['link'] );


}

TIP: Remove your username and password for the database from the question — Can O'Spam feels disassociated
– Can O'Spam feels disassociated, Commented Oct 2, 2015 at 12:09

davidkonrad · Accepted Answer · 2015-10-02 11:45:59Z

3

Add headers for the custom fields, as you have above <th>Edit</th><th>Delete</th>
Use column rendering to add the content of the custom fields. Since you are using an "array of arrays" as datasource, you must do it this way :

small demonstration -> http://jsfiddle.net/pqgynvys/

var table = $('#example').DataTable({
    data : data.data,
    columns : [
        null,
        null,
        null,
        null,
        null,
        null,
        null,
       { render : function() {
             return '<button>edit</button>'
         }
       }, 
       { render : function() {
             return '<button>delete</button>'
         }
       } 
  ]   
})

answered Oct 2, 2015 at 11:45

davidkonrad

85.7k17 gold badges211 silver badges273 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jackhammer013 Over a year ago

Thank you for the response. I'll update this one coz @Gyrocode.com answers first and I find it much simpler. Thanks though!

Gyrocode.com · Accepted Answer · 2015-10-02 11:56:31Z

SOLUTION

In HTML add one column in the table header for Action.

In DataTables initialization options add columnDefs to target that 8^th column ("targets": 7, zero-based index) and use render option to produce content for that column.

In the render function, you can use row variable to access the data for the row. Since you're returning array of arrays in your PHP script, you can access your ID by using row[0] (1^st column, zero-based index).

var table = $('#CustomerList').DataTable( {
   "processing": true,
   "serverSide": true,
   "ajax": "api/customer/all"
   "columnDefs": [
        { 
            "targets": 7,
            "render": function(data, type, row, meta){
               return '<a href="/qms/public/customer/' + row[0] + '/edit">Edit</a>';  
            }
        }            
    ]        
});

DEMO

See this jsFiddle for code and demonstration.

Collectives™ on Stack Overflow

Adding custom field jQuery Datatables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related