I lately published an issue about hooking up two queries to omit elements present in another:

MYSQL - How to join two queries to omit elements found in the second query (or perhaps any better solution?)

Now i'm wondering how to check out numerous meta_secrets as well as their corresponding values to attain another thing.

I'm basically building a celebration system with custom fields, simplified, a celebration may have:

  • Start Date
  • Finish Date
  • Ongoing (Good or bad)

They are saved as

-----------
wp_postmeta
-----------
meta_key
meta_value
post_id

e.g.

-----------
wp_postmeta
-----------
Start Date
2011-07-30
10

-----------
wp_postmeta
-----------
End Date
2011-08-30
10

-----------
wp_postmeta
-----------
Ongoing
Yes
10

My confusion is based on trying to check out multiple *wordpress_postmeta* records and hooking up all of them with the right publish.

For instance, I wish to discover the posts that:

  • Start Date > Today
  • Finish Date > Today
  • Ongoing = Yes

Building upon the prior question, I'm trying this question:

SELECT * FROM wp_posts, wp_postmeta
WHERE wp_posts.ID = wp_postmeta.post_id

AND wp_posts.ID IN (
SELECT post_id FROM wp_postmeta
WHERE wp_postmeta.meta_key = 'Start Date'
AND wp_postmeta.meta_value > NOW())

AND wp_posts.ID IN (
SELECT post_id FROM wp_postmeta
WHERE wp_postmeta.meta_key = 'Ongoing'
AND wp_postmeta.meta_value = 'Yes')

AND wp_posts.ID IN (
SELECT post_id FROM wp_postmeta
WHERE wp_postmeta.meta_key = 'End Date'
AND wp_postmeta.meta_value > NOW())

Which isn't really working.

I seem like there has to be a method to find all of the related meta_secrets and join their values towards the publish table, so it may be easier utilized. For instance, can you really discover the meta_value for 'Start Date' and join it towards the wordpress_posts having a column title *start_date_value* or something like that like this?

Or how must i be approaching this issue rather?


While using answer from @karevn, I emerged using the following code which works just as I would like:

$query = array(
        'category_name' => 'event',
        'meta_query' => array(
            'relation' => 'AND',
            array(
                'key' => 'Start Date',
                'value' => $today,
                'compare' => '>'
            ),
            array(
                'key' => 'End Date',
                'value' => $today,
                'compare' => '>'
            ),
            array(
                'key' => 'Ongoing',
                'value' => 'Yes',
                'compare' => '='
            ),
        )
    );

I believe this can be a pretty effective feature of wordpress.

You should use WP_Query class with meta_query option and steer clear of writing your personal SQL whatsoever. See Codex: http://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters. It should be a much better means by WordPress. Your query will appear virtually such as this then:

$query = new WP_Query(array('meta_compare' => array(
    array('key' => 'Start Date', 'compare' => '>', 'type' => 'DATE', 'value' => $today),
    array('key' => 'End Date', 'compare' => '>', 'type' => 'DATE', 'value' = $today),
    array('key' => 'Ongoing', 'value' => 'Yes')));

Along side it effect of utilizing this code is the fact that its results might be cached by DB caching wordpress plugin.