This question at the moment stands as more advice than code, depending where I can take this. Price may up.
I have 'stupidly' designed and proposed mobile web-app with a Most Loved/Liked post rating feature.
Now this feature, basically has a 'Love this post' counter on each post, so if you like it, or love it, you click the 'Love/Like' button, and there will be a counter which tells you how many people have liked the post...
Quite basic that bit is, when you use a plugin like [[LINK href="http://wordpress.org/extend/plugins/wp-postratings/"]]http://wordpress.org/extend/plugins/wp-postratings/[[/LINK]]
But I then want in the side bar, a button which brings up a 'Most Loved' archive, listing all the posts in order of how many loves/likes they have had.
Is this possible and can some one please advise on how I should do this. Links to tutorials or personal project experience of the same idea, etc, etc..
John Cotton answers:
Very easy with wp-postratings.
That plugin creates a table to store all its data in, including a post id and timestamp against each row.
So wpdb queries for most popular ever, last week etc all very easy.
I've also used GD Press Tools as a view logger and done queries on that.
Most Read Stories on [[LINK href="http://www.golfbusinessnews.com"]]GolfBusinessNews.com[[/LINK]] does just that.
Francisco Javier Carazo Gil answers:
The table that creates the plugin is the next one:
CREATE TABLE $wpdb->ratings (".
"rating_id INT(11) NOT NULL auto_increment,".
"rating_postid INT(11) NOT NULL ,".
"rating_posttitle TEXT NOT NULL,".
"rating_rating INT(2) NOT NULL ,".
"rating_timestamp VARCHAR(15) NOT NULL ,".
"rating_ip VARCHAR(40) NOT NULL ,".
"rating_host VARCHAR(200) NOT NULL,".
"rating_username VARCHAR(50) NOT NULL,".
"rating_userid int(10) NOT NULL default '0',".
"PRIMARY KEY (rating_id)) $charset_collate;
You can create a widget which contains the most important stories. For example, with WPDB as John Cotton said you can do the next one:
$myrows = $wpdb->get_results( "SELECT rating_postid, rating_posttitle FROM $wpdb->ratings ORDER BY rating_rating LIMIT 10);
If you want to get the results into a widget, do the following in your theme:
echo $before_title;?>Your widget ttile<?php echo $after_title;
// fill results with $myrows
Gabriel Reguly answers:
If you can afford a prize like $100, I can code a good solution to you and we can try to merge the code with the plugin so others can befit from this.
How does it sounds?
Marcus Graichen answers:
On this site: [[LINK href="http://www.commonwealthwriters.org/"]]www.commonwealthwriters.org[[/LINK]] I used the 'I like' plugin ( [[LINK href="http://www.my-tapestry.com/i-like-this/ "]]http://www.my-tapestry.com/i-like-this/ [[/LINK]]) which only allows you to 'like' posts, ..must like the facebook system.
I have to agree with above that WP-Postratings is much more powerful and flexible, ..BUT this works simpler, and is easier to graphically customise.
Then the query to get your most popular posts using the plugins integration into your postmeta table in your db would be:
$request = "SELECT ID, post_author, post_title, meta_value, post_excerpt FROM $wpdb->posts, $wpdb->postmeta";
$request .= " WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id";
$request .= " AND post_status='publish' AND post_type='post' AND meta_key='_liked'";
$request .= " ORDER BY $wpdb->postmeta.meta_value+0 DESC LIMIT 10 ";
$posts = $wpdb->get_results($request);
foreach ($posts as $post) :
$post_title = stripslashes($post->post_title);
$permalink = get_permalink($post->ID);
$excerpt = $post->post_excerpt;
$curauth = get_userdata($post->post_author);
//just putting the returned fields into usable variables
You can see I have only queried the fields I need, in this case ID, author, title and excerpt.
Let me know if you choose to use this and need help setting it up.