How do I use PHP & Datatables AJAX for a giant database?

GodsDead

When I tried to use the Server side scripts for datatables the default option is to pull the entire database! Which on very large databases reaches the PHP max allocated memory, Bumping the memory isn't a fix, I want a solid solution.
I remember years ago to pull data from a large database using Ajax we could pagenate the PHP results, then when you selected a new page from AJAX it would load the next load via PHP using the SQL LIMIT, is this built in to datatables? So we are only querying what we need, not the entire database.
If so can someone please post me an example.

jLinux

It shouldnt... when you use server side, it should send a number of rows to be pulled...

GodsDead

Im most likely doing it wrong, its been a very long time since I used PHP & Ajax with a database! at present im using server_processing.php direct to try and get the data.
https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/server_processing.php

But all im getting is:
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 64 bytes) in /var/www/public/web-stats/inc/ssp.class.php on line 49

allan

Can you link to the page showing the issue please? Also, please show the contents of server_processing.php. Is it expecting GET or POST for example?

How many records per page are you requesting?

Allan

GodsDead

Its pretty much just that exact document, I grab the DB details from constants from a main config:
http://pastebin.com/vXMmvBUD

allan

That script is expecting $_GET parameters. Are you sending GET parameters or POST? It would be useful if you could like to the page please. Have you enabled sserverSide and are you not using the legacy mode. Again, a test case showing the issue would help us to resolve this.

Allan

GodsDead

Ah! Im not sending any POST or GET to the script, im using it RAW so I can understand what its outputting, there are no comments that is what its expecting! I see the universal $_GET call at the bottom now, what is it expecting? Why is this not in the documentation? I want to run the script by itself so I can see the data im getting back, so I can understand the format thats needed for the datatables and to make sure data is infant coming through. I do not have a live version, im building this locally. What do I need to pass into the $_GET? I assume that serversideOption is part of the JS for the datatables, im not at that stage yet, I want to get data being displayed by the PHP script first, but it looks as if there is no error message is no Get/Post is sent which gives grave confusion as this isnt documented either!

allan

Why is this not in the documentation?

It is - server-side processing manual. This details the parameters DataTables and those it expects in return.

What do I need to pass into the $_GET?

See the documentation I linked to above.

I assume that serversideOption is part of the JS for the datatables

Yes - see the serverSide reference documentation.

Allan