WordPress.org

Ready to get started?Download WordPress

Forums

How can I query on the postmeta table for two keys (2 posts)

  1. MyOnlineTown Houston
    Member
    Posted 1 month ago #

    I need to replicate this info with SQL so I can get this data for reporting. I have not been able to find a way to query to get the data I want because the wp_postmeta table uses the same fields to store multiple pieces of data

    Compare with multiple custom field values (text based values)

    http://www.advancedcustomfields.com/resources/how-to/how-to-query-posts-filtered-by-custom-field-values/

    I use ACF to add extra info into posts. How do I create a SQL query something like this?

    Select ID, title, venue, start_date

    ID and Title are from the wp_posts table.

    wp_postmeta table
    meta_key = start_date
    meta_value = 20130610

    meta_key = venue
    meta_value = House of blues

    How can I query this data so I can report and get metrics by venue?

    I also need show all posts where venue = house of blues or all posts where venue = house of blues from 20130601 to 20140630 as an example.

    Any help would be greatly appreciated. Right now I have to keep exporting the data separately to excel and then sorting by ID and moving columns around. Very time consuming and not efficient. I need to show reports to customers.

  2. vtxyzzy
    Member
    Posted 3 weeks ago #

    A query like this should be close to what you need. Just modify the post_type, dates, and venue to your own values.

    $post_type = 'parks';
    $start_date = '20100901';
    $end_date = '20100920';
    $venue = 'wicks';
    $args = array(
    	'numberposts' => -1,
    	'post_type' => $post_type,
    	'meta_query' => array(
    		'relation' => 'AND',
    		array(
    			'key' => 'start_date',
    			'value' => array( $start_date, $end_date ),
    			'compare' => 'BETWEEN'
    		),
    		array(
    			'key' => 'venue',
    			'value' => $venue,
    			'compare' => '='
    		)
    	),
    	'meta_key' => 'start_date',
    	'order_by' => 'meta_value',
    );
    
    // get results
    $the_query = new WP_Query( $args );
    print_r($the_query->request);
    if ( $the_query->have_posts() ) { ?>
       <ul>
          <?php while ( $the_query->have_posts() ) { ?>
             <li>
                <?php $the_query->the_post();
                $date = get_post_meta( $post->ID, 'start_date', true );
                $venue = get_post_meta( $post->ID, 'venue', true );
                echo "$post->ID $post->post_title $venue $date";
                ?>
             </li>
          <?php } ?>
       </ul>
    <?php }

Reply

You must log in to post.

About this Topic

Tags