Answers

• gaarakenpachi Posts: 6Questions: 2Answers: 0

  March 2016

  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;
  }