We are using Custom Post Types for directory listings - store locations. We are creating a page for every state, but then want to list all the cities that have a location. We are using meta key/values for the address data. Every store location has: name, address, city, state, zip.
If you are viewing the California page, I want to make a list of all the cities that actually have locations in there. For example, if we had locations in San Diego & Los Angeles, but not San Francisco, we do not want to show San Francisco. The idea is that it will automatically update the list of cities as we add addresses and we don't have to manually make the city list.
Anyway, I am trying to do a custom query that:
a) Finds all the locations where the meta_key=California
b) Within this result set, find all the unique city names
So far I have:
$querystr = " SELECT wposts.ID FROM $wpdb->posts wposts LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id WHERE wpostmeta.meta_key = 'state' AND wpostmeta.meta_value = '".get_the_title($post->ID)."' ORDER BY wpostmeta.meta_value ASC ";
This only pulls all the locations within a state ( get_the_title($post->ID) returns the name of the state as each state has it's own page).
How can I take this query one step further and pull all the unique cities within the state?