0

I need to list out specific custom field items that contain an associated custom field entry. For example, the custom fields are named "type" and "food". I would like to list out all of the breakfast foods. "Breakfast" being the "type" custom field and "bacon", "eggs", "biscuits" being the "food". Right now, I have this query, but it lists out every type of food when I need it to list out only the breakfast types. Would I need to add "WHERE meta_value = 'type' AND meta_key = 'breakfast'" somewhere? I've tried a few places and nothing would work.

<?php
    $metakey = 'type';

    $stocktypes = $wpdb->get_col($wpdb->prepare("SELECT DISTINCT meta_value FROM $wpdb->postmeta WHERE meta_key = %s ORDER BY meta_value ASC", $metakey) );
    if ($stocktypes) {
        foreach ($stocktypes as $stocktype) {
            echo "<option value=\"" . $stocktype . "\">" . $stocktype . "</option>";
        }
    }
?> 
  • 2
    This is confusing. You say that you have two custom fields, "type" and "food", but then say that you have a "breakfast" type. Something is missing. The postmeta table is not that complicated. It sounds almost like you are talking about a custom taxonomy, and if you aren't perhaps you should be. Please clarify. How do you get sub-types of a type using custom fields? – s_ha_dum Dec 11 '13 at 14:48
  • Yes, it is confusing! I have a list of posts that have custom fields set for each post. Each post has a custom field of either breakfast, lunch or dinner. Each post also has a type of food assigned to it e.g. bacon, eggs, busciuts. That is the "type" custom field. I want to display the "type" of every post that has the breakfast custom field assigned to it. – user715564 Dec 11 '13 at 14:52
  • So you actually need to query over two (or more) custom fields? – s_ha_dum Dec 11 '13 at 14:59
0

Ok, if i understand you clearly, then this is the solution

// first get the post ids that have the breakfast food type
$post_ids = $wpdb->get_col( "SELECT DISTINCT post_id FROM $wpdb->postmeta WHERE meta_key = 'type' AND meta_value='Breakfast'" );

// now get the food names
$stocktypes = $wpdb->get_col( "SELECT DISTINCT meta_value FROM $wpdb->postmeta WHERE meta_key = 'food' AND post_id IN (". implode(',', array_map('absint', $post_ids) ) .")" );


if ($stocktypes) {
    foreach ($stocktypes as $stocktype) {
        echo "<option value=\"" . $stocktype . "\">" . $stocktype . "</option>";
    }
}
  • Perfect! That's exactly what I need. So instead of doing one query I need to break it up into two and then use AND post_id IN (". implode(',', array_map('absint', $post_ids) ) ."). Yes, that is excellent, thanks. – user715564 Dec 11 '13 at 16:17
1

Let's see if I got you right. Try this one:

SELECT m.meta_value FROM wp_postmeta m
    INNER JOIN
wp_postmeta k
    ON
m.post_id = k.post_id
WHERE m.meta_key = 'food'
AND k.meta_key = 'type' 
AND k.meta_value = 'breakfast'

I'm not sure whether this is scalable, you may consider to use custom taxonomy as suggested before.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.