Home / WordPress custom query and pagination

WordPress custom query and pagination

1. Create the view section where data will show

<div class="all_contact_area">
	<form class="ViewListing SubmitAuto"
		action="<?php echo get_site_url().'/wp-admin/admin-ajax.php'?>">
		<input type="hidden" name="action" value="NAME_OF_THE_AJAX">

		<input type="hidden" name="PageNumber" value="1">
		<input type="hidden" name="PerPage" value="10">
		<input type="hidden" name="order_sc" value="DESC">
	</form>
	<div class="ViewTableData">
	</div>
</div>

2. Essential jQuery
– Must have to include jQuery
– Make a view.js or any of your custom name

(function ($) {
	"use strict";
	$(document).ready(function () {
		ViewListing();
	});

	function ViewListing() {
		$('form.ViewListing').submit();
	}

	$(document).on('change', '.ViewListing.SubmitAuto', function () {
		$(this).submit();
	});

	$(document).on('click', '.RefreshData', function () {
		var SubmitIt = $(this).attr('SubmitIt');
		$('.' + SubmitIt).submit();
	});

	$(document).on('submit', 'form.ViewListing', function () {
		var CurrentForm = $(this);
		var ActionURL = $(this).attr('action');
		var FormData = $(this)
			.find(':input')
			.serializeArray();
		var ViewOn = $(this).next('.ViewTableData');

		$.ajax({
			dataType: 'json',
			url: ActionURL,
			type: 'POST',
			data: FormData,
			beforeSend: function () {
				$(ViewOn).block({
					message: '<h3>Processing...</h3>'
				});
			},
			success: function (Response) {
				var MsgSuccess = (Response.MsgSuccess);
				var FormData = (Response.FormData);
				var MsgError = (Response.MsgError);
				if (FormData) {
					ViewOn.html(FormData);
				}
			}
		});
		return false;
	});

	$(document).on('click', '.section_pagination .SetPage', function () {
		var PageNumber = $(this).data('page');
		$(this)
			.closest('.all_contact_area')
			.find('input[name=PageNumber]')
			.val(PageNumber)
			.change()
			.closest('form')
			.submit();
		return false;
	});

	$(document).on('change paste keyup', '.SearchContactLive', function () {
		var Search = $(this).val();
		$('.all_contact_area')
			.find('input[name=Search]')
			.val(Search)
			.change();

	});
}(jQuery));

3. add_action wp_ajax page configure

add_action('wp_ajax_NAME_OF_THE_AJAX', 'NAME_OF_THE_AJAX');
add_action('wp_ajax_nopriv_NAME_OF_THE_AJAX', 'NAME_OF_THE_AJAX');
function NAME_OF_THE_AJAX(){
	global $wpdb;
	extract($_POST);

	$PageNumber = ($PageNumber)?:1;
	$PerPage    = 2;

	if(!$MsgError){
		$querystr = "
			SELECT $wpdb->posts.*
			FROM $wpdb->posts
			WHERE $wpdb->posts.post_status = 'publish'
			AND $wpdb->posts.post_type = 'post'
			ORDER BY $wpdb->posts.$order_by $order_sc
		";
		$TotalData = count($wpdb->get_results($querystr, OBJECT));


		if ($TotalData > 0) {
			$LastPage   = ceil($TotalData / $PerPage);
		}
		if($LastPage < 1){ $LastPage = 1; }

		if($PageNumber > $LastPage){
			$PageNumber = $LastPage;
		}

		if($PageNumber < 2){
			$Offset = 0;
		}else{
			$Offset = ($PageNumber - 1) * $PerPage;
		}


		$querystr     = $querystr." LIMIT $PerPage OFFSET $Offset";
		$allData  = $wpdb->get_results($querystr, OBJECT);
		$SelectedData = count($allData);
		
		ob_start(); ?>
		<table class="table table-hover">
			<thead>
				<tr style="background-color: #EEE;" >
					<th>#</th>
					<th><i class="fas fa-sign-in-alt"></i> <span class="hidden-xs">Name</span></th>
					<th><i class="fas fa-sign-in-alt"></i> <span class="hidden-xs">Email</span></th>
				</tr>
			</thead>
			<tbody>
				<?php if($allData){ foreach($allData as $Invoice){
					?>
					<tr onclick="">
						<td>
							<?php echo $Invoice->ID; ?>
						</td>
						<td><?php echo $Invoice->ID; ?></td>
					</tr>
				<?php } } ?>
			</tbody>
		</table>
		<?php
		if ($LastPage > 1) {
			?>
			<div class="text-right">
				<div class="section_pagination">
					<label class=" text-muted">
						Showing <?php echo $Offset+1; ?> - <?php echo $Offset+$SelectedData; ?> of <?php echo $TotalData; ?> items
					</label>
					<nav>
						<ul class="pagination">
							<?php
							$qData = array(
								'LastPage'   => $LastPage,
								'PageNumber' => $PageNumber
							);
							echo Pagination($qData);
							?>
						</ul>
					</nav>
				</div>
			</div>
			<?php
		}
		?>
		<?php $FormData = ob_get_clean();
	}

	if ($MsgError) {
		$Return->MsgError = implode(', ', $MsgError);
	}
	if ($FormData) {
		$Return->FormData = $FormData;
	}
	if ($MsgSuccess) {
		$Return->MsgSuccess = $MsgSuccess;
	}
	
	echo ($Return)?json_encode($Return):'';
	die(); exit();
}

4. pagination function

function Pagination($Data){
	$LastPage   = $Data['LastPage'];
	$PageNumber = $Data['PageNumber'];
	$PrePage    = $PageNumber - 1;
	$NextPage   = $PageNumber + 1;

	ob_start();
	?>
	<li class="SetPage first <? echo ($LastPage < 2)?'disabled':''; ?>" data-page="1"><span><<</span></li>
	<li class="SetPage previous <? echo ($LastPage < 2)?'disabled':''; ?>" data-page="<?php echo $PrevPage; ?>"><span><</span></li>

	<?
	$PageStart = $PageNumber - 2;

	if(($PageEnd = $PageNumber + 2) < 5){
		$PageEnd = 5;
	}

	if ($PageStart < 1) {
		$PageStart = 1;
	}
	if ($PageEnd > $LastPage) {
		$PageEnd = $LastPage;
	}
	while ($PageStart <= $PageEnd) {
		?>
		<li class="paginate_button SetPage <? echo ($PageStart == $PageNumber)?'active':'';?>" data-page="<?php echo $PageStart; ?>"><span><?php echo $PageStart; ?></span></li>
		<?
		$PageStart++;
	}
	?>

	<li class="paginate_button SetPage next <? echo ($PageNumber == $LastPage)?'disabled':''; ?>" data-page="<?php echo $NextPage; ?>"><span>></span></li>
	<li class="paginate_button SetPage last <? echo ($PageNumber == $LastPage)?'disabled':''; ?>" data-page="<?php echo $LastPage; ?>"><span>>></span></li>

	<?
	return ob_get_clean();
}

all rights reserved