Help setting up a sql query

Question

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>";
        }
    }
?>

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
– s_ha_dum, Commented Dec 11, 2013 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
– user715564, Commented Dec 11, 2013 at 14:52
So you actually need to query over two (or more) custom fields? — s_ha_dum
– s_ha_dum, Commented Dec 11, 2013 at 14:59

Shazzad · Accepted Answer · 2013-12-11 15:35:15Z

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>";
    }
}

answered Dec 11, 2013 at 15:35

Shazzad

3,3511 gold badge18 silver badges23 bronze badges

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
– user715564

2013-12-11 16:17:13 +00:00
Commented Dec 11, 2013 at 16:17

Add a comment |

David · Accepted Answer · 2013-12-11 15:16:35Z

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.

answered Dec 11, 2013 at 15:16

David

3,34520 silver badges38 bronze badges

Add a comment |

Stack Exchange Network

Help setting up a sql query

2 Answers 2

Your Answer

Hot Network Questions

Help setting up a sql query

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions