6

I am looking for a way to display the most used tags over the last 30 days. I am no coder, but I have come up with this mashup to display a list of the most used 28 tags (preference to fit my theme). I cannot, for the life of me, figure out how to limit the tags to the most used in the last 30 days.

Here is what I have:

<ul id="footer-tags">
    <?php
        $tags = get_tags( array('orderby' => 'count', 'order' => 'DESC', 'number'=>28) );
            foreach ( (array) $tags as $tag ) {
            echo '<li><a href="' . get_tag_link ($tag->term_id) . '" rel="tag">' . $tag->name . '</a></li>';
            }
    ?>
</ul>

Update

This is the full code I am using right now, following One Trick Pony's code below.

<ul id="footer-tags">
<?php
global $wpdb;
$term_ids = $wpdb->get_col("
   SELECT DISTINCT term_taxonomy_id FROM $wpdb->term_relationships
      INNER JOIN $wpdb->posts ON $wpdb->posts.ID = object_id
      WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= $wpdb->posts.post_date");

if(count($term_ids) > 0){

  $tags = get_tags(array(
    'orderby' => 'count',
    'order'   => 'DESC',
    'number'  => 28,
    'include' => $term_ids,
  ));
foreach ( (array) $tags as $tag ) {
echo '<li><a href="' . get_tag_link ($tag->term_id) . '" rel="tag">' . $tag->name . '</a></li>';
}
}
?>
</ul>

The website is slightly less than ~4 weeks old, so to test, I changed INTERVAL 30 DAY to INTERVAL 3 DAY and the tags being returned seem random and some haven't been used in 2+ weeks and have only been used a single time. As well, only 8 tags are being displayed, when more have been used.

To check that the correct number of days have been queried, I did the following:

Completely deleted all items in the trash for posts and pages, I don't have any custom post types.

Did the same with drafts.

Ran a query in phpmyadmin to delete all post revisions - DELETE FROM wp_posts WHERE post_type = "revision";

Ran a query in phpmyadmin to check if the results are the posts from the last 3 days - SELECT * from wp_posts WHERE DATE_SUB(CURDATE(), INTERVAL 3 DAY) <= post_date

The results from the phpmyadmin query were, in fact, the posts from the last 3 days, but the front-end display did not change.

Update 2

Here are some screen shots. Maybe the screenshots can help find where my code is wrong.

Blog Post with Category and Tags

Image of blog post

wp_posts table with the post ID of above post

wp_posts

wp_terms table with the term_id of the tags used

wp_terms

wp_term_taxonomy with the tags' term_id as term_taxonomy_id

wp_term_taxonomy

wp_term_relationships with term_taxonomy_id assigned to post as object_id

wp_term_relationships

Update 3

I think I figured out the problem, but do not know how to fix it.

The SQL query gets the term_taxonomy_id, not the actual tag ID and get_tag_link uses term_id

2
  • Updated with new full code and procedures followed and tasks completed to check for errors. Apr 18, 2012 at 8:54
  • Does the code above need the WordPress tables prefix or does wpdb already take that into consideration? When I add the prefix to the tables in the code. Nothing at all is returned. Apr 23, 2012 at 15:39

5 Answers 5

4

The problem was that the SQL query code was getting the term_taxonomy_id, not the actual tag ID.

I added an additional INNER JOIN using the term_taxonomy table to get the term_id. This seems to work, but if a mod can improve this, please do!

<ul id="footer-tags">
<?php $wpdb->show_errors(); ?> 
<?php
global $wpdb;
$term_ids = $wpdb->get_col("
    SELECT term_id FROM $wpdb->term_taxonomy
    INNER JOIN $wpdb->term_relationships ON $wpdb->term_taxonomy.term_taxonomy_id=$wpdb->term_relationships.term_taxonomy_id
    INNER JOIN $wpdb->posts ON $wpdb->posts.ID = $wpdb->term_relationships.object_id
    WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= $wpdb->posts.post_date");

if(count($term_ids) > 0){

  $tags = get_tags(array(
    'orderby' => 'count',
    'order'   => 'DESC',
    'number'  => 28,
    'include' => $term_ids,
  ));
foreach ( (array) $tags as $tag ) {
echo '<li><a href="' . get_tag_link ($tag->term_id) . '" rel="tag">' . $tag->name . '</a></li>';
}
}
?>
</ul>
1
  • Not working for me on WP 4.8.2
    – Fred K
    Oct 2, 2017 at 18:59
1

This query should help you get you the term IDs used by posts from the last 30 days:

SELECT DISTINCT term_taxonomy_id FROM $wpdb->term_relationships    
  INNER JOIN $wpdb->posts ON $wpdb->posts.ID = object_id    
  WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= $wpdb->posts.post_date

Then you pass these IDs to the "include" argument of get_tags()


Full code:

global $wpdb;
$term_ids = $wpdb->get_col("
   SELECT DISTINCT term_taxonomy_id FROM $wpdb->term_relationships
      INNER JOIN $wpdb->posts ON $wpdb->posts.ID = object_id
      WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= $wpdb->posts.post_date");

if($term_ids > 0){

  $tags = get_tags(array(
    'orderby' => 'count',
    'order'   => 'DESC',
    'number'  => 28,
    'include' => $term_ids,
  ));

  // your foreach loop here...

}else{
  print 'no posts were tagged in the last 30 days';

}

There are probably more efficient ways to do this though.

9
  • Thanks for the code. I changed INTERVAL 30 DAY to INTERVAL 3 DAY to test it out. I left the number of tags at 28. 11 tags were pulled. Today is April 11 - The tags pulled by the function were last used on - 4/9, 4/11, 4/10, 4/2, 4/2, 3/31, 4/2, 4/3, 4/3, 4/6 Apr 11, 2012 at 17:16
  • Did a quick test and seems to work on my setup. Maybe you have newer posts in the trash that are using some of those tags? The query above doesn't differentiate between post status or post type. Apr 11, 2012 at 21:10
  • I have emptied the trash. I have no custom post types. I have three drafts, their last update dates are: 4/7, 4/5, 4/2. Now 9 tags are being displayed. Here is the complete code I am using (minus CSS, of course) Apr 12, 2012 at 5:02
  • <ul id="footer-tags"> <?php global $wpdb; $term_ids = $wpdb->get_col(" SELECT DISTINCT term_taxonomy_id FROM $wpdb->term_relationships INNER JOIN $wpdb->posts ON $wpdb->posts.ID = object_id WHERE DATE_SUB(CURDATE(), INTERVAL 3 DAY) <= $wpdb->posts.post_date"); if($term_ids > 0){ $tags = get_tags(array( 'orderby' => 'count', 'order' => 'DESC', 'number' => 28, 'include' => $term_ids, )); foreach ( (array) $tags as $tag ) { echo '<li><a href="' . get_tag_link ($tag->term_id) . '" rel="tag">' . $tag->name . '</a></li>'; } } ?> </ul> Apr 12, 2012 at 5:02
  • Sorry, that feels like I just broke a rule... Sorry, I am new here. Let me know the correct method, if I did. Apr 12, 2012 at 5:04
0

You can limit tags using "number" parameter...

 $tags = get_tags( array('orderby' => 'count', 'order' => 'DESC','number'=>28) );

For reference to get_tags function read this http://codex.wordpress.org/Function_Reference/get_tags

2
  • 1
    This limits the number of tags, not the number of days to determine popularity. Apr 11, 2012 at 7:35
  • Stephen is correct, although it is a cleaner way of limiting the number of tags. I edited the code. Still looking for a solution to limit the most popular in the last 30 days. Apr 11, 2012 at 13:08
0

I think the get_tags->count does not really count the tags in a range. I have implemented this solution, please let me know if this works for you:

global $wpdb;
$term_ids = $wpdb->get_col("
    SELECT term_id , count(*) cont FROM $wpdb->term_taxonomy
    INNER JOIN $wpdb->term_relationships ON $wpdb->term_taxonomy.term_taxonomy_id=$wpdb->term_relationships.term_taxonomy_id
    INNER JOIN $wpdb->posts ON $wpdb->posts.ID = $wpdb->term_relationships.object_id
    WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= $wpdb->posts.post_date AND  $wpdb->term_taxonomy.taxonomy='post_tag'
    GROUP BY term_id
    ORDER BY cont DESC
    LIMIT 5
");    
if ( count( $term_ids ) > 0 ) {
    $tags = get_tags( array(
        'orderby' => 'count',
        'order'   => 'DESC',
        'number'  => 5,
        'include' => $term_ids,
    ) );
    foreach ( (array) $tags as $tag ) {
        echo '<li><a href="' . get_tag_link ($tag->term_id) . '" rel="tag">' . 
            $tag->name . '</a></li>';
    }
}
1
  • Why you are using get_tags now when you already have 5 tags order by count ?
    – Sumit
    Mar 19, 2016 at 6:22
0

It worked, But I think performance isn't good.

global $wpdb;

$limit = 10;

$sql      = "SELECT ID FROM $wpdb->posts WHERE $wpdb->posts.post_type = 'digi_posts' AND $wpdb->posts.post_status = 'publish' AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= $wpdb->posts.post_date";
            $sql      = "SELECT term_taxonomy_id FROM $wpdb->term_relationships WHERE object_id in ($sql) GROUP BY term_taxonomy_id ORDER BY COUNT(term_taxonomy_id) DESC";
            $sql      = "SELECT term_id FROM $wpdb->term_taxonomy WHERE taxonomy = 'post_tag' AND term_taxonomy_id in ($sql) LIMIT {$limit}";
            $term_ids = $wpdb->get_col( $sql );

if ( count( $term_ids ) > 0 ) {
    $tags = get_tags( array(
        'orderby' => 'count',
        'order'   => 'DESC',
        'number'  => $limit,
        'include' => $term_ids,
    ) );
    foreach ( (array) $tags as $tag ) {
        echo '<li><a href="' . get_tag_link ($tag->term_id) . '" rel="tag">' . 
            $tag->name . '</a></li>';
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.