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.