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
RookieProgrammer
Posts: 4Questions: 3Answers: 0
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);
<?php
>
?>
<!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);
<?php
>
?>
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
You've enabled
serverSideso the server script should only return the data for the required page. It looks like you've hard-wireddrawto1- this needs to match thedrawin 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
Hey Colin
Thanks a ton for pointing it out....I do not have much records but it's working smoothly..