Export WordPress Posts and Meta Information in CSV format using SQL Query

A friend managed to help me with my solution in the end. This was his final SQL query to export all posts with meta information to a CSV file.

SELECT DISTINCTpost_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 meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Asking Price (ZAR)' AND wp_postmeta.post_id = wp_posts.ID) as "Asking Price (ZAR)",(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Author' AND wp_postmeta.post_id = wp_posts.ID) as Author,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Binding' AND wp_postmeta.post_id = wp_posts.ID) as Binding,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Book Condition' AND wp_postmeta.post_id = wp_posts.ID) as "Book Condition" ,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Book Number' AND wp_postmeta.post_id = wp_posts.ID) as "Book Number" ,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Book Type' AND wp_postmeta.post_id = wp_posts.ID) as "Book Type",(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Edition' AND wp_postmeta.post_id = wp_posts.ID) as  Edition,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Illustrator' AND wp_postmeta.post_id = wp_posts.ID) as Illustrator ,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Inscription' AND wp_postmeta.post_id = wp_posts.ID) as Inscription ,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'ISBN' AND wp_postmeta.post_id = wp_posts.ID) as  ISBN,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Jacket Condition' AND wp_postmeta.post_id = wp_posts.ID) as "Jacket Condition",(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Location' AND wp_postmeta.post_id = wp_posts.ID) as  Location,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Published Place' AND wp_postmeta.post_id = wp_posts.ID) as "Published Place",(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Published Year' AND wp_postmeta.post_id = wp_posts.ID) as "Published Year" ,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Publisher' AND wp_postmeta.post_id = wp_posts.ID) as Publisher ,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Size' AND wp_postmeta.post_id = wp_posts.ID) as SizeFROM wp_postsWHERE post_type = 'post' ORDER BYpost_title, post_content

Thanks everyone for your input!

Try the many CSV export plugins. I've used this one to great effect.

I think that everything you need is in the *_posts and *_postmeta tables.

There are two ways you can do it. First, just join the *_postmeta table on the *_post table.

"SELECT * FROM {$wpdb->posts} LEFT JOIN {$wpdb->postmeta} ON {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id"

You will want to enumerate your fields instead of using the asterix, or course. You will end up with multiple rows for each post that you will have to loop through and organize.

Second, write subqueries.

"SELECT *,(SELECT meta_value FROM {$wpdb->postmeta} WHERE {$wpdb->postmeta}.meta_key = 'Publisher' AND {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID) as Publisher,... FROM {$wpdb->posts}"

Subqueries should perform just fine in this context. Again, enumerate your fields.