Showing counts on non forum pages - Posted (2913 Views)
Lon2
Junior Member
Lon2
Posts: 151
151
I searched first but I'm not sure if I'm using the correct terminology. I want to show some counts, like members, topics, etc on a different pages that are not the forum. Do I need to add server includes and what would I use to get totals? Thanks!
 Sort direction, for dates DESC means newest first  
 Page size 
Posted
Carefree
Advanced Member
Carefree
Posts: 4224
4224
Yes, you'll need to use includes. Depending on exactly what counts you desire, the SQL string(s) will be different.

I created a page with a variety of counts:

  • Members
  • Active (or moderated) topics
  • Active (or moderated) topics within a specified forum
  • Active (or moderated) replies
  • Active members signed in
  • Guests
  • Active auction items
  • Polls
  • Quizzes
  • Recipes
  • Shared Photos
In lines 42-43, you will have to change the "(forumpath)" to reflect your actual forum path: using Snitz for example, you only need the forum/ portion following the ".com/" part of the URL.
Other than that mentioned above, you don't have to change any code to use these. All you need is to create the output where you want them displayed. Samples are provided.
Code:

<%
'###############################################################################
'##
'##			Snitz Forums 2000 v3.4.07
'##
'###############################################################################
'##
'## Copyright © 2000-06 Michael Anderson, Pierre Gorissen,
'##			Huw Reddick and Richard Kinser
'##
'## This program is free. You can redistribute and/or modify it under the
'## terms of the GNU General Public License as published by the Free Software
'## Foundation; either version 2 or (at your option) any later version.
'##
'## All copyright notices regarding Snitz Forums 2000 must remain intact in
'## the scripts and in the HTML output.	The "powered by" text/logo with a
'## link back to http://forum.snitz.com in the footer of the pages MUST
'## remain visible when the pages are viewed on the internet or intranet.
'##
'## This program is distributed in the hope that it will be useful but
'## WITHOUT ANY WARRANTY; without even an implied warranty of MERCHANTABILITY
'## or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License
'## for more details.
'##
'## You should have received a copy of the GNU General Public License along
'## with this program; if not, write to:
'##
'##			Free Software Foundation, Inc.
'##			59 Temple Place, Suite 330
'##			Boston, MA 02111-1307
'##
'## Support can be obtained from our support forums at:
'##
'##			http://forum.snitz.com
'##
'## Correspondence and marketing questions can be sent to:
'##
'##			manderson@snitz.com
'##
'###############################################################################
%>
<!--#INCLUDE VIRTUAL="(forumpath)/config.asp"-->
<!--#INCLUDE VIRTUAL="(forumpath)/inc_func_common.asp"-->
<%
dim intMembers, intTopics, intReplies, intMessages
dim intActiveMembers, intGuests, intAuctionItems, intForumTopics
dim intPolls, intQuizzes, intRecipes, intPhotos
set my_Conn = Server.CreateObject("ADODB.Connection")
my_Conn.Open(strConnString)
'	##	Obtain counts
strSql = "SELECT COUNT(MEMBER_ID) AS CNT FROM " & strMemberTablePrefix & "MEMBERS WHERE M_STATUS=1"
set rsCount = my_Conn.Execute(strSql)
if not rsCount.EOF then
	intMembers=rsCount("CNT")
	rsCount.Close
end if
set rsCount = Nothing
strSql = "SELECT COUNT(TOPIC_ID) AS CNT FROM " & strTablePrefix & "TOPICS WHERE T_STATUS=1"
set rsCount = my_Conn.Execute(strSql)
if not rsCount.EOF then
	intTopics=rsCount("CNT")
	rsCount.Close
end if
set rsCount = Nothing
strSql = "SELECT COUNT(REPLY_ID) AS CNT FROM " & strTablePrefix & "REPLY WHERE R_STATUS=1"
set rsCount = my_Conn.Execute(strSql)
if not rsCount.EOF then
	intReplies=rsCount("CNT")
	rsCount.Close
end if
set rsCount = Nothing
'	##	Sum of (Topics and Replies) to get total active posts
intMessages = intTopics+intReplies
'	##	Detect presence of active users table and total active users online
if TableExists(strTablePrefix & "ACTIVE_USERS") then
	'	##	Table exists, get count
	strSql = "SELECT COUNT(MEMBER_ID) AS CNT FROM " & strTablePrefix & "ACTIVE_USERS WHERE MEMBER_ID > 0"
	set rsCount = my_Conn.Execute(strSql)
	if not rsCount.EOF then
		intActiveMembers=rsCount("CNT")
		rsCount.Close
	end if
	set rsCount = Nothing
	strSql = "SELECT COUNT(MEMBER_ID) AS CNT FROM " & strTablePrefix & "ACTIVE_USERS WHERE MEMBER_ID < 1"
	set rsCount = my_Conn.Execute(strSql)
	if not rsCount.EOF then
		intGuests=rsCount("CNT")
		rsCount.Close
	end if
	set rsCount = Nothing
end if
'	##	Detect presence of auction table and total active auction items
if TableExists(strTablePrefix & "AUCTIONITEMS") then
	'	##	Table exists, get count
	strSql = "SELECT COUNT(AUCTIONID) AS CNT FROM " & strTablePrefix & "AUCTIONITEMS WHERE ENDDATE > '" & DateToStr(strForumTimeAdjust) & "'"
	set rsCount = my_Conn.Execute(strSql)
	if not rsCount.EOF then
		intAuctionItems=rsCount("CNT")
		rsCount.Close
	end if
	set rsCount = Nothing
end if	
'	##	Detect presence of polls table and total polls
if TableExists(strTablePrefix & "POLLS") then
	'	##	Table exists, get count
	strSql = "SELECT COUNT(POLL_ID) AS CNT FROM " & strTablePrefix & "POLLS"
	set rsCount = my_Conn.Execute(strSql)
	if not rsCount.EOF then
		intPolls=rsCount("CNT")
		rsCount.Close
	end if
	set rsCount = Nothing
end if	
'	##	Detect presence of quiz table and total quizzes
if TableExists(strTablePrefix & "QUIZ") then
	'	##	Table exists, get count
	strSql = "SELECT COUNT(QUIZ_ID) AS CNT FROM " & strTablePrefix & "QUIZ WHERE Q_STATUS=1"
	set rsCount = my_Conn.Execute(strSql)
	if not rsCount.EOF then
		intQuizzes=rsCount("CNT")
		rsCount.Close
	end if
	set rsCount = Nothing
end if	
'	##	Detect presence of recipe table and total recipes
if TableExists(strTablePrefix & "RECIPE") then
	'	##	Table exists, get count
	strSql = "SELECT COUNT(RECIPE_ID) AS CNT FROM " & strTablePrefix & "RECIPE"
	set rsCount = my_Conn.Execute(strSql)
	if not rsCount.EOF then
		intRecipes=rsCount("CNT")
		rsCount.Close
	end if
	set rsCount = Nothing
end if	
'	##	Detect presence of album table and total photos
if TableExists(strTablePrefix & "ALBUM") then
	'	##	Table exists, get count
	strSql = "SELECT COUNT(PHOTO_ID) AS CNT FROM " & strTablePrefix & "ALBUM WHERE PHOTO_STATUS=1"
	set rsCount = my_Conn.Execute(strSql)
	if not rsCount.EOF then
		intPhotos=rsCount("CNT")
		rsCount.Close
	end if
	set rsCount = Nothing
end if


'	##	List of counts collected:
'	##
'	##	intMembers      =	Total members
'	##	intTopics       =	Total active (or moderated) topics
'	##	intForumTopics  =	Total active (or moderated) topics in a specified forum
'	##	intReplies      =	Total active (or moderated) replies
'	##	intMessages     =	Total active (or moderated) topics + replies
'	##	intActiveMembers=	Total active members signed in
'	##	intGuests       =	Total guests online
'	##	intAuctionItems =	Total active auction items
'	##	intPolls        =	Total polls
'	##	intQuizzes      =	Total quizzes
'	##	intRecipes      =	Total recipes
'	##	intPhotos       =	Total shared photos in albums
'	##
'	##	To display values using html, include them within < % and  % > (delete spaces), thus:
'	##	Total Members Online:  < %intMembers% > (delete spaces)
'	##
'	##	To display values using asp, follow this example:
'	##	Response.Write	"Total Members Online: " & intMembers
'	##
'	##	To obtain quantity of topics in any given forum, use:
'	##	Call TopicsInForum(1) - where "1" = Forum ID number

my_Conn.Close
set my_Conn = Nothing


Sub TopicsInForum(intForumID)
	'	##	Total Topics in a Particular Forum
	intForumTopics = 0
	strSql = "SELECT COUNT(TOPIC_ID) AS CNT FROM " & strTablePrefix & "TOPICS WHERE T_STATUS=1 AND FORUM_ID=" & intForumID
	set rsCount = my_Conn.Execute(strSql)
	if not rsCount.EOF then
		intForumTopics=rsCount("CNT")
		rsCount.Close
	end if
	set rsCount = Nothing
End Sub

Function TableExists(tabletoFind)
	TableExists = False
	set adoxConn = CreateObject("ADOX.Catalog")
	set adodbConn = Server.CreateObject("ADODB.Connection")
	adodbConn.open(strConnString)
	adoxConn.activeConnection = adodbConn
	isthere = false
	for each table in adoxConn.tables
		if lcase(table.name) = lcase(tabletoFind) then
			isthere = true
			exit for
		end if
	next
	adodbConn.close
	set adodbConn = nothing
	set adoxConn = nothing
	if isthere then TableExists = True
End Function
%>
Last edited by Carefree on 06 November 2010, 13:52
Posted
Carefree
Advanced Member
Carefree
Posts: 4224
4224
See above.
Posted
Lon2
Junior Member
Lon2
Posts: 151
151
Does anyone have a simple script for this? smile
Last edited by Lon2 on 06 November 2010, 17:03
Posted
Carefree
Advanced Member
Carefree
Posts: 4224
4224
Code:

<!--#INCLUDE VIRTUAL="/forums/config.asp"-->
<!--#INCLUDE VIRTUAL="/forums/inc_func_common.asp"-->
<%
dim intTopics
set my_Conn = Server.CreateObject("ADODB.Connection")
my_Conn.Open(strConnString)
strSql = "SELECT COUNT(TOPIC_ID) AS CNT FROM " & strTablePrefix & "TOPICS WHERE T_STATUS = 1 AND FORUM_ID < 4"
set rsCount = my_Conn.Execute(strSql)
if not rsCount.EOF then
	intTopics=rsCount("CNT")
	rsCount.Close
end if
set rsCount = Nothing
Response.Write	"Total Topics: " & intTopics
my_Conn.Close
set my_Conn = Nothing
%>
Posted
Lon2
Junior Member
Lon2
Posts: 151
151
I appologize but I'm somewhat of a newbie. I am able to get your suggestion to work on a page all by itself with just one forum ID count but not on an HTML page with head and body tags. I would like to put a forum name with quantities on another page. For example: "Our Animal forum currently has X Topics" I'm not sure how to do this.
Would I create the script above for every Forum ID I want to get counts for or is there a way to call out individual forum counts without reusing the script for every forum ID?
I appreciate your help on this!
Last edited by Lon2 on 06 November 2010, 18:06
Posted
Carefree
Advanced Member
Carefree
Posts: 4224
4224
That last script was specifically for the group of forums you had mentioned (1-3). If you want to get a variety, you'd be better off using the method I posted in my first reply. Then it would be as simple as "Call TopicsInForum(Forum_ID)", followed with
Code:
Response.Write intForumTopics
. I'll make you a sample web page with this included so you can see how it's done.
Last edited by Carefree on 06 November 2010, 18:46
Posted
Lon2
Junior Member
Lon2
Posts: 151
151
For clarification, I would like to show individual counts for each forum. For example:

Animals - 56 Topics
Mammals - 37 Topics
Reptiles - 9 Topics

Not the 3 combined.
I appreciate your patience with me! smile
Last edited by Lon2 on 06 November 2010, 19:10
Posted
Carefree
Advanced Member
Carefree
Posts: 4224
4224
Here you go: html version. I left the routine to get members because you mentioned that earlier. Notice the lines 20-21
Code:

		intForumID=1
		Call TopicsInForum(intForumID)
That's how you can retrieve information for different forums on demand. Copy those two lines where you want the output, and just change the forum number to the forum you want shown.
For example, to get output for forums 1 & 2, change lines 20-21 to say:

Code:

		intForumID=1
		Call TopicsInForum(intForumID)
		intForumID=2
		Call TopicsInForum(intForumID)


Code:

<html>
	<head>
		<title>Title Here</title>
	</head>
	<body>
		<!--#INCLUDE VIRTUAL="(forumpath)/config.asp"-->
		<!--#INCLUDE VIRTUAL="(forumpath)/inc_func_common.asp"-->
		<%
		dim intMembers, intTopics, intForumID
		set my_Conn = Server.CreateObject("ADODB.Connection")
		my_Conn.Open(strConnString)
		strSql = "SELECT COUNT(MEMBER_ID) AS CNT FROM " & strMemberTablePrefix & "MEMBERS WHERE M_STATUS=1"
		set rsCount = my_Conn.Execute(strSql)
		if not rsCount.EOF then
			intMembers=rsCount("CNT")
			rsCount.Close
		end if
		set rsCount = Nothing

		intForumID=1
		Call TopicsInForum(intForumID)

		my_Conn.Close
		set my_Conn = Nothing

		Sub TopicsInForum(intForumID)
			'	##	Total Topics in a Particular Forum
			intForumTopics = 0
			strSql = "SELECT F_SUBJECT FROM " & strTablePrefix & "FORUM WHERE FORUM_ID=" & intForumID
			set rsTitle = my_Conn.Execute(strSql)
			if not rsTitle.EOF then
				strForumTitle=rsTitle("F_SUBJECT")
				rsTitle.Close
			end if
			set rsTitle=Nothing
			strSql = "SELECT COUNT(TOPIC_ID) AS CNT FROM " & strTablePrefix & "TOPICS WHERE T_STATUS=1 AND FORUM_ID=" & intForumID
			set rsCount = my_Conn.Execute(strSql)
			if not rsCount.EOF then
				intForumTopics=rsCount("CNT")
				rsCount.Close
			end if
			set rsCount = Nothing
			Response.Write	"Our """ & strForumTitle & """ forum currently has " & intForumTopics & " topics.<br>"
		End Sub
		%>
	</body>
</html>

Example output:
Our "Welcome" forum currently has 8 topics. Our "Snitz Modifications" forum currently has 476 topics.
Last edited by Carefree on 09 November 2010, 21:30
Posted
Lon2
Junior Member
Lon2
Posts: 151
151
Thanks again Carefree. I get the following error:

Microsoft VBScript compilation error '800a0411'

Name redefined

/topic_totals/test.asp, line 10

dim intMembers, intTopics, intForumID, strForumTitle
---------------------------------------^
Update: I got it to work by removing strForumTitle from the dim statement but I'm not sure if that's the correct thing to do.
This just doesn't seem right. So I have to repeat the entire routine (everything you have in the body, between these <& &> tags), with server includes, every place on the page that I want totals to be?
Last edited by Lon2 on 07 November 2010, 14:50
Posted
Lon2
Junior Member
Lon2
Posts: 151
151
Does anyone have an easy way to do this?
You Must enter a message