How to limit sql query using pageLength?

Mon

I'd like to retrieve record from database limit with pageLength. How can I do this?
Let's say. If I add "pageLength": 20. My sql query will be "select * from students order by desc limit pageLength". How can I get pageLength value in php? Thank you.

jLinux

I think you would be best off using the serverSide value, its a lot more advanced, but it does what you're looking for.

Read the manual before jumping straight to "I dont know how to use it" :-D

Mon

Hi jLinux, thanks for your answer. I tried server side scripting also. I faced the same issue at there. I used $_REQUEST['length'].
$totalrow = "select * from students";
$limitrow = "select * from studens limit $_REQUEST['length']";
I assign totalrow to recordsTotal and limitrow to recordsFiltered.
But pagination shows only one page because of $_REQUEST['length']. If I change value of recordsFiltered to $totalrow . It works fine. I don't want to retrieve all records whenever user access to my page. How could I do this? Thank you.

allan

limitrow to recordsFiltered

That's not how server-side processing works. From the documentation that jLinux linked to:

recordsFiltered - Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned for this page of data).

If you are going to use server-side processing then you need to fully implement the protocol described on that page. If you don't need filtering on the table, then that number can also just match the recordsTotal - but if you do need filtering, then your server-side processing script will need to be a bit more complex.

Allan

Mon

Thanks Allan. What I worry is to face with large amount of records in database. I become more clear after reading http://datatables.net/forums/discussion/8789/tips-for-using-datatables-with-very-large-data-sets. Thank you for your explanation. I am going to use server side scripting and will let datatable control my records. By the way, I will follow this example http://legacy.datatables.net/release-datatables/examples/server_side/server_side.html. Will it be okay to load large amount of records? Is there any option to load large record?Please kindly give me suggestion. Thank you.

allan

I would suggest not using any of the code on the legacy site for new projects. It is marked as legacy for a reason!

The DataTables package includes example server-side processing scripts.

Allan

Mon

Thanks Allan. I will try with it. Thank you.