SQL Query for Custom Post Type and Multiple Custom Fields

Question

I am using WP Data Tables to create a table from the SQL DB. In the wordpress backend the example code to use looked like this:

SELECT post_id, post_date
FROM wp_posts
WHERE post_type =  'custom_post_type'
AND post_status =  'publish'

Im trying to get custom field values from the post meta. Here is what I have so far...

SELECT post_id, post_date
FROM wp_posts
WHERE post_type =  'custom_post_type'
AND post_status =  'publish'
AND SELECT custom_field_key_1, custom_field_key_2, custom_field_key_3
FROM wp_postmeta
WHERE post_id = post_id

UPDATE:

I found that p.ID was needed instead of post_id and that I need search for the meta_key. Something like...

SELECT p.post_title, 
       p.post_date,
       pm.meta_key = 'custom_field_key'
FROM wp_posts p 
INNER JOIN wp_postmeta pm 
ON p.ID = pm.post_id
WHERE p.post_type = 'custom_post_type'
AND p.post_status = 'publish'

I believe you need JOIN here.

PM 77-1
– PM 77-1

2013-03-02 00:15:07 +00:00
Commented Mar 2, 2013 at 0:15 — PM 77-1
– PM 77-1, Commented Mar 2, 2013 at 0:15

sgeddes · Accepted Answer · 2013-03-02 00:21:32Z

4

Use an INNER JOIN:

SELECT p.post_id, 
       p.post_date, 
       pm.custom_field_key_1, 
       pm.custom_field_key_2, 
       pm.custom_field_key_3
FROM wp_posts p 
   INNER JOIN wp_postmeta pm 
       ON p.post_id = pm.post_id
WHERE p.post_type = 'custom_post_type'
   AND p.post_status = 'publish'

answered Mar 2, 2013 at 0:21

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

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

2 Comments

sgeddes Over a year ago

@user2106176 -- glad this helped. Looking at your update, I think you need to move "pm.meta_key = 'custom_field_key'" to your WHERE clause. You can't set a field value in your SELECT statement. Best of luck!

Naren Verma Over a year ago

I know this answer is too old but it's now working with custom_field_key

PM 77-1 · Accepted Answer · 2013-03-02 00:18:53Z

0

Assuming that standard SQL is supported you will need something like this (untested):

SELECT w.post_id, w.post_date, m.custom_field_key_1, m.custom_field_key_2, m.custom_field_key_3
FROM wp_posts w, wp_postmeta m
WHERE post_type =  'custom_post_type' AND post_status =  'publish'
AND w.post.id = m.post.id

answered Mar 2, 2013 at 0:18

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

Comments

bobs · Accepted Answer · 2013-03-02 00:22:18Z

0

You can try this.

SELECT p.post_id, p.post_date,
    pm.custom_field_key_1, pm.custom_field_key_2, pm.custom_field_key_3
FROM wp_posts p
JOIN wp_postmeta pm ON p.post_id = pm.post_id
WHERE p.post_type =  'custom_post_type'
AND p.post_status =  'publish'

answered Mar 2, 2013 at 0:22

bobs

22.3k12 gold badges71 silver badges81 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:25:03Z

Ok even though you updated an answer. I took your example and I worked this out:

SELECT p.ID, 
       p.post_title, 
       pm.meta_value as 'value1', 
       pma.meta_value as 'value2'

FROM  wp_posts p 
      INNER JOIN wp_postmeta AS pm  ON pm.post_id  = p.ID
      INNER JOIN wp_postmeta AS pma ON pma.post_id = p.ID

WHERE
      pma.meta_key = 'custom_field_key_1' AND
      pm.meta_key = 'custom_field_key_2' AND

      p.post_type = 'your_post_type' AND 
      p.post_status = 'publish'

So here I am using alias AS for values hosted in the same table, INNER JOIN for wp_postmeta and post_id and that's all.

References: Multiple Inner join same table

By doing this you will get an array with posts and your selected custom fields:

array(1) {
    [0]=> object(stdClass)#341 (4) {
          ["ID"]=>    string(1) "1123"
          ["post_title"]=>  string(15) "Your post title"
          ["custom_field_key_1"]=> string(12) "Your value 1 "
          ["custom_field_key_2"]=> string(29) "Your value 2"
}

You can add as many alias and meta_key as you need. Hope this help!

Collectives™ on Stack Overflow

SQL Query for Custom Post Type and Multiple Custom Fields

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related