WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] List categories by author ~ WITH COUNTER ~ (3 posts)

  1. 2famous
    Member
    Posted 9 months ago #

    Think this one should have been in the "advanced" forum, but I'm not advanced enough to post there :P

    I need to make a list of categories that a given author has published in. Then I want a little number next to it telling how many entries THAT author has in THAT category. Sounds simple, but it's not :P

    The following code produces a nice list of the categories that a given author has published in. It's half way to my goal. I also want the numbers!

    <?php
        $author = get_query_var('author');
        $categories = $wpdb->get_results("
        	SELECT DISTINCT(terms.term_id) as ID, terms.name, terms.slug, tax.description
        	FROM $wpdb->posts as posts
        	LEFT JOIN $wpdb->term_relationships as relationships ON posts.ID = relationships.object_ID
        	LEFT JOIN $wpdb->term_taxonomy as tax ON relationships.term_taxonomy_id = tax.term_taxonomy_id
        	LEFT JOIN $wpdb->terms as terms ON tax.term_id = terms.term_id
        	WHERE 1=1 AND (
        		posts.post_status = 'publish' AND
        		posts.post_author = '$author' AND
        		tax.taxonomy = 'category' )
        	ORDER BY terms.name ASC
        ");
        ?>
        <ul>
        	<?php foreach($categories as $category) : ?>
        	<li>
        		<a href="<?php echo get_category_link( $category->ID ); ?>" title="<?php echo $category->name ?>">
        			<?php echo $category->name.' '.$category->description; ?>
    
        		</a>
        	</li>
        	<?php endforeach; ?>
        </ul>

    When I try to tweak it to do the job I want, I don't really get a result that I'm satisfied with: The following code, which I thought was going to work, gives me only the name and the content of ONE category! (And tells me how many posts that's inside of that category, but it doesn't really tell me how many posts my author has there... which is a problem)

    <?php
    
        $author = get_query_var('author');
    
    $categories = $wpdb->get_results("
        SELECT DISTINCT(terms.term_id) as ID, terms.name, terms.slug, tax.description, count(posts.id) AS 'count'
        FROM $wpdb->posts as posts
        LEFT JOIN $wpdb->term_relationships as relationships ON posts.ID = relationships.object_ID
        LEFT JOIN $wpdb->term_taxonomy as tax ON relationships.term_taxonomy_id = tax.term_taxonomy_id
        LEFT JOIN $wpdb->terms as terms ON tax.term_id = terms.term_id
        WHERE posts.post_status = 'publish' AND
            posts.post_author = '$author' AND
            tax.taxonomy = 'category'
        ORDER BY terms.name ASC
    ");
    ?>
    <ul>
        <?php foreach($categories as $category) : ?>
        <li>
            <a href="<?php echo get_category_link( $category->ID ); ?>" title="<?php echo $category->name ?>">
                <?php echo $category->name.'('.$category->count.') '.$category->description; ?>
            </a>
        </li>
        <?php endforeach; ?>
    </ul>

    If there are any brighter brains than mine out there I would have appreciated some of your light on my problem before it drives me crazy and gets me hospitalized :P

  2. 2famous
    Member
    Posted 9 months ago #

    This code counts the posts in the category, and works fine. I want to combine this with the code above, but I don't know how to do it...

    <?php
        $counter = "SELECT COUNT(*)
        FROM $wpdb->posts
        LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
        LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
        WHERE $wpdb->term_taxonomy.term_id = 412
        AND $wpdb->term_taxonomy.taxonomy = 'category'
        AND $wpdb->posts.post_status = 'publish'
        AND post_author = '1'
        ";
    
        $user_count = $wpdb->get_var($counter);
    
        echo $user_count;
    
        ?>
  3. 2famous
    Member
    Posted 9 months ago #

    I figured it out... had to run to separate SELECT functions: one to fetch the list of categories, and then one more functions within that loop to count how many entries there is within the category. I would have preferred to have these two loops as one, but this works out for me.

    <?php
    
    // This will get us a list of the categories that our Author has published in
    $author = get_query_var('author');
    $categories = $wpdb->get_results("
    
    SELECT DISTINCT(terms.term_id) as ID, terms.name, terms.slug, tax.description
    FROM $wpdb->posts as posts
    LEFT JOIN $wpdb->term_relationships as relationships ON posts.ID = relationships.object_ID
    LEFT JOIN $wpdb->term_taxonomy as tax ON relationships.term_taxonomy_id = tax.term_taxonomy_id
    LEFT JOIN $wpdb->terms as terms ON tax.term_id = terms.term_id
    WHERE posts.post_status = 'publish' AND
        posts.post_author = '$author' AND
        tax.taxonomy = 'category'
    ORDER BY terms.name ASC
    ");
    
    // This loop picks up categories
    foreach($categories as $category) : 
    
    $catid = $category->ID;
    
    // Now, inside the loop, we need to count how many posts that the Author has published.
    $counter = "SELECT COUNT(*)
    FROM $wpdb->posts
    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    WHERE $wpdb->term_taxonomy.term_id = $catid
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->posts.post_status = 'publish'
    AND post_author = '$author'
    ";
    
    $user_count = $wpdb->get_var($counter);
    
    echo '<div class="archive_author">' . $category->name . '<br/><span class="subcounter">' . $user_count . ' posts</span></div>';
    
    endforeach; 
    
    ?>

    Thanks

Reply

You must log in to post.

About this Topic