ansaurus

Question

Wordpress - Total User Count who only have posts

Answer 1

+1 A:

I don't use wordpress, but if the schema at http://codex.wordpress.org/images/8/83/WP_27_dbsERD.png is close to the version that you are using, then you should be able to do something like

SELECT COUNT(*) FROM wp_posts GROUP BY post_author

If you wanted to know which users had what number of posts you could do

SELECT COUNT(*) AS number_of_posts, u.user_login FROM 
wp_user u, wp_post p WHERE u.ID = p.post_author HAVING number_of_posts > 0;

brian_d 2010-05-11 05:20:14

thanks, i got very strange thing, if i run the first query at phpmyadmin, it shows correctly, but when i echo that query at php, it shows wrong, i don't know why, my syntax is like this<?php $author_count=$wpdb->get_var("SELECT COUNT(*) FROM wp_posts GROUP BY post_author"); echo $author_count ?>

knightrider 2010-05-11 05:37:10

I'm not familiar with the wordpress DB api. I would guess that either you need the $wpdb prefix on $wpdb->wp_posts like in your sample code or that the api does not like the GROUP BY clause for some reasonWhat is the strange thing - an error message or incorrect output?

brian_d 2010-05-11 05:46:10

it is incorrect output, and one thing i noticed is, at wp_post, there are other post_type also, which is page, so i changed the query like this SELECT COUNT(*) FROM wp_posts GROUP BY post_author AND post_type='post' , it only output 2 in my db, actually it have around like 30, is my query wrong ?

knightrider 2010-05-11 06:07:29

try <?php $author_count=$wpdb->get_var("SELECT COUNT(DISTINCT post_author) FROM $wpdb->posts"); echo $author_count; ?>

windyjonas 2010-05-11 06:09:43

btw, you should probably add the criteria. "and post_status = 'publish'"

windyjonas 2010-05-11 06:11:34

you can use DISTINCT instead of GROUP BY as windyjonas mentions. also, your syntax above should be `...WHERE post_type='post' GROUP BY post_author...` and not `...GROUP BY post_author AND post_type='post'`

brian_d 2010-05-11 06:29:30

thanks windyjonas, output is correct now. Thanks brian_d, sorry for being a noob.

knightrider 2010-05-11 07:19:42

ansaurus

tags:

views:

answers:

Wordpress - Total User Count who only have posts

related questions