Support » Plugin: Exports and Reports » [Plugin: Exports and Reports] Query "Advanced Custom Fields" wp_postmeta with wp_posts AND

  • Thank you for this amazing plugin! It has solved a huge problem for me!
    I am using “Advanced Custom Fields” ( http://wordpress.org/extend/plugins/advanced-custom-fields/ ) which are stored in the wp_postmeta table. Using this plugin, I can export them to CSV with the post title and other info, make updates, then re-import them with “CSV Importer” – http://wordpress.org/extend/plugins/csv-importer/ (in order to re-import, you have to delete all the posts you are importing, there’s no “update” feature) – this saves me hours of opening each post to update it.
    I based my solution on this forum post: http://wordpress.org/support/topic/plugin-exports-and-reports-help-joining-queries
    Here is the code that is working for me (I have 24 terms, but I cut the list off to 4 just to show the example):

    SELECT
    p.ID,p.post_title,p.post_content,p.post_name,p.post_status,
    m1.meta_value AS My_First_Advanced_Custom_Field,
    m2.meta_value AS My_Second_Advanced_Custom_Field,
    m3.meta_value AS My_Third_Advanced_Custom_Field,
    m4.meta_value AS My_Fourth_Advanced_Custom_Field —->Important, no comma here!
    FROM wp_posts AS p
    LEFT JOIN wp_postmeta AS m1 ON m1.post_id = p.ID AND m1.meta_key = ‘My_First_Advanced_Custom_Field’
    LEFT JOIN wp_postmeta AS m2 ON m2.post_id = p.ID AND m2.meta_key = ‘My_Second_Advanced_Custom_Field’
    LEFT JOIN wp_postmeta AS m3 ON m3.post_id = p.ID AND m3.meta_key = ‘My_Third_Advanced_Custom_Field’
    LEFT JOIN wp_postmeta AS m4 ON m4.post_id = p.ID AND m4.meta_key = ‘My_Fourth_Advanced_Custom_Field’
    WHERE p.post_type = ‘post’

    That works great — and a few notes for anyone trying this:
    -> don’t freak out if you can’t see all the fields in the “View Reports” tab – for some reason it doesn’t show some of my fields, like, “post_content” – but when you export to CSV – it’s there.
    -> Also, it seems to have a problem with dashes. I had one custom field name that broke the report, but when I changed the dash to an underscore, it worked fine.
    -> If you place the csv importer or ACF field_name in the “Label(optional)” spot, it saves you having to update the column headers when you go to import. (For example:
    Field name: post_title and Label: csv_post_title)
    -> I found I needed the post_status column because it will download trashed/draft posts too and you can use it to filter them out.

    My question:
    How can I add the post tags to the mix? I can’t figure out how to link up wp_terms and wp_term_relationships ?
    It’d also be great if I could filter for category, or pull the category field.

    http://wordpress.org/extend/plugins/exports-and-reports/

  • The topic ‘[Plugin: Exports and Reports] Query "Advanced Custom Fields" wp_postmeta with wp_posts AND’ is closed to new replies.