SQL CSV import/export post data across tables

Question

I need to:

Join wordpress data from multiple tables so all data pertaining to each post resides on a single row for export to CSV.

and

Split data back across the tables during import of the CSV.

I believe this can by done via 2 mySQL queries.

My intention is to export data, manipulate cell values and add new rows for the purpose of bulk editing and creation of new wp posts.

So far I have understood that I can be quite explicit in collating data for export like this:

SELECT DISTINCT
  ID, post_author, post_content, post_title
  ,(SELECT meta_value 
    FROM wp_postmeta
    WHERE wp_postmeta.meta_key = 'wpcf-album-title'
      AND wp_postmeta.post_id = wp_posts.ID
   ) as "wpcf-album-title"
,(SELECT id FROM wp_geo_mashup_locations, wp_geo_mashup_location_relationships WHERE wp_geo_mashup_locations.id = wp_geo_mashup_location_relationships.location_id AND wp_geo_mashup_location_relationships.object_id = wp_posts.ID) as "id"
FROM wp_posts
WHERE post_type = 'post' 
ORDER BY
 ID, post_author, post_content, post_title
INTO OUTFILE '/tmp/test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '~'
LINES TERMINATED BY '\n'

The above gives me a selection and export to CSV file.

How do I go back the other way, splitting the data back across the tables?

AjV Jsy · Answer 1 · 2013-03-18T00:42:56.960

1

It looks like wp_geo_mashup_location_relationships.location_id is a valid field in a valid table (finally found it here http://psc.apl.washington.edu/wordpress/wp-content/uploads/_people/PSCAdmin-migrate-5cb4d.sql ), so...

You need to let SQL know about the wp_geo_mashup_location_relationships table by including it in the FROM clause, otherwise you'll get that error.

,(SELECT lat 
  FROM wp_geo_mashup_locations, wp_geo_mashup_location_relationships
  WHERE wp_geo_mashup_locations.id = wp_geo_mashup_location_relationships.location_id
    AND wp_geo_mashup_location_relationships.object_id = wp_posts.ID
 ) as "lat"

edited Mar 18 '13 at 00:42

answered Mar 18 '13 at 00:35

AjV Jsy

5,799
4
34
30

Thank you, I didn't realise I needed to put the additional reference in the FROM. In general would you say this is a suitable method for collating data for a post on a single row to then edit in a CSV? And do you have an example of going back the other way for import ? – timoto Mar 18 '13 at 12:48

SQL CSV import/export post data across tables

1 Answers1