How to load thousands of records (json) generated from own php

santycg

Hi!

I have created a Datatable which loads its data from a custom made PHP file that creates a JSON string.
That JSON contains ALL the products from a Prestashop shop, using its API.

So I connect to Prestashop API, get ALL the products and generate the JSON manually, which is loaded by the Datatable:

.........
"processing": true,
"serverSide": false,
"ajax": {
"url": 'myscript.php',
"type": "GET",
"dataType": "json",
"contentType": "application/json; charset=utf-8"
},
.........

If I have just a few products in Prestashop, it works fine. The problem is that with 15.000 products in Prestashop, it obviously ends the connection with time-out. The problem is of course in my script, that tries to load ALL the products instead of, the first 100, then the next 100, etc.

I need to know how can I do this with Datatables. How to tell my script to retrieve more records, etc.
I mean... How can I communicate the Datatable with my PHP script to generate/retrieve more records from Prestashop? Is it possible?

Of course I will have to modify my script to ask for specific range of products. I need to know what range to ask for, depending on the data already loaded in the datatable, the current page, etc. I hope you understand what I mean.

Thanks

kthorngren

Use Server Side Processing. The protocol is discussed here. Your server script will need to handle the parameters sent and respond with the documented response parameters. you can see some examples here.

Kevin

santycg

Thanks, kthorngren.

I tried this and I understand what I need now.

First I tried with Datatables ajax 'GET' method and I received an error, probably because the request URL was too long (there are a lot of columns).

Then I tried with POST method and it works fine. Now I need to know if I can filter Prestashop data by ranges, pages, etc.

kthorngren

Here is the example ssp.class.php script that are used by examples. Maybe you can use it as a guide to learn how to perform basic sorting, searching and paging of your data.

Kevin

santycg

Thanks, kthorngren.

I knew ssp.class.php, I already use it for retrieving data from MySQL database. However, doing it manually for retrieving data from Prestashop API is alittle bit different, but I finally achieved it!

However, I have a question regarding recordsTotal and recordsFiltered...

It is not possible for me to get the "total records", the "filtered records" and the first "page" of records in the same API request (there are more than 15.000 records). I have to make three API requests to obtain recordsTotal, recordsFiltered and data (PS API limitation).

Is there any advantage of getting JUST the recordsTotal value? Or will it be the same (no effect) to the Datatable, so it doesn't matter if I get ir or not?

Thanks!

kthorngren

I suspect the recordsTotal and recrodsFiltered are used only for the info element, ie, Showing 1 to 10 of 57 entries and to build the proper number of paging buttons. I could be wrong though. You can test it to see.

Depending on your needs you might be able to get away with returning bogus values. You can turn of the info element display or create your own. You can create your own paging buttons - maybe just a forward and back - and use the page() API to move between the pages.

Kevin

santycg

Thanks, Kevin.

Yes, I did some tests and it affects the datatable pagination. The problem is that I can't count the 'total records', 'filtered records' and retrieve the data in a single request, so I will have to do it manually or hide the pagination buttons and make a custom one.

Thanks!

allan

Unfortunately the total number of records is a requirement of DataTables' server-side processing mode, so it can perform the paging correctly.

recordsTotal and recordsFiltered will be equal if you don't apply any filtering.

I have to make three API requests to obtain recordsTotal, recordsFiltered and data (PS API limitation).

That is the case with SQL databases as well - three queries are required to get those three pieces of information. Can the API not give you just the numbers, or does it need to send all the records back?!

Allan