Inside phpMyAdmin, I'm dealing with custom meta fields in WordPress.
Say I've custom meta for 3 fields- Address, Latitude, Longitude and would like to run an SQL query to show only these 3 posts using their values as rows below.
I'm able to only learn how to get among the posts to exhibit, and it is data by running this:
SELECT DISTINCT wp_postmeta.meta_value AS address FROM wp_postmeta, wp_posts WHERE post_type = ‘dealers’ AND wp_postmeta.meta_key = ‘_dealer_address’
Could someone point me inside a direction of methods to operate the query to incorporate all 3 posts?
What I’m attempting to do is mimic the setup on the table within this: http://code.google.com/apis/maps/articles/phpsqlsearch_v3.html#outputxml
Because I've got a custom publish type (sellers) making use of your custom metabox for latitude and longitude.
Your SQL query examined all right in phpMyAdmin and looked just like my other by hand-produced test dealer table, however when I attempted to exchange the google maps example query within the PHP- it had been a no-go.
Could it be too complicated of the query you believe?
This is actually the test query that actually works without needing the wordpress custom publish:
$query = sprintf("SELECT address, name, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20",
Certainly avoid distinct. 99% of times, it is a sign that something's wrong (using the schema or perhaps your knowledge of the schema).
I believe that Cheluis' suggestion may not connect the lat/lengthy using the address.
I do not possess a wordpress installation to experience with. I am hoping that you have a primary key (say, publish_id). You do not utilize it to become listed on the 2 tables together inside your original example, that is most likely why you are while using distinct.
If the id is available, you are able to join to the wordpress_postmeta table several occasions, e.g.
SELECT meta1.meta_value AS address, meta2.meta_value as latitude, meta3.meta_value as longitude FROM wp_posts posts, wp_postmeta meta1, wp_postmeta meta2, wp_postmeta meta3 WHERE posts.post_id = meta1.post_id AND posts.post_type = ‘dealers’ AND meta1.meta_key = ‘_dealer_address’ AND posts.post_id = meta2.post_id AND posts.post_type = ‘dealers’ AND meta1.meta_key = ‘_dealer_latitude’ AND posts.post_id = meta3.post_id AND posts.post_type = ‘dealers’ AND meta1.meta_key = ‘_dealer_longitude’
This assumes that every dealer may have the 3 characteristics. Otherwise, you will need to change to an outer join, which can make the query much more ugly.
Hope that can help.
I'm not acquainted with WordPress, I suppose which you can use the UNION ALL clausule. I'm not sure if it's the very best solution however i believe that it cant works. Something similar to:
SELECT DISTINCT wp_postmeta.meta_value AS address, null as latitude, null as longitude FROM wp_postmeta, wp_posts WHERE post_type = ‘dealers’ AND wp_postmeta.meta_key = ‘_dealer_address’ Union All SELECT null as address, distinct(second_value) as latitude, null as longitude FROM wp_postmeta, wp_posts WHERE post_type = ‘dealers’ AND wp_postmeta.meta_key = ‘_dealer_latitude’ Union all SELECT null as address, null as latitude, distinct(third_value) as longitude FROM wp_postmeta, wp_posts WHERE post_type = ‘dealers’ AND wp_postmeta.meta_key = ‘_dealer_longitude’
Or something like that such as this. Using the right values with every union.