Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

share|improve this question
    
Updated with new full code and procedures followed and tasks completed to check for errors. –  Travis Pflanz Apr 18 '12 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. –  Travis Pflanz Apr 23 '12 at 15:39
add comment

3 Answers

up vote 2 down vote accepted

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>
share|improve this answer
add comment

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

share|improve this answer
    
This limits the number of tags, not the number of days to determine popularity. –  Stephen Harris Apr 11 '12 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. –  Travis Pflanz Apr 11 '12 at 13:08
add comment

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.

share|improve this answer
    
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 –  Travis Pflanz Apr 11 '12 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. –  onetrickpony Apr 11 '12 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) –  Travis Pflanz Apr 12 '12 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> –  Travis Pflanz Apr 12 '12 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. –  Travis Pflanz Apr 12 '12 at 5:04
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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