How do I create my own blog?

"Blog" is short for "Weblog" and describes a web site that is more like a digital diary... they range from accounts of general day-to-day activities to musings on very specific topics. Basically, for every possible topic that captures the opinion of at least one person, there is probably a blog devoted. A blog generally consists of the ability for the owner/author to post articles, and the ability for end users to read the articles and read/post comments about the articles. Some blogs turn out to be more like a forum, where there is more content based on discussion by end users than on the author's original entry; other blogs have very little activity at all.

Developing such a site is not an inordinate amount of work; in fact, the majority of the work involved comes well after initial development--keeping it fresh and up to date. And while there are plenty of existing blog products out there, several people have expressed an interest in developing their own solution. Reasons for reinventing the wheel include having full control over the code and feature set, avoiding third party software on their web site, or as a learning exercise.

So, first, you must decide which features you want to incorporate. For this article, I will focus on the following pieces of core end-user functionality:

view individual posts
view monthly archives
post/read comments

Note that I am only going to go through the user-facing interfaces; the admin functionality will be your own project.

Next, you will need to have a database of some sort. This will be presented assuming you have SQL Server; if you have Microsoft Access, little will change except stored procedures will become stored queries.

Using Query Analyzer, create the database and a user (SQL Authentication) as follows:

-- create database with default parameters
CREATE DATABASE Blog
GO

-- create a login called 'BlogUser'
-- feel free to change the password
EXEC sp_addlogin 'BlogUser', 'password', 'Blog'
GO

-- switch to the context of the new database
USE Blog
GO

-- add BlogUser as a user in this database
-- assign him to the db_datareader role
EXEC sp_adduser 'BlogUser', 'BlogUser', 'db_datareader'
GO

-- add the new user to the db_datawriter role
EXEC sp_addrolemember 'db_datawriter', 'BlogUser'
GO

Next, we will need to create some tables. For the data, we will need two tables: Posts and Comments.

CREATE TABLE dbo.Posts
(
PostID INT IDENTITY(1,1) NOT NULL
PRIMARY KEY CLUSTERED,
dt SMALLDATETIME NOT NULL
DEFAULT CURRENT_TIMESTAMP,
title VARCHAR(255) NOT NULL,
body TEXT NOT NULL
)
GO

CREATE INDEX idx_NC1 ON dbo.Posts(dt)
GO

GRANT SELECT ON dbo.Posts TO [BlogUser]
GO

CREATE TABLE dbo.Comments
(
CommentID INT IDENTITY(1,1) NOT NULL
PRIMARY KEY NONCLUSTERED,
PostID INT NOT NULL FOREIGN KEY
REFERENCES dbo.Posts(PostID),
dt SMALLDATETIME NOT NULL
DEFAULT CURRENT_TIMESTAMP,
alias VARCHAR(32),
body TEXT NOT NULL
)
GO

CREATE CLUSTERED INDEX idx_C1 ON dbo.Comments(PostID, dt)
GO

GRANT SELECT, INSERT ON dbo.Comments TO [BlogUser]
GO

In order to organize the data, I decided to use a modified calendar table (see Article #2519) as opposed to coding all of my SQL statements with date calculations. This table will store 5 years' worth of "first-of-the-months," however that was an arbitrary choice--feel free to pick whatever end date you like. In any case, you'll see how this will help in a few of the stored procedures later on.

CREATE TABLE dbo.Months
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,
m AS CONVERT(VARCHAR(9), DATENAME(MONTH, dt)),
y AS CONVERT(CHAR(4), YEAR(dt))
)
GO

GRANT SELECT ON dbo.Months TO [BlogUser]
GO

-- populate a table of months
SET NOCOUNT ON
DECLARE @dt SMALLDATETIME

-- the first of the current month
SET @dt = DATEADD(DAY, -DAY(GETDATE())+1, DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())))

WHILE @dt <= '20101201'
BEGIN
INSERT dbo.Months(dt) SELECT @dt
SET @dt = DATEADD(MONTH, 1, @dt)
END
GO

Okay, now what? Well, you have your table, but no data. So here's a quick procedure to add a post:

CREATE PROCEDURE dbo.AddPost
@title VARCHAR(255),
@body TEXT
AS
BEGIN
SET NOCOUNT ON
INSERT dbo.Posts (title, body)
SELECT @title, @body
END
GO

GRANT EXEC ON dbo.AddPost TO [BlogUser]
GO

EXEC dbo.AddPost 'foo', 'Here is a pretty lengthy post... not.'

Now you can use Query Analyzer to insert a few fake posts, to make sure everything is set up correctly.

Next comes the interface. For this example, we will have a very basic navigation scheme:

Now, let's establish the set of pages required to support the functionality we need:

Given this, we're going to need some stored procedures for retrieving posts in three different ways: get the list of monthly archives, get the list of posts for a given month, and get the individual post in its entirety (including any comments). Here are those stored procedures:

-- for navigation, get list of months and post count
CREATE PROCEDURE dbo.GetArchiveCounts
AS
BEGIN
SET NOCOUNT ON
SELECT CONVERT(CHAR(8), m.dt, 112), m.m, m.y, postCount = COUNT(p.PostID)
FROM dbo.Months m
INNER JOIN dbo.Posts p
ON p.dt >= m.dt
AND p.dt < DATEADD(MONTH, 1, m.dt)
GROUP BY m.dt, m.m, m.y
ORDER BY m.dt DESC
END
GO

GRANT EXEC ON dbo.GetArchiveCounts TO [BlogUser]
GO

-- get the list of posts for a given month
CREATE PROCEDURE dbo.GetArchive
@dt SMALLDATETIME
AS
BEGIN
SET NOCOUNT ON

-- print summary at top of page
SELECT m.m, m.y, postCount = COUNT(p.PostID)
FROM dbo.Months m
INNER JOIN dbo.Posts p
ON p.dt >= m.dt
AND p.dt < DATEADD(MONTH, 1, m.dt)
WHERE m.dt = @dt
GROUP BY m.dt, m.m, m.y

-- print excerpts
SELECT PostID, dt, title
FROM dbo.Posts p
WHERE p.dt >= @dt
AND p.dt < DATEADD(MONTH, 1, @dt)
ORDER BY p.dt DESC
END
GO

GRANT EXEC ON dbo.GetArchive TO [BlogUser]
GO

-- get the body and details of the post and any comments
CREATE PROCEDURE dbo.GetPostAndComments
@postID INT
AS
BEGIN
SET NOCOUNT ON

SELECT dt, title, body
FROM dbo.Posts
WHERE PostID = @postID

SELECT dt, alias, body
FROM dbo.Comments
WHERE PostID = @postID
ORDER BY dt
END
GO

GRANT EXEC ON dbo.GetPostAndComments TO [BlogUser]
GO

The header file will have important information like your connection file, as well as style and navigation elements, to be used in all pages. There is no sense repeating this information in every page--reduce, reuse, recycle!

Your connection file (conn.asp) is very simple (see Article #2126 for more information on connection strings):

<%
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Data Source=x.x.x.x; Initial Catalog=Blog; User ID=BlogUser; Password=?"
%>

The header file (header.asp) will then look something like this, including the dynamic generation of monthly archive navigation:

<html>
<head>
<title>MyBlog</title>
<style>
* { font-family:georgia,}
a.nav { font-family:tahoma,verdana; }
</style>
</head>
<body>
<table border=1 cellpadding=10>
<tr>
<th colspan=2>MyBlog</th>
</tr>
<tr valign=top>
<td><a class=nav href=default.asp>Home</a>
<%
Set rs = conn.Execute("EXEC dbo.getArchiveCounts")
If Not rs.EOF Then
Do While Not rs.EOF
Response.Write "<br><a class=nav href=archive.asp?m=" & _
rs(0) & ">" & rs(1) & " " & rs(2) & "</a>" & _
" (" & rs(3) & ")"
rs.MoveNext()
Loop
End If
rs.Close()
Set rs = Nothing
%>
</td>
<td>

I'll leave it up to you to generate your own CSS and pay more attention to layout (remember, I said this would be basic). The main point here is that the majority of the content on every page is identical, so it makes sense to modularize that section of code. Now, it looks kind of funny, because the page just ends with an opening <td>. Well, the only part of every page that is going to be truly dynamic is the content of this main <td> element. Before we get too far, let's create the basic footer (footer.asp):

</td>
</tr>
</table>
</body>
</html>
<%
conn.Close
Set conn = Nothing
%>

So every page will look, more or less, something like this:

<%
' do stuff here for this specific page, e.g.
Response.Write "Welcome to my blog"
%>

If you save the above as default.asp and load it in a browser, you should see this:

Now you can see that filling in the main content will be relatively easy (regardless of how complex your layout and navigation become), and that the navigation for archives will be completely self-sustaining. So, let's move to the archive page that is referenced in the header file.

<%
m = Request.QueryString("m")

' if month is empty, get this month's archive
If isNumeric(m) = 0 Or Len(m) <> 8 then
m = Year(Date()) & Right("0" & Month(Date()), 2) & "01"
End If

Set rs = conn.Execute("EXEC dbo.getArchive '" & m & "'")

If Not rs.EOF Then
Response.Write rs(2) & " post(s) for " & rs(0) & " " & rs(1) & "<p>"
Set rs = rs.NextRecordSet()
If Not rs.EOF Then
Do While Not rs.EOF
Response.Write "<a href=viewpost.asp?id=" & rs(0) & ">" & _
rs(2) & "</a> (" & rs(1) & ")<p>"
rs.MoveNext()
Loop
End If
Else
Response.Redirect "default.asp"
End If
%>

Save the above as archive.asp, and load it in the browser. You should see something similar to this:

Now, we can move to the viewing page, which will show the entire article, any comments, and a form to add a comment:

<%
id = Request.QueryString("id")

If isNumeric(id) = 0 Then
Response.Redirect "default.asp"
End If

Set rs = conn.Execute("EXEC dbo.getPostAndComments " & id)

If Not rs.EOF Then
body = Replace(rs(2), VBCrLf, " <br>")
' display article and body:
Response.Write rs(1) & "<br>" & rs(0) & "<p>" & body

Set rs = rs.NextRecordSet()
If Not rs.EOF Then
Do While Not rs.EOF
body = Replace(rs(2), VBCrLf, " <br>")
Response.Write "<p><hr size=1>" & body & _
"<p>Posted by " & rs(1) & " - " & rs(0)
rs.MoveNext()
Loop
End If
Response.Write "<p><hr size=1><b>Add a Comment</b>" & _
"<p><form method=post action=addComment.asp>" & _
"<input type=hidden name=id value=" & id & ">" & _
"Name:<br>" & _
"<input type=text name=alias maxlength=32>" & _
"<p>Comments:<br>" & _
"<textarea rows=4 cols=50 name=body></textarea>" & _
"<p><input type=submit value='Add Comment'>" & _
"</form>"
Else
Response.Redirect "default.asp"
End If
%>

Save the above as viewpost.asp, and you should see something like this if you click on an active post in the archive page:

Now, we need a stored procedure and an ASP page that will take incoming comments and save them. Here is the stored procedure:

CREATE PROCEDURE dbo.AddComment
@postID INT,
@alias VARCHAR(32) = '',
@body TEXT
AS
BEGIN
SET NOCOUNT ON
INSERT dbo.Comments(postID, alias, body)
SELECT @postID, @alias, @body
END
GO

GRANT EXEC ON dbo.AddComment TO [BlogUser]
GO

The ASP page (addComment.asp) does not need all of the navigation, as it will simply be used to call a stored procedure and then redirect back to the view page.

<%
id = Request.Form("id")
alias = Replace(Trim(Request.Form("alias")), "'", "''")
body = Replace(Trim(Request.Form("body")), "'", "''")

If isNumeric(id) = 0 or Len(body) = 0 Then
Response.Redirect "default.asp"
End If

sql = "EXEC dbo.AddComment " & id & ",'" & alias & "','" & body & "'"

conn.Execute sql,,129
conn.Close()
set conn = nothing

Response.Redirect "viewpost.asp?id=" & id
%>

Now, if you insert a comment into the viewing page and hit the Add Comment button, you should see something like this:

I think that's it! Not bad for about an hour's work, huh? You can download the code here: ASPFAQ_basic_blog_2539.zip (4 KB). Please read the readme.txt file so that you can make the necessary adjustments before the sample will run.

Yes, it's very simple. You may want to add features like search, RSS feed, stripping HTML, and threaded comments. In addition, I have left it as an exercise to the reader to build a separate admin interface for adding/updating posts, and any other admin functionality you may want to have. I just wanted to document the core design in a place where it can be re-used by others!



// home // advanced search // news // categories // sql build chart // downloads // statistics
ASP FAQ

How do I create my own blog?

Related Articles