Custom MySQL Query
Custom MySQL Query
Manny89
Posts: 5Questions: 0Answers: 0
Hi,
I'm new to DataTables and while I like the functionality so far, I was hoping to receive a little support.
First of all, I have loaded a list with more than 2,500 results. This causes a slow page load prior to the DataTables script kicking in and cutting the table down to 10 rows.
I have read other support requests for server side loading (sAjaxSource) and this works fine for database queries that only use one table. My database tables are normalised, however, and make use of primary and foreign keys to pull in data from multiple tables.
Therefore, I was wondering if it was possible to provide the DataTables script with a custom MySQL query that also makes use of ORDER BY and LIMIT clauses in order to display the desired results at the front end?
Thanks in advance. I look forward to learning more about how this script works.
I'm new to DataTables and while I like the functionality so far, I was hoping to receive a little support.
First of all, I have loaded a list with more than 2,500 results. This causes a slow page load prior to the DataTables script kicking in and cutting the table down to 10 rows.
I have read other support requests for server side loading (sAjaxSource) and this works fine for database queries that only use one table. My database tables are normalised, however, and make use of primary and foreign keys to pull in data from multiple tables.
Therefore, I was wondering if it was possible to provide the DataTables script with a custom MySQL query that also makes use of ORDER BY and LIMIT clauses in order to display the desired results at the front end?
Thanks in advance. I look forward to learning more about how this script works.
This discussion has been closed.
Replies
If you want to use limit (i.e. server-side processing) with DataTables, which will allow you to easily cope with millions of rows, then the documentation for that is here: http://datatables.net/usage/server-side
Allan
I shall do just that, then.
Allan
It's good to hear that you are able to process 10,000 records without any problems. It shows that my site will be able to quadruple in size without any performance issues in the long term.
set_time_limit(0);
or having to edit the php.ini file and increasing the timeout
I've got this fully operational for most queries (besides complex use of with-clauses and some other specific stuff.)
If you want to see my full code, pm me.
I build the javascript through php (hence no php tags, but you know the drill).
php[code]$query = "select whatever from whatever";[/code]
jQ[code]...
,"fnServerData": function ( sSource, aoData, fnCallback ) {
aoData.push( { "name": "trueQuery", "value": "'.$query.'","type": "POST" } );
[/code]
with php tags this would ofc be[code] aoData.push( { "name": "trueQuery", "value": "<?php echo $query ?>","type": "POST" } )[/code]
On server-side : [code]
$table=(isset($_GET['trueQuery']))?$_GET['trueQuery']:"this is a bad query";
$sTable = " ( $table ) as tab ";[/code]
Giving the query an alias is key.
If you pass on large queries via the url, you have to add the following to the httpd.conf of your apache
[code]LimitRequestLine 65536[/code] (default is 4K)
Be sure to use [code]utf8_decode[/code] on your $_GET vars on server-side if you use special chars in the query or the search filters.
In onder to make things simple, I saved a JSON result into a txt so that I can use the data comparable to "sources/array.txt" in the example (http://datatables.net/release-datatables/examples/ajax/defer_render.html)
So, basically, I have this txt test.txt (generated with a mysql query in a php page) which looks like this (over 2000 rows):
[code]{"spel_id":"2012-09-24 15:43:56","locatie":"white room","speler":"Arne","sum(punten)":"17"},{"spel_id":"2012-09-24 15:43:56","locatie":"white room","speler":"Bjorg","sum(punten)":"26"},{"spel_id":"2012-09-24 15:43:56","locatie":"white room","speler":"Bram","sum(punten)":"-11"},{"spel_id":"2012-09-24 15:43:56","locatie":"white room","speler":"Filip","sum(punten)":"-32"},{"spel_id":"2012-09-24 15:47:29","locatie":"white room","speler":"Filip","sum(punten)":"-12"},{"spel_id":"2012-09-24 15:47:29","locatie":"white room","speler":"Hugo","sum(punten)":"30"},{"spel_id":"2012-09-24 15:47:29","locatie":"white room","speler":"Maarten","sum(punten)":"6"},[/code]
the following file is my code to generate the datatable:
[code]
Test DataTables
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"sAjaxSource": "test.txt",
"bDeferRender": true
} );
} );
kaarting
locatie
kaarter
punten
[/code]
i get "Processing..." and "0 of Showing 0 to 0 of 0 entries" although I get it to work perefectly directly from the DOM.
Please be easy on my with high end prgramming terms.. I didn't have any notice of php less than a month ago :) I'm really new at programming in any way (except for SQL)
Thanks alot in advance!