Server side processing with extra html in columns

gaarakenpachi

How would I get the server side code to return html in with the database data

I'm working from the examples given on this site for server side processing

...
$columns = array(
    array( 'db' => 'ID', 'dt' => 0 ),
    array( 'db' => 'TestName',  'dt' => 1 ),
    array( 'db' => 'Passed',   'dt' => 2 ),
    array( 'db' => 'Failed',     'dt' => 3 ),
    // Col 4 is a "notes" text area with default text from DB
    // Col 5 is a save button which calls ajax with the ID value from col 0
);
...

Is there a way to implement column 4 and 5 as above?

This is a simplied example of what I'm doing, basically all my tables have a column which contains a graphical representation of some data, which is working great if I do all the data loading at page load, but that's starting to take over a minute so I want to use datatables' server side processing if possible.

gaarakenpachi

I started from this example http://datatables.net/development/server-side/php_cake

Modified it to work with code igniter and MVC

This is the first working code I got to, could be cleaned up a bit.

View snippet

<table id='DevicesTable' style='width:100%'>
    <thead>
        <th>ID</th>
        <th>header1</th>
        <th>header2</th>
        <th>header1</th>
        <th>edittableHeader</th>
        <th></th>
        <th></th>
    </thead>
    <tbody>
        <tr>
            <td colspan="7" class="dataTables_empty">Loading data from server...</td>
        </tr>
    </tbody>
</table>

Controller snippet

public function tableData() {
    $this->load->model('test_model');
    $this->autoRender = false;          
    $output = $this->devices_model->GetData();
    
    // Flags so I can encode html code in json for styling
    echo json_encode($output, JSON_HEX_QUOT | JSON_HEX_TAG);
}

JS Snippet

$(document).ready(function() {
    var tableSrc = base_url + 'index.php/devices/tableData';
    $('#DevicesTable').dataTable({
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxSource": tableSrc,
        "aoColumns": [
            { "sTitle": "ID", "sType": "html" },
            { "sTitle": "header1", "sType": "html" },
            { "sTitle": "header2", "sType": "numeric" },
            { "sTitle": "header3", "sType": "string" },
            { "sTitle": "edittableHeader", "sType": "html" },
            { "sTitle": "", "sType": "html", "searchable": false, "orderable": false }, //button
            { "sTitle": "", "sType": "html", "searchable": false, "orderable": false }  //status div
        ]
    }).css("width","100%");
});

Model snippet

public function GetData() {
    // 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', 'DBcol1', 'DBcol2', 'DBcol3', 'DBcol4');
    // Indexed column (used for fast and accurate table cardinality)
    $sIndexColumn = $aColumns[0];
    // DB table to use
    $sTable = "testTable";
    
    // 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
    $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])."%' ";
        }
    }
     
    // SQL queries
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
        FROM $sTable
        $sWhere
        $sOrder
        $sLimit";
    $rResult = $this->db->query($sQuery);
     
    // Data set length after filtering
    $sQuery = "SELECT FOUND_ROWS()";
    $rResultFilterTotal = $this->db->query($sQuery);
    $aResultFilterTotal = $rResultFilterTotal->result_array();
    $tempFilteredTotal = array_values($aResultFilterTotal[0]);
    $iFilteredTotal = $tempFilteredTotal[0];
     
    // Total data set length
    $sQuery = "SELECT COUNT(`".$sIndexColumn."`) FROM $sTable";
    $rResultTotal = $this->db->query($sQuery);
    $aResultTotal = $rResultTotal->result_array();
    $tempTotal = array_values($aResultTotal[0]);
    $iTotal = $tempTotal[0];

    // JSON-ify Output
    $output = array(
            "sEcho" => intval($_GET['sEcho']),
            "iTotalRecords" => $iTotal,
            "iTotalDisplayRecords" => $iFilteredTotal,
            "aaData" => array()
    );
    foreach ($rResult->result_array() as $aRow) {
        $row = array();
        for ($i=0; $i<count($aColumns); $i++) {
            $dbID = $aRow[$aColumns[0]];
            if ($aColumns[$i] == "DBcol1") {
                $row[] = $aRow[$aColumns[$i]] != '' ? $aRow[$aColumns[$i]] : "N/A";
            } else if ($aColumns[$i] == "DBcol4") {
                $row[] = "<textarea id='notes" . $dbID . "' cols='40' rows='4' name='myname' wrap='virtual'>" . $aRow[$aColumns[$i]] . "</textarea>";
            } else if ($aColumns[$i] != ' ') {
                // General output
                $row[] = $aRow[$aColumns[$i]];
                
            }
            // Formatted output
        }
        $row[] = "<button style='width:110px' onClick='saveChangesAction(\"" . $dbID . "\")' type='button' class='btn' aria-haspopup='true' aria-expanded='false'>Save Changes</button>";
        $row[] = "<div class='alert' role='alert' id='saveChangesResultDiv" . $dbID . "'  style='width:380px'></div>";
        $output['aaData'][] = $row;
    }
    return $output;
}