Select rows as columns for wordpress post meta
WordPress's wp_postmeta
table has all the additional fields for a post but they are in rows so it's easy to add more.
However, now I want to query for all the fields of all the posts lets say, I obviously want those fields in a column and not a row.
This is my query that I am running
SELECT p.post_title,
m.meta_value,
m.meta_key
FROM wp_posts p
JOIN wp_postmeta m
ON p.id = m.post_id
WHERE p.id = 72697;
This will give me all the meta_values and their respective meta keys as columns. But I need the meta keys values as columns and meta values as rows
For example a meta_key
could be additional_description
and it's value could be What's up
So I need something like this
SELECT p.post_title, additional_description
FROM wp_posts p
JOIN wp_postmeta m
ON p.id = m.post_id
WHERE p.id = 72697;
I need it as a column. I also need all of the posts and not a specific one, but whenever I remove the where
it just doesn't query (I have lots of posts, that could be an issue).
Here is some sample data and how I want the results to show up
wp_postmeta table
meta_key post_id meta_key meta_value
1 5 total_related 5
2 5 updated 0
3 5 cricket 1
4 8 total_related 8
5 8 updated 1
6 8 cricket 0
wp_post table
id post_title other things I dont care about
5 This is awesome
8 This is more awesome
wp_post id is related to post_id on wp_postmeta table
Result wanted
post_title total_related updated cricket
This is awesome 5 0 1
This is more awesome 8 1 0
php mysql sql wordpress
add a comment |
WordPress's wp_postmeta
table has all the additional fields for a post but they are in rows so it's easy to add more.
However, now I want to query for all the fields of all the posts lets say, I obviously want those fields in a column and not a row.
This is my query that I am running
SELECT p.post_title,
m.meta_value,
m.meta_key
FROM wp_posts p
JOIN wp_postmeta m
ON p.id = m.post_id
WHERE p.id = 72697;
This will give me all the meta_values and their respective meta keys as columns. But I need the meta keys values as columns and meta values as rows
For example a meta_key
could be additional_description
and it's value could be What's up
So I need something like this
SELECT p.post_title, additional_description
FROM wp_posts p
JOIN wp_postmeta m
ON p.id = m.post_id
WHERE p.id = 72697;
I need it as a column. I also need all of the posts and not a specific one, but whenever I remove the where
it just doesn't query (I have lots of posts, that could be an issue).
Here is some sample data and how I want the results to show up
wp_postmeta table
meta_key post_id meta_key meta_value
1 5 total_related 5
2 5 updated 0
3 5 cricket 1
4 8 total_related 8
5 8 updated 1
6 8 cricket 0
wp_post table
id post_title other things I dont care about
5 This is awesome
8 This is more awesome
wp_post id is related to post_id on wp_postmeta table
Result wanted
post_title total_related updated cricket
This is awesome 5 0 1
This is more awesome 8 1 0
php mysql sql wordpress
sample data and wished result will be better .
– echo_Me
Jul 1 '14 at 18:12
@echo_Me added.
– user1952811
Jul 1 '14 at 18:18
This question has been asked and answered several times. There are several approaches, each with advantages and drawbacks. The general problem you are addressing is the EAV model. A brief example of several approaches can be found here: http://stackoverflow.com/questions/8764290/what-is-best-performance-for-retrieving-mysql-eav-results-as-relational-table
– spencer7593
Jul 1 '14 at 18:18
add a comment |
WordPress's wp_postmeta
table has all the additional fields for a post but they are in rows so it's easy to add more.
However, now I want to query for all the fields of all the posts lets say, I obviously want those fields in a column and not a row.
This is my query that I am running
SELECT p.post_title,
m.meta_value,
m.meta_key
FROM wp_posts p
JOIN wp_postmeta m
ON p.id = m.post_id
WHERE p.id = 72697;
This will give me all the meta_values and their respective meta keys as columns. But I need the meta keys values as columns and meta values as rows
For example a meta_key
could be additional_description
and it's value could be What's up
So I need something like this
SELECT p.post_title, additional_description
FROM wp_posts p
JOIN wp_postmeta m
ON p.id = m.post_id
WHERE p.id = 72697;
I need it as a column. I also need all of the posts and not a specific one, but whenever I remove the where
it just doesn't query (I have lots of posts, that could be an issue).
Here is some sample data and how I want the results to show up
wp_postmeta table
meta_key post_id meta_key meta_value
1 5 total_related 5
2 5 updated 0
3 5 cricket 1
4 8 total_related 8
5 8 updated 1
6 8 cricket 0
wp_post table
id post_title other things I dont care about
5 This is awesome
8 This is more awesome
wp_post id is related to post_id on wp_postmeta table
Result wanted
post_title total_related updated cricket
This is awesome 5 0 1
This is more awesome 8 1 0
php mysql sql wordpress
WordPress's wp_postmeta
table has all the additional fields for a post but they are in rows so it's easy to add more.
However, now I want to query for all the fields of all the posts lets say, I obviously want those fields in a column and not a row.
This is my query that I am running
SELECT p.post_title,
m.meta_value,
m.meta_key
FROM wp_posts p
JOIN wp_postmeta m
ON p.id = m.post_id
WHERE p.id = 72697;
This will give me all the meta_values and their respective meta keys as columns. But I need the meta keys values as columns and meta values as rows
For example a meta_key
could be additional_description
and it's value could be What's up
So I need something like this
SELECT p.post_title, additional_description
FROM wp_posts p
JOIN wp_postmeta m
ON p.id = m.post_id
WHERE p.id = 72697;
I need it as a column. I also need all of the posts and not a specific one, but whenever I remove the where
it just doesn't query (I have lots of posts, that could be an issue).
Here is some sample data and how I want the results to show up
wp_postmeta table
meta_key post_id meta_key meta_value
1 5 total_related 5
2 5 updated 0
3 5 cricket 1
4 8 total_related 8
5 8 updated 1
6 8 cricket 0
wp_post table
id post_title other things I dont care about
5 This is awesome
8 This is more awesome
wp_post id is related to post_id on wp_postmeta table
Result wanted
post_title total_related updated cricket
This is awesome 5 0 1
This is more awesome 8 1 0
php mysql sql wordpress
php mysql sql wordpress
edited Nov 13 '18 at 5:53
Cœur
19k9112154
19k9112154
asked Jul 1 '14 at 18:08
user1952811user1952811
1,05332143
1,05332143
sample data and wished result will be better .
– echo_Me
Jul 1 '14 at 18:12
@echo_Me added.
– user1952811
Jul 1 '14 at 18:18
This question has been asked and answered several times. There are several approaches, each with advantages and drawbacks. The general problem you are addressing is the EAV model. A brief example of several approaches can be found here: http://stackoverflow.com/questions/8764290/what-is-best-performance-for-retrieving-mysql-eav-results-as-relational-table
– spencer7593
Jul 1 '14 at 18:18
add a comment |
sample data and wished result will be better .
– echo_Me
Jul 1 '14 at 18:12
@echo_Me added.
– user1952811
Jul 1 '14 at 18:18
This question has been asked and answered several times. There are several approaches, each with advantages and drawbacks. The general problem you are addressing is the EAV model. A brief example of several approaches can be found here: http://stackoverflow.com/questions/8764290/what-is-best-performance-for-retrieving-mysql-eav-results-as-relational-table
– spencer7593
Jul 1 '14 at 18:18
sample data and wished result will be better .
– echo_Me
Jul 1 '14 at 18:12
sample data and wished result will be better .
– echo_Me
Jul 1 '14 at 18:12
@echo_Me added.
– user1952811
Jul 1 '14 at 18:18
@echo_Me added.
– user1952811
Jul 1 '14 at 18:18
This question has been asked and answered several times. There are several approaches, each with advantages and drawbacks. The general problem you are addressing is the EAV model. A brief example of several approaches can be found here: http://stackoverflow.com/questions/8764290/what-is-best-performance-for-retrieving-mysql-eav-results-as-relational-table
– spencer7593
Jul 1 '14 at 18:18
This question has been asked and answered several times. There are several approaches, each with advantages and drawbacks. The general problem you are addressing is the EAV model. A brief example of several approaches can be found here: http://stackoverflow.com/questions/8764290/what-is-best-performance-for-retrieving-mysql-eav-results-as-relational-table
– spencer7593
Jul 1 '14 at 18:18
add a comment |
3 Answers
3
active
oldest
votes
What about something like this?
SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
ON p.id = m3.post_id AND m3.meta_key = 'cricket'
And since you aren't looking for a specific post you should be able to do this.
If you want to query specific post_types
you can try something like this
SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
ON p.id = m3.post_id AND m3.meta_key = 'cricket'
WHERE p.post_type = 'my_custom_post_type';
Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!
– user1952811
Jul 1 '14 at 19:04
Worked for me.thanks +10
– Alive to Die
Dec 19 '16 at 20:19
add a comment |
Try that:
select post_title ,
MAX(CASE WHEN `meta_key`='total_related' THEN meta_value END)as 'total_related',
MAX(CASE WHEN `meta_key` = 'updated' THEN meta_value END) as 'updated' ,
MAX(CASE WHEN `meta_key` = 'cricket' THEN meta_value END) as 'cricket'
FROM wp_posts p
JOIN wp_postmeta m ON p.id = m.post_id
GROUP BY p.id
@spencer7593 why group by because he have just one idWHERE p.id = 72697
– echo_Me
Jul 1 '14 at 18:32
I need to query more then one post
– user1952811
Jul 1 '14 at 18:36
@user1952811 check edited answer.
– echo_Me
Jul 1 '14 at 18:37
add a comment |
There are several approaches.
Here's an example of one way to get the specified result, using correlated subqueries in the SELECT list:
SELECT p.post_title
, ( SELECT m1.meta_value
FROM wp_post_metadata m1
WHERE m1.meta_key = 'total_related'
AND m1.post_id = p.id
ORDER BY m1.meta_key LIMIT 1
) AS `total_related`
, ( SELECT m2.meta_value
FROM wp_post_metadata m2
WHERE m2.meta_key = 'updated'
AND m2.post_id = p.id
ORDER BY m2.meta_key LIMIT 1
) AS `updated`
, ( SELECT m3.meta_value
FROM wp_post_metadata m3
WHERE m3.meta_key = 'cricket'
AND m3.post_id = p.id
ORDER BY m3.meta_key LIMIT 1
) AS `cricket`
FROM wp_posts p
WHERE p.id IN (5,8)
There are several other approaches, each with its own advantages and drawbacks.
There's a somewhat related question I referenced in a comment on the question. That question illustrates several approaches, but omits a correlated subquery approach.)
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f24516710%2fselect-rows-as-columns-for-wordpress-post-meta%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
What about something like this?
SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
ON p.id = m3.post_id AND m3.meta_key = 'cricket'
And since you aren't looking for a specific post you should be able to do this.
If you want to query specific post_types
you can try something like this
SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
ON p.id = m3.post_id AND m3.meta_key = 'cricket'
WHERE p.post_type = 'my_custom_post_type';
Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!
– user1952811
Jul 1 '14 at 19:04
Worked for me.thanks +10
– Alive to Die
Dec 19 '16 at 20:19
add a comment |
What about something like this?
SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
ON p.id = m3.post_id AND m3.meta_key = 'cricket'
And since you aren't looking for a specific post you should be able to do this.
If you want to query specific post_types
you can try something like this
SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
ON p.id = m3.post_id AND m3.meta_key = 'cricket'
WHERE p.post_type = 'my_custom_post_type';
Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!
– user1952811
Jul 1 '14 at 19:04
Worked for me.thanks +10
– Alive to Die
Dec 19 '16 at 20:19
add a comment |
What about something like this?
SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
ON p.id = m3.post_id AND m3.meta_key = 'cricket'
And since you aren't looking for a specific post you should be able to do this.
If you want to query specific post_types
you can try something like this
SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
ON p.id = m3.post_id AND m3.meta_key = 'cricket'
WHERE p.post_type = 'my_custom_post_type';
What about something like this?
SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
ON p.id = m3.post_id AND m3.meta_key = 'cricket'
And since you aren't looking for a specific post you should be able to do this.
If you want to query specific post_types
you can try something like this
SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
ON p.id = m3.post_id AND m3.meta_key = 'cricket'
WHERE p.post_type = 'my_custom_post_type';
answered Jul 1 '14 at 18:42
MaazMaaz
2,29422145
2,29422145
Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!
– user1952811
Jul 1 '14 at 19:04
Worked for me.thanks +10
– Alive to Die
Dec 19 '16 at 20:19
add a comment |
Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!
– user1952811
Jul 1 '14 at 19:04
Worked for me.thanks +10
– Alive to Die
Dec 19 '16 at 20:19
Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!
– user1952811
Jul 1 '14 at 19:04
Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!
– user1952811
Jul 1 '14 at 19:04
Worked for me.thanks +10
– Alive to Die
Dec 19 '16 at 20:19
Worked for me.thanks +10
– Alive to Die
Dec 19 '16 at 20:19
add a comment |
Try that:
select post_title ,
MAX(CASE WHEN `meta_key`='total_related' THEN meta_value END)as 'total_related',
MAX(CASE WHEN `meta_key` = 'updated' THEN meta_value END) as 'updated' ,
MAX(CASE WHEN `meta_key` = 'cricket' THEN meta_value END) as 'cricket'
FROM wp_posts p
JOIN wp_postmeta m ON p.id = m.post_id
GROUP BY p.id
@spencer7593 why group by because he have just one idWHERE p.id = 72697
– echo_Me
Jul 1 '14 at 18:32
I need to query more then one post
– user1952811
Jul 1 '14 at 18:36
@user1952811 check edited answer.
– echo_Me
Jul 1 '14 at 18:37
add a comment |
Try that:
select post_title ,
MAX(CASE WHEN `meta_key`='total_related' THEN meta_value END)as 'total_related',
MAX(CASE WHEN `meta_key` = 'updated' THEN meta_value END) as 'updated' ,
MAX(CASE WHEN `meta_key` = 'cricket' THEN meta_value END) as 'cricket'
FROM wp_posts p
JOIN wp_postmeta m ON p.id = m.post_id
GROUP BY p.id
@spencer7593 why group by because he have just one idWHERE p.id = 72697
– echo_Me
Jul 1 '14 at 18:32
I need to query more then one post
– user1952811
Jul 1 '14 at 18:36
@user1952811 check edited answer.
– echo_Me
Jul 1 '14 at 18:37
add a comment |
Try that:
select post_title ,
MAX(CASE WHEN `meta_key`='total_related' THEN meta_value END)as 'total_related',
MAX(CASE WHEN `meta_key` = 'updated' THEN meta_value END) as 'updated' ,
MAX(CASE WHEN `meta_key` = 'cricket' THEN meta_value END) as 'cricket'
FROM wp_posts p
JOIN wp_postmeta m ON p.id = m.post_id
GROUP BY p.id
Try that:
select post_title ,
MAX(CASE WHEN `meta_key`='total_related' THEN meta_value END)as 'total_related',
MAX(CASE WHEN `meta_key` = 'updated' THEN meta_value END) as 'updated' ,
MAX(CASE WHEN `meta_key` = 'cricket' THEN meta_value END) as 'cricket'
FROM wp_posts p
JOIN wp_postmeta m ON p.id = m.post_id
GROUP BY p.id
edited Jul 1 '14 at 18:37
answered Jul 1 '14 at 18:27
echo_Meecho_Me
32.9k54170
32.9k54170
@spencer7593 why group by because he have just one idWHERE p.id = 72697
– echo_Me
Jul 1 '14 at 18:32
I need to query more then one post
– user1952811
Jul 1 '14 at 18:36
@user1952811 check edited answer.
– echo_Me
Jul 1 '14 at 18:37
add a comment |
@spencer7593 why group by because he have just one idWHERE p.id = 72697
– echo_Me
Jul 1 '14 at 18:32
I need to query more then one post
– user1952811
Jul 1 '14 at 18:36
@user1952811 check edited answer.
– echo_Me
Jul 1 '14 at 18:37
@spencer7593 why group by because he have just one id
WHERE p.id = 72697
– echo_Me
Jul 1 '14 at 18:32
@spencer7593 why group by because he have just one id
WHERE p.id = 72697
– echo_Me
Jul 1 '14 at 18:32
I need to query more then one post
– user1952811
Jul 1 '14 at 18:36
I need to query more then one post
– user1952811
Jul 1 '14 at 18:36
@user1952811 check edited answer.
– echo_Me
Jul 1 '14 at 18:37
@user1952811 check edited answer.
– echo_Me
Jul 1 '14 at 18:37
add a comment |
There are several approaches.
Here's an example of one way to get the specified result, using correlated subqueries in the SELECT list:
SELECT p.post_title
, ( SELECT m1.meta_value
FROM wp_post_metadata m1
WHERE m1.meta_key = 'total_related'
AND m1.post_id = p.id
ORDER BY m1.meta_key LIMIT 1
) AS `total_related`
, ( SELECT m2.meta_value
FROM wp_post_metadata m2
WHERE m2.meta_key = 'updated'
AND m2.post_id = p.id
ORDER BY m2.meta_key LIMIT 1
) AS `updated`
, ( SELECT m3.meta_value
FROM wp_post_metadata m3
WHERE m3.meta_key = 'cricket'
AND m3.post_id = p.id
ORDER BY m3.meta_key LIMIT 1
) AS `cricket`
FROM wp_posts p
WHERE p.id IN (5,8)
There are several other approaches, each with its own advantages and drawbacks.
There's a somewhat related question I referenced in a comment on the question. That question illustrates several approaches, but omits a correlated subquery approach.)
add a comment |
There are several approaches.
Here's an example of one way to get the specified result, using correlated subqueries in the SELECT list:
SELECT p.post_title
, ( SELECT m1.meta_value
FROM wp_post_metadata m1
WHERE m1.meta_key = 'total_related'
AND m1.post_id = p.id
ORDER BY m1.meta_key LIMIT 1
) AS `total_related`
, ( SELECT m2.meta_value
FROM wp_post_metadata m2
WHERE m2.meta_key = 'updated'
AND m2.post_id = p.id
ORDER BY m2.meta_key LIMIT 1
) AS `updated`
, ( SELECT m3.meta_value
FROM wp_post_metadata m3
WHERE m3.meta_key = 'cricket'
AND m3.post_id = p.id
ORDER BY m3.meta_key LIMIT 1
) AS `cricket`
FROM wp_posts p
WHERE p.id IN (5,8)
There are several other approaches, each with its own advantages and drawbacks.
There's a somewhat related question I referenced in a comment on the question. That question illustrates several approaches, but omits a correlated subquery approach.)
add a comment |
There are several approaches.
Here's an example of one way to get the specified result, using correlated subqueries in the SELECT list:
SELECT p.post_title
, ( SELECT m1.meta_value
FROM wp_post_metadata m1
WHERE m1.meta_key = 'total_related'
AND m1.post_id = p.id
ORDER BY m1.meta_key LIMIT 1
) AS `total_related`
, ( SELECT m2.meta_value
FROM wp_post_metadata m2
WHERE m2.meta_key = 'updated'
AND m2.post_id = p.id
ORDER BY m2.meta_key LIMIT 1
) AS `updated`
, ( SELECT m3.meta_value
FROM wp_post_metadata m3
WHERE m3.meta_key = 'cricket'
AND m3.post_id = p.id
ORDER BY m3.meta_key LIMIT 1
) AS `cricket`
FROM wp_posts p
WHERE p.id IN (5,8)
There are several other approaches, each with its own advantages and drawbacks.
There's a somewhat related question I referenced in a comment on the question. That question illustrates several approaches, but omits a correlated subquery approach.)
There are several approaches.
Here's an example of one way to get the specified result, using correlated subqueries in the SELECT list:
SELECT p.post_title
, ( SELECT m1.meta_value
FROM wp_post_metadata m1
WHERE m1.meta_key = 'total_related'
AND m1.post_id = p.id
ORDER BY m1.meta_key LIMIT 1
) AS `total_related`
, ( SELECT m2.meta_value
FROM wp_post_metadata m2
WHERE m2.meta_key = 'updated'
AND m2.post_id = p.id
ORDER BY m2.meta_key LIMIT 1
) AS `updated`
, ( SELECT m3.meta_value
FROM wp_post_metadata m3
WHERE m3.meta_key = 'cricket'
AND m3.post_id = p.id
ORDER BY m3.meta_key LIMIT 1
) AS `cricket`
FROM wp_posts p
WHERE p.id IN (5,8)
There are several other approaches, each with its own advantages and drawbacks.
There's a somewhat related question I referenced in a comment on the question. That question illustrates several approaches, but omits a correlated subquery approach.)
edited Jul 1 '14 at 18:41
answered Jul 1 '14 at 18:26
spencer7593spencer7593
86k118197
86k118197
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f24516710%2fselect-rows-as-columns-for-wordpress-post-meta%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
sample data and wished result will be better .
– echo_Me
Jul 1 '14 at 18:12
@echo_Me added.
– user1952811
Jul 1 '14 at 18:18
This question has been asked and answered several times. There are several approaches, each with advantages and drawbacks. The general problem you are addressing is the EAV model. A brief example of several approaches can be found here: http://stackoverflow.com/questions/8764290/what-is-best-performance-for-retrieving-mysql-eav-results-as-relational-table
– spencer7593
Jul 1 '14 at 18:18