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)
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!
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.
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.
Nilesh shiragave answers:
Check this
http://codex.wordpress.org/Function_Reference/wp_list_authors#Authors_Full_Names_and_Number_of_Posts