CODEWITHSUNDEEP
mysqlpdopaginationdatatables
asterix_jv
asterix_jv

Reputation: 834

how show only ten results per time in datatables?

I need to know how datatable not show all the results all the time. Right now the datatable script call all the users and show in the page, when all are show it proceed to make the paginations(10 per page)...I need to change this form because I have 17,000 users in this table and allways the page freezing when I go to users.php..... I need to know if I need change something in jquery.dataTables.min.js or jquery.dataTables.js? or I need to make different the call to show the users?

I use the next code to show the users:

<? $sql = 'SELECT * FROM PACIENTES';
    $result = $conn->query($sql);
    while($row = $result->fetch(PDO::FETCH_ASSOC)) {
?>

Best regards!

Upvotes: 0

Views: 1333

Answers (1)

Jay Rizzi
Jay Rizzi

Reputation: 4304

it shouldnt be rendering the 17,000 records at once, it should be using the request collection of variables such as iDisplayStart and iDisplayLength to determine a LIMIT that you include in your query that will render X amount of rows from the total data set, if you never set the idisplayLength it should be passing along a 10 as default

I know this isnt PDO but it should be very similar syntax

 /**
 * Paging
 */
$sLimit = "";
if ( isset( $input['iDisplayStart'] ) && $input['iDisplayLength'] != '-1' ) {
    $sLimit = " LIMIT ".intval( $input['iDisplayStart'] ).", ".intval( $input['iDisplayLength'] );
}

http://datatables.net/development/server-side/php_mysqli

if you have this LIMIT in your query and it still is taking forever, you may need to refactor/optimize your sql statement, as it is not a DT issue

Upvotes: 1

Related Questions