1

Inside phpMyAdmin, I am working with custom meta fields in WordPress.

Say I have custom meta for 3 fields- Address, Latitude, Longitude and wish to run an SQL query to display only these 3 columns with their values as rows below.

I can only figure out how to get one of the columns to show, and it’s 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 in a direction of how to run the query to include all 3 columns?

What I’m trying to do is mimic the setup of the table in this: http://code.google.com/apis/maps/articles/phpsqlsearch_v3.html#outputxml

Because I have a custom post type (dealers) using your custom metabox for latitude and longitude.

Your SQL query tested just fine in phpMyAdmin and looked identical to my other manually-created test dealer table, but when I tried to replace the google maps example query in the PHP- it was a no-go.

Is it too complicated of a query you think?

This is the test query that works without using the wordpress custom post:

$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",
Improve this question
3
  • General note: Stay away from DISTINCT! It's scary. It hurts. It doesn't help you. Use GROUP BY if you have duplicates or you want to aggregate. DISTINCT is never helpful, always confusing to beginners and will hardly ever return the results you expect. Commented Apr 7, 2011 at 21:50
  • Are you asking the same question as this one? The linked one is for a different RDBMS, but presently I'm trying to establish what you are after. Commented Apr 8, 2011 at 7:20
  • I have updated my post to be more clear of what i'm trying to do. Andriy, yes it does seem similair... but as someone posted on that "it is the application's job for display of the table, not the server". I see that maybe trying to make my table look like the original test is unnecessary? Commented Apr 8, 2011 at 17:09

2 Answers 2

Reset to default
2

Definitely stay away from distinct. 99% of the time, it's a sign that something's wrong (with the schema or your understanding of the schema).

I think that Cheluis' suggestion might not associate the lat/long with the address.

I don't have a wordpress installation to play with. I really hope that there's a primary key (say, post_id). You don't use it to join the two tables together in your original example, which is probably why you're using the distinct.

If this id exists, you can join back to the wp_postmeta table several times, 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_type = ‘dealers’ AND posts.post_id = meta1.post_id AND meta1.meta_key = ‘_dealer_address’ AND posts.post_id = meta2.post_id AND meta2.meta_key = ‘_dealer_latitude’ AND posts.post_id = meta3.post_id AND meta3.meta_key = ‘_dealer_longitude’

This assumes that each dealer will have all three attributes. If not, you'll need to switch to an outer join, which will make the query even more ugly.

Hope that helps.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I am not familiar with WordPress, I guess that you can use the UNION ALL clausule. I don't know if it is the best solution but I think that it cant works. Something like:

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 this. With the right values with every union.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.