Robert Tamayo | April 17, 2018

Adding Comments to a Blog Part 1: The Backend

When I first began learning web development a couple years ago, I decided to take on a couple of "big" projects to help me learn, the first of which being to write my own blogging platform that I could use on websites I build. The blogging platform I'm using to write this post is called Bob Blog 2 and is the second evolution of that original code. I have a lot of points to talk about regarding the making of this blogging platform, but today I'm going to go over what I added this past week: comments.

1. Setting up the database

The first step in creating comments for this blog is to decide how the comments are going to work. Normally, people who read a post or watch a video can comment on the content by filling out a small form. Depending on the platform, they may have to have a user account in order to post a comment. For my case, I'm not going to require people to create an account with an email verification system and a password, but I'm going to require both a name and an email account so that I can build those in later.

The next step is to think about what a comment is, or more specifically how it is stored in the database. A comment belongs to a specific blog post, and it can't belong to 2 different posts, so all I needed was a blog id. It also can only have 1 person who made the comment, but I don't want to store guest information in the comments database. That information needs to be included in a separate guest table. I also want to include the reply count and an id of the comment to which it is a reply, if it is a reply.

So far, I have 2 tables, commentbase and guestbase:

commentbase

commentid: INT

commentblogid: INT

commentguestid: INT

replyto: INT, default: NULL

timewritten: DATETIME

hasreplies: INT, default: 0

guestbase

guestid: INT

guestname: VARCHAR (20)

guestemail: VARCHAR (50)

2. Updating the API

The next step is to add in the capabilities to update the database and retrieve data from it. Bob Blog 2 is set up as an API that receives GET and POST requests and returns data in JSON format. I wanted to be able to pull in comments using http queries, such as "type=comments&postid=2".

$type = filter_var($_GET['type'], FILTER_SANITIZE_STRING);
if ($type === 'comment') {
    // process request
} else if ($type === 'reply') {
    // process request
} else if ($type === 'guest') {
    // process request
}

3. Getting Comments Using the API

Let's say we had the following request:

http://www.estellaphoto.com/blog/api/?key&type=comment&postid=1

To the API, this is as simple as it gets. It's just a request for all comments belonging to the post with postid 1. Here's how it's implemented:

$postid = $_GET['postid'];
settype($postid, 'integer');
if (isset($_POST['comment'])) { 
    // post a comment
} else {
    // get comments
    
    // offset
        $offset = 0;
        $limit = 100;
        $limit_and_offset = '';
        
        if (isset($_GET['limit'])) {
            if (filter_var($_GET['limit'], FILTER_VALIDATE_INT)) {
                $limit = $_GET['limit'];
                settype($limit, 'integer');
            }
        }
        if (isset($_GET['offset'])) {
            if (filter_var($_GET['offset'], FILTER_VALIDATE_INT)) {
                $offset = $_GET['offset'];
                settype($offset, 'integer');
            }
            $limit_and_offset = " LIMIT $offset , $limit ";
        } else {
            $limit_and_offset = " LIMIT $limit ";
        }
        
        $sql = "SELECT commentbase.*, guestbase.guestname
        FROM commentbase
            JOIN guestbase 
            ON commentbase.commentguestid = guestbase.guestid
        WHERE commentbase.commentblogid = $postid 
        AND commentbase.replyto IS NULL 
        ORDER BY commentbase.timewritten
        $limit_and_offset";
        $data = executeSQL();
        finish($data);
}

Notice the key parts: I'm also checking for a limit and offset in the $_GET array, I'm specifically excluding replies from the return data, I'm ordering by the time the comment was written in chronological order, and I'm calling two custom functions at the end. The function executeSQL() uses a PDO object to execute the statement and return the data in JSON format. The function finish($data) is responsible for calling both echo($data) and exit() to finish the script.

4. Posting Comments Using the API

To post a comment to the API, all you need to set in the GET request is the "comment" argument. The backend can check and limit the number of characters in this part, but HTML5 lets <textarea> elements define a "limit" attribute, so I'll let the frontend handle the character limit. Building off of what was above, let's see the additions:

if (isset($_GET['comment'])) { // post a comment
        $param_values[] = [
            'key' => ':comment',
            'value' => filter_var($_GET['comment'], FILTER_SANITIZE_STRING)
        ];
        
        $commentguestid = $_GET['guestid'];
        settype($commentguestid, 'integer');
        
        $replyto = 'NULL';
        if (isset($_GET['replyto'])) {
            $replyto = $_GET['replyto'];
            settype($replyto, 'integer');
        }
        $sql = "INSERT INTO commentbase (comment, commentblogid, commentguestid, replyto, timewritten)
        VALUES (:comment, $postid, $commentguestid, $replyto, NOW())";
        $data = executeSQL();
        
        if ($replyto != 'NULL') {
            $sql = "UPDATE commentbase
            SET hasreplies = hasreplies + 1
            WHERE commentid = $replyto";
            
            $data_update = executeSQL();
        }
        
        $sql = "UPDATE blogbase
        SET hascomments = hascomments + 1
        WHERE id = $postid";
        executeSQL();
        
        finish($data);

One of the key things here is the $param_values array. This array is used to store key/value pairs to be used in a prepared statement when I execute the SQL query. This, along with the call to filter_var() to get the value content, is an important basic step for database security when using values provided by a user.

I'm also checking if the argument "replyto" is set. If it is, this means that this comment is actually a reply, and that means we have to update the original comment's reply count in a separate query.

Finally, another important thing to notice is that I'm updating the blog's comment count no matter what; whether it is a reply or a comment, I want the comment count to increment for that blog. This will be useful for the frontend to display the number of comments a post has.

5. Getting Replies to a Comment Using the API

I decided to make the request that returns replies separate from the request that returns comments. The reason is as follows: All replies are comments, but not all comments are replies. So when the API is used to return just the first 25 comments, for example, what happens when the first comment has 100 replies? Does it return 125 comments, 100 of which being replies? And what if each comment has 100 replies? In that case, the point of setting a limit is completely defeated. That's why replies need to treated separately.

} else if ($type == 'replies') {
    // fields for both getting and creating replies
    $commentid = $_GET['commentid'];
    settype($commentid, 'integer');
    
    // NOTE: same limit and offset code from above goes here.
    // removed for this blog post
    $sql = "SELECT commentbase.*, guestbase.guestname
    FROM commentbase
        JOIN guestbase 
        ON commentbase.commentguestid = guestbase.guestid
    WHERE commentbase.replyto = $commentid
    ORDER BY commentbase.timewritten
    $limit_and_offset";
    $data = executeSQL();
    finish($data);
}

There's really nothing unexpected above. In the actual code, I included the limit and offset section, but I removed it for readability here. Other than that, this is a basic JOIN statement.

So far, I have been making JOIN statements to combine the comment data with the guest data. This is important because it keeps the guest table and data separate from the the comment data. I'll examine the code for creating a guest next.

6. Creating a New Guest Using the API

There is no way to return guest information from the API. Emails are stored in the database and never come back as part of a request. Nonetheless, guest information is necessary for displaying the comment, and so I needed a way to create a new guest using the API. This part is simple:

} else if ($type == 'guest') {
    $guestemail = filter_var($_GET['guestemail'], FILTER_SANITIZE_STRING);
    $guestname = filter_var($_GET['guestname'], FILTER_SANITIZE_STRING);
    
    $param_values[] = [
        'key' => ':guestemail',
        'value' => $guestemail
    ];
    $param_values[] = [
        'key' => ':guestname',
        'value' => $guestname
    ];
    
    $sql = "INSERT INTO guestbase (guestemail, guestname)
    VALUES (:guestemail, :guestname)";    
    $data = executeSQL();
    finish($data);
}

The only thing worth noting here is that I'm once again using the $param_values array. As stated earlier, this is used to create prepared statements dynamically. Here's how that works:

$stmt = $conn->prepare($sql);
$size = sizeof($param_values);
for ($i = 0; $i < $size; $i++) {
    $stmt->bindParam($param_values[$i]['key'], $param_values[$i]['value']);
}
$stmt->execute();

Basically, it loops through the $param_values array and finds ":guestemail" and replaces it with the value upon execution. I had a little bit of a tricky problem to debug here when I first created this loop. The first time around, I was using something like this:

$key = $param_values[$i]['key'];
$value = $param_values[$i]['value'];
$stmt->bindParam($key, $value);

The problem is that the parameters aren't actually bound until the exact moment $stmt->execute() is called. This meant that the $key and $value variables were not what they were originally when I called stmt->bindParam(), but were instead the last known values that they were set to at the last index of $param_values.

7. Executing the SQL Statement and Returning Data

The final step in setting up the API is actually something I only had to modify slightly. This is the executeSQL() function:

function executeSQL(){
    global $sql;
    global $param_values;
    global $db_server;
    global $db_username;
    global $db_password;
    global $db_name;
    
    try {
        $conn = new PDO("mysql:host=$db_server;dbname=$db_name", $db_username, $db_password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $conn->prepare($sql);
        $size = sizeof($param_values);
        for ($i = 0; $i < $size; $i++) {
            $stmt->bindParam($param_values[$i]['key'], $param_values[$i]['value']);
        }
        $stmt->execute();
        try {
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $data = json_encode($rows);
        } catch (Exception $e) {
            if ($sql_insert) {
                $data = json_encode(['lastinsertid' => $last_id = $conn->lastInsertId()]);
            }
        }
        
    } catch (PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
    $conn = null;
    return $data;
}

Notice that I'm wrapping the $stmt->fetchAll() call in a try/catch block. If this is an UPDATE statement, there won't be any rows to return. Instead, I'm checking for the $sql_insert flag and then returning the lastinsertid of the statement. This function returns JSON, but it does not echo anything. That's where the next function comes in:

function finish($data) {
    echo ($data);
    exit;
}

Sometimes I want to do things after executing the SQL query, so I didn't want the function that executes the query to be the same one that echoes the data and exits.

That's all there is to the backend!

Final Notes

At this moment, I haven't updated this blog to include the comments. I'll be sure to do that soon so that readers can test posting the comments.
I'll provide a link to the code shortly, as well.
Part 2 of this post is going to cover the frontend implementation of the comments.

Comments: