How to Write a SQL Query to Display list of most used tags in the last 30 days in WordPress website

0 Views

I am looking for a way to display the most used tags over the last 30 days on my Baseball blog, built on WordPress. 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:

Read More
<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

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 2

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

UPDATE 3

I have recently created a plugin to display the most popular recently used tags – https://wordpress.org/plugins/recent-popular-tags/

Post Views: 0

Related posts

Leave a Reply

2 comments

  1. The PHP variables you are inserting in your SQL string are the PHP objects that can be used to access WordPress tables from within PHP; whereas you are after the names of the tables and columns for accessing the data from within SQL.

    You want instead:

    "SELECT DISTINCT term_taxonomy_id FROM wp_term_relationships
  INNER JOIN wp_posts ON wp_posts.ID = wp_term_relationships.object_id
  WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= wp_posts.post_date"

    As an aside: should you ever need to insert the value of a PHP variable into a SQL statement, be very careful to escape it first in order to prevent any malicious code from being injected.

  2. 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>

    UPDATE

    I have recently created a plugin to display the most popular recently used tags – https://wordpress.org/plugins/recent-popular-tags/