WordPress.org

Ready to get started?Download WordPress

Forums

Exports and Reports
Query "Advanced Custom Fields" wp_postmeta with wp_posts AND (1 post)

  1. wakingmedia
    Member
    Posted 2 years ago #

    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/

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic

  • RSS feed for this topic
  • Started 2 years ago by wakingmedia
  • This topic is not a support question
  • WordPress version: 3.3.1