Mysql - Wordpress: list custom posts with more than one metadata value

Question

In wordpress, custom posts are held in the wp_posts table, and their ID is referenced in another table wp_meta with the following columns post_id, meta_key, meta_value

Let's say I have a recipe called pudding in wp_posts, and in wp_meta I have 3 meta_keys: "time_for_preparation", "ingredients", "difficulty"

How do I write a mysql query that returns me a single row with the 3 meta_keys?

If I do an inner join I'll just see 3 sets of "pudding", each with the same wp_posts data and ONE different meta_key

What I want is a single row of pudding with an array of meta_keys

can that be done?

you need output like this pudding "time_for_preparation, ingredients,difficulty" — rahularyansharma, Oct 17 '11 at 09:13
comma separated would be good, but ideally I need columns with the meta_key "title" holding the meta_value "value"...like if everything was in the wp_posts table in a single row — Igor, Oct 17 '11 at 09:50

score 0 · Answer 1 · edited May 23 '17 at 12:27

0

create table wp_post
( 
 id int identity(1,1),
 name nvarchar(200) 
)


create table wp_meta
(
  id int identity(1,1),
   postid int,
  metaname nvarchar(200)
)



insert into wp_post select 'Apple'

insert into wp_meta select 1,'red'

select * from wp_post
select  * from wp_meta


select *
 ,stuff((select  ', ' +metaname from wp_meta where postid=w.id for xml path('')), 1, 1,' ')
 from wp_post as w

this is all done by me in MS SQL Server

for changing this into you can see this post which have for MYSql how-do-i-create-a-comma-separated-list-using-a-sql-query

Edited

as i dont have mysql but i tried to convert this by using the url which i have suggested you so i think this will be in mysql like below

SELECT w.name,
         GROUP_CONCAT(m.meta_key, ',')
    FROM wp_post w
    JOIN wp_meta m ON m.post_id= w.id    
GROUP BY w.name

edited May 23 '17 at 12:27

Community

1
1

answered Oct 17 '11 at 09:58

rahularyansharma

11,156
18
79
135

It's more like this: create table wp_post ( id int identity(1,1), name nvarchar(200) ) create table wp_meta ( id int identity(1,1), postid int, metakey nvarchar(200) metaname nvarchar(200) ) insert into wp_post select 'Apple' insert into wp_meta select 1,'color','red' insert into wp_meta select 1,'brand','del monte' insert into wp_meta select 1,'weight','1oz' And I want to be returned a set of data like name=Apple, color=red, brand=del monte,weight=1oz Thanks for helping! – Igor Oct 17 '11 at 10:22
what you get by this in MYSQl SELECT w.name, GROUP_CONCAT(m.meta_key, ',') FROM wp_post w JOIN wp_meta m ON m.post_id= w.id GROUP BY w.name – rahularyansharma Oct 17 '11 at 10:28

Mysql - Wordpress: list custom posts with more than one metadata value

1 Answers1