Forest Blog article navigation bar

I've now thoroughly tested the article navigation modification mentioned in my article and have implemented it on this blog.

The modification is currently only for blogs running from MSSQL Server although with a slight modification, it will work on MS Access run blogs too (see update below). I expect it'd be fairly easy to modify the script for MySQL run blogs too using the MySQL LIMIT function (LIMIT intArticlesStart, intBlogArticleLimit).

To start with, I replaced the current SQL for selecting just the top intBlogArticleLimit articles with two new SQL statements which select the top intBlogArticleLimit articles that are NOT IN the first intArticlesStart articles.

I also added two new variables: intArticlesStart which tells the SQL where to count from and intArticlesTotal which retrives the total number of articles (or the total number of articles in the selected category) from a new pair of SQL statements (sqlArticlesTotal).

Finally, I added a function: intOffset() to find the difference between the number of articles left and the number of articles to retrieve per page.

Putting all this together, I modified the default.asp file (in the root of the blog) by replacing lines 111 - 120:

'set sql query string based on database type
select case strDatabase
case "MSSQL"

'specify category if required
if intCategory = 0 then
sqlArticles = "SELECT TOP "& intBlogArticleLimit &" Articles.Article_ID, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Content, Articles.Article_Posted, Articles.Article_Comments, Articles.Article_Images, Categories.Category_ID, Categories.Category_Title, Users.User_ID, Users.User_Name FROM FB_Users AS Users INNER JOIN (FB_Categories AS Categories INNER JOIN FB_Articles AS Articles ON Categories.Category_ID = Articles.Article_Category_ID) ON Users.User_ID = Articles.Article_User_ID WHERE Article_Status = 1 AND Article_Level <= "& intLevel &" ORDER BY Article_Posted DESC;"
else
sqlArticles = "SELECT TOP "& intBlogArticleLimit &" Articles.Article_ID, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Content, Articles.Article_Posted, Articles.Article_Comments, Articles.Article_Images, Categories.Category_ID, Categories.Category_Title, Users.User_ID, Users.User_Name FROM FB_Users AS Users INNER JOIN (FB_Categories AS Categories INNER JOIN FB_Articles AS Articles ON Categories.Category_ID = Articles.Article_Category_ID) ON Users.User_ID = Articles.Article_User_ID WHERE Article_Status = 1 AND Article_Category_ID = "& intCategory &" AND Article_Level <= "& intLevel &" ORDER BY Article_Posted DESC;"
end if

with the following:

'set sql query string based on database type
select case strDatabase
case "MSSQL"
' added variables for article navigation
Dim sqlArticlesTotal, rsArticlesTotal, intArticlesTotal, intArticlesStart

If Request.QueryString("Start") <> "" And IsNumeric(Request.QueryString("Start")) = True Then
intArticlesStart = Cint(Request.QueryString("Start"))
Else
intArticlesStart = 0
End If

' function to get the last page of articles offset
Function intOffset()
intOffset = intArticlesTotal
Do While intOffset > intBlogArticleLimit
intOffset = intOffset - intBlogArticleLimit
Loop
End Function

'specify category if required
if intCategory = 0 then

' added sql statement to retrieve total articles
sqlArticlesTotal = "SELECT Count(*) AS RESULT FROM FB_Articles WHERE Article_Status = 1 AND Article_Level <= "& intLevel &";"

' modified to get article pages navigation: first, previous, next, last
sqlArticles = "SELECT TOP "& intBlogArticleLimit &" Articles.Article_ID, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Content, Articles.Article_Posted, Articles.Article_Comments, Articles.Article_Images, Categories.Category_ID, Categories.Category_Title, Users.User_ID, Users.User_Name FROM FB_Users AS Users INNER JOIN (FB_Categories AS Categories INNER JOIN FB_Articles AS Articles ON Categories.Category_ID = Articles.Article_Category_ID) ON Users.User_ID = Articles.Article_User_ID WHERE Article_Status = 1 AND Article_Level <= "& intLevel &" AND Articles.Article_ID NOT IN (SELECT TOP " & intArticlesStart & " Articles.Article_ID FROM FB_Users AS Users INNER JOIN (FB_Categories AS Categories INNER JOIN FB_Articles AS Articles ON Categories.Category_ID = Articles.Article_Category_ID) ON Users.User_ID = Articles.Article_User_ID WHERE Article_Status = 1 AND Article_Level <= "& intLevel &" ORDER BY Article_Posted DESC) ORDER BY Article_Posted DESC;"

else
' added sql statement to retrieve total articles in caregory
sqlArticlesTotal = "SELECT Count(*) AS RESULT FROM FB_Articles WHERE Article_Category_ID = "& intCategory &" AND Article_Status = 1 AND Article_Level <= "& intLevel &";"

' modified to get article pages navigation: first, previous, next, last
sqlArticles = "SELECT TOP "& intBlogArticleLimit &" Articles.Article_ID, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Content, Articles.Article_Posted, Articles.Article_Comments, Articles.Article_Images, Categories.Category_ID, Categories.Category_Title, Users.User_ID, Users.User_Name FROM FB_Users AS Users INNER JOIN (FB_Categories AS Categories INNER JOIN FB_Articles AS Articles ON Categories.Category_ID = Articles.Article_Category_ID) ON Users.User_ID = Articles.Article_User_ID WHERE Article_Status = 1 AND Article_Category_ID = "& intCategory &" AND Article_Level <= "& intLevel &" AND Articles.Article_ID NOT IN (SELECT TOP " & intArticlesStart & " Articles.Article_ID FROM FB_Users AS Users INNER JOIN (FB_Categories AS Categories INNER JOIN FB_Articles AS Articles ON Categories.Category_ID = Articles.Article_Category_ID) ON Users.User_ID = Articles.Article_User_ID WHERE Article_Status = 1 AND Article_Category_ID = "& intCategory &" AND Article_Level <= "& intLevel &" ORDER BY Article_Posted DESC) ORDER BY Article_Posted DESC;"

end if

To retrieve the total number of articles, I then added the following recordset after line 131 (end select):

' Article count script
set rsArticlesTotal = server.createobject("adodb.recordset")
rsArticlesTotal.cursorlocation = 3
rsArticlesTotal.open sqlArticlesTotal, adoConn
if not (rsArticlesTotal.bof or rsArticlesTotal.eof) then
intArticlesTotal = rsArticlesTotal("RESULT")
else
intArticlesTotal = 0
end if
rsArticlesTotal.close
Set rsArticlesTotal = nothing

The next step was to write the article navigation bar to show the links: first page, previous page, next page, last page only when there are articles available. To do this, I added the following code after line 542 (loop)

' show article pages navigation: first, previous, next, last.
' get Category from QueryString if present and prepare it for re-adding.
Dim strCategory
If Not Request.QueryString("Category") = "" Then
strCategory = "?Category=" & Request.QueryString("Category") & "&amp;"
Else
strCategory = "?"
End If
' write the article paging div if article pages navigation is present
If intArticlesTotal > intBlogArticleLimit Then
Response.Write "<div id='articlepaging'>"
Response.Write "<ul>"
' first page link
If Not intArticlesStart = 0 Then
Response.Write "<li><a href='/default.asp" & strCategory & "Start=0' title='first page of articles'>first page</a></li>"
End If
' previous page link
If (intArticlesStart - intBlogArticleLimit) >= intBlogArticleLimit Then
Response.Write "<li><a href='/default.asp" & strCategory & "Start=" & (intArticlesStart - intBlogArticleLimit) & "' title='previous page of articles'>previous page</a></li>"
End If
' next page OR last page link
If (intArticlesStart + intArticlesCount) < intArticlesTotal Then
' check if the next page IS the last page
If (intArticlesStart + intArticlesCount + intBlogArticleLimit) >= intArticlesTotal Then
' if it is, show link for last page
Response.Write "<li><a href='/default.asp" & strCategory & "Start=" & (intArticlesTotal - intOffset()) &"' title='last page of articles'>last page</a></li>"
Else
' if it isn't, show link for next page
Response.Write "<li><a href='/default.asp" & strCategory & "Start=" & (intArticlesStart + intBlogArticleLimit) &"' title='next page of articles'>next page</a></li>"
End If
End If
' last page link
If (intArticlesStart + intArticlesCount + intBlogArticleLimit) < intArticlesTotal Then
Response.Write "<li><a href='/default.asp" & strCategory & "Start=" & (intArticlesTotal - intOffset()) &"' title='last page of articles'>last page</a></li>"
End If
Response.Write "</ul>"
Response.Write "<hr />"
Response.Write "</div>"
End If

To finish it all off, I styled the article navigation bar by adding the following two styles near the bottom of the blog's CSS file:

#content #articlepaging ul{
display:inline;
list-style: none;
margin:0;
padding:0;
}
#content #articlepaging li{
display:inline;
margin:0 10px 0 0;
padding:0;
}


Update

For blogs running from MS Access databases there is an issue when no querystring is present or when the querystring includes start=0 (meaning intArticleStart = 0) which causes a Jet database error:

Microsoft JET Database Engine error '80040e14'

Syntax error. in query expression 'Article_Status = 1 AND Article_Level <= 4 AND Articles.Article_ID NOT IN (SELECT TOP 0...

Host Forest forum member SSalhi, who uses MS Access as his blog database, has provided a fix for this issue which simply checks if intArticleStart = 0

If it is, it uses the original SQL and if it isn't, it uses the modified SQL above. Full code below.

'specify category if required
if intCategory = 0 then
' added sql statement to retrieve total articles
sqlArticlesTotal = "SELECT Count(*) AS RESULT FROM FB_Articles WHERE Article_Status = 1 AND Article_Level <= "& intLevel &";"
' modified to get article pages navigation: first, previous, next, last
if intArticlesStart = 0 then
sqlArticles = "SELECT TOP "& intBlogArticleLimit &" Articles.Article_ID, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Content, Articles.Article_Posted, Articles.Article_Comments, Articles.Article_Images, Categories.Category_ID, Categories.Category_Title, Users.User_ID, Users.User_Name FROM FB_Users AS Users INNER JOIN (FB_Categories AS Categories INNER JOIN FB_Articles AS Articles ON Categories.Category_ID = Articles.Article_Category_ID) ON Users.User_ID = Articles.Article_User_ID WHERE Article_Status = 1 AND Article_Level <= "& intLevel &" ORDER BY Article_Posted DESC;"
else
sqlArticles = "SELECT TOP "& intBlogArticleLimit &" Articles.Article_ID, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Content, Articles.Article_Posted, Articles.Article_Comments, Articles.Article_Images, Categories.Category_ID, Categories.Category_Title, Users.User_ID, Users.User_Name FROM FB_Users AS Users INNER JOIN (FB_Categories AS Categories INNER JOIN FB_Articles AS Articles ON Categories.Category_ID = Articles.Article_Category_ID) ON Users.User_ID = Articles.Article_User_ID WHERE Article_Status = 1 AND Article_Level <= "& intLevel &" AND Articles.Article_ID NOT IN (SELECT TOP " & intArticlesStart & " Articles.Article_ID FROM FB_Users AS Users INNER JOIN (FB_Categories AS Categories INNER JOIN FB_Articles AS Articles ON Categories.Category_ID = Articles.Article_Category_ID) ON Users.User_ID = Articles.Article_User_ID WHERE Article_Status = 1 AND Article_Level <= "& intLevel &" ORDER BY Article_Posted DESC) ORDER BY Article_Posted DESC;"
end if
else
' added sql statement to retrieve total articles in caregory
sqlArticlesTotal = "SELECT Count(*) AS RESULT FROM FB_Articles WHERE Article_Category_ID = "& intCategory &" AND Article_Status = 1 AND Article_Level <= "& intLevel &";"
' modified to get article pages navigation: first, previous, next, last
if intArticlesStart = 0 then
sqlArticles = "SELECT TOP "& intBlogArticleLimit &" Articles.Article_ID, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Content, Articles.Article_Posted, Articles.Article_Comments, Articles.Article_Images, Categories.Category_ID, Categories.Category_Title, Users.User_ID, Users.User_Name FROM FB_Users AS Users INNER JOIN (FB_Categories AS Categories INNER JOIN FB_Articles AS Articles ON Categories.Category_ID = Articles.Article_Category_ID) ON Users.User_ID = Articles.Article_User_ID WHERE Article_Status = 1 AND Article_Category_ID = "& intCategory &" AND Article_Level <= "& intLevel &" ORDER BY Article_Posted DESC;"
else
sqlArticles = "SELECT TOP "& intBlogArticleLimit &" Articles.Article_ID, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Content, Articles.Article_Posted, Articles.Article_Comments, Articles.Article_Images, Categories.Category_ID, Categories.Category_Title, Users.User_ID, Users.User_Name FROM FB_Users AS Users INNER JOIN (FB_Categories AS Categories INNER JOIN FB_Articles AS Articles ON Categories.Category_ID = Articles.Article_Category_ID) ON Users.User_ID = Articles.Article_User_ID WHERE Article_Status = 1 AND Article_Category_ID = "& intCategory &" AND Article_Level <= "& intLevel &" AND Articles.Article_ID NOT IN (SELECT TOP " & intArticlesStart & " Articles.Article_ID FROM FB_Users AS Users INNER JOIN (FB_Categories AS Categories INNER JOIN FB_Articles AS Articles ON Categories.Category_ID = Articles.Article_Category_ID) ON Users.User_ID = Articles.Article_User_ID WHERE Article_Status = 1 AND Article_Category_ID = "& intCategory &" AND Article_Level <= "& intLevel &" ORDER BY Article_Posted DESC) ORDER BY Article_Posted DESC;"
end if
end if

To test this, I used MS SQL Server Enterprise Manager's Data Transformation Services to export my blog's SQL Server Database to an MS Access Database and changed the connection string accordingly in the file \Includes\inc-dataconnection.asp.

I can confirm that SSalhi's fix works ;-)

Comments on this article can be left here.