2 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
6 /* Array of database columns which should be read and sent back to DataTables. Use a space where
7 * you want to insert a non-database field (for example a counter or static image)
9 $aColumns = array( 'id', 'firstname', 'surname', 'zip', 'country' );
11 /* Indexed column (used for fast and accurate table cardinality) */
17 /* Database connection information */
19 $gaSql['password'] = "";
21 $gaSql['server'] = "localhost";
23 /* REMOVE THIS LINE (it just includes my SQL connection user/pass) */
24 include( $_SERVER['DOCUMENT_ROOT']."/datatables/mysql.php" );
27 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
28 * If you just want to use the basic configuration for DataTables with PHP server-side, there is
29 * no need to edit below this line
35 $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
36 die( 'Could not open connection to server' );
38 mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
39 die( 'Could not select database '. $gaSql['db'] );
46 if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
48 $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
49 mysql_real_escape_string( $_GET['iDisplayLength'] );
57 if ( isset( $_GET['iSortCol_0'] ) )
59 $sOrder = "ORDER BY ";
60 for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
62 if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
64 $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
65 ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
69 $sOrder = substr_replace( $sOrder, "", -2 );
70 if ( $sOrder == "ORDER BY" )
79 * NOTE this does not match the built-in DataTables filtering which does it
80 * word by word on any field. It's possible to do here, but concerned about efficiency
81 * on very large tables, and MySQL's regex functionality is very limited
84 if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
87 for ( $i=0 ; $i<count($aColumns) ; $i++ )
89 $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
91 $sWhere = substr_replace( $sWhere, "", -3 );
95 /* Individual column filtering */
96 for ( $i=0 ; $i<count($aColumns) ; $i++ )
98 if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
108 $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
115 * Get data to display
118 SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
124 $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
126 /* Data set length after filtering */
130 $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
131 $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
132 $iFilteredTotal = $aResultFilterTotal[0];
134 /* Total data set length */
136 SELECT COUNT(".$sIndexColumn.")
139 $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
140 $aResultTotal = mysql_fetch_array($rResultTotal);
141 $iTotal = $aResultTotal[0];
148 "sEcho" => intval($_GET['sEcho']),
149 "iTotalRecords" => $iTotal,
150 "iTotalDisplayRecords" => $iFilteredTotal,
154 while ( $aRow = mysql_fetch_array( $rResult ) )
157 for ( $i=0 ; $i<count($aColumns) ; $i++ )
159 if ( $aColumns[$i] == "version" )
161 /* Special output formatting for 'version' column */
162 $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
164 else if ( $aColumns[$i] != ' ' )
167 $row[] = $aRow[ $aColumns[$i] ];
170 $output['aaData'][] = $row;
173 echo json_encode( $output );