Arcanius Style Recent Comments Explained

Album Cover: Echoes, Silence, Patience and Grace

"Time will turn us into statues, eventually."
Foo Fighters / Statues

Posted on February 20, 2006 11:00 PM in Web Development
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

Back in December, I promised I'd explain how the recent comments list that appears in my blog's subheader was implemented if anyone asked. Well, the guy who inspired them asked, so I'd better follow through.

I spent a lot of time trying to come up with an SQL query that would give me the list in a format that would be easy to work with, but after struggling with duplicates and the GROUP BY clause for way too long, I came to the conclusion that SQL just wasn't going to do it for me (at least the SQL that MySQL supports).

Once I had come to that realization, I still needed to get a little imaginative about how I would gather the list of entries that had most recently been commented on. If I was just trying to enumerate the latest comments, it would be a piece of cake because it would involve only the comments table in my database. However, I needed to be able to access information about the blog entries so I could link to them along with the title. Therefore, I needed to include my database's blog table as well.

I wanted to be sure that the functionality I was implementing was flexible in case my design allowed for more (or required fewer) items in the list at some point in the future. So I started off by writing a PHP function that would take as an argument the number of entries to be listed. Right now, I call that function like so from my subheader:

recent_comments(3);

The first thing I do in the recent_comments($n) function is select the n+1th most recently commented on entry so I have a boundary to work with. That query looks something like:

SELECT MAX(comments.id) AS cid, blogid, MAX(commentdate) AS m FROM blog, comments WHERE blog.id = comments.blogid GROUP BY blogid ORDER BY m DESC LIMIT 3,1

Once I have that n+1th entry (in this case, the 4th), I can then turn right around and use its id to grab all the comments and associated blog entries that came after it. This could return a list of n comments or a very large list depending on the popularity of the most recently commented on entries, so the next step is to loop through the results and store the n unique ids in an associative array.

Once I have an associative array with the n unique entry ids as its keys, I can reiterate through the results and increment the counter of each key. So if The Difference Between Gray and Grey has been commented on twice, when I'm done iterating the following will be true:

$titles[232] = 2

Now, all I have left to do is to grab the n most recently commented on entries using a query that should look semi-familiar:

SELECT MAX(comments.id) AS cid, blogid, title, MAX(commentdate) AS m FROM blog, comments WHERE blog.id = comments.blogid GROUP BY blogid ORDER BY m DESC LIMIT 3

That gives me the list I need, but without the count. But since I have the count saved in the $titles array, I can reference that when I'm building my output.

And that, my friends, is how Arcanius Style Recent Comments came to be.

Clear as mud?

Comments

control panel in windows 10 on January 11, 2018 at 10:50 PM:

Open control panel windows 10 has listed all the necessary points and mention all the steps and tricks to find the control panel.

Permalink

Amazing Post! It is really so useful and helpful post. Thanks for share.

Permalink

freeregistrationkey on February 11, 2018 at 9:44 AM:

Always a good job right here

Permalink

Post Comments

If you feel like commenting on the above item, use the form below. Your email address will be used for personal contact reasons only, and will not be shown on this website.

Name:

Email Address:

Website:

Comments:

Check this box if you hate spam.