Free cookie consent management tool by TermsFeed Policy Generator

source: branches/WebApplication/MVC2/HeuristicLabWeb.PluginHost/HLWebPluginHost/Content/jQuery/jQueryPlugins/DataTables-1.7.6/examples/server_side/server_side.html @ 6286

Last change on this file since 6286 was 6286, checked in by dkahn, 13 years ago

#1198 Added jQuery plus plugins
File size: 12.7 KB
RevLine 
[6286]1<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
2<html>
3  <head>
4    <meta http-equiv="content-type" content="text/html; charset=utf-8" />
5    <link rel="shortcut icon" type="image/ico" href="http://www.datatables.net/media/images/favicon.ico" />
6   
7    <title>DataTables example</title>
8    <style type="text/css" title="currentStyle">
9      @import "../../media/css/demo_page.css";
10      @import "../../media/css/demo_table.css";
11    </style>
12    <script type="text/javascript" language="javascript" src="../../media/js/jquery.js"></script>
13    <script type="text/javascript" language="javascript" src="../../media/js/jquery.dataTables.js"></script>
14    <script type="text/javascript" charset="utf-8">
15      $(document).ready(function() {
16        $('#example').dataTable( {
17          "bProcessing": true,
18          "bServerSide": true,
19          "sAjaxSource": "../examples_support/server_processing.php"
20        } );
21      } );
22    </script>
23  </head>
24  <body id="dt_example">
25    <div id="container">
26      <div class="full_width big">
27        <i>DataTables</i> server-side processing example
28      </div>
29     
30      <h1>Preamble</h1>
31      <p>There are many ways to get your data into DataTables, and if you are working with seriously large databases, you might want to consider using the server-side options that DataTables provides. Basically all of the paging, filtering, sorting etc that DataTables does can be handed off to a server (or any other data source - Google Gears or Adobe Air for example!) and DataTables is just an events and display module.</p>
32      <p>The example here shows a very simple display of the CSS data (used in all my other examples), but in this instance coming from the server on each draw. Filtering, multi-column sorting etc all work as you would expect.</p>
33     
34      <h1>Live example</h1>
35      <div id="dynamic">
36<table cellpadding="0" cellspacing="0" border="0" class="display" id="example">
37  <thead>
38    <tr>
39      <th width="20%">Rendering engine</th>
40      <th width="25%">Browser</th>
41      <th width="25%">Platform(s)</th>
42      <th width="15%">Engine version</th>
43      <th width="15%">CSS grade</th>
44    </tr>
45  </thead>
46  <tbody>
47    <tr>
48      <td colspan="5" class="dataTables_empty">Loading data from server</td>
49    </tr>
50  </tbody>
51  <tfoot>
52    <tr>
53      <th>Rendering engine</th>
54      <th>Browser</th>
55      <th>Platform(s)</th>
56      <th>Engine version</th>
57      <th>CSS grade</th>
58    </tr>
59  </tfoot>
60</table>
61      </div>
62      <div class="spacer"></div>
63     
64     
65      <h1>Initialisation code</h1>
66      <pre>$(document).ready(function() {
67  $('#example').dataTable( {
68    "bProcessing": true,
69    "bServerSide": true,
70    "sAjaxSource": "../examples_support/server_processing.php"
71  } );
72} );</pre>
73     
74     
75      <h1>Server side (PHP) code</h1>
76      <pre>&lt;?php
77  /*
78   * Script:    DataTables server-side script for PHP and MySQL
79   * Copyright: 2010 - Allan Jardine
80   * License:   GPL v2 or BSD (3-point)
81   */
82 
83  /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
84   * Easy set variables
85   */
86 
87  /* Array of database columns which should be read and sent back to DataTables. Use a space where
88   * you want to insert a non-database field (for example a counter or static image)
89   */
90  $aColumns = array( 'engine', 'browser', 'platform', 'version', 'grade' );
91 
92  /* Indexed column (used for fast and accurate table cardinality) */
93  $sIndexColumn = "id";
94 
95  /* DB table to use */
96  $sTable = "ajax";
97 
98  /* Database connection information */
99  $gaSql['user']       = "";
100  $gaSql['password']   = "";
101  $gaSql['db']         = "";
102  $gaSql['server']     = "localhost";
103 
104 
105  /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
106   * If you just want to use the basic configuration for DataTables with PHP server-side, there is
107   * no need to edit below this line
108   */
109 
110  /*
111   * MySQL connection
112   */
113  $gaSql['link'] =  mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) or
114    die( 'Could not open connection to server' );
115 
116  mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
117    die( 'Could not select database '. $gaSql['db'] );
118 
119 
120  /*
121   * Paging
122   */
123  $sLimit = "";
124  if ( isset( $_GET['iDisplayStart'] ) &amp;&amp; $_GET['iDisplayLength'] != '-1' )
125  {
126    $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
127      mysql_real_escape_string( $_GET['iDisplayLength'] );
128  }
129 
130 
131  /*
132   * Ordering
133   */
134  if ( isset( $_GET['iSortCol_0'] ) )
135  {
136    $sOrder = "ORDER BY  ";
137    for ( $i=0 ; $i&lt;intval( $_GET['iSortingCols'] ) ; $i++ )
138    {
139      if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
140      {
141        $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
142          ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
143      }
144    }
145   
146    $sOrder = substr_replace( $sOrder, "", -2 );
147    if ( $sOrder == "ORDER BY" )
148    {
149      $sOrder = "";
150    }
151  }
152 
153 
154  /*
155   * Filtering
156   * NOTE this does not match the built-in DataTables filtering which does it
157   * word by word on any field. It's possible to do here, but concerned about efficiency
158   * on very large tables, and MySQL's regex functionality is very limited
159   */
160  $sWhere = "";
161  if ( $_GET['sSearch'] != "" )
162  {
163    $sWhere = "WHERE (";
164    for ( $i=0 ; $i&lt;count($aColumns) ; $i++ )
165    {
166      $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
167    }
168    $sWhere = substr_replace( $sWhere, "", -3 );
169    $sWhere .= ')';
170  }
171 
172  /* Individual column filtering */
173  for ( $i=0 ; $i&lt;count($aColumns) ; $i++ )
174  {
175    if ( $_GET['bSearchable_'.$i] == "true" &amp;&amp; $_GET['sSearch_'.$i] != '' )
176    {
177      if ( $sWhere == "" )
178      {
179        $sWhere = "WHERE ";
180      }
181      else
182      {
183        $sWhere .= " AND ";
184      }
185      $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
186    }
187  }
188 
189 
190  /*
191   * SQL queries
192   * Get data to display
193   */
194  $sQuery = "
195    SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
196    FROM   $sTable
197    $sWhere
198    $sOrder
199    $sLimit
200  ";
201  $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
202 
203  /* Data set length after filtering */
204  $sQuery = "
205    SELECT FOUND_ROWS()
206  ";
207  $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
208  $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
209  $iFilteredTotal = $aResultFilterTotal[0];
210 
211  /* Total data set length */
212  $sQuery = "
213    SELECT COUNT(".$sIndexColumn.")
214    FROM   $sTable
215  ";
216  $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
217  $aResultTotal = mysql_fetch_array($rResultTotal);
218  $iTotal = $aResultTotal[0];
219 
220 
221  /*
222   * Output
223   */
224  $output = array(
225    "sEcho" =&gt; intval($_GET['sEcho']),
226    "iTotalRecords" =&gt; $iTotal,
227    "iTotalDisplayRecords" =&gt; $iFilteredTotal,
228    "aaData" =&gt; array()
229  );
230 
231  while ( $aRow = mysql_fetch_array( $rResult ) )
232  {
233    $row = array();
234    for ( $i=0 ; $i&lt;count($aColumns) ; $i++ )
235    {
236      if ( $aColumns[$i] == "version" )
237      {
238        /* Special output formatting for 'version' column */
239        $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
240      }
241      else if ( $aColumns[$i] != ' ' )
242      {
243        /* General output */
244        $row[] = $aRow[ $aColumns[$i] ];
245      }
246    }
247    $output['aaData'][] = $row;
248  }
249 
250  echo json_encode( $output );
251?&gt;</pre>
252     
253     
254      <h1>Other examples</h1>
255      <h2>Basic initialisation</h2>
256      <ul>
257        <li><a href="../basic_init/zero_config.html">Zero configuration</a></li>
258        <li><a href="../basic_init/filter_only.html">Feature enablement</a></li>
259        <li><a href="../basic_init/table_sorting.html">Sorting data</a></li>
260        <li><a href="../basic_init/multi_col_sort.html">Multi-column sorting</a></li>
261        <li><a href="../basic_init/multiple_tables.html">Multiple tables</a></li>
262        <li><a href="../basic_init/hidden_columns.html">Hidden columns</a></li>
263        <li><a href="../basic_init/dom.html">DOM positioning</a></li>
264        <li><a href="../basic_init/state_save.html">State saving</a></li>
265        <li><a href="../basic_init/alt_pagination.html">Alternative pagination styles</a></li>
266        <li>Scrolling:
267          <a href="../basic_init/scroll_x.html">Horizontal</a> /
268          <a href="../basic_init/scroll_y.html">Vertical</a> /
269          <a href="../basic_init/scroll_xy.html">Both</a> /
270          <a href="../basic_init/scroll_y_theme.html">Themed</a> /
271          <a href="../basic_init/scroll_y_infinite.html">Infinite</a>
272        </li>
273        <li><a href="../basic_init/language.html">Change language information (internationalisation)</a></li>
274        <li><a href="../basic_init/themes.html">ThemeRoller themes (Smoothness)</a></li>
275      </ul>
276     
277      <h2>Advanced initialisation</h2>
278      <ul>
279        <li><a href="../advanced_init/events_pre_init.html">Events (pre initialisation)</a></li>
280        <li><a href="../advanced_init/events_post_init.html">Events (post initialisation)</a></li>
281        <li><a href="../advanced_init/column_render.html">Column rendering</a></li>
282        <li><a href="../advanced_init/html_sort.html">Sorting without HTML tags</a></li>
283        <li><a href="../advanced_init/dom_multiple_elements.html">Multiple table controls (sDom)</a></li>
284        <li><a href="../advanced_init/length_menu.html">Defining length menu options</a></li>
285        <li><a href="../advanced_init/dom_toolbar.html">Custom toolbar (element) around table</a></li>
286        <li><a href="../advanced_init/highlight.html">Row highlighting with CSS</a></li>
287        <li><a href="../advanced_init/complex_header.html">Column grouping through col/row spans</a></li>
288        <li><a href="../advanced_init/row_grouping.html">Row grouping</a></li>
289        <li><a href="../advanced_init/row_callback.html">Row callback</a></li>
290        <li><a href="../advanced_init/footer_callback.html">Footer callback</a></li>
291        <li><a href="../advanced_init/language_file.html">Change language information from a file (internationalisation)</a></li>
292      </ul>
293     
294      <h2>Data sources</h2>
295      <ul>
296        <li><a href="../data_sources/dom.html">DOM</a></li>
297        <li><a href="../data_sources/js_array.html">Javascript array</a></li>
298        <li><a href="../data_sources/ajax.html">Ajax source</a></li>
299        <li><a href="../data_sources/server_side.html">Server side processing</a></li>
300      </ul>
301     
302      <h2>Server-side processing</h2>
303      <ul>
304        <li><a href="../server_side/server_side.html">Obtain server-side data</a></li>
305        <li><a href="../server_side/custom_vars.html">Add extra HTTP variables</a></li>
306        <li><a href="../server_side/post.html">Use HTTP POST</a></li>
307        <li><a href="../server_side/column_ordering.html">Custom column ordering (in callback data)</a></li>
308        <li><a href="../server_side/pipeline.html">Pipelining data (reduce Ajax calls for paging)</a></li>
309        <li><a href="../server_side/row_details.html">Show and hide details about a particular record</a></li>
310        <li><a href="../server_side/select_rows.html">User selectable rows (multiple rows)</a></li>
311      </ul>
312     
313      <h2>API</h2>
314      <ul>
315        <li><a href="../api/add_row.html">Dynamically add a new row</a></li>
316        <li><a href="../api/multi_filter.html">Individual column filtering (using "input" elements)</a></li>
317        <li><a href="../api/multi_filter_select.html">Individual column filtering (using "select" elements)</a></li>
318        <li><a href="../api/highlight.html">Highlight rows and columns</a></li>
319        <li><a href="../api/row_details.html">Show and hide details about a particular record</a></li>
320        <li><a href="../api/select_row.html">User selectable rows (multiple rows)</a></li>
321        <li><a href="../api/select_single_row.html">User selectable rows (single row) and delete rows</a></li>
322        <li><a href="../api/editable.html">Editable rows (with jEditable)</a></li>
323        <li><a href="../api/form.html">Submit form with elements in table</a></li>
324        <li><a href="../api/counter_column.html">Index column (static number column)</a></li>
325        <li><a href="../api/show_hide.html">Show and hide columns dynamically</a></li>
326        <li><a href="../api/api_in_init.html">API function use in initialisation object (callback)</a></li>
327        <li><a href="../api/tabs_and_scrolling.html">DataTables scrolling and tabs</a></li>
328        <li><a href="../api/regex.html">Regular expression filtering</a></li>
329      </ul>
330     
331      <h2>Plug-ins</h2>
332      <ul>
333        <li><a href="../plug-ins/plugin_api.html">Add custom API functions</a></li>
334        <li><a href="../plug-ins/sorting_plugin.html">Sorting and type detection</a></li>
335        <li><a href="../plug-ins/paging_plugin.html">Custom pagination controls</a></li>
336        <li><a href="../plug-ins/range_filtering.html">Range filtering / custom filtering</a></li>
337        <li><a href="../plug-ins/dom_sort.html">Live DOM sorting</a></li>
338        <li><a href="../plug-ins/html_sort.html">Automatic HTML type detection</a></li>
339      </ul>
340     
341     
342      <p>Please refer to the <a href="http://www.datatables.net/"><i>DataTables</i> documentation</a> for full information about its API properties and methods.</p>
343     
344     
345      <div id="footer" style="text-align:center;">
346        <span style="font-size:10px;">DataTables &copy; Allan Jardine 2008-2010</span>
347      </div>
348    </div>
349  </body>
350</html>
Note: See TracBrowser for help on using the repository browser.