Custom MySQL Query

Manny89

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.

allan

If you are dealing with around 2'500 rows, I'd suggest having a script on the server which returns the data in a JSON format and enable deferred rendering in DataTables. In that manner, you can craft your SQL however you want - see: http://datatables.net/release-datatables/examples/ajax/defer_render.html

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

Manny89

So, basically, it would be better to use my SQL query to create a JSON file and then reference that in the DataTables code with the sAjaxSource line?

I shall do just that, then.

allan

Yes - I think that's the jist of it. You probably wouldn't create a static JSON file, just output the JSON that is created dynamically, but that's the idea.

Allan

Chytkam

I do something similar with my data Manny but I use datatables to access a MYSQL database "view" which is a single view of multiple tables built on joins. It acts like one table and i can limit and order via the datatables api. I know some views can cause performance issues but it shouldn't with only 2500 records, i process over 10k records using some basic views to join data.

Manny89

I would normally go with a VIEW but the script I am writing is to tie in with a CMS that is coded to work dynamically every time a new site launches. For an individual site, I would use a VIEW, but not in this instance.

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.

Chytkam

Yeah i actually have tables that process a lot more than that, it just depends on how long you want to look at the processing indicator, for the large tables i just paginate them and they work fine. The only issue I have ran into is while using server side processing, my php ajax source that generates my json for me will have a timeout parameter. I have solved it either using

set_time_limit(0);

or having to edit the php.ini file and increasing the timeout

Osiris

Just in case you still need to know how to use specific queries with server-side scripting ...
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.

Rodriguez

I'm having the same issue as the TS. Looks pretty straight forward, but it doesn't seem to work for me :(

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!

Rodriguez

Looking further into this I guess the JSON format I'm using is not correct. I'm gonna have to look what's going wrong with creating the JSON file.