CODEWITHSUNDEEP
mysqlwordpress
Ledgemonkey
Ledgemonkey

Reputation: 7481

How do I select from multiple rows in wp_postmeta table using wpdb select statement Wordpress

I have a select statement like this:

   $results = $wpdb->get_results( "SELECT $wpdb->postmeta.meta_value 
   FROM $wpdb->postmeta    
   WHERE 1=1 AND $wpdb->postmeta.meta_key = 'geo_short_address'" );

But I'm now trying to filter this statement to only include those 'geo_short_address' that also have an entry or not empty in a meta-key field called '_Company'

They have the same post id, for instance:

    post_id        meta_key           meta_value
       53     geo_short_address      Nottingham
       53        _Company            Ledgemonkey

So I only want to return Nottingham if the meta_key _Company exists in the post_id

There are other entries in the DB that will not have the meta_key _Company they are the ones I want to exclude ...?

I have tried various things but can't seem to get the combination..?

Upvotes: 0

Views: 2360

Answers (1)

O. Jones
O. Jones

Reputation: 108806

To restate your question, you want to filter the postmeta table on multiple criteria. You need to do something like this (edit to include meta_key criteria).

SELECT whatever
  FROM $wpdb->postmeta.meta_value
 WHERE 1=1
   AND $wpdb->postmeta.post_id IN (
             SELECT distinct $wpdb->postmeta.post_id
               FROM $wpdb->postmeta
              WHERE $wpdb->postmeta.post_value = 'Ledgemonkey'
                AND $wpdb->postmeta.meta_key = '_Company'
             )
   AND $wpdb->postmeta.meta_key = 'geo_short_address'

This can be a bit of a performance hairball if you have tens of thousands of postmeta rows, but it will work.

By the way, you can get rid of that ugly but inconsequential 1=1 by judicious use of the implode() function.

Upvotes: 1

Related Questions