WordPress Orders Pagination Setup
//esc_html_e( 'Reports', 'textdomain' );
global $wpdb;
$results = $wpdb->get_results("select id , product_id,price,email,first_name,last_name, order_status, payer_order_id,order_date FROM " . $wpdb->prefix . "orders WHERE 1 ORDER BY order_date DESC");
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
// if ($_POST['email'] == "vipul.kumar@123789.org") {
//print_r($_POST);
$curl = curl_init();
curl_setopt_array($curl, array(
CURLOPT_URL => "https://www.astrowow.com/apis/requeueOrder",
CURLOPT_RETURNTRANSFER => true,
CURLOPT_ENCODING => "",
CURLOPT_MAXREDIRS => 10,
CURLOPT_TIMEOUT => 30,
CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
CURLOPT_CUSTOMREQUEST => "POST",
CURLOPT_POSTFIELDS => $_POST,
CURLOPT_USERPWD => 'astro-affiliates:wFU9jZtq',
CURLOPT_HTTPHEADER => array(
"content-type: multipart/form-data;"
),
));
$response = curl_exec($curl);
$err = curl_error($curl);
curl_close($curl);
if ($err) {
// echo "cURL Error #:" . $err;
$api_res = json_encode($err);
} else {
$api_res = $response;
}
$resAr = json_decode($api_res, true);
//print_r($resAr);
if (isset($resAr['status']) && ($resAr['status'] == 200)) {
echo 'Resend Successfull!';
} else {
echo 'Failed to Resend';
}
}
?>
<script>
</script><script>
</script><script>
</script><style>
.widefat .check-column {
padding: 10px!important;
}
html, body {
max-width: 100%;
overflow-x: hidden;
}
</style>Report Orders
Order ID
Product
Email
Name
DOB
Birth Place
State
Gender
Date
Resend
<script>
$(function(){
$("#example").dataTable();
});
/* $('.ajax-resend-from1').submit(function(e){
//var payer_order_id = $("#payer_order_id").val();
var resendForm = 1;
var ajaxurl = '<?php echo admin_url("admin-ajax.php"); ?>';
$.ajax({
type: "POST",
dataType:'json',
//url: "/wp-admin/admin-ajax.php",
action: 'resend_from',
// data: resendForm,
success: function(response) {
console.log(response);
}
});
return false;
}); */
$(document).ready( function() {
$('.hidep').delay(1000).fadeOut();
});
</script>### Step 1: Update the SQL Query
Modify your SQL query to fetch only a specific number of records based on the current page.
### Step 2: Get the Total Number of Records
You will also need to get the total number of records to calculate the pagination links.
### Step 3: Generate Pagination Links
Use the `paginate_links` function to generate the pagination controls.
Here's the modified code:
function my_custom_menu_page() {
global $wpdb;
// Define how many results you want per page
$results_per_page = 10; // Change this to whatever number you want
// Get the current page number
$current_page = isset($_GET['paged']) ? absint($_GET['paged']) : 1;
// Calculate the offset for the query
$offset = ($current_page - 1) * $results_per_page;
// Get total number of records
$total_orders = $wpdb->get_var("SELECT COUNT(*) FROM " . $wpdb->prefix . "orders");
// Fetch results with limit and offset
$results = $wpdb->get_results("SELECT id, product_id, price, email, first_name, last_name, order_status, payer_order_id, order_date
FROM " . $wpdb->prefix . "orders
ORDER BY order_date DESC
LIMIT $results_per_page OFFSET $offset");
// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
// Your cURL logic here...
}
?>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"/>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap.min.css"/>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap.min.js"></script>
<style>
.widefat .check-column {
padding: 10px!important;
}
html, body {
max-width: 100%;
overflow-x: hidden;
}
</style>
<div class="container">
<h2>Report Orders</h2><br>
<table class="table widefat fixed" id="example" cellspacing="0">
<thead>
<tr>
<th class="manage-column column-cb check-column" scope="col">Order ID</th>
<th class="manage-column column-cb check-column" scope="col">Product</th>
<th class="manage-column column-cb check-column" scope="col">Email</th>
<th class="manage-column column-cb check-column" scope="col">Name</th>
<th class="manage-column column-cb check-column" scope="col">DOB</th>
<th class="manage-column column-cb check-column" scope="col">Birth Place</th>
<th class="manage-column column-cb check-column" scope="col">State</th>
<th class="manage-column column-cb check-column" scope="col">Gender</th>
<th class="manage-column column-cb check-column" scope="col">Date</th>
<th class="manage-column column-cb check-column" scope="col">Resend</th>
</tr>
</thead>
<tbody>
<?php
foreach($results as $key => $res) {
$thepost = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . $wpdb->prefix . "products WHERE id = %d", $res->product_id));
$thepost1 = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . $wpdb->prefix . "birthdata WHERE order_id = %d", $res->id));
$download = ($res->payer_order_id != '')
? '<br><a href="http://52.52.17.200/reports/var/spool/'.$res->payer_order_id.'.bundle.pdf" target="_blank">Download PDF</a>'
: '';
$resend = ($res->payer_order_id != '')
? "<form class='form' name='resend_form' action='' method='post'>
<input type='hidden' name='order' id='order".$res->payer_order_id."' value =".$res->payer_order_id." >
<button type='submit' class='btn btn-primary'>Resend</button>
</form>"
: '';
echo "<tr class='kk'>
<td>".$res->payer_order_id." ".$download."</td>
<td>".($res->product_id == 85 ? "Sun, Moon & Rising Report" : $thepost->name)."</td>
<td>".$res->email."</td>
<td>".$thepost1->first_name." ".$thepost1->last_name."</td>
<td>".$thepost1->month."/".$thepost1->day."/".$thepost1->year." ".$thepost1->hour." : ".$thepost1->minute."</td>
<td>".$thepost1->place."</td>
<td>".$thepost1->state."</td>
<td>".$thepost1->gender."</td>
<td>".$res->order_date."</td>
<td>".$resend."</td>
</tr>";
}
?>
</tbody>
</table>
<?php
// Pagination
$total_pages = ceil($total_orders / $results_per_page);
$pagination_links = paginate_links(array(
'total' => $total_pages,
'current' => $current_page,
'format' => '?paged=%#%',
'prev_text' => __('« Previous'),
'next_text' => __('Next »'),
));
if ($pagination_links) {
echo '<div class="pagination">' . $pagination_links . '</div>';
}
?>
</div>
<script>
$(function() {
$("#example").dataTable();
});
$(document).ready(function() {
$('.hidep').delay(1000).fadeOut();
});
</script>
<?php
}### Explanation of Changes:
1. **Pagination Variables**:
– Defined `$results_per_page` to set how many orders to show per page.
– Retrieved the current page using `$_GET['paged']`, defaulting to 1 if not set.
– Calculated the `$offset` for the SQL query.
2. **SQL Query Adjustments**:
– Updated the SQL query to use `LIMIT` and `OFFSET` based on the current page.
3. **Total Records**:
– Retrieved the total number of orders using a separate query for pagination calculations.
4. **Pagination Links**:
– Used `paginate_links` to generate pagination controls and displayed them after the table.
### Note:
– Adjust `$results_per_page` to fit your design and requirements.
– Make sure to handle potential SQL injection and validate input where necessary.
### Step 1: Modify the DataTables Initialization
Ensure that DataTables is initialized with the pagination feature enabled. DataTables will automatically handle pagination based on the number of rows it has.
### Step 2: Remove Manual Pagination Logic
You don't need the PHP pagination logic since DataTables will take care of it.
### Updated Code
Here's the modified code with DataTables handling pagination:
function my_custom_menu_page() {
global $wpdb;
// Fetch results from the database without pagination
$results = $wpdb->get_results("SELECT id, product_id, price, email, first_name, last_name, order_status, payer_order_id, order_date
FROM " . $wpdb->prefix . "orders
ORDER BY order_date DESC");
// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
// Your cURL logic here...
}
?>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"/>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap.min.css"/>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap.min.js"></script>
<style>
.widefat .check-column {
padding: 10px!important;
}
html, body {
max-width: 100%;
overflow-x: hidden;
}
</style>
<div class="container">
<h2>Report Orders</h2><br>
<table class="table widefat fixed" id="example" cellspacing="0">
<thead>
<tr>
<th class="manage-column column-cb check-column" scope="col">Order ID</th>
<th class="manage-column column-cb check-column" scope="col">Product</th>
<th class="manage-column column-cb check-column" scope="col">Email</th>
<th class="manage-column column-cb check-column" scope="col">Name</th>
<th class="manage-column column-cb check-column" scope="col">DOB</th>
<th class="manage-column column-cb check-column" scope="col">Birth Place</th>
<th class="manage-column column-cb check-column" scope="col">State</th>
<th class="manage-column column-cb check-column" scope="col">Gender</th>
<th class="manage-column column-cb check-column" scope="col">Date</th>
<th class="manage-column column-cb check-column" scope="col">Resend</th>
</tr>
</thead>
<tbody>
<?php
foreach($results as $key => $res) {
$thepost = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . $wpdb->prefix . "products WHERE id = %d", $res->product_id));
$thepost1 = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . $wpdb->prefix . "birthdata WHERE order_id = %d", $res->id));
$download = ($res->payer_order_id != '')
? '<br><a href="http://52.52.17.200/reports/var/spool/'.$res->payer_order_id.'.bundle.pdf" target="_blank">Download PDF</a>'
: '';
$resend = ($res->payer_order_id != '')
? "<form class='form' name='resend_form' action='' method='post'>
<input type='hidden' name='order' id='order".$res->payer_order_id."' value =".$res->payer_order_id." >
<button type='submit' class='btn btn-primary'>Resend</button>
</form>"
: '';
echo "<tr class='kk'>
<td>".$res->payer_order_id." ".$download."</td>
<td>".($res->product_id == 85 ? "Sun, Moon & Rising Report" : $thepost->name)."</td>
<td>".$res->email."</td>
<td>".$thepost1->first_name." ".$thepost1->last_name."</td>
<td>".$thepost1->month."/".$thepost1->day."/".$thepost1->year." ".$thepost1->hour." : ".$thepost1->minute."</td>
<td>".$thepost1->place."</td>
<td>".$thepost1->state."</td>
<td>".$thepost1->gender."</td>
<td>".$res->order_date."</td>
<td>".$resend."</td>
</tr>";
}
?>
</tbody>
</table>
</div>
<script>
$(function() {
$("#example").DataTable({
"paging": true, // Enable pagination
"pageLength": 10, // Number of entries to show per page
"lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]] // Pagination options
});
});
$(document).ready(function() {
$('.hidep').delay(1000).fadeOut();
});
</script>
<?php
}### Key Changes Made
1. **Removed Pagination Logic**: The PHP pagination logic and related calculations were removed since DataTables will manage pagination on the client side.
2. **DataTables Initialization**:
– Configured DataTables with the `"paging": true` option to enable pagination.
– Set the default number of entries to display per page to 10 using `"pageLength": 10`.
– Added a length menu to allow users to select how many entries they want to view using `"lengthMenu"`.
3. **Client-Side Rendering**: DataTables will automatically handle the rendering of the table with pagination, search, and sorting functionality.
### Additional Notes
– Ensure that jQuery and DataTables JavaScript/CSS files are correctly loaded and available in your environment.
– You can further customize DataTables options based on your requirements (e.g., search, sort, etc.).
– This approach improves performance since it avoids server-side pagination and utilizes client-side capabilities.
### Step 1: Check JavaScript Console for Errors
Make sure to check the browser's JavaScript console (F12) for any errors that might indicate issues with loading DataTables or jQuery. Any JavaScript errors can prevent the DataTables script from running correctly.
### Step 2: Confirm DataTables and jQuery Versions
Ensure that the versions of jQuery and DataTables you are using are compatible. Here's a setup using more recent versions:
function my_custom_menu_page() {
global $wpdb;
// Fetch results from the database without pagination
$results = $wpdb->get_results("SELECT id, product_id, price, email, first_name, last_name, order_status, payer_order_id, order_date
FROM " . $wpdb->prefix . "orders
ORDER BY order_date DESC");
// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
// Your cURL logic here...
}
?>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/1.11.5/css/dataTables.bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.11.5/js/dataTables.bootstrap.min.js"></script>
<style>
.widefat .check-column {
padding: 10px!important;
}
html, body {
max-width: 100%;
overflow-x: hidden;
}
</style>
<div class="container">
<h2>Report Orders</h2><br>
<table class="table table-striped table-bordered" id="example" cellspacing="0">
<thead>
<tr>
<th>Order ID</th>
<th>Product</th>
<th>Email</th>
<th>Name</th>
<th>DOB</th>
<th>Birth Place</th>
<th>State</th>
<th>Gender</th>
<th>Date</th>
<th>Resend</th>
</tr>
</thead>
<tbody>
<?php
foreach($results as $key => $res) {
$thepost = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . $wpdb->prefix . "products WHERE id = %d", $res->product_id));
$thepost1 = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . $wpdb->prefix . "birthdata WHERE order_id = %d", $res->id));
$download = ($res->payer_order_id != '')
? '<br><a href="http://52.52.17.200/reports/var/spool/'.$res->payer_order_id.'.bundle.pdf" target="_blank">Download PDF</a>'
: '';
$resend = ($res->payer_order_id != '')
? "<form class='form' name='resend_form' action='' method='post'>
<input type='hidden' name='order' id='order".$res->payer_order_id."' value =".$res->payer_order_id." >
<button type='submit' class='btn btn-primary'>Resend</button>
</form>"
: '';
echo "<tr>
<td>".$res->payer_order_id." ".$download."</td>
<td>".($res->product_id == 85 ? "Sun, Moon & Rising Report" : $thepost->name)."</td>
<td>".$res->email."</td>
<td>".$thepost1->first_name." ".$thepost1->last_name."</td>
<td>".$thepost1->month."/".$thepost1->day."/".$thepost1->year." ".$thepost1->hour." : ".$thepost1->minute."</td>
<td>".$thepost1->place."</td>
<td>".$thepost1->state."</td>
<td>".$thepost1->gender."</td>
<td>".$res->order_date."</td>
<td>".$resend."</td>
</tr>";
}
?>
</tbody>
</table>
</div>
<script>
$(document).ready(function() {
$('#example').DataTable({
"paging": true, // Enable pagination
"pageLength": 10, // Default number of entries to show
"lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]] // Length menu options
});
// Fade out messages after 1 second
$('.hidep').delay(1000).fadeOut();
});
</script>
<?php
}### Key Changes
1. **Updated jQuery and DataTables**: Used more recent versions of jQuery (3.6.0) and DataTables (1.11.5) to ensure compatibility.
2. **Corrected Class Names**: Used Bootstrap classes for styling the table (`table-striped table-bordered`).
3. **Simplified Table Markup**: Removed unnecessary attributes from the table and added Bootstrap classes for better styling.
4. **Initialized DataTable in `$(document).ready()`**: Ensured DataTables is initialized when the DOM is fully loaded.
### Step 3: Additional Troubleshooting
If the above code still does not work:
1. **Inspect the Network Tab**: Check the network tab to ensure that all scripts are being loaded correctly without any 404 errors.
2. **Test in Isolation**: If possible, create a new simple page with just the DataTables setup to see if it works in isolation. This can help determine if there's a conflict with other scripts or styles in your WordPress environment.
3. **Check Theme/Plugin Conflicts**: Sometimes themes or plugins can interfere with JavaScript execution. Temporarily disable them to see if the issue persists.
### Step 4: Debugging DataTables Functionality
Add this line right before the DataTable initialization to log the table content and check if it's being initialized correctly:
console.log($('#example').html());This will help you verify if the table rows are populated as expected before DataTables is applied.
If you continue to experience issues, please share any error messages from the console, and I'll be glad to assist further!