User to Blog Association in Movable Type

Published on by Pavel Ushakov in Blog Services, Movable Type category.

Today, while working with one of our corporate customers I was asked to provide a report that lists all their Movable Type 4 users and the blogs those users are associated with.

Unfortunately Movable Type 4 doesn’t provide any type of reporting functionality, especially in the form of CVS file that can be imported into Excel for further manipulation.

So the solution was to use phpMyAdmin that comes pre-installed with most blog hosting accounts, and create a query that would display exactly what we need. After about 30 minutes of studying Movable Type 4 tables I was able to come up with the following query that does exactly what we need. Since I wasn’t able to find anything like that on Google, I decided to make the code available to anyone with a large Movable Type installation, looking to see users to blogs association.


SELECT
`mt_author`.`author_name`,
`mt_author`.`author_nickname`,
`mt_author`.`author_email`,
`mt_blog`.`blog_name`,
`mt_blog`.`blog_site_url`
FROM
mt_author, mt_blog, mt_association
WHERE
((`mt_author`.`author_status` = 1)
AND
(`mt_author`.`author_auth_type` = 'MT')
AND
(`mt_author`.`author_id` = `mt_association`.`association_author_id`)
AND
(`mt_blog`.`blog_id` = `mt_association`.`association_blog_id`))
ORDER BY author_name;

I hope this solves someone’s problem!