Get tag IDs for a specific category in WordPress (MySQL), order by popular

If you want to get tags based on category, and order by tag count value, here is the query:

Assuming that the category ID is 123456

SELECT wp_term_taxonomy.term_id as term_id, COUNT(*) AS count FROM wp_term_taxonomy INNER JOIN wp_term_relationships ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_id WHERE wp_term_taxonomy.taxonomy = 'post_tag' AND wp_term_relationships.object_id IN ( SELECT `object_id` FROM `wp_term_relationships` WHERE `term_taxonomy_id` = 123456 ) GROUP BY term_id ORDER BY `wp_term_taxonomy`.`term_id` DESC

Will return (sample):

term_idcount
1235
3213

Notice that the count here is not default WordPress term count, but count of returning rows of same values, for posts under caterogy of 123456.

Warning: This is the raw query phpMyadmin, you need to do all the security prepare stuffs.

There is another version using the built-in WordPress functions:

https://wordpress.stackexchange.com/questions/75017/get-tags-specific-category

How to set/limit number of comments to display per post?

Hook this function to set number of comments to retrieve:

//limit comment
 function limit_comments($query) {
    $query->query_vars['number'] = 5; //set the number of comments
    $query->query_vars['paged'] = 1;
  }

  add_action('pre_get_comments', 'limit_comments');

Where Do I Put This Code?

This code should be placed in the functions.php file of your active theme or a custom functions plugin.

How to completely disable anchor Adsense ads ?

Anchor ads, either sticking on top or bottom, is pretty annoying to me because it sometimes messes up with the navigation bar or other important messages. Further more, It takes pretty much space on mobile devices.

You can completely disable all anchor ads, both top and bottom by

  • Go to Ads > Overview > By site and click on the Edit icon next to your site
  • Disable the Auto ads option in the Site settings to disable Auto ads completely on that site.

How to update post_modified FAST after each comment?

One of my ways of keeping my posts up to date is to update the post_modified value after some of certain actions like commenting, liking…

You shouldn’t use wp_update_post() because it calls wp_insert_post(), which determines that the post exists and sets the post_modified and post_modified variables to the current date.

This process will take way longer than using direct DB query.

function vl_post_modified_update ($post_id) {
global $wpdb;
// Get the current time
$time = current_time('mysql');
$post_modified_gmt = get_gmt_from_date($time);

$post_id = 123; //the post ID;

$res = $wpdb->query("UPDATE $wpdb->posts SET post_modified = '{$time}', post_modified_gmt = '{$post_modified_gmt}'  WHERE ID = {$post_id}" );
return $res;
}

Next step is to hook this function after user comment is submited

add_action('wp_insert_comment','vl_post_modified_update',99,1);

update_term_meta() not serializing array? Fixed

 WordPress functions like:

update_post_meta()
update_term_meta()
update_user_meta()

will serialize data automatically before adding to the MySQL, so the the data will be stored as serialized string:

$data = Array
(
    [123] => 1
    [a] => 1
    [456] => 1
);

update_term_meta ($termid,'_your_key', $data);
a:3:{i:123;i:1;s:1:"a";i:1;i:456;i:1;}

If you are using PODS to create metadata, somehow it causes the function not to be serialized correctly, so you have to use the function

$data = maybe_serialized($data) 

then

update_term_meta ($termid,'_your_key', $data);

If you want to get back the data, you have to unserialized the data again.

$data = get_term_meta ($termid,'_your_key');
$data = maybe_unserialize($data);

How to escape strange characters from text field input?

It’s a must practice to sanitize all data from user input before insert to database.

To escape unwanted characters, use the core WordPress core function sanitize_text_field():

$title = sanitize_text_field( $_POST['title'] );
update_post_meta( $post->ID, 'title', $title );

Behind the scenes, sanitize_text_field() does the following:

  • Checks for invalid UTF-8
  • Converts single less-than characters (<) to entity
  • Strips all tags
  • Removes line breaks, tabs and extra white space
  • Strips octets

(MYSQL) Get tags taxonomy by recent posts in WordPress DB

I’m developing a simple “trending hashtags”, which based on tags were used by recent posts. The result will be:

pidtags
postid2tag1,tag2,tag3
postid1tag1,tag5,tag6
GET TAGS BY RECENT POSTS

Here is the MySQL:

<?php
global $wpdb;
$results = $wpdb->get_results("SELECT `{$wpdb->prefix}term_relationships`.`object_id` AS pid,
GROUP_CONCAT(`{$wpdb->prefix}term_relationships`.`term_taxonomy_id`) AS tags 
FROM `{$wpdb->prefix}term_relationships` 
JOIN {$wpdb->prefix}term_taxonomy 
ON (`{$wpdb->prefix}term_relationships`.`term_taxonomy_id` = `{$wpdb->prefix}term_taxonomy`.`term_id` AND {$wpdb->prefix}term_taxonomy.taxonomy='post_tag') 
WHERE `object_id` IN (SELECT `ID` FROM `{$wpdb->prefix}posts` WHERE `post_type` = 'post' ORDER BY `{$wpdb->prefix}posts`.`post_date` DESC) 
GROUP BY `object_id` 
ORDER BY `{$wpdb->prefix}term_relationships`.`object_id` DESC LIMIT ", OBJECT );

Add Custom Post Type to the main query

If you want to display custom post type posts along with “default” posts in latest posts page, category page, archive page or tag page, you could you this function. Basically it will add custom post types to the main query before looping:

add_action( 'pre_get_posts', 'add_cpt_to_query' );
function add_cpt_to_query( $query ) {
        // Return if in admin area
        if( is_admin() ) {
            return $query;
        }
        // add 'cpt' to main_query  
      if ( is_tag() && $query->is_main_query() || is_archive() && $query->is_main_query() )
        $query->set( 'post_type', array( 'post', 'page', 'custom_post_type' ) );
      return $query;
}

Where Do I Put This Code?

This code should be placed in the functions.php file of your active theme or a custom functions plugin.