Turning a Wordpress post_meta table into an easier to use View

Question

I'm using Wordpress and trying to create a view to make reviewing and analyzing my resort data a little easier. Resort data is stored in the post_meta table in Wordpress and referenced in a custom post type known as "resorts". The following query gives me the result set I want to parse:

SELECT a.id, a.post_type, a.post_title, b.post_id, b.meta_key, b.meta_value
FROM alpinezone_postmeta b
INNER JOIN alpinezone_posts a ON a.id = b.post_id
WHERE a.post_type = "resorts" 

What I want to do with this result set is have each unique meta_key of a set I define become a column and then each row should be a unique b.post_id (or a.id), which corresponds to an individual resort's record.

So ultimately I end up with:

post_title |   phone_num      | state         |
resort1    |   800-200-1111   | Vermont       |
resort2    |   800-200-2222   | New Hampshire |
resort 3   |   800-200-2323   | Maine         |

Basically ...... I'm not that great at MySQL so trying to figure out the best way to handle this. I do have a list of all the meta_key I want to place into columns, there are 36 of them capturing a range of information.

EDIT: Some more detail.

Current Structure - shows what table it comes from as well

*alpinezone_posts   alpinezone_postmeta alpinezone_postmeta*
post_title          meta_key            meta_value
----------------------------------------------------------------
sugarloaf           snow_phone          888-234-2222
sugarloaf           vertical_feet       2300
sugarloaf           site_url            sugarloaf.com
wachusett           snow_phone          888-111-2222
wachusett           vertical_feet       1000
wachusett           site_url            wachusett.com

These two tables are joined on post_id from table alpinezone_postmeta and id from table alpinezone_posts.

Only want results where the post_type in table alpinezone_posts is = "resorts"

How I want it to look in new view

post_title  snow_phone      vertical_feet   site_url
-------------------------------------------------------
sugarloaf   888-234-2222    2300            sugarloaf.com
wachusett   888-111-2222    1000            wachusett.com

Answer 1

I understand you have one post for each resort, each with a more or less complete set of metadata. If that's correct, then here's how you can build a query that produces the table you describe. It's not really elegant, but it works without using another programming language or Excel (Excel might be able to make what you want from an export of the post_meta table, but my Excel-fu is not good enough...)

SELECT posts.title,
    m1.meta_value as <your meta key 1>,
    m2.meta_value as <your meta key 2>,
    m3.meta_value as <your meta key 3>,
    ...
FROM alpinezone_posts
    LEFT OUTER JOIN alpinezone_postmeta AS m1 ON alpinezone_posts.id = m1.post_id,
    LEFT OUTER JOIN alpinezone_postmeta AS m2 ON alpinezone_posts.id = m2.post_id,
    LEFT OUTER JOIN alpinezone_postmeta AS m3 ON alpinezone_posts.id = m3.post_id,
    ...
WHERE 
    m1.meta_key = <your meta key 1>
    AND m2.meta_key = <your meta key 2>
    AND m3.meta_key = <your meta key 3>
    ...

Using LEFT OUTER JOIN instead of INNER JOIN makes sure you get a result row for each resort even if you don't have a value for every meta_key.

If you can use PHP and an HTML table would help you, you could also loop all posts and use the get_post_custom() function.

Answer 2

Here's a stored procedure I wrote that takes a given post type and pivots all related postmeta data. You don't need to know the meta keys, it will figure them all out for you.

This isn't the fastest query, and we use it only for migration and deep dives into data, but it does the job.

Note that this temporarily resets the max length of the concat function so you can build a large SQL statement:

CREATE PROCEDURE `wp_posts_pivot`(IN post_type_filter varchar(50))
BEGIN

/* allow longer concat */
declare max_len_original INT default 0;
set max_len_original = @@group_concat_max_len;
set @@group_concat_max_len=100000;

SET @sql = NULL;
SELECT 
    GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(pm.meta_key = ''',
                meta_key,
                ''', pm.meta_value, NULL)) AS `',
                meta_key,
                '`'))
INTO @sql FROM
    wp_posts p
        INNER JOIN
    wp_postmeta AS pm ON p.id = pm.post_id
WHERE
    p.post_type = post_type_filter;

SET @sql = CONCAT('SELECT p.id
                    , p.post_title
                    , ', @sql, ' 
                   FROM wp_posts p
                   LEFT JOIN wp_postmeta AS pm 
                    ON p.id = pm.post_id
                    where p.post_type=\'',post_type_filter,'\'
                   GROUP BY p.id, p.post_title');

/* reset the default concat */
set @@group_concat_max_len= max_len_original;

/*
select @sql;
*/

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END

You can then call this with a simple call such as this one, which will select a single row for each 'page' post type along with all meta values:

call wp_posts_pivot('page');