WordPress Extract Posts from MySQL Database

Glenn Blog Leave a Comment

If you want to extract your WordPress Posts from an existing MySQL Database then use this snippet in the SQL Query area for your MySQL Database:-

SELECT DISTINCT
post_title
, post_content
,(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
, post_content

This will filter out the WordPress posts with their respective Categories and you can then export them in the format that you require (i.e. .sql, .csv, .doc etc)

NB If this isn’t working for you then remember that this is using the default WordPress database prefix of, ‘wp_’. Of course for security you shouldn’t be using the default WordPress database prefix, so do a search and replace on ‘wp’ and enter in your own database prefix.

Credit

About the Author
WordPress Extract Posts from MySQL Database 0da77002febbfe28d15144a57bbcc2c9 s 180 d mm r g

Glenn

A highly experienced Web Designer / Web Developer (Webflow, WordPress, Weebly & Custom), Front-end / Back-end Developer & New Media Specialist, with extensive knowledge of a wide spectrum of technologies in the Development and Creative Industries, built up over a number of years.