Sorting one widget by another plugins data WordPress

John Cotton answers:

Hi

I wrote something similar myself a little while ago, but never ended up using it (so it's a bit rough).

But you're welcome to play with it and - at the very least - it might give you some ideas.

Regards

John
<?php
/*
Plugin Name: Post Ratings Rank Widget
Version: 0.1
Plugin URI: http://www.dynamicarray.co.uk/wordpress/plugins
Author: John Cotton
Author URI: http://www.dynamicarray.co.uk
Description: Ranks posts based on WP-PostRatings - This Week, Last Week, Last Month
*/

class post_ratings_rank_widget extends WP_Widget {

function post_ratings_rank_widget() {
//Constructor
$widget_ops = array('classname' => 'post_ratings_rank_widget', 'description' => 'Top Posts based on WP-PostRatings data' );
$this->WP_Widget('post_ratings_rank', 'Top Posts based on WP-PostRatings data', $widget_ops);
}

function widget($args, $instance) {
extract($args, EXTR_SKIP);
global $wpdb;

$sql = "SELECT * FROM (
SELECT '1' AS Period, W.id, W.post_title, SUM(R.rating_rating) AS totalScore -- This Week
FROM {$wpdb->posts} W INNER JOIN {$wpdb->prefix}ratings R ON W.id = R.rating_postid
WHERE YEARWEEK(W.post_date) = YEARWEEK(CURDATE())
AND YEARWEEK(FROM_UNIXTIME(R.rating_timestamp)) = YEARWEEK(CURDATE())
AND W.post_type = 'post' AND W.post_status = 'publish'
GROUP BY W.id, W.post_title ORDER BY totalScore DESC LIMIT 10 ) T1
UNION
SELECT * FROM (
SELECT '2' AS Period, W.id, W.post_title, SUM(R.rating_rating) AS totalScore -- Last Week
FROM {$wpdb->posts} W INNER JOIN {$wpdb->prefix}ratings R ON W.id = R.rating_postid
WHERE YEARWEEK(W.post_date) = YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 1 WEEK))
AND YEARWEEK(FROM_UNIXTIME(R.rating_timestamp)) = YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 1 WEEK))
AND W.post_type = 'post' AND W.post_status = 'publish'
GROUP BY W.id, W.post_title ORDER BY totalScore DESC LIMIT 10 ) T1
UNION
SELECT * FROM (
SELECT '3' AS Period, W.id, W.post_title, SUM(R.rating_rating) AS totalScore -- Last Month
FROM {$wpdb->posts} W INNER JOIN {$wpdb->prefix}ratings R ON W.id = R.rating_postid
WHERE (MONTH(W.post_date) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))) AND (YEAR(W.post_date) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)))
AND (MONTH(FROM_UNIXTIME(R.rating_timestamp)) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))) AND (YEAR(FROM_UNIXTIME(R.rating_timestamp)) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)))
AND W.post_type = 'post' AND W.post_status = 'publish'
GROUP BY W.id, W.post_title ORDER BY totalScore DESC LIMIT 10 ) T1
UNION
SELECT * FROM (
SELECT '4' AS Period, W.id, W.post_title, SUM(R.rating_rating) AS totalScore -- Last Year
FROM {$wpdb->posts} W INNER JOIN {$wpdb->prefix}ratings R ON W.id = R.rating_postid
WHERE (YEAR(W.post_date) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)))
AND (YEAR(FROM_UNIXTIME(R.rating_timestamp)) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)))
AND W.post_type = 'post' AND W.post_status = 'publish'
GROUP BY W.id, W.post_title ORDER BY totalScore DESC LIMIT 10 ) T1

ORDER BY Period ASC, totalScore DESC";

$posts = $wpdb->get_results($sql);

echo $before_widget;

echo '<style type="text/css">';
echo ' ul#tabnav { text-align: left; margin: 1em 0 1em 0; font: bold 11px verdana, arial, sans-serif; border-bottom: 1px solid #fff; list-style-type: none; padding: 3px 10px 3px 10px; }';
echo ' ul#tabnav li { display: inline; }';
echo ' body#tab1 li.tab1, body#tab2 li.tab2, body#tab3 li.tab3, body#tab4 li.tab4 { border-bottom: 1px solid #fff; background-color: transparent; }';
echo ' body#tab1 li.tab1 a, body#tab2 li.tab2 a, body#tab3 li.tab3 a, body#tab4 li.tab4 a { background-color: transparent; color: #000; position: relative; top: 1px; padding-top: 4px; }';
echo ' ul#tabnav li a { padding: 3px 4px; border: 1px solid #fff; background-color: #transparent; color: #666; margin-right: 0px; text-decoration: none; border-bottom: none;}';
echo ' ul#tabnav li.tab1 a {color: #fff;}';
echo ' ul#tabnav a:hover { background: #fff; color: #000; }';
echo ' #mostPopular-2, #mostPopular-3{ display: none; }';
echo '</style>';

echo '<script type="text/javascript" language="javascript">';
echo ' function togglePeriod(p_period) { jQuery(".mostPopular").hide(); jQuery("#mostPopular-"+p_period).show(); jQuery("ul#tabnav li a").css("color", "#666"); jQuery("ul#tabnav li.tab"+p_period+" a").css("color", "#fff"); }';
echo '</script>';


echo '<h2>Most Popular Posts</h2>';

echo '<ul id="tabnav"><li class="tab1"><a href="javascript: togglePeriod(1);">This Week</a></li><li class="tab2"><a href="javascript: togglePeriod(2);">Last Week</a></li><li class="tab3"><a href="javascript: togglePeriod(3);">Last Month</a></li><li class="tab4"><a href="javascript: togglePeriod(4);">Last Year</a></li></ul>';


$currentPeriod = 0;

foreach($posts as $post) :
if($currentPeriod <> $post->Period) {
if($currentPeriod <> 0) {
echo '</ol>';
}
$currentPeriod = $post->Period;
echo '<ol style="font-size: 0.8em; color: #fff;" class="mostPopular" id="mostPopular-'. $post->Period .'">';
}

echo '<li style="font-size: 1.2em; "><a href="'. get_permalink($post->id) .'" rel="bookmark" title="Permanent Link to '. $post->post_title .'">'. $post->post_title .'</a></li>';

endforeach;

echo '</ol>';


echo $after_widget;
}

function update($new_instance, $old_instance) {
// save the widget
// TODO
return $instance;
}

function form($instance) {
// widgetform in backend
// TODO

return $instance;
}
}


/**
* Initialize the plugin
*
*/
function post_ratings_rank_init () {
add_action( 'widgets_init', 'post_ratings_rank_widget_init' );
}
add_action( 'plugins_loaded', 'post_ratings_rank_init' );


function post_ratings_rank_widget_init () {
register_widget( 'post_ratings_rank_widget' );
}



?>