Upon reinitializing warning is shown and existing pagination is missing, current pagination show the default 50 records only.
I have alternatively implemented another pagination which is working,but I require the in built javascript pagination.
Below is my HTML code-
<!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">
<title>Data</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<script src="https://code.jquery.com/jquery-3.7.1.js"></script>
<!-- Styles -->
<link href="assets/css/style.css" rel="stylesheet">
<style>
#pagination a {
padding: 5px;
text-decoration: none;
color: #007bff;
}
#pagination .current {
padding: 5px;
font-weight: bold;
color: #000;
}
#loading-message {
text-align: center;
display: none;
}
</style>
<title> Dashboard</title>
<div class="content-wrap">
<div class="container mt-5">
<h5>Journal Subscriptions and Revenue</h5>
<table id="bootstrap-data-table-export" class="table table-striped table-bordered">
<thead>
<tr>
<th>Name</th>
<th>Cust_No_</th>
<th>Journal_ID</th>
<th>Content_Year</th>
<th>Subject</th>
<th>Journal_Title</th>
<th>Type_of_Subscription</th>
</tr>
</thead>
<tbody id="table-body">
<!-- Data will be loaded here -->
<?php foreach ($results as $row): ?>
<tr>
<td><?= htmlspecialchars($row['Name']); ?></td>
<td><?= htmlspecialchars($row['Cust_No_']); ?></td>
<td><?= htmlspecialchars($row['Journal_ID']); ?></td>
<td><?= htmlspecialchars($row['Content_Year']); ?></td>
<td><?= htmlspecialchars($row['Subject']); ?></td>
<td><?= htmlspecialchars($row['Journal_Title']); ?></td>
<td><?= htmlspecialchars($row['Type_of_Subscription']); ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<!-- Pagination Links -->
<div id="pagination">
<!-- Pagination links will be loaded here -->
<?php if ($page > 1): ?>
<a href="?page=<?= $page - 1; ?>">Previous</a>
<?php endif; ?>
<span>Page <?= $page; ?> of <?= $totalPages; ?></span>
<?php if ($page < $totalPages): ?>
<a href="?page=<?= $page + 1; ?>">Next</a>
<?php endif; ?>
</div>
</div>
<div id="loading-message">
<p>Loading more data...</p>
</div>
</div>
<script>
let currentPage = 1;
let totalPages = 1;
let loading = false;
function loadData(page) {
$.ajax({
url: 'journal_data_19.php',
type: 'GET',
data: { page: page },
dataType: 'json',
success: function(data) {
if (data.error) {
alert('Error: ' + data.error);
return;
}
totalPages = data.totalPages;
currentPage = data.currentPage;
const tableBody = $('#table-body');
tableBody.empty(); // Clear existing data
data.results.forEach(row => {
tableBody.append(
`<tr>
<td>${row.Name}</td>
<td>${row.Cust_No_}</td>
<td>${row.Journal_ID}</td>
<td>${row.Content_Year}</td>
<td>${row.Subject}</td>
<td>${row.Journal_Title}</td>
<td>${row.Type_of_Subscription}</td>
</tr>`
);
});
updatePagination();
},
error: function(xhr, status, error) {
console.error('AJAX Error:', status, error);
}
});
}
function updatePagination() {
const paginationElement = document.getElementById('pagination');
if (paginationElement) {
let paginationHtml = '';
// Previous button
if (currentPage > 1) {
paginationHtml += `<a href="#" onclick="loadData(${currentPage - 1}); return false;">Previous</a> `;
}
// Page numbers
for (let i = 1; i <= totalPages; i++) {
if (i === currentPage) {
paginationHtml += `<span class="current">${i}</span> `;
} else {
paginationHtml += `<a href="#" onclick="loadData(${i}); return false;">${i}</a> `;
}
}
// Next button
if (currentPage < totalPages) {
paginationHtml += `<a href="#" onclick="loadData(${currentPage + 1}); return false;">Next</a>`;
}
paginationElement.innerHTML = paginationHtml;
} else {
console.error('Pagination element not found');
}
}
function loadMoreData(page) {
if (loading) return; // Prevent multiple requests at the same time
loading = true;
$('#loading-message').show();
$.ajax({
url: 'journal_data_19.php',
type: 'GET',
data: { page: page },
dataType: 'json',
success: function(data) {
if (data.error) {
alert('Error: ' + data.error);
return;
}
totalPages = data.totalPages;
currentPage = data.currentPage;
const tableBody = $('#table-body');
// Append the fetched rows to the table
data.results.forEach(row => {
tableBody.append(
`<tr>
<td>${row.Name}</td>
<td>${row.Cust_No_}</td>
<td>${row.Journal_ID}</td>
<td>${row.Content_Year}</td>
<td>${row.Subject}</td>
<td>${row.Journal_Title}</td>
<td>${row.Type_of_Subscription}</td>
</tr>`
);
});
loading = false;
$('#loading-message').hide();
},
error: function(xhr, status, error) {
console.error('AJAX Error:', status, error);
loading = false;
$('#loading-message').hide();
}
});
}
$(document).ready(function() {
loadData(currentPage); // Initial load
// Event listener for scrolling
$(window).scroll(function() {
if ($(window).scrollTop() + $(window).height() >= $(document).height() - 100 && !loading && currentPage < totalPages) {
loadMoreData(++currentPage);
}
});
});
//JAVASCRIPT PAGINATION SECTION---------------------$(document).ready(function() {
$('#bootstrap-data-table-export').DataTable({
"processing": true,
"serverSide": true,
"ajax": {
"url": "journal_data_19.php",
"type": "GET",
"dataSrc": function (json) {
// Check if json.data exists and contains data
if (json.data && json.data.length > 0) {
return json.data;
} else {
console.error('No data found or incorrect format');
return [];
}
},
"error": function (xhr, error, thrown) {
console.error('An error occurred while fetching data:', error);
}
},
"pageLength": 50,
"lengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
"columns": [
{ "data": "Name" },
{ "data": "Cust_No_" },
{ "data": "Journal_ID" },
{ "data": "Content_Year" },
{ "data": "Type_of_Subscription" },
{ "data": "Journal_Title" },
{ "data": "Subject" }
],
"paging": true,
"searching": true,
"ordering": true,
"dom": 'Bfrtip',
"buttons": ['copy', 'csv', 'excel', 'pdf', 'print'],
"scrollY": "500px",
"scrollCollapse": true,
"responsive": true
});
});
</script>
<script src="assets/js/lib/jquery.nanoscroller.min.js"></script>
<!-- nano scroller -->
<script src="assets/js/lib/menubar/sidebar.js"></script>
<script src="assets/js/lib/preloader/pace.min.js"></script>
<!-- sidebar -->
<!-- bootstrap -->
<script src="assets/js/scripts.js"></script>
<!-- scripit init-->
<script src="assets/js/lib/data-table/datatables.min.js"></script>
<!-- <script src="assets/js/lib/data-table/buttons.dataTables.min.js"></script> -->
<script src="assets/js/lib/data-table/dataTables.buttons.min.js"></script>
<script src="assets/js/lib/data-table/buttons.flash.min.js"></script>
<script src="assets/js/lib/data-table/jszip.min.js"></script>
<script src="assets/js/lib/data-table/pdfmake.min.js"></script>
<script src="assets/js/lib/data-table/vfs_fonts.js"></script>
<script src="assets/js/lib/data-table/buttons.html5.min.js"></script>
<script src="assets/js/lib/data-table/buttons.print.min.js"></script>
<script src="assets/js/lib/data-table/datatables-init.js"></script>
This is my PHP code-
<?php
session_start();
ob_start(); // Start output buffering
if (!isset($_SESSION['loggedin'])) {
header('Location: index.html');
exit;
}
ini_set('max_execution_time', 0); // No time limit
$DATABASE_HOST = 'localhost';
$DATABASE_USER = 'root';
$DATABASE_PASS = '';
$DATABASE_NAME = 'destination_db';
// Number of records to display per page
$limit = 50;
// Get the current page number from the AJAX request
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
if ($page < 1) $page = 1; // Ensure page number is at least 1
// Calculate the offset for the SQL query
$offset = ($page - 1) * $limit;
// Parameters from DataTables
$start = $_GET['start'];
$length = $_GET['length'];
$searchValue = $_GET['search']['value']; // For search filtering
try {
$pdo = new PDO("mysql:host=$DATABASE_HOST;dbname=$DATABASE_NAME;charset=utf8", $DATABASE_USER, $DATABASE_PASS);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Get the total number of records
$totalStmt = $pdo->query('SELECT COUNT(*) FROM journal_subscription js
JOIN journal_revenue jr
ON js.Cust_No_ = jr.Ship_to_no_
AND js.Type_of_Subscription = jr.Type_of_Sales
AND js.Content_Year = jr.Content_Year
AND js.Journal_ID = jr.Journal_ID');
$totalRows = $totalStmt->fetchColumn();
$totalPages = ceil($totalRows / $limit);
// Prepare the SQL query with LIMIT and OFFSET
$stmt = $pdo->prepare('SELECT js.Cust_No_, js.Name, js.Journal_ID, js.Content_Year, js.Type_of_Subscription, jr.Subject, jr.Journal_Title
FROM journal_subscription js
JOIN journal_revenue jr
ON js.Cust_No_ = jr.Ship_to_no_
AND js.Type_of_Subscription = jr.Type_of_Sales
AND js.Content_Year = jr.Content_Year
AND js.Journal_ID = jr.Journal_ID
LIMIT :limit OFFSET :offset');
// Bind parameters
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
// Execute query
$stmt->execute();
// Fetch results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Return JSON response
echo json_encode([
'results' => $results,
'totalPages' => $totalPages,
'currentPage' => $page
], JSON_HEX_TAG | JSON_HEX_AMP | JSON_HEX_APOS | JSON_HEX_QUOT);
} catch (PDOException $e) {
header('Content-Type: application/json');
echo json_encode(['error' => $e->getMessage()]);
}
ob_end_clean(); // End output buffering and clean output
?>
