records are not showing upon clicking next page, searching and sorting not working

records are not showing upon clicking next page, searching and sorting not working

RookieProgrammerRookieProgrammer Posts: 4Questions: 3Answers: 0
edited April 2021 in DataTables

Hi Everyone,

I am completely new to this data table stuff. I am trying to show data from MySQL database using Jquery Data table however I am facing an issue here. So my data loads upon loading the page however whenever I try to next page button it doesn't go to next page and do not load data on screen but it shows required data when I checked my Ajax Response. Moreover, Searching and sorting functions are also not working. I have included all necessary CDNs so I believe source file are not an issue.

your expert guidance will be highly appreciated on this matter on urgent basis....I have mentioned my code as follows...

<?php
include_once 'db_config.php';

ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
error_reporting(E_ERROR);


?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.24/css/jquery.dataTables.min.css"/>
    <title>Pagination-2</title>
</head>
<body>  
<div class="container">
<table id="example" class="table table-hover table-striped">
        <thead>
            <tr>
                <th>User Id</th>
                <th>User name</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Email </th>
                <th>Gender</th>
            </tr>
        </thead>
    </table> 
</div>
<script>
$(document).ready(function() {
    $('#example').DataTable( {
        //"processing": true,
        "serverSide": true,
        "ajax": {
            url:"pagi.php",
            method:"POST",
        "columns": [
            {"data": "user_id"},
            {"data": "user_name"},
            {"data": "fname"},
            {"data": "lname"},
            {"data": "eml"},
            {"data": "gnd"}
        ]
        }
    });
});
</script>
</body>
</html>
============================================================================================
<?php

include_once 'db_config.php';

ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
error_reporting(E_ERROR);

$start = $_POST['start'];
$end = $_POST['length'];

$sql = $conn->query("SELECT * FROM user_list LIMIT $start, $end");  
$sql->setFetchMode(PDO::FETCH_ASSOC);

$sql1 = $conn->query("SELECT * FROM user_list");  
$sql1->setFetchMode(PDO::FETCH_ASSOC);
//$page_result = $sql;  
$total_records = $sql1->rowCount(); 
$data = array();
while($row = $sql->fetch()){ 
    $data[] = array(
            $row['user_id'],
            $row['user_name'],
            $row['fname'],
            $row['lname'],
            $row['eml'],
            $row['gnd']
        );
};
$output  = array(
    'draw' => 1,
    'recordsTotal' => $total_records,
    'recordsFiltered' => $total_records,
    'data' => $data,
);
echo json_encode($output);
?>  

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 15,118Questions: 1Answers: 2,583
    edited April 2021 Answer ✓

    You've enabled serverSide so the server script should only return the data for the required page. It looks like you've hard-wired draw to 1 - this needs to match the draw in the request. The protocol is discussed here. Also see examples here.

    To make things simpler, if you're not expecting many records, say less that 10k, you can remove that option as the client is able to efficiently deal with that much data.

    Cheers,

    Colin

  • RookieProgrammerRookieProgrammer Posts: 4Questions: 3Answers: 0

    Hey Colin

    Thanks a ton for pointing it out....I do not have much records but it's working smoothly..

This discussion has been closed.