How to get First column of Jquery Datatable as Che

Question

I have a requirement where I need to add the first column and its subsequent row values as Checked or Unchecked checkbox. The issue that I am facing as of now is one of dynamic data from the server side. Here, both column Name and Row Values are coming from the server side. Here is the code.

   $('#SettingsDatatable').dataTable({
   "order": [],
   "dom": "Bfrtip",
   "buttons": ["copy", "csv", "excel", "pdf", "print"],
   "columnDefs": [{
   "className": "dt-center", "orderable": false, "width": 20
                        }], 
  "columns": dataObject[0].columns,
   "data": dataObject[0].data
                    });

How can I add the checkbox column and how can it be checked and unchecked based on the value from dataObject[0].data? Please help. Thanks.

Answer 1

you can use columnDefs to render custom html

var dataObject = [{
  "columns": [{
    "title": "Select"
  }, {
    "title": "DbColumn Name"
  }, {
    "title": "UserColumn Name"
  }],
  "data": [
    ["False", "STARTDATE", ""],
    ["True", "ENDDATE", ""],
    ["False", "CLASS ", ""],
    ["True", "AUDIT_DATE ", ""]
  ]
}];

$('#SettingsDatatable').dataTable({
  "order": [],
  "dom": "Bfrtip",
  "buttons": ["copy", "csv", "excel", "pdf", "print"],
  "columnDefs": [{
    "targets": 0,
    "render": function(data, type, full, meta) {
      return '<input type="checkbox" ' + (data == 'True' ? 'checked' : '') + ' />';
    }
  }, {
    "className": "dt-center",
    "orderable": false,
    "width": 20
  }],
  "columns": dataObject[0].columns,
  "data": dataObject[0].data
});

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.1/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.1/css/jquery.dataTables.css" rel="stylesheet" />
<table id="SettingsDatatable"></table>

Answer 2

Here is the solution using PHP, MySQL & jQuery. Please convert according to your need for e.g.

1. Change your DB from MySQL to Oracle.
2. Change server side dropdown to use checkbox.
3. Please give exact path for $settingsDatatableServer variable (server side file where code mentioned).

Client side (jQuery):

settingsDatatable = $("#SettingsDatatable").dataTable({
    aLengthMenu: [ [10, 25, 50, 100, "-1"], [10, 25, 50, 100, "All"] ],
        iDisplayLength: 10,
        searching: true,
        "aaSorting": [],
        "order": [[ 0, "asc" ]],
        "sPaginationType": "full_numbers",

        "bProcessing": true,
        "serverSide": true,
        "bDestroy": true,
        "cache": false,
        "sAjaxSource": "<?php echo $settingsDatatableServer; ?>",

        "aoColumnDefs": [
            { "bSortable": false, "aTargets": [ 1, 2, 3, 4, 5 ] },
            { "data": "column1" },
            { "data": "column2" },
            { "data": "column3" },
            { "data": "column4" },
            { "data": "column5" },
        ],
   });

Server side (PHP + MySQL)

<?php

if($_GET) {

    $aColumns = array( 'column1', 'column2', 'column3', 'column4', 'column5' );

    $sIndexColumn = "column1";

    $sTable = $yourtable;

    $sSql['user'] = DB_USER;
    $sSql['password'] = DB_PASSWORD;
    $sSql['db'] = DB_NAME;
    $sSql['server'] = DB_HOST;

    function fatal_error ( $sErrorMessage = '' )
    {
        header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
        die( $sErrorMessage );
    }

    if ( ! $sSql['link'] = mysqli_connect( $sSql['server'], $sSql['user'], $sSql['password']  ) )
    {
        fatal_error( 'Could not open connection to server' );
    }

    if ( ! mysqli_select_db( $sSql['link'], $sSql['db'] ) )
    {
        fatal_error( 'Could not select database ' );
    }


    /*
     * 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' : 'asc') .", ";
            }
        }

        $sOrder = substr_replace( $sOrder, "", -2 );
        if ( $sOrder == "ORDER BY" )
        {
            $sOrder = "";
        }
    }


    /*
     * Filtering
     */
    $sWhere = "";
    if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
    {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            $sWhere .= "`".$aColumns[$i]."` LIKE '%". $_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 '%". $_GET['sSearch_'.$i] ."%' ";
        }
    }


    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
        FROM $sTable
        $sWhere
        $sOrder
        $sLimit
        ";
    $rResult = mysqli_query( $sSql['link'], $sQuery ) or fatal_error( 'MySQL Query Error: ' . mysqli_errno() );

    $sQuery = "
        SELECT FOUND_ROWS()
    ";
    $rResultFilterTotal = mysqli_query( $sSql['link'], $sQuery ) or fatal_error( 'MySQL Filter Error: ' . mysqli_errno() );
    $aResultFilterTotal = mysqli_fetch_array($rResultFilterTotal);
    $iFilteredTotal = $aResultFilterTotal[0];

    $sQuery = "
        SELECT COUNT(`".$sIndexColumn."`)
        FROM $sTable
    ";
    $rResultTotal = mysqli_query( $sSql['link'], $sQuery ) or fatal_error( 'MySQL Total Error: ' . mysqli_errno() );
    $aResultTotal = mysqli_fetch_array($rResultTotal);
    $iTotal = $aResultTotal[0];

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

    while ( $aRow = mysqli_fetch_array( $rResult ) )
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ ) {

            $row[0] = '<select id=' . $aRow['column'] . "-" . $aColumns[$i] . ' class="dropdownselect" style="width:80px; color:red; font-weight:bold; text-align:left; padding-left:3px;"><option selected="selected" value="true">true</option> <option value="false">false</option></select>';
            $row[] = $aRow[ $aColumns[$i] ];

        }

        $output['aaData'][] = $row;
    }

    echo json_encode( $output );

}

?>