Creating a PHP/MySQL/JSON Comment System

December 31st,2009 by Allan Bogh

See my latest article with complete instructions and code here.

For the Open Code Project I needed a comment system. I had been considering different ways to make a comment system, such as simple comments with no replies, advanced comments with replies, and including AJAX/JSON to refresh the comment when one had been submitted. When considering these options it's best to start simple, with something that you can expand upon.

I downloaded the "Advanced Comment System" from HotScripts (publisher site here) because it was a simple comment system which used PHP and MySQL and included an installer for the MySQL database. There's a downside to using someone else's scripts and that's having to deal with their code and ideas of how something should look. I wanted a little more information in my database than what is offered, and the ACS administration webpage is hacked together, but it's a good starting point for something simple.

To begin, I uploaded the ACS files once I modified the config.php file to match my database settings. I then browsed to the install.php file. This set up the database table, called "advanced_comment_system". The table contained 5 columns: id, date_inserted, page, username, and message. This table is very minimalistic when you consider the necessary security items that need to be in place for comment systems today.

To make a semi-secure comment system you first need to track the IP address of the individual making the comment. This will allow you to block people based on IP. You would typically set up a blocked list of IPs and then if someone with the IP tried to make a comment then you would prevent that user somehow. This can cause some issues because IPs change users and someone can be using a proxy, but any preventative measures are better than none.

The ACS includes other forms of security, like a slider bar and random letters to type, but I decided to avoid these since other websites and blogs only used names, emails, and comments. The IP tracking should be a good start, but I can always improve the system to add browser type and such.

You will also need to link the user making the comment to your list of user accounts. This is usually done with a user ID, not a username, like the ACS uses. I switched my database over to the user ID, but left it as an optional field since I would like non-registered users to also make comments.

The comment author's name and email are required fields, but if someone is logged in then these fields are auto-populated. The comment is a simple text area which is cleaned before being inserted into the database.

So this is where you ask "what does it look like?!?"

The Database

Comment System database schema

Field	Type	Null	Default	Extra
id	int(10)	No		auto_increment
date_inserted	datetime	No
article	int(11)	No
author	int(11)	Yes	NULL
message	text	No
authorname	varchar(64)	No
authoremail	varchar(128)	No
authorwebsite	varchar(128)	Yes	NULL
ip	varchar(24)	No

Displaying the Comments

I have several files which control articles, database entries, and the webpage itself. The articles are loaded into objects for easy reference and to reduce the number of database calls. Some information is better kept in the database, like comments, so I don't load those into my article objects.

The following code retrieves the comments from an article and formats the view. The rendered result can be found on any article page, like this one.

//print the comments
echo '<div id="comments">';
                   
$comments = $article->getComments();
 echo '<a name="comments"></a><h1>Comments ('.count($comments).')</h1><br />';
foreach($comments as $comment){
        echo '<p style="border-bottom:1px solid #000;"><span
                    style="float:right;">'.$comment['date_inserted'].'</span><span
                    class="bold">'.$comment['authorname']."</span> ".
                    ($comment['authorwebsite']!=""?'- <a
                    href="'.$comment['authorwebsite'].'">'.
                    $comment['authorwebsite'].'</a> ':'').'<br />'.
                    nl2br($comment['message']).'</p>';
}
echo '</div>'; //end comments div

The previous code relies on the articles object to retrieve the code from the database. Below is the function from the articles object.

public function getComments(){
    return $this->db->select("advanced_comment_system.id,
                                 date_inserted,author,authorname,
                                 authoremail,authorwebsite,message",
                             "advanced_comment_system",
                             "article='{$this->id}' 
                 ORDER BY date_inserted ASC;");
}

You can see from the code above that I carry around a database object in the articles object. This allows me to use different types of databases depending on how I develop the system. Currenly my website is designed for MySQL, but I could extend it easily to T-SQL in the future. The database function follows.

/**
 * select takes a csv list of columns, a table name or list to select from, and a where clause.
 * Additional modifications can be added to the whereclause, like order by limit, etc.
 * It returns an array of associative arrays containing the column data.
 * 
 */
function select($columns="*",$fromtable=null,$whereclause=null,$limit=null){
   $query = mysql_query("SELECT $columns ".
                          (!is_null($fromtable)?"FROM $fromtable ":"").
                          (!is_null($whereclause)?" WHERE $whereclause":"").
                          (!is_null($limit)?" LIMIT $limit":""),
                        $this->dbconn);	
		
   $results = array();
   if(!$query){
      die(mysql_error($this->dbconn));
   }		
   while($result = mysql_fetch_assoc($query)){
      $results[] = $result;
   }
   return $results;
}

Inserting new Comments

The next part of the development was inserting new comments into the database. Since I absolutely hate forms, I kept this to the end. In my decision-making process I decided to use JSON to submit the new comment, but I didn't take the time to perform dynamic updates on the webpage. I will do these updates later, but for now I use JSON to refresh the window.

The form code is below. This submits the comment to the JSON function. It requires some information from an author object, such as the user ID, and the article ID. *Line breaks have been added to improve readabiltiy, if code errors then remove some line breaks.

echo '<div id="createcomment">';
echo '<form action="article.php?id='.$article->id.
        '" method="post" onsubmit="(new Comments()).
        createComment(this);return false;">';
echo '<input type="hidden" name="articleid" value="'.$article->id.'" />';
echo '<input type="hidden" name="authorid" value="'.
        (isset($_SESSION['author'])?$author->id:'NULL').'" />';
echo '<label for="authorname" class="bold">Your name</label>:<br />
        <input type="text" id="authorname" name="authorname" title="Your name" 
        onclick="this.style.backgroundColor='';" size="60" '.
        (isset($_SESSION['author'])?'value="'.$author->info['name'].'"':'NULL').
        ' /><br />';
echo '<label for="email" class="bold">Email (not used to contact you)</label>:<br />
        <input type="text" id="email" name="email" title="Email" 
        onclick="this.style.backgroundColor='';" size="60" '.
        (isset($_SESSION['author'])?'value="'.$author->info['email'].'"':'').
        ' /><br />';
echo '<label for="website">Website</label>:<br />
        <input type="text" id="website" size="60" name="website" /><br /><br />';
echo '<label for="comment" class="bold">Comment</label>:<br />';
echo '<textarea id="comment" name="comment" title="Comment" 
        onclick="this.style.backgroundColor='';" cols="60" rows="10">
        </textarea><br /><br />';
echo '<input type="submit" name="submit" value="Create Comment" />';
echo '</form>';
echo '</div>';

The comment is sent to a JavaScript function when the submit button is clicked (when the form is submitted). This function uses the JSON2 library and zXMLHttp.

View the comment JavaScript source code here. The code is too long to include in this post, so please click on the link to view the JS source code in a new window or tab.

The comment.js code accesses another PHP page called comment.php, which inserts the new record to the database and returns the index of the new record to the JavaScript file. The index can be used later to pull the new record from the database without updating the entire page. This functionality is not included here, but the foundation is set up.

header("Content-Type: text/plain; charset=UTF-8");
require("../includes/config.inc.php");

$oData = json_decode($HTTP_RAW_POST_DATA);

switch($oData->requesting){
   case "Comment":
	if(strlen($oData->comment) > 0 && 
           strlen($oData->authorname) > 0 && 
           strlen($oData->authoremail) > 0){
              //sanitize the data
              //bumped in for formatting
$oData->comment = mysql_real_escape_string(htmlspecialchars($oData->comment));
$oData->authorname = mysql_real_escape_string(htmlspecialchars($oData->authorname));
$oData->website = mysql_real_escape_string($oData->authorwebsite);
$oData->authoremail = mysql_real_escape_string($oData->authoremail);

//insert comment into system
$theId = $db->insert("advanced_comment_system",
                     "date_inserted,article,author,message,authorname,authoremail,authorwebsite,ip",
                     "NOW(),'{$oData->articleid}',{$oData->authorid},'{$oData->comment}',
                     '{$oData->authorname}','{$oData->authoremail}',".
                      ($oData->authorwebsite==''?"NULL":"'{$oData->authorwebsite}'").
                      ",'{$_SERVER['REMOTE_ADDR']}'");
		
              //$theId = 0;
              echo json_encode($theId);
        }else{
            echo json_encode(-1);
        }
        break;
}

When the comment is inserted into the database the comment.php page will return a number other than -1 (-1 means error). The Javascript comments.js will process the result and issue a warning or refresh the page from the updateView function.

This comment system lays a foundation for truly AJAX/JSON comments, providing a way to update the page. While it doesn't perform dynamic updates at the moment, it will make the coding simpler to implement in the future. The administration of the comments happens in the Advanced Comment System admin section, so no administration webpages were necessary in this development, only slight modifications were needed.

If you have comments or questions, please feel free to make them. I will be testing the system for the next couple of days as well to reduce bugs, but if you notice something please help me improve the system by making a comment.