CODEWITHSUNDEEP
phpmysqlwordpresscustom-post-typeadvanced-custom-fields
Christoforos A
Christoforos A

Reputation: 21

Mysql SELECT custom fields (post_meta) for custom post type

I am trying to query Wordpress posts.

I have created a custom post type People. Each people post has custom fields name, age, location, birthday and more. The custom fields are created using the Advanced Custom Fields Plugin.

I wand to to query all the custom fields for People custom post type.

I want an output like this:

+----+-------------+-----------+--------+-----------------------------+----------+-----+
| id | post_title  | name          | age    | location                    | birthday   | 
+----+-------------+-----------+--------+-----------------------------+----------+-----+
|  1 | SAMPLE      | some_name     | XX     | sample_location             | 10/07/1980 |
|  1 | SAMPLE      | some_name     | XX     | sample_location             | 10/07/1980 |
|  1 | SAMPLE      | some_name     | XX     | sample_location             | 10/07/1980 |
|  1 | SAMPLE      | some_name     | XX     | sample_location             | 10/07/1980 |
|  1 | SAMPLE      | some_name     | XX     | sample_location             | 10/07/1980 |
+----+-------------+-----------+--------+-----------------------------+----------+-----+

What would be the proper syntax to do this?

I have tried:

SELECT * 
FROM  `wp_posts` ,  `wp_postmeta` 
WHERE  `post_type` =  'people'

But this list all the wordpress regular post fields.

Can anyone help on this?

Upvotes: 1

Views: 2675

Answers (2)

Christoforos A
Christoforos A

Reputation: 21

Here is the solution for anyone might have the same question!

SELECT posts_people.ID AS people_ID,
   posts_people.post_title AS people_post_title,
   (select meta_value from wp_postmeta where meta_key = 'name' AND post_id = posts_people.ID) as name,
   (select meta_value from wp_postmeta where meta_key = 'age' AND post_id = posts_people.ID) as age,
   (select post_title from wp_posts where ID = SUBSTRING_INDEX(SUBSTRING_INDEX((select meta_value from wp_postmeta where meta_key = 'location' AND post_id = posts_people.ID),'";',1),':"',-1)) as location,
   (select meta_value from wp_postmeta where meta_key = 'birthday' AND post_id = posts_people.ID) as stelexos_kinito
FROM wp_posts AS posts_people
WHERE post_type = 'people' and post_status = 'publish'

Upvotes: 1

Tarreq
Tarreq

Reputation: 1365

Firstly, you're selecting from two tables without joining or setting the relation between them (it should be kind of relationship to join each data set to another)

You can do something like this: 

SELECT t1.*, t2.* 
FROM posts as t1 
INNER JOIN postmeta as t2 
ON t1.ID = t2.post_id 
WHERE t1.post_type = 'people' 
AND t2.meta_key = 'name'

** NOTE: you should notice your tables names, and replace in query as required, also the columns names in your table.

Upvotes: 0

Related Questions