SQL-Fu
December 14th, 2006 by Jake Scaltreto
When I first started this site, it was suggested that I add stories posted here to the 3n22.com RSS feed. I put it off and put it off until I got bored last night and decided to finally do something about it. The RSS feed for 3n22.com is generated from the MySQL database that's backing up phpBB. This site - which is using the WordPress CMS - also uses MySQL as the database server, but the two databases are completely separate.It was my original thought that I could query both databases separately, store the data in a class, reorder it reverse chronologically and then output it in XML. Well, the more I thought about it, the less appealing this idea sounded. It would mean a complete rewrite of the RSS script, plus it would mean a lot more overhead having to query twice and then do some type of sort function after the fact. I resolved to come up with a way to do everything in one SQL statement.
I'm a novice at PHP; perl is my language of choice. However, since the RSS script was written in PHP, I had little choice but to work with it. It was no problem, though, the little PHP I do know was enough to get me through the project.
The big challenge was going to be forming the SQL statement. Yes, I've done some SQL before - jBlogg uses SQL (obviously) not to mention all the stuff I had to do when converting over the old flat-file database jBlogg to the new one - but, for me, this would be the mother of all SQL statements. What I needed to do was query two separate databases containing different field names and return a single set of regular data.
Some browsing through the MySQL manual revealed exactly the function I would need: UNION. UNION allows you to combine two SELECT statements into a single output - provided the two have the same number of columns returned. I built a SQL statement that would make your mother proud.
Now that I had a way to actually grab the data, I needed to solve the problem of arranging the rows reverse chronologically. Ordinarily you could just do something along the lines of ORDER BY date DESC, but it soon became clear that that would not work. It turns out that phpBB stores its dates as integer POSIX time and WordPress stores it as a DATE data type. I can only assume that the phpBB folks went this way for compatibility with older databases that don't support the DATE data type. Who knows. In any case, some more searching through the MySQL manual revealed a nifty little function called FROM_UNIXTIME(). This function converts POSIX time into a format compatible with the DATE data type. This was the last key I needed and my ultimate SQL statement was born. Behold it, in all its glory:
SELECT
f.forum_name, t.topic_title, u.user_id,
u.username, u.user_sig, u.user_sig_bbcode_uid,
p.post_id, pt.post_text, pt.post_subject,
pt.bbcode_uid, FROM_UNIXTIME(p.post_time) AS post_time,
t.topic_replies, t.topic_first_post_id
FROM
phpbb_forums AS f,
phpbb_topics AS t,
phpbb_users AS u,
phpbb_posts AS p,
phpbb_posts_text AS pt
WHERE
t.forum_id = f.forum_id
AND p.poster_id = u.user_id
AND pt.post_id = p.post_id
AND p.topic_id = t.topic_id
UNION
SELECT
'jscom', wp.post_title, wp.post_author,
wu.user_login, '', 0, wp.ID, NULL ,
wp.post_title, 0, wp.post_date AS post_time, 0, 0
FROM
wordpress.wp_posts AS wp,
wordpress.wp_users AS wu
WHERE
wp.post_status = 'publish'
AND wp.post_author = wu.ID
ORDER BY post_time DESC LIMIT 15;
I tried to tab it, but its not working so hot, so you'll just have to use your imagination.
With that SQL query and a few other tweaks for formatting, the RSS was fully integrated. Now I need to find another project to mess around with. Be seeing you.
- Posted in Linux