CODEWITHSUNDEEP
mysqlsqlwordpress
Kamil
Kamil

Reputation: 812

Select 3 columns with specific keys from Wordpress postmeta table

I want to make store locator using PHP and MySQL with Wordpress as CMS. I am using Pods plugin to create custom posts. There, I can add new locations. For each point I insert latitude and longitude. Now, as wordpress table works, these values are added as new rows in table, where meta_key is lat or lng and the meta_value is the value for that property.

Is there any way, probably using nested queries, to get the data in format:

| id | lat | lng |

from the data in format:

| id | post_id | meta_key | meta_value

I was trying to use query:

SELECT la.id AS id, la.lat AS lat, ln.lng AS lng
FROM (

    SELECT post_id AS id, meta_value AS lat
    FROM  `wp_postmeta` 
    WHERE  `meta_key` LIKE  'lat'
) AS la, (

    SELECT post_id AS id, meta_value AS lng
    FROM  `wp_postmeta` 
    WHERE  `meta_key` LIKE  'lng'
) AS ln
GROUP BY id

but it's returning only proper values for latitude and only one value for longitude repeated in each row.

Upvotes: 0

Views: 445

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

First, you don't want to use subqueries in MySQL when you can avoid them.

Similarly, you don't need an aggregation. Instead, you need a join condition:

select lat.post_id, lat.meta_value as lat, lng.meta_value as lng
from wp_postmeta lat join
     wp_postmeta lng
     on lat.post_id = lng.post_id and
        lat.meta_key = 'lat' and
        lng.meta_key = 'lng';

Upvotes: 0

Related Questions