I have custom post type "store" that has two different custom fields: Coutry and City. When in the single country page I list all the stores in that country.
What I would like to do is to list all the distinct cities that have stores based on the country selected. Just to tell the user that this country has stores in these cities before I list all the stores.
I'm able to list all the cities but not the ones that are based on the country selected. I use this query that I call with a shortcode that has some other stuff there too but I need that $country to be included into that SQL query in a way that it works together with the 'city' and returns only the cities that are in that country:
$country= $atts['country'];
global $wpdb;
$query = "SELECT DISTINCT meta_value
FROM $wpdb->postmeta
WHERE meta_key = 'city'
;";
$cities= $wpdb->get_results( $query );
foreach( $cities as $city) :
echo'<li>'; echo $city->meta_value; echo '</li>';
endforeach;
I haven't been dealing with the SQL queries that much that I could figure this out.
So for example There are 50 stores in total and 5 of them are in USA: 3 in New York, 1 in Seattle and 1 in Miami. I would just like to have the cities listed that are in the USA like this:
Miami, New York, Seattle
So how could I query the unique meta field values from meta_key 'cities' based on the meta field value from meta_key 'country'?
Thanks in advance!
country
, then to use those post IDs to grab the meta values withcity
filtered by those post IDs