Ask your WordPress questions! Pay money and get answers fast! Comodo Trusted Site Seal
Official PayPal Seal

Please, help me to sort a SQL results WordPress

  • SOLVED

Please, help me to sort an users by quantity of written posts by theirs.


S T E P 1

Get users IDs:
$users = $wpdb->get_results( "SELECT id FROM $wpdb->users;" );


S T E P 2

Get & Calculate a quantity of posts written by an each one user.
<em>post_type is <strong>post</strong></em>


S T E P 3

Display results in DESC order:
User ID - 10 posts
User ID - 6 posts
User ID - 2 posts
...


If you know a better way, I'll be really appreciated.

Answers (4)

2012-05-30

Ludovic Bonnet answers:

Hi, the good sql query is :

$res = $wpdb->get_results( "SELECT count(ID) AS nb, post_author FROM $wpdb->posts WHERE post_type = 'post' AND post_status = 'publish' group by post_author ORDER BY nb DESC" );


Igor comments:

Hi Ludovic,

That is what I need!
So elegant solution. Thank you so much!

2012-05-30

Arnav Joy answers:

/*
Use this to output list for S T E P 3

Note : I am using Ludovic Bonnet's function here

@Ludovic Bonnet's I think you will not mind it.

*/

<?php

global $wpdb;

$res = $wpdb->get_results( "SELECT count(ID) AS nb, post_author FROM $wpdb->posts WHERE post_type = 'post' AND post_status = 'publish' group by post_author ORDER BY nb DESC" );
if(!empty($res)){
foreach ($res as $ud){
echo 'USER '.$ud->post_author.' -- '.$ud->nb.' posts'.'<br />';
}
}
?>


Igor comments:

Hi Arnav,

Thank you for your help in last mile :)
I'm really appreciate that.

2012-05-30

Dylan Kuhn answers:

You can get close with

<?php wp_list_authors( array( 'orderby' => 'post_count', 'order' => 'DESC', 'optioncount' => true ) ); ?>

To get exactly the output you want, you'd probably copy some of the code from that core function.

2012-05-30

Nilesh shiragave answers:

Check this

http://codex.wordpress.org/Function_Reference/wp_list_authors#Authors_Full_Names_and_Number_of_Posts