webapp/resources/datatables-1.9.4/examples/server_side/scripts/filter_col.php

   1 <?php
   2   /* MySQL connection */
   3         include( $_SERVER['DOCUMENT_ROOT']."/datatables/mysql.php" ); /* ;-) */
   4
   5         /*
   6          * Local functions
   7          */
   8         function fatal_error ( $sErrorMessage = '' )
   9         {
  10                 header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
  11                 die( $sErrorMessage );
  12         }
  13
  14
  15         /*
  16          * MySQL connection
  17          */
  18         if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) )
  19         {
  20                 fatal_error( 'Could not open connection to server' );
  21         }
  22
  23         if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
  24         {
  25                 fatal_error( 'Could not select database ' );
  26         }
  27
  28
  29
  30         /* Paging */
  31         $sLimit = "";
  32         if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
  33         {
  34                 $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
  35                         intval( $_GET['iDisplayLength'] );
  36         }
  37
  38         /* Ordering */
  39         if ( isset( $_GET['iSortCol_0'] ) )
  40         {
  41                 $sOrder = "ORDER BY  ";
  42                 for ( $i=0 ; $i<mysql_real_escape_string( $_GET['iSortingCols'] ) ; $i++ )
  43                 {
  44                         $sOrder .= fnColumnToField(mysql_real_escape_string( $_GET['iSortCol_'.$i] ))."
  45                                 ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
  46                 }
  47                 $sOrder = substr_replace( $sOrder, "", -2 );
  48         }
  49
  50         /* Filtering - NOTE this does not match the built-in DataTables filtering which does it
  51          * word by word on any field. It's possible to do here, but concerned about efficiency
  52          * on very large tables, and MySQL's regex functionality is very limited
  53          */
  54         $sWhere = "";
  55         if ( $_GET['sSearch'] != "" )
  56         {
  57                 $sWhere = "WHERE ( engine LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
  58                                 "browser LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
  59                                 "platform LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
  60                                 "version LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
  61                                 "grade LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' )";
  62         }
  63
  64         for ( $i=0 ; $i<$_GET['iColumns'] ; $i++ )
  65         {
  66                 if ( $_GET['sSearch_'.$i] != '' )
  67                 {
  68                         if ( $sWhere != "" )
  69                         {
  70                                 $sWhere .= " AND ";
  71                         }
  72                         else
  73                         {
  74                                 $sWhere .= "WHERE ";
  75                         }
  76                         $sWhere .= fnColumnToField($i) ." LIKE '%".mysql_real_escape_string( $_GET['sSearch_'.$i] )."%'";
  77                 }
  78         }
  79
  80         $sQuery = "
  81                 SELECT SQL_CALC_FOUND_ROWS id, engine, browser, platform, version, grade
  82                 FROM   ajax
  83                 $sWhere
  84                 $sOrder
  85                 $sLimit
  86         ";
  87         $rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
  88
  89         $sQuery = "
  90                 SELECT FOUND_ROWS()
  91         ";
  92         $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
  93         $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
  94         $iFilteredTotal = $aResultFilterTotal[0];
  95
  96         $sQuery = "
  97                 SELECT COUNT(id)
  98                 FROM   ajax
  99         ";
 100         $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
 101         $aResultTotal = mysql_fetch_array($rResultTotal);
 102         $iTotal = $aResultTotal[0];
 103
 104         $sOutput = '{';
 105         $sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
 106         $sOutput .= '"iTotalRecords": '.$iTotal.', ';
 107         $sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
 108         $sOutput .= '"aaData": [ ';
 109         while ( $aRow = mysql_fetch_array( $rResult ) )
 110         {
 111                 $sOutput .= "[";
 112                 $sOutput .= '"'.str_replace('"', '\"', $aRow['engine']).'",';
 113                 $sOutput .= '"'.str_replace('"', '\"', $aRow['browser']).'",';
 114                 $sOutput .= '"'.str_replace('"', '\"', $aRow['platform']).'",';
 115                 if ( $aRow['version'] == "0" )
 116                         $sOutput .= '"-",';
 117                 else
 118                         $sOutput .= '"'.str_replace('"', '\"', $aRow['version']).'",';
 119                 $sOutput .= '"'.str_replace('"', '\"', $aRow['grade']).'"';
 120                 $sOutput .= "],";
 121         }
 122         $sOutput = substr_replace( $sOutput, "", -1 );
 123         $sOutput .= '] }';
 124
 125         echo $sOutput;
 126
 127
 128         function fnColumnToField( $i )
 129         {
 130                 if ( $i == 0 )
 131                         return "engine";
 132                 else if ( $i == 1 )
 133                         return "browser";
 134                 else if ( $i == 2 )
 135                         return "platform";
 136                 else if ( $i == 3 )
 137                         return "version";
 138                 else if ( $i == 4 )
 139                         return "grade";
 140         }
 141 ?>