How can I query on the postmeta table for two keys

• dhobson98

• June 12, 2014 at 9:19 am

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.

• dhobson98

• August 22, 2014 at 8:15 am

Is there a better way to get this or do I simply have to run the update three times. The issue is that 3 of the columns I need are in the meta_key value.

[Add most of the info from post and also Venue]

INSERT into wp_venue_title_startdate (post_id, meta_post_id, post_author, post_title, meta_venue, post_type, post_status)
SELECT p.id, m.post_id, p.post_author, p.post_title, m.meta_value,p.post_type,p.post_status
FROM wp_posts p, wp_postmeta m
WHERE m.post_id=p.id AND m.meta_key=”venue” AND p.post_status=”publish”;

[Add User Name]
UPDATE wp_venue_title_startdate tbl_sd
INNER JOIN wp_users u
ON tbl_sd.post_author = u.id
SET tbl_sd.user_login=u.user_login

[Add Start Date]
UPDATE wp_venue_title_startdate tbl_sd
INNER JOIN wp_postmeta m
ON tbl_sd.post_id = m.post_id
SET tbl_sd.meta_start_date=m.meta_value
WHERE m.meta_key=”start_date”

[Add END Date]
UPDATE wp_venue_title_startdate tbl_sd
INNER JOIN wp_postmeta m
ON tbl_sd.post_id = m.post_id
SET tbl_sd.meta_end_date=m.meta_value
WHERE m.meta_key=”end_date”