Home | About Chris Hope | Contact Chris Hope | Search:

MySQL queries for article summaries part 2 of 2

Posted January 6th, 2009 in MySql

On Saturday I posted a summary of post information for 2008 and on Sunday shared the MySQL queries to get some of the information. This post is part 2, showing the queries to get the remaining information.

Summary by section

The database tables for content behind this blog website have a content table and then a "tree" table which relates child posts with parent posts. The tree is actually only one level deep (an example "parent" is the PHP section and all the posts under that are "children" of the PHP post). I created the database back in 2003 and I guess I must have been intending having the ability to have multiple parents. Anyway...

To work out a count of posts per category, I needed to join the content table to the tree and then back to the content table again. The cc alias in the SQL below is for "category child" and cp for "category parent".

The SQL below gets the parent title and a count of posts belonging to that parent, specifying the date range of posts between 2008-01-01 and 2009-01-01 (see part 1 about the format of the date_created field).

SELECT cp.title, COUNT(*)
FROM content cc
INNER JOIN tree t ON cc.content_id = t.child_id
INNER JOIN content cp ON cp.content_id = t.parent_id
WHERE cc.date_created >= '2008-01-01'
AND cc.date_created < '2009-01-01'
GROUP BY cp.title

Complete list of posts, formatted with concatenate

Finally in the summary post, I listed all the posts for 2008 group by category. Again I had to do the same sort of join as the above table with the same date range selection. The fields selected were the parent's title (so I would know which section they belonged to) and then a concatenated field of HTML which I could simply copy and paste into the HTML source view of the content field in my content editor.

SELECT cp.title, 
  CAST(
    CONCAT('<li><a href="', cc.fullurl, '">', cc.title, '</a> (', 
      DATE_FORMAT(cc.date_created, '%d %b %Y'),
    ')</li>')
  AS CHAR)
FROM content cc
INNER JOIN tree t ON cc.content_id = t.child_id
INNER JOIN content cp ON cp.content_id = t.parent_id
WHERE cc.date_created >= '2008-01-01' 
AND cc.date_created < '2009-01-01'
ORDER BY cp.title, cc.date_created

I needed to do a CAST(... AS CHAR) because for some reason phpMyAdmin decided it was a BLOB field type when it displayed and wouldn't render it. I guess on export it might have rendered it as text but I needed to preview it in the browser.

The DATE_FORMAT converted the YYYY-MM-D HH:MM:SS datetime format into a format like e.g. 12 Dec 2008.

I then exported the data from phpMyAdmin using a tab separator for fields, opened it in Excel so I could see the category names in one column and the HTML in another, and simply copied and pasted the HTML columns into sections in the summary post.

My monthly roundup will now contain this data so I'll need to use the above SQL query in combination with a PHP script to auto-generate the data for the post. That will save time when I do each post :)

Comments

blog comments powered by Disqus