How to get table rows wise records as column wise?

Votes : 3.3
559
I'm work with wordpress cms. In post meta table,
id post_id meta_key meta_value
1 1 like_count 20
2 1 view_count 40
3 1 downloads 10
4 2 like_count 5
5 2 view_count 14
6 3 answer 5
7 3 view_count 36
All post metas have been recorded as row wise. Sometimes filtering, searching records very difficult. it's much better that all post meta records can get as column wise. like,
post_id likes views downloads
1 20 40 10

Your Answer

1 Answer

answered
In your case, post_id is like foreign_key. In my case, It should be a unique column.
  1. post_id { 1 } : has three meta keys, but it has not answer meta key.
  2. post_id { 2 } : has two meta keys, but downloads or answer meta keys not in there.
  3. post_id { 3 } : has two meta keys, but like_count or downloads not in there.
if any post_id has meta key, can get row meta values. But if it has not meta_key, cannot get row meta value. Then these records will be null. And your result only have 3 meta keys and give them like nick names, I think,
  • likes - like_count meta key
  • views - view_count meta key
  • downloads - is not different.
How can we do this, "SELECT post_id,likes FROM (SELECT post_id,meta_value as likes FROM post_meta where meta_key='like_count') as x;" You can get all records witch they have like_count meta_key in this "(SELECT post_id,meta_value as likes FROM post_meta where meta_key='like_count')" mysql query. then assign to "x" as mysql from parameter. Then get result from x. Now you can get like_count as column wise. Finally records are like this,
post_id likes
1 20
2 5
Then we need get other records. "SELECT post_id,likes,views,downloads FROM ( SELECT post_id,meta_value as likes FROM post_meta where meta_key='like_count' ) as x JOIN ( SELECT post_id,meta_value as views FROM post_meta where meta_key='view_count' ) as y ON x.user_id = y.user_id JOIN ( SELECT post_id,meta_value as downloads FROM post_meta where meta_key='downloads' ) as z ON z.user_id = y.user_id;" Get view_count and downloads records like previous one. then assign to y and z. then join x,y,z. Each result match by user_id. Finally get result as you wish.
post_id likes views downloads
1 20 40 10
2 5 14 null
2 36 null null

This example is explain for one table, as well as you can join different tables.

What is the codrate ?

codrate.com is a standard, fast cross browsing and highly versatile site. It is useful for many large number of Program Development Industries. So you can get support form Codrators , who are the codrate's joiners around world to help your program developments, You can answer other codrator's questions. Communicate with them. Share your knowledge with them. Do you have an interest in programming, So publish your articles about programming. It will help to maintain your professional co-profile. Actually codrate.com is not such as a regular web site. It will be gave new experience, best narrow cross-browser view, reduce processing time to receive browsing request, it's mean do not wasting your time to browsing codrate's web pages because it has been upgrade always modern coding ways. So, what do you waiting for ?. Try your own.

Copyright 2015 Pride - Company. Design by Esila