Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 

  • Data table Server-side processing using Cakephp

    @Meghaa
    Recommendations Offline Message

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 2.03k
    Comment on it

    In cakePhp if have an extensive database which contain a huge number of record so you can apply the datatables server side processing there. By applying the datatable server side processing you are furnished with pagination highlight in which it depends upon you, that what number of records you need to appear of predefined values that are 10, 25, 50, 100. In datatable server side handling you likewise have the inquiry catch in which you can look the specific field passage of the specific table.

    At the base of the record there is pagination which demonstrates the page number where you are and you can change to numerous pages and you can likewise guide hop from one page to other.

     

    Code for the Controller. Here we are having the UsersController. The code for the UsersController is composed underneath.

    2.  
    3. <?php
    4. /**
    5. * Static content controller.
    6. *
    7. * This file will render views from views/pages/
    8. *
    9. * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
    10. * Copyright (c) Cake Software Foundation, Inc. (http://cakefoundation.org)
    11. *
    12. * Licensed under The MIT License
    13. * For full copyright and license information, please see the LICENSE.txt
    14. * Redistributions of files must retain the above copyright notice.
    15. *
    16. * @copyright     Copyright (c) Cake Software Foundation, Inc. (http://cakefoundation.org)
    17. * @link          http://cakephp.org CakePHP(tm) Project
    18. * @package       app.Controller
    19. * @since         CakePHP(tm) v 0.2.9
    20. * @license       http://www.opensource.org/licenses/mit-license.php MIT License
    21. */
    22.  
    23. App::uses('AppController', 'Controller');
    24.  
    25. /**
    26. * Static content controller
    27. *
    28. * Override this controller by placing a copy in controllers directory of an application
    29. *
    30. * @package       app.Controller
    31. * @link http://book.cakephp.org/2.0/en/controllers/pages-controller.html
    32. */
    33. class UsersController extends AppController {
    34.  
    35. /**
    36. * This controller does not use a model
    37. *
    38. * @var array
    39. */
    40. public $uses = array();
    41.  
    42. /**
    43. * Displays a view
    44. *
    45. * @return void
    46. * @throws NotFoundException When the view file could not be found
    47. *    or MissingViewException in debug mode.
    48. */
    49. public function main_page() { 
    50.     $this->set('users', $this->User->find('all'));
    51.  
    52. }
    53.  
    54. public function ajax() { 
    55.  
    56.     $this->layout = "ajax";
    57.     $this->modelClass = "User";
    58.     $this->autoRender = false;          
    59.  
    60. //
    61.  
    62.     $output = $this->User->GetData();    
    63.  
    64.  
    65. //        pr($_GET);exit;
    66.     echo json_encode($output);
    67.     exit;
    68. }
    69.  
    70. }
    71.  
    72. [Click and drag to move]
    73.  

     

    The code for the main_page.ctp is defined below

    1. <script type="text/javascript" src="http://code.jquery.com/jquery-1.12.0.min.js"></script>
    2. <script type="text/javascript" src="http://cdn.datatables.net/1.10.11/js/jquery.dataTables.min.js"></script>
    3. <link rel="stylesheet" type="text/css" href="http://cdn.datatables.net/1.10.11/css/jquery.dataTables.min.css">
    4.  
    5.  
    6.  
    7.  
    8. <script type="text/javascript">
    9.     $(document).ready(function() {
    10.         $('#ajaxtable').dataTable({
    11.             "bProcessing": true,
    12.             "bServerSide": true,
    13.             "sAjaxSource": "<?php echo $this->Html->Url(array('controller' => 'Users', 'action' => 'ajax')); ?>"
    14.         });
    15.     });
    16. </script>
    17.  
    18. <h1>Browser List</h1>
    19.  
    20. <table id="ajaxtable">
    21.     <thead>
    22.         <tr>
    23.             <th>Id</th>
    24.             <th>Name</th>
    25.             <th>First Name</th>
    26.             <th>Last Name</th>
    27.             <th>Gender</th>
    28.             <th>Password</th>
    29.             <th>Status</th>        
    30.         </tr>
    31.     </thead>
    32.     <tbody>
    33.         <tr>
    34.             <td colspan="4" class="dataTables_empty">Loading data from server...</td>
    35.         </tr>
    36.     </tbody>
    37. </table>

     

    The code for the model that is User.php is defined below

    1. <?php
    2. class User extends AppModel {
    3.     public function GetData() {
    4. /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    5. * Easy set variables
    6. */
    7.  
    8. /* Array of database columns which should be read and sent back to DataTables. Use a space where
    9. * you want to insert a non-database field (for example a counter or static image)
    10. */
    11. $aColumns = array( 'user_id','username','first_name','last_name','gender','password','status' );
    12.  
    13. /* Indexed column (used for fast and accurate table cardinality) */
    14. $sIndexColumn = "user_id";
    15.  
    16. /* DB table to use */
    17. $sTable = "users";
    18.  
    19. App::uses('ConnectionManager', 'Model');
    20. $dataSource = ConnectionManager::getDataSource('default');
    21.  
    22. /* Database connection information */
    23. $gaSql['root']       = $dataSource->config['login'];
    24. $gaSql['']   = $dataSource->config['password'];
    25. $gaSql['user']         = $dataSource->config['database'];
    26. $gaSql['localhost']     = $dataSource->config['host'];
    27.  
    28.  
    29. //yprint_r( $sIndexColumn);
    30.  
    31. /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    32. * If you just want to use the basic configuration for DataTables with PHP server-side, there is
    33. * no need to edit below this line
    34. */
    35.  
    36. /*
    37. * Local functions
    38. */
    39. function fatal_error ( $sErrorMessage = '' )
    40. {
    41.     header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
    42.     die( $sErrorMessage );
    43. }
    44.  
    45.  
    46. /*
    47. * MySQL connection
    48. */
    49. if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['localhost'], $gaSql['root'], $gaSql['']  ) )
    50. {
    51.     fatal_error( 'Could not open connection to server' );
    52. }
    53.  
    54. if ( ! mysql_select_db( $gaSql['user'], $gaSql['link'] ) )
    55. {
    56.     fatal_error( 'Could not select database ' );
    57. }
    58.  
    59.  
    60. /*
    61. * Paging
    62. */
    63. $sLimit = "";
    64. if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
    65. {
    66.     $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
    67.     intval( $_GET['iDisplayLength'] );
    68. }
    69.  
    70.  
    71. /*
    72. * Ordering
    73. */
    74. $sOrder = "";
    75. if ( isset( $_GET['iSortCol_0'] ) )
    76. {
    77.     $sOrder = "ORDER BY  ";
    78.     for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
    79.     {
    80.         if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
    81.         {
    82.             $sOrder .= "`".$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."` ".
    83.             ($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
    84.         }
    85.     }
    86.  
    87.     $sOrder = substr_replace( $sOrder, "", -2 );
    88.     if ( $sOrder == "ORDER BY" )
    89.     {
    90.         $sOrder = "";
    91.     }
    92. }
    93.  
    94.  
    95. /*
    96. * Filtering
    97. * NOTE this does not match the built-in DataTables filtering which does it
    98. * word by word on any field. It's possible to do here, but concerned about efficiency
    99. * on very large tables, and MySQL's regex functionality is very limited
    100. */
    101. $sWhere = "";
    102. if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
    103. {
    104.     $sWhere = "WHERE (";
    105.         for ( $i=0 ; $i<count($aColumns) ; $i++ )
    106.         {
    107.             $sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
    108.         }
    109.         $sWhere = substr_replace( $sWhere, "", -3 );
    110.         $sWhere .= ')';
    111. }
    112.  
    113. /* Individual column filtering */
    114. for ( $i=0 ; $i<count($aColumns) ; $i++ )
    115. {
    116.     if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
    117.     {
    118.         if ( $sWhere == "" )
    119.         {
    120.             $sWhere = "WHERE ";
    121.         }
    122.         else
    123.         {
    124.             $sWhere .= " AND ";
    125.         }
    126.         $sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
    127.     }
    128. }
    129.  
    130.  
    131. /*
    132. * SQL queries
    133. * Get data to display
    134. */
    135. $sQuery = "
    136. SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
    137. FROM   $sTable
    138. $sWhere
    139. $sOrder
    140. $sLimit
    141. ";
    142. $rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
    143.  
    144. /* Data set length after filtering */
    145. $sQuery = "
    146. SELECT FOUND_ROWS()
    147. ";
    148. $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
    149. $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
    150. $iFilteredTotal = $aResultFilterTotal[0];
    151.  
    152. /* Total data set length */
    153. $sQuery = "
    154. SELECT COUNT(`".$sIndexColumn."`)
    155. FROM   $sTable
    156. ";
    157. $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
    158. $aResultTotal = mysql_fetch_array($rResultTotal);
    159. $iTotal = $aResultTotal[0];
    160.  
    161.  
    162. /*
    163. * Output
    164. */
    165. $output = array(
    166. /*"sEcho" => intval($_GET['sEcho']),
    167. "iTotalRecords" => $iTotal,
    168. "iTotalDisplayRecords" => $iFilteredTotal,
    169. "aaData" => array()
    170. */
    171. "draw" => intval($_GET['sEcho']),
    172. "recordsTotal" => $iTotal,
    173. "recordsFiltered" => $iFilteredTotal,
    174. "data" => array()
    175. );
    176.  
    177. while ( $aRow = mysql_fetch_array( $rResult ) )
    178. {
    179.     $row = array();
    180.     for ( $i=0 ; $i<count($aColumns) ; $i++ )
    181.     {
    182.         if ( $aColumns[$i] == "version" )
    183.         {
    184.             /* Special output formatting for 'version' column */
    185.             $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
    186.         }
    187.         else if ( $aColumns[$i] != ' ' )
    188.         {
    189.             /* General output */
    190.             $row[] = $aRow[ $aColumns[$i] ];
    191.         }
    192.     }
    193.     $output['data'][] = $row;
    194. }
    195.  
    196. return $output;
    197. }
    198. }
    199. ?>
    200.  
    201.  
    202.  
    203.  

     

    datatable serverside processing
    Comment on it

 0 Comment(s)

Sign In
Forgot Password?
                           OR                           
Create an account !!
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Reset Password
Fill out the form below and reset your password: