Mysql Articles

An addition to my website is the top commenters page.  Even though the page doesn't look that complex, there is still a bit going on behind the scenes.

To get the top commenter count I have to thank Ryan Peterson in helping me write this custom MySQL query.  I used Group By to lump the results together based on the commenter's email address.  Then use count(*) to count the number of records in the group. Also used the NOT function in MySQL to filter my email address.

Mysql:

SELECT `Comment`.`author`, `Comment`.`id`, `Comment`.`url`, count(*) AS `count` FROM `cake_comments` AS `Comment` WHERE 1 = 1 AND NOT(`Comment`.`email`=\'m@marcgrabanski.com\') GROUP BY `Comment`.`email` ORDER BY `count` DESC LIMIT 0, 10

Since I didn't want to load all of the related comments at once, I decied to use a little jQuery and Ajax to show comments that they have made.

First, I put a span tag around the comment count, because without JavaScript you won't see this functionality. On page load I swapped the spans into links with $(this).replaceWith('<a>' + $(this).html() + '</a>');

Instead adding behavior later after append, I used a jQuery object inside replaceWith  so I can attach behavior to the link and I like how the code looks.

JavaScript:

$(this).replaceWith(
    $('<a>' + $(this).html() + '</a>').click(function(){
        //code here
    })
);

Using CakePHP's JavaScript object generator, $javascript->object($data); it was easy to send JSON back to the client and parse with jQuery. Here is the full source of the JavaScript file.

JavaScript:

$(document).ready(function(){
    $('.get_comments').each(function(){
        $(this).replaceWith( 
            $('<a>'+$(this).html()+'</a>').click(function(){
                link = $(this);
                $.post('comments/get/comments', {
                    'data[Comment][id]': $(this).siblings('.author').attr('id')
                }, function(data){
                    out = '';
                    for (i in data) {
                        prefix = data[i].Article.type ? 'article/' : 'answers/';
                        out += '<li><a href="' + prefix + data[i].Article.slug + '#c' + data[i].Comment.id + '">' + data[i].Article.title + '</a>' + 
                            data[i].Comment.created + '</li>';
                    }
                    $('<ol>' + out + '</ol>').hide().appendTo(link.parents('li:first')).slideDown();
                    $(link).replaceWith( $(link).html() );
                }, 'json');
            })
        );
    });
});

Update: I think I'll post the CakePHP code just in case someone is interested. Here is the controller, I use the RequestHandler component var $components = array('RequestHandler'); and the Time helper var $helpers = array('Time'); in the top of the controller.

PHP:

function get($type = null) 
{
    if ($this->RequestHandler->isAjax()) {
        Configure::write('debug', 0);
        if ($type == 'comments') {
            $comment = $this->Comment->read(array('Comment.email'), $this->data['Comment']['id']);
            $results = $this->Comment->find('all', array(
                'conditions' => array('email' => $comment['Comment']['email']),
                'fields' => 'Article.title, Article.slug, Article.type, Comment.id, Comment.created'
            ));
            $this->set(compact('results'));
        }
    }
}

I also turn debug off with Configure::write('debug', 0);. Also, I only use $type  so that I can setup my code to get types of data if I want later - more of a design pattern I typically follow.

Then in my view I use the time helper and output a JSON object.

PHP:

<?php
if ($result):
        if (isset($result['Comment']['created'])):
            $results[$key]['Comment']['created'] =
                $time->timeAgoInWords($result['Comment']['created']); 
        endif;
    endforeach;
    echo  $javascript->object($results); 
?>

To see in action, click the comments count next to someone's name on the top commenters page.

Note on June 23, 2008: The post you are reading is years old, but shows the trail of the beginnings of MarcGrabanski.com. Thank you for reading!

Without much prior PHP knowledge, I managed to program this blog in less than a week. Here is how I did it. Note: This is an overview and not actual code examples.

1. First, I went through Code Grrl's Build a Blog Tutorials

   Sure, I could have stopped there but I really didn't like how the blog was programmed. I like this tutorial though because I dug through the code and learned basic PHP, but I wanted much more functionality.

2. I wanted clean URLs so I found Making "clean" URLs with Apache

   I customized the url so I can add keywords inside it if I want. I love these clean URLs. Apache also has documentation on mod_rewrite.

3. Then I added user authentication

   This was the most basic, yet effective login script I found. Of course I modded it to make it a little more secure.

4. I rewrote the code to use Object Oriented PHP

   My blog was basically built but I didn't like how the code looked. I thought PHP looked horrible until I ran into some object oriented code. Makes things look much nicer.

5. Created Tag Cloud Navigation

   I started with ByteMyCode's Tag Cloud code and then moved to selecting from the database with the "LIKE" MySQL commmand.

   Mysql:

   $sql = "SELECT * FROM php_blog WHERE tags LIKE '%$tag%' ORDER BY timestamp DESC";

   I also had to count the tags so I used the array_count_values PHP function.

   PHP:

   $tags = array_count_values($DBtags); // a very useful function to count the tags!

   Wow gotta love those tags!

6. Added More Features

   I added an image uploader to my admin page, RSS 2.0 and many minor tweaks until I had my very own customized blog built from scratch!

Some functions I found very useful on php.net:

PHP:

list()
split()
htmlspecialchars()
str_replace()
explode()
include(dirname(__FILE__)

I now have my own custom blog! You can too! I heavily modified each example and made them my own. It feels so nice to have a custom blog!