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

Question

Apologies, this is a duplicate of entry of this post but I found I'm not getting the right answer there and not sure how to ask to move it over to SO?

I need to export all WordPress Posts and related Meta information using an SQL query in CSV format. I've managed to export just the Posts but realised that the Meta information is in another table.

Could someone please tell me how I would go about doing this please?

Thanks!

EDIT: I have already tried a number of plugins, none of which have worked for this specific scenario. I really do need to work on the query itself. Thanks

EDIT: The final outcome of the CSV file should look something like this. On one row, these are the columns that I will end up with. (I will delete the additional columns in CSV, I'm just giving a shortened version here)

post_content | post_title | **meta_key** | *meta_value* | **meta_key** | *meta_value* | **meta_key** | *meta_value* | **meta_key** | *meta_value* | **meta_key** | *meta_value*

So one post per row with the meta information in the same row.

EDIT: With the S-ha-dum, I've managed to get some way, but I have about 12 meta_value > meta_key values to pull out of the DB so I need help to write the rest of the query please

SELECT *,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Publisher' AND wp_postmeta.post_id = wp_posts.ID) as Publisher FROM wp_posts

Hi! Did you find a solution for this? Fronting the same problem — 4ndro1d, Feb 17 '14 at 16:04

score 5 · Accepted Answer · answered Oct 14 '12 at 17:00

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 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 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 Size
FROM wp_posts
WHERE post_type = 'post' 
ORDER BY
post_title
, post_content

Thanks everyone for your input!

this did not work for me, I always got "Error in query (1242): Subquery returns more than 1 row", but this answer did it for me: http://stackoverflow.com/a/24517188/214500 — mtness, May 05 '17 at 08:39

score 0 · Answer 2 · answered Oct 12 '12 at 09:22

0

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

answered Oct 12 '12 at 09:22

SMacFadyen

3,145
2
25
37

Thanks SMacFadyen. But none of the CSV exporters I have tried does what I need it to do. In this case, export the posts AND meta in one CSV file as described above. – SixfootJames Oct 12 '12 at 10:34
Also found that exporter to be extremely lacking. – Foxinni Feb 26 '13 at 07:37

score 0 · Answer 3 · answered Oct 12 '12 at 14:40

0

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.

answered Oct 12 '12 at 14:40

s_ha_dum

2,840
2
18
23

Thanks s_ha_dum! I appreciate coming back to me. SQL is not my strong point at all. Could you explain what "enumerate your fields" means please? – SixfootJames Oct 12 '12 at 14:47
Write the out-- 'post_content','post_title',... You don't need the whole table so there is no need to pull the whole table. – s_ha_dum Oct 12 '12 at 14:49
S-ha-dum, if I run your first option, I get an SQL Syntax error. I am pasting this in phpMyAdmin's SQL Run SQL Box. ("SELECT * FROM {$wpdb->posts} LEFT JOIN {$wpdb->postmeta} ON {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id") - no brackets – SixfootJames Oct 12 '12 at 14:56
I am assuming I need to replace what is in the {} braces? – SixfootJames Oct 12 '12 at 14:57
I think I need to write it out like this...? SELECT * FROM `wp_posts` LEFT JOIN `wp_postmeta` ... just not sure how to write out the rest. – SixfootJames Oct 12 '12 at 15:01
Those brackets will only work in PHP, just like the `$wpdb->` only works in PHP with the [WordPress database object](http://codex.wordpress.org/Class_Reference/wpdb#Tables) loaded. If you want to run this as pure SQL you will have to replace those parts with the tablenames. – s_ha_dum Oct 12 '12 at 15:10
Thanks S-ha-dum. I am going through some SQL lessons over at W3CSchools as I try and figure this out. This is what I have now...... SELECT * FROM wp_posts LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id – SixfootJames Oct 12 '12 at 15:13
That is pulling out data without any syntax errors. Just need to check if that now is the right data and then figure out how to export this as a CSV file using a ~ as the delimiter....don't ask, commas have been giving me a migraine because so many of the entries have commas in the posts. – SixfootJames Oct 12 '12 at 15:15
Ok, I have extracted data, but it's still not in the right format I need it in i.e. post_content | post_title | meta_key | meta_value | meta_key | meta_value | meta_key | meta_value | meta_key | meta_value | meta_key | meta_value – SixfootJames Oct 12 '12 at 15:24
Right, it looks like your second option is working a little better S-ha-dum. SELECT *,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Publisher' AND wp_postmeta.post_id = wp_posts.ID) as Publisher FROM wp_posts Now all I need is to be able to pull the rest of the Meta information the same way. – SixfootJames Oct 12 '12 at 15:45

score 0 · Answer 4 · answered Feb 26 '13 at 07:56

Custom Fields Inline SQL Exporting Query

I figured out an intense looking query. It's pulls posts with their custom fields in columns, inline. Note that the 'Concat' in the query is to spoof in some text where the related post would be found and more custom fields can be added at your discretion. My SQL app (Sequel Pro) has a CSV exporting function which I use after querying the remote DB. Work's very well.

SELECT *

FROM (

    SELECT 
        `wp_posts`.`ID` , 
        `wp_posts`.`post_title` AS `Title`,
        `wp_posts`.`post_date` AS `Date`,

        MAX( CASE WHEN `wp_postmeta`.`meta_key` = 'useremail'
        THEN `wp_postmeta`.`meta_value`
        END ) AS `Email`,

        MAX( CASE WHEN `wp_postmeta`.`meta_key` = 'usercell'
        THEN `wp_postmeta`.`meta_value`
        END ) AS `Cell Phone`,

        concat('http://yoururl.com/?p=',`wp_posts`.`ID`) as `URL`

    FROM `wp_posts`

    LEFT JOIN `wp_postmeta` ON ( `wp_posts`.`ID` = `wp_postmeta`.`post_id` )

    WHERE `wp_posts`.`post_status` = 'publish'

    AND `wp_posts`.`post_type` = 'customposttype'

    GROUP BY `wp_posts`.`ID`

    ORDER BY `wp_posts`.`post_date` DESC

) AS `t` WHERE 1 =1

RESULT:

Custom Fields Inline Export Result

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

4 Answers4