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 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