Posted by on July 8, 2011

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 ,b.term_taxonomy_id,c.term_id 
from wp_posts a, wp_term_relationships b,
wp_term_taxonomy c,wp_terms d
where = b.object_id and b.term_taxonomy_id = c.term_taxonomy_id
and c.term_id = d.term_id


Be the first to comment.

Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>