Support » Fixing WordPress » How can I query on the postmeta table for two keys

  • 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)

    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.

Viewing 1 replies (of 1 total)
  • 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',
    			'key' => 'start_date',
    			'value' => array( $start_date, $end_date ),
    			'compare' => 'BETWEEN'
    			'key' => 'venue',
    			'value' => $venue,
    			'compare' => '='
    	'meta_key' => 'start_date',
    	'order_by' => 'meta_value',
    // get results
    $the_query = new WP_Query( $args );
    if ( $the_query->have_posts() ) { ?>
          <?php while ( $the_query->have_posts() ) { ?>
                <?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";
          <?php } ?>
    <?php }
Viewing 1 replies (of 1 total)
  • The topic ‘How can I query on the postmeta table for two keys’ is closed to new replies.