MySQL is driving me nuts. I’ve been requested for a csv file of WordPress data. The posts have a bunch of meta entries and for the most part, I have things coming out ok. But I’m trying to connect user id’s in the Other Authors to their display names (See below). The difficulty I’m having is trying to output each display name in that column.
Tracking Number | Post Title | Post Date | Primary Author | Other Authors
1153 | Title of Post | 2013/03/06 | Tom Smith | 213, 100, 600
And here’s my query:
SELECT results.ID as ID, results.post_title as post_title, results.post_date
as post_date, results.post_content as post_content, u.display_name as
primary_author, results.other_authors as other_authors
FROM (SELECT m.meta_value as ID,p.post_title as post_title, p.post_date as
post_date,p.post_content as post_content,m2.meta_value as primary_author,
m3.meta_value as other_authors
FROM wp_posts p
INNER JOIN wp_postmeta m ON (p.ID = m.post_id)
INNER JOIN wp_postmeta m2 ON (p.ID = m2.post_id)
INNER JOIN wp_postmeta m3 ON (p.ID = m3.post_id)
WHERE p.post_type='post' AND p.post_status NOT IN ('trash') AND
(m.meta_key='idea_tracking_num' AND m.meta_value != '')
AND m2.meta_key='primary_author' AND m3.meta_key='other_authors'
ORDER BY p.post_date
DESC) as results
INNER JOIN wp_users u on (u.ID = results.primary_author)
INNER JOIN wp_users u2 on (u2.ID = results.other_authors)
I’ve also tried this, but it just outputs the first one:
SELECT results.ID as ID, results.post_title as post_title, results.post_date
as post_date, results.post_content as post_content, u.display_name as
primary_author, u2.display_name as other_authors
FROM (SELECT m.meta_value as ID,p.post_title as post_title, p.post_date as
post_date,p.post_content as post_content,m2.meta_value as primary_author,
m3.meta_value as other_authors
FROM wp_posts p
INNER JOIN wp_postmeta m ON (p.ID = m.post_id)
INNER JOIN wp_postmeta m2 ON (p.ID = m2.post_id)
INNER JOIN wp_postmeta m3 ON (p.ID = m3.post_id)
WHERE p.post_type='post' AND p.post_status NOT IN ('trash') AND
(m.meta_key='idea_tracking_num' AND m.meta_value != '')
AND m2.meta_key='primary_author' AND m3.meta_key='other_authors'
ORDER BY p.post_date
DESC) as results
INNER JOIN wp_users u on (u.ID = results.primary_author)
INNER JOIN wp_users u2 on (u2.ID = results.other_authors)
