MySQL get list of WordPress tags for a specific post
In this tutorial I will show you how to get a list of tags for a specified post ID from your MySQL (or other) database.
WordPress can be quite complex at times, and personally I found this one quite a challenge, so ended up searching Google to find the answer.
So anyway here is the SQL you need to do the job –
SELECT slug FROM wp_terms
INNER JOIN wp_term_taxonomy
ON wp_term_taxonomy.term_id = wp_terms.term_id
INNER JOIN wp_term_relationships
ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE taxonomy = "post_tag" AND object_id = XX;
Obviously you replace the XX on the last line with the post id you want to get a list of tags for.
Another useful bit of SQL to get post id and post title from your database is this –
select ID,post_title from wp_posts;
From this you can work out which post you need the ID for and paste it into the code above to get the tags.
My personal use for this was some integration with one of my previous posts ‘How to post to Twitter using bash script‘. It occurred to me that the tags from a post are probably suitable to use as hashtags in a tweet to Twitter.
One possible issue you could run into if you have many tags on your post is the twitter limit on tweet size. One method to remove that in your bash script is to count characters in your string before posting it using something like ‘
wc -c‘. Another less accurate method could be when you are parsing the output list of tags from the SQL to limit it to a specific number of terms.
Hope that helps you in your quest to post tweets with related hashtags based on your wordpress database. You can check out more of my bash script tips and code snippets here –
Full credit for the SQL should go to this website which told me the answer – http://gabrielharper.com/blog/2012/07/sql-to-get-post-tags-in-wordpress/