145 posts
  • Has been part of the Envato Community for over 2 years
  • Located in Austria
  • Has collected 1+ items on Envato Market
  • Sells items exclusively on Envato Market
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");
3497 posts
  • Elite Author: Sold more than $75,000 on Envato Market
  • Has sold $750,000+ on Envato Market
  • Located in United States
  • Helps us moderate the forums
+10 more
sevenspark 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
  • Has referred 1+ members
  • Has sold $1,000+ on Envato Market
  • Has been a beta tester for an Envato feature
  • Has collected 10+ items on Envato Market
+3 more
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
  • Has been part of the Envato Community for over 2 years
  • Located in Austria
  • Has collected 1+ items on Envato Market
  • Sells items exclusively on Envato Market
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.

3497 posts
  • Elite Author: Sold more than $75,000 on Envato Market
  • Has sold $750,000+ on Envato Market
  • Located in United States
  • Helps us moderate the forums
+10 more
sevenspark 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
  • Has been part of the Envato Community for over 2 years
  • Located in Austria
  • Has collected 1+ items on Envato Market
  • Sells items exclusively on Envato Market
LovelessDesign says

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

3497 posts
  • Elite Author: Sold more than $75,000 on Envato Market
  • Has sold $750,000+ on Envato Market
  • Located in United States
  • Helps us moderate the forums
+10 more
sevenspark Moderator says

You’re welcome, glad it helped! :)

Helpful Information

  • Please read our community guidelines. Self promotion and discussion of piracy is not allowed.
  • Open a support ticket if you would like specific help with your account, deposits or purchases.
  • Item Support by authors is optional and may vary. Please see the Support tab on each item page.

Most of all, enjoy your time here. Thank you for being a valued Envato community member.

Post Reply

Format your entry with some basic HTML. Read the Full Details, or here is a refresher:

<strong></strong> to make things bold
<em></em> to emphasize
<ul><li> or <ol><li> to make lists
<h3> or <h4> to make headings
<pre></pre> for code blocks
<code></code> for a few words of code
<a></a> for links
<img> to paste in an image (it'll need to be hosted somewhere else though)
<blockquote></blockquote> to quote somebody

:grin: :shocked: :cry: Complete List of Smiley Codes

by
by
by
by
by
by