I'm a bit new to php and am having some trouble combining the functionality of two custom queries in Wordpress in order to (1) get pagination to work properly, and (2) sort my posts according to a voting plugin.
(1) The following is a custom query, which is working with pagination, but does not sort my posts according to votes (set in a voting plugin).
$total = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wposts.post_status = 'publish'
AND wposts.post_type IN ('youtube', 'vimeo')
GROUP BY wposts.ID
";
$totalposts = $wpdb->get_results($total, OBJECT);
$ppp = intval(get_query_var('posts_per_page'));
$wp_query->found_posts = count($totalposts);
$wp_query->max_num_pages = ceil($wp_query->found_posts / $ppp);
$on_page = intval(get_query_var('paged'));
if($on_page == 0){ $on_page = 1; }
$offset = ($on_page-1) * $ppp;
$wp_query->request = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wposts.post_status = 'publish'
AND wposts.post_type IN ('youtube', 'vimeo')
GROUP BY wposts.ID
LIMIT $ppp OFFSET $offset
";
$pageposts = $wpdb->get_results($wp_query->request, OBJECT);
(2) The following is another custom query. When this query is combined with the function in #3, it does sort my posts according to votes. However it does not work with pagination.
$enddate = date('Y-m-d');
$increment_date = strtotime("+1 day", strtotime( $enddate ));
$enddate = date("Y-m-d", $increment_date);
$increment_date = strtotime("-1 month", strtotime( $enddate ));
$startdate = date("Y-m-d", $increment_date);
$query = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wposts.post_status = 'publish'
AND wposts.post_type IN ('youtube', 'vimeo')
AND post_date >= '$startdate'
AND post_date <= '$enddate'
GROUP BY wposts.ID
";
(3) And the function for sort according to votes is...
function ShowPostByVotes($query) {
global $wpdb, $voteiu_databasetable;
$upperlimit = get_option('voteiu_limit');
if ($upperlimit == '') { $upperlimit = 100; } $lowerlimit = 0;
$votesarray = array();
$pageposts = $wpdb->get_results($query, OBJECT);
$query_limited = $query." LIMIT ".$lowerlimit.", ".$upperlimit;
$posttablecontents = mysql_query( $query_limited );
while ($row = mysql_fetch_array($posttablecontents)) {
$post_id = $row['ID'];
$vote_array = GetVotes($post_id, "array");
array_push($votesarray, array(GetVotes($post_id)));
}
array_multisort($votesarray, SORT_DESC, $pageposts);
$output = $pageposts;
return $output;
}
My difficulty is that I can't seem to create a query/function that both sorts my posts by votes, and also works with pagination...I apologize I know it's a bit of a big question, but I've been struggling with this for a few days with no luck and can't seem to get anywhere.
