How can I display all values of a custom field from posts with a certain value of another custom field or from certain post types?

Question

I know the question is quite ambiguous. Let me explain:

I have a page that lists all posts with a certain value of a custom field. (Eg.: The page Rentals lists all posts with the value rental of custom field offer_type ).

I'd like to add a filtering option by city, so that only posts with a certain value of city custom field get displayed. For this I created a form using the get method that builds a custom query (in my ex. with two custom fields, 'offer_type' and 'city').

What I need is a way to list all the cities (values of city custom field) for the posts with the rental value of offer_type. Each city has to be listed once.

I tried this bit of code (note that I took all the form wrapping out):

$metakey = 'city';
   $cities = $wpdb->get_col($wpdb->prepare("SELECT DISTINCT meta_value 
           FROM $wpdb->postmeta WHERE meta_key = %s ORDER BY meta_value ASC", $metakey) );

   if ($cities) {   
                 foreach ( $cities as $city ) {
                 echo $city;
            }
        }

but this lists all the values of city custom field for all the posts (so if for eg. I have a post with value of offer_type different than rental, its value of city custom field will be displayed in my list).

I know the issue revolves around sql queries and joining tables, but I really lack the skills to solve this problem.

Please help!

Answer 1

take a look at the reference docs for the WP_Query class: http://codex.wordpress.org/Class_Reference/WP_Query

something like this:

$args = array(
  //some key/value pairs...whatever
          'meta_query => array(
                          array(
                         'key' => somekey,
                         'value' => somevalue,
                         'compare' => some comparison operator
                         ),
                         array(
                         'key' => some other key,
                         'value' => some other value,
                         'compare' => some comparison operator
                         )
                     )
               )

$my_query = new WP_Query( $args );

//some code to do something with the results of the query

Answer 2

this is not a direct answer to your question but a line of code you would use as reference to work onto.

here is a copy of what i use to search thru my table, i search between a data range and then limit it to only display instances of an entered usr_id

 $this->items = $wpdb->get_results($wpdb->prepare("SELECT  *  FROM $table_name WHERE `datec` BETWEEN CAST('$start_date' AS DATE) AND CAST('$end_date' AS DATE) AND `usr_id`= $usr_id"), ARRAY_A);

hope this helps :)