adminer1

Reporting In WordPress

I have been looking for decent plugins to setup some reporting on my wordpress sites for a while. My search is now over I have found the

best reporting plugins for wordpress

They are Adminer and Exports and reports
Adminer gives a way of quickly viewing and running sql scripts on wordpress tables within wordpress. Exports and Reports gives a way of generating reports from those scripts within wordpress. I have not found anything else like them. I was using them to setup some reports and that was when  I came up with my first problem with queries on wordpress tables it was

How to run a SQL Query to show the post details with the category information.

I was trying to join the wp_post table to the wp_term_taxonomy table so I could find
the categories that match the posts. It took me a while. Below is a diagram that shows how the category information is organized in

the category id is not in the wp_posts table in wordpress

The code I wrote to do the join is below
select a.id ,b.term_taxonomy_id,c.term_id 
from wp_posts a, wp_term_relationships b,
wp_term_taxonomy c,wp_terms d
where a.id = b.object_id and b.term_taxonomy_id = c.term_taxonomy_id
and c.term_id = d.term_id