I haven’t found a solution with google. I have a loop that I want to sort with a sql table. The posts that have the most entries in my own sql-table, should be shown first.
Do you have an idea how to realize that?
$args = array( 'paged' => $paged, 'cat' => $catfilter, ); if ($blogposts->have_posts()) : while ($blogposts->have_posts()) : $blogposts->the_post(); $pageid = get_the_ID(); $team1res = $wpdb->get_var("SELECT COUNT(*) FROM wp_votelock WHERE page_id=$pageid AND team=1");
I think you’ll want to store your vote count as a meta field for the post (probably update the count meta field when the vote is tallied, or if you expect many votes then set a job to count votes periodically). That way you can sort by the post’s meta count in the query. In your example above it’s too late unless you want to loop twice and reorder your posts.
Assuming you have more page loads than votes, it’s going to be more efficient to run a COUNT on each vote (and store the result) rather than on each page load × number of posts/page.
In general, to sort by count, try something like this:
SELECT COUNT(`column_to_count`) as `cnt` FROM `tablename` GROUP BY `column_to_count` ORDER BY `cnt` DESC
@sevenspark: so I should store the $team1res in a metafield. That was my idea too but how can I store it as a metafield? Doesn’t the user who vote has to be admin? I just know the other way when I type, as an admin, type something in a metafield and get the variable in the php code.
Keep your separate table of individual posts (I assume you have a structure that stores post ID, user identifier, timestamp, or something like that) but each time you add a row to the table update a meta field for that post with the new count.
You’d use the update_post_meta function. The role of the user is not important but you should be using nonces to make sure that the submitted form is valid.
Thanks a lot sevenspark. You have perfectly identified my problem again and gave me the perfect solution. Great.