145 posts
  • Austria
  • Bought between 1 and 9 items
  • Exclusive Author
  • Has been a member for 1-2 years
LovelessDesign says

Hey,

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");
3418 posts
  • Elite Author
  • Sold between 250 000 and 1 000 000 dollars
  • Community Moderator
  • Bought between 100 and 499 items
  • Referred more than 2000 users
  • Has been a member for 4-5 years
  • Repeatedly Helped protect Envato Marketplaces against copyright violations
+4 more
sevenspark Volunteer moderator says

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.

1148 posts
  • Referred between 1 and 9 users
  • Sold between 1 000 and 5 000 dollars
  • Has been a member for 4-5 years
  • Bought between 10 and 49 items
  • Exclusive Author
  • Envato Studio (Microlancer) Beta Tester
  • United States
fillerspace says

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
145 posts
  • Austria
  • Bought between 1 and 9 items
  • Exclusive Author
  • Has been a member for 1-2 years
LovelessDesign says

@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.

3418 posts
  • Elite Author
  • Sold between 250 000 and 1 000 000 dollars
  • Community Moderator
  • Bought between 100 and 499 items
  • Referred more than 2000 users
  • Has been a member for 4-5 years
  • Repeatedly Helped protect Envato Marketplaces against copyright violations
+4 more
sevenspark Volunteer moderator says

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.

145 posts
  • Austria
  • Bought between 1 and 9 items
  • Exclusive Author
  • Has been a member for 1-2 years
LovelessDesign says

Thanks a lot sevenspark. You have perfectly identified my problem again and gave me the perfect solution. Great. :)

3418 posts
  • Elite Author
  • Sold between 250 000 and 1 000 000 dollars
  • Community Moderator
  • Bought between 100 and 499 items
  • Referred more than 2000 users
  • Has been a member for 4-5 years
  • Repeatedly Helped protect Envato Marketplaces against copyright violations
+4 more
sevenspark Volunteer moderator says

You’re welcome, glad it helped! :)

by
by
by
by
by
by