How to get category post in WordPress

This article’s writing is specified on how to get the category post available in WordPress-based website. If there is a requirement or an urgent matter regarding on retrieving all those article which has been posted specifically their categories and store it in a file for an example a CSV file, it can be done easily.

So, in the context of this article, it will be done by executing specific query to the database which is used by the WordPress’ based website. In this article, the method used is by executing the query on MySQL Command Console. It can also be done using another method such as accessing the database by using some kind of MySQL Editor application or a web-based tool such as PHPMyadmin. The most important things are the query for extracting categories of the posts can be executed properly.

Below are the steps taken until it is done :

1. Check whether the database can be accessed. It can be done by checking whether the MySQL service is active or not. Check the reference available in the article titled ‘Check MySQL Service Status’ in this link.

2. Access MySQL Database Console if the service is active. It can be learned from the article titled ‘MySQL Database Access from Command Line in Linux’ which can be visited in this link.

3. After successfully accessing MySQL Database Console, execute the following query :

SELECT DISTINCT
post_title
,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Asking Price (US\$)' AND wp_postmeta.post_id = wp_posts.ID) AS "Asking Price (US\$)"
,(SELECT group_concat(wp_terms.name separator ',') 
    FROM wp_terms
    INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id
    INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE taxonomy= 'category' and wp_posts.ID = wpr.object_id
) AS "Categories"
,(SELECT group_concat(wp_terms.name separator ', ') 
    FROM wp_terms
    INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id
    INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE taxonomy= 'post_tag' and wp_posts.ID = wpr.object_id
) AS "Tags"
FROM wp_posts
WHERE post_type = 'post' 
ORDER BY
post_title
 INTO OUTFILE '/tmp/test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

I actually found the query above in the stackoverflow’s website specifically in this link. I modify the query based on my need just to select the post_title. And in the end of the query it is followed with an additional query to store the query result into a csv file. Pay attention to the above query and make neccessary modification if it is needed especially to the name of the table since the prefix of the table name can be different from one WordPress web-based application with another. The above example has the default prefix name which is ‘wp_’.

Another thing which is an important thing to be looked at is the CSV file name and the location where the CSV file is stored. In the above context, it is stored in ‘/tmp/test.csv’. It can be changed depends on the environment of the server where the query is being executed.

I haven’t been able to split the above query and describe the usage for each small part of the query but it works for me.