WordPress pagination with custom MySQL query

Simple pagination with custom query


$per_page = 50; # Set per page
$paged = ( get_query_var('paged') != 0 ) ? get_query_var('paged') : 1 ; ## get paged number
$offset = (( $paged - 1 ) * $per_page); ## set offset

$trial_list = football_trial_list(null, $offset, $per_page);

$trial_total = football_trial_list(null,0,-1);
$total = count($trial_total);

$max_num_pages = ceil( $total / $per_page );

$pagination = football_pagination($max_num_pages);

function football_trial_list($post_id = null, $offset = 0, $per_page = 3, $request = null){
global $wpdb;
$user_id = get_current_user_id();
$table_team = $wpdb->prefix . 'trial';

$distance = "";
if(isset($request) && !empty($request)){
if(isset($request['post_code']) && !empty($request['post_code'])){

$address_data = football_get_latitude_longitude($request['post_code']);
$lat = $address_data['lat'];
$lng = $address_data['lng'];

$distance = ", ( 3959 * acos( cos( radians(".$lat.") ) * cos( radians( tr.lat ) ) * cos( radians( tr.lng ) - radians(".$lng.") ) + sin( radians(".$lat.") ) * sin( radians( tr.lat ) ) ) ) AS distance";
}
}

$query = "SELECT tr.*, p.post_title, p.post_content $distance FROM $table_team AS tr ";
$query .= " INNER JOIN ".$wpdb->prefix."posts AS p ON ( tr.post_id = p.ID ) ";
$query .= " WHERE p.post_type = 'trials' AND p.post_status = 'publish' ";

// if get single post data
if(!empty($post_id) && $post_id != null){
$query .= " AND p.ID = $post_id ";
}

if(isset($request) && !empty($request)){
// if(isset($request['post_code']) && !empty($request['post_code'])){
// $query .= " AND tr.postcode LIKE '%".$request['post_code']."%'";
// }

if(isset($request['trial_name']) && !empty($request['trial_name'])){
$query .= " AND p.post_title LIKE '%".$request['trial_name']."%'";
}
if(isset($request['trial_age']) && !empty($request['trial_age'])){
if($request['trial_age'] >= 35)
$query .= " AND tr.trial_age >= ".$request['trial_age']."";
else
$query .= " AND tr.trial_age <= ".$request['trial_age']."";
}

// if(!empty($request['post_code']) && !empty($request['distance_min']) && !empty($request['distance_max'])){
if(!empty($request['post_code'])){
$query .= " HAVING distance BETWEEN ".$request['distance_min']." AND ".$request['distance_max']." ";
}

// Upcoming Trials
if(isset($request['upcoming']) && $request['upcoming'] = true){
$query .= " AND tr.trial_date >= CURDATE()";
}
}
else{
$query .= " AND tr.user_id = $user_id ";
}

// Upcoming Trials
if(isset($request['upcoming']) && $request['upcoming'] = true)
$query .= " ORDER BY tr.trial_date ASC ";
else
$query .= " ORDER BY p.post_date DESC ";

if($per_page != -1){
$query .= " LIMIT $offset, $per_page";
}

// echo $query; #die;
return $wpdb->get_results( $query, OBJECT );
}
// =============================================
// Default pagination
// =============================================
function football_pagination($max_num_pages){

if($max_num_pages > 1){
$big = 999999999;
$pagination = "<div class='toolbar'>";
$pagination .= paginate_links( array(
'type' => 'list',
'base' => str_replace( $big, '%#%', esc_url( get_pagenum_link( $big ) ) ),
'format' => '?paged=%#%',
'current' => max( 1, get_query_var('paged') ),
'total' => $max_num_pages,
'prev_next' => true,
'prev_text' => 'Prev',
'next_text' => 'Next',
) );
$pagination .= "</div>";

return $pagination;
}
else{
return '';
}
}

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s