The Wonders of SQL and the mySQL database - Tue, 29 Jul 2008

I’m just finishing up the last few changes to the Forum and Blog and they should be incorporated into the live versions very soon.

Over the past few weeks, my customization (maybe I’m overdoing the customization a bit, but there’s no use in going back now) has led me to write some new Forum pages. In particular, to develop my Search and Userlist pages, I’ve needed to delve into the world of mySQL databases and the SQL (Structured Query Language) that is used to access data from them.

I’ve worked with many different databases in the past, but have never previously had the opportunity with any of them to use more than simple requests, similar to “SELECT * FROM table1 WHERE id = 10″. But that’s all different now, as I’ve had to research and test and try all sorts of various and complex requests to get things to work. Here’s one example:

$userlist = $bbdb->get_results(”SELECT id, user_login, user_nicename, user_url, user_registered, SUBSTRING_INDEX(display_name, ‘/’, 1) as country, SUBSTRING_INDEX(display_name, ‘/’, -1) as user_ip, (SELECT COUNT(post_id) FROM bb_posts WHERE id = poster_id AND post_status = 0) as numposts, (SELECT post_time FROM bb_posts WHERE id = poster_id AND post_status = 0 GROUP BY post_id DESC LIMIT 1) AS post_time, (SELECT COUNT(comment_id) FROM wp_comments WHERE id = user_id AND comment_approved = 1) as numcomments, (SELECT MAX(comment_date_gmt) FROM wp_comments WHERE id = user_id AND comment_approved = 1) as maxcomment FROM wp_users WHERE user_status = 0 GROUP BY id ORDER BY numposts + numcomments DESC”);

Even this one is relatively simple, since it doesn’t even get into the complexity involved in joining databases (and that’s another matter with LEFT joins, RIGHT joins, INNER joins, OUTER joins, CROSS joins, STRAIGHT join - you name it!). And the mySQL documentation is excellent. They even tell you how to best optimize your requests to be as fast as possible.

My conclusion … Well, I am extremely impressed by the SQL language and its abilities. Once you get used to it, it is very nice to work with.

Sun just recently purchased mySQL for a billion dollars. I think they got a good deal.