Web Wiz Forums Modifications - REQ: Update Members Post Counts

REQ: Update Members Post Counts

Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums Modifications
Forum Description: Mod's and Add-on's for Web Wiz Forums.
URL: https://forums.webwiz.net/forum_posts.asp?TID=21513
Printed Date: 28 March 2026 at 2:21pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: REQ: Update Members Post Counts

Posted By: MortiOli
Subject: REQ: Update Members Post Counts
Date Posted: 08 October 2006 at 2:01am

I understand there's the tool to resync topic and post counts, but am I right in saying that this is just counts for each forum?

The reason I ask is that I have a lot of members with minus post counts (no idea why...probably from the Snitz forum I had a while back). I also have a moderator who thought it would be amusing to change their post count to ~10 times the amount they have.

Is there anyway to run a script to update members post counts?

Cheers!

Replies:

Posted By: WebWiz-Bruce
Date Posted: 08 October 2006 at 3:03pm

Sorry there isn't

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: MortiOli
Date Posted: 05 November 2006 at 12:17am

Any chance this could be moved to the mod forum, as a request please?

Posted By: MortiOli
Date Posted: 18 November 2006 at 10:28am

Anyone interested in creating this please?

Posted By: Ali Bilgrami
Date Posted: 18 November 2006 at 6:15pm

well you can run an SQL query on your DB with some criteria to increase the no of posts times X or +X...

-------------
Lets!

Posted By: MortiOli
Date Posted: 19 November 2006 at 12:40pm

I don't really want to increase / decrease people's posts by X amount, but I want to run something that counts everyone's posts and topics, then updates their member profile to reflect their true post count.

Posted By: MortiOli
Date Posted: 08 January 2007 at 9:03pm

Following on from this...

I remembered I had a script to do exactly this on Snitz forums. Basically you ran the page and it went off, counted up all topics created by each person, and all replies made that person. Obviously the end result was there post count, which it updated their profile with.

I've tried to go through it and update it to reflect WWF tables etc, but I've hit a dead end with some of them. If someone could look at what I have and help out, that would be brilliant;

<!--#INCLUDE FILE="config.asp" -->
<!--#INCLUDE FILE="inc_func_common.asp" -->
<%
Server.ScriptTimeOut = 180 ' drv: Set to 3 minutes or set higher if needed. I have no idea.
set my_Conn = Server.CreateObject("ADODB.Connection")
my_Conn.Open strConnString
Call GetMembers()
my_Conn.Close
set my_Conn = nothing

Sub GetMembers()
  dim strSql, rs
  strSql = "SELECT " & strDbTable & "Author.Author_ID, " &_
   strDbTable & "Author.No_of_posts, " &_
   strDbTable & "Author.Username " &_
   " FROM " &  strDbTable & "Author " &_
   " WHERE " & strDbTable & "Author.No_of_posts > 0 " &_
   " ORDER BY " & strDbTable & "Author.Author_ID;"
  set rs = Server.CreateObject("ADODB.Recordset")
  rs.open  strSql,  my_Conn
  response.write("<table cellpadding=""2"" cellspacing=""0"" border=""1"">" & vbCrLf &_
   "<tr valign=""bottom"" align=""center""><td align=""left""><b>Member's Name</b></td><td><b>M_Post<br>Count</b></td><td><b>Record<br>Count</b></td></tr>" & vbCrLf)
  If not (rs.EOF or rs.BOF) then  '## No replies found in DB
   do until rs.EOF
    response.write("<tr><td><b>" & rs.Fields("Username").Value & "</b></td><td align=""right"">" & rs.Fields("No_of_posts").Value & "</td><td align=""right"">")
    if (rs.Fields("No_of_posts").Value > 0) then Call GetPosts(rs.Fields("Author_ID").Value,rs.Fields("No_of_posts").Value)
    response.write("</td></tr>" & vbCrLf)
    rs.MoveNext
   loop
  End If
  response.write("</table>")
  rs.close
  set rs = nothing
End Sub
Sub GetPosts(intMemberID,intPostCount)
  dim strSql, rs, memberpostcount
  memberpostcount = intPostCount
  strSql = "(SELECT " & strDbTable & "Author.Author_ID, " &_
   strTablePrefix & "Topic.Topic_ID, " &_
   " 0 AS REPLY_ID, " &_
   strTablePrefix & "Topic.T_DATE AS R_DATE " &_
   " FROM " & strDbTable & "Author LEFT JOIN " & strTablePrefix & "Topic ON " &_
   strDbTable & "Author.Author_ID = " & strTablePrefix & "Topic.Start_Thread_ID " &_
   " WHERE (((" & strDbTable & "Author.Author_ID)=" & intMemberID & "))) UNION ALL " &_
   " (SELECT " & strDbTable & "Author.Author_ID, " &_
   strTablePrefix & "REPLY.Topic_ID, " &_
   strTablePrefix & "REPLY.REPLY_ID, " &_
   strTablePrefix & "REPLY.R_DATE " &_
   " FROM " & strDbTable & "Author LEFT JOIN " & strTablePrefix & "REPLY ON " &_
   strDbTable & "Author.Author_ID = " & strTablePrefix & "REPLY.R_AUTHOR " &_
   " WHERE (((" & strDbTable & "Author.Author_ID)=" & intMemberID & "))) " &_
   " ORDER BY R_DATE DESC;"
  set rs = Server.CreateObject("ADODB.Recordset")
  'rs.cachesize = strPageSize
  rs.open  strSql,  my_Conn, 3
  If not (rs.EOF or rs.BOF) then  '## No replies found in DB
   rs.movefirst
   response.write(rs.Recordcount)
   do until rs.EOF
    If (rs.Fields("REPLY_ID").Value > 0) Then
     Call UpdateReply(rs.Fields("REPLY_ID").Value,memberpostcount)
    Else
     If (rs.Fields("Topic_ID").Value > 0) Then Call UpdateTopic(rs.Fields("Topic_ID").Value,memberpostcount)
    End If
    ' drv: subtract one from post count
    If (memberpostcount > 0) then memberpostcount = memberpostcount - 1
    rs.MoveNext
   loop
  End If
  rs.close
  set rs = nothing
End Sub
Sub UpdateReply(intReplyID,intMemberPostCount)
 dim strSql
 '## Forum_SQL
 strSql = "UPDATE " & strTablePrefix & "REPLY " &_
  " SET " & strTablePrefix & "REPLY.R_POSTS = " & intMemberPostCount & " " &_
  " WHERE (" & strTablePrefix & "REPLY.REPLY_ID = " & intReplyID & ");"
 my_conn.Execute (strSql)
End Sub
Sub UpdateTopic(intTopicID,intMemberPostCount)
 dim strSql
 '## Forum_SQL
 strSql = "UPDATE " & strTablePrefix & "Topic " &_
  " SET " & strTablePrefix & "Topic.T_POSTS = " & intMemberPostCount & " " &_
  " WHERE (" & strTablePrefix & "Topic.Topic_ID = " & intTopicID & ");"
 my_conn.Execute (strSql)
End Sub
%>

Many thanks!

Posted By: MortiOli
Date Posted: 25 March 2007 at 6:04pm

Using the above as an idea, would someone be able to help me in coding an ASP page to do the following;

- Count all posts for each user

- Update each users No_of_Posts depending on the above

I've been running some queries on my database, and found that my forum and database says 126,456 posts, however, running a SUM on all users post counts, shows 148,596 - a difference of 22,140 between the actual count and members profiles.

Not too sure where this difference has occurred (maybe during my Snitz conversion), but running a search query on this forum shows me as having posted 212 posts, but my user profile shows 259...? Strange. Maybe posts don't always update with deletions etc?

Posted By: MrMellie
Date Posted: 26 March 2007 at 9:44am

Oli, I could probably sort this for you. I had to do something similar for the Photo Album to resync the image and comment counts whilst I was developing it. I'm sure it could be simply changed to do what you require.

Posted By: MortiOli
Date Posted: 26 March 2007 at 12:18pm

If you don't mind, that would be great!

Thanks

Posted By: MrMellie
Date Posted: 26 March 2007 at 10:18pm

You have email Oli.

Posted By: MortiOli
Date Posted: 27 March 2007 at 12:44pm

Thanks Ric!

Testing it now - a couple of problems, but due to the server timeout settings, so I've had to temporarily change them in common.asp so it continues to run to update each member (setting them in the update file doesn't work, as common.asp seems to override it).

Thanks again!

Posted By: MortiOli
Date Posted: 27 March 2007 at 12:58pm

Just completed running it on my test forum, and not sure why, but there's a difference in numbers...

Forum says 124,280 (which a count on tblThreads also shows)

Sum of No_of_posts in tblAuthor shows 124,177

Strange

but a lot closer than it was! Any ideas?

Posted By: MrMellie
Date Posted: 27 March 2007 at 1:13pm

Have you ever deleted a user? Just wondering if there's some threads that relate to a now non-existant author_id?

edit: scratch that, there's a foreign key constraint on author_id that doesn't allow threads with non-existant author_id's.

Posted By: MrMellie
Date Posted: 27 March 2007 at 1:38pm

OK, just incase there are somehow some threads with non-existant authors (maybe created from the Snotz transfer?) try running this SQL against the database. It will show any author_id's in tblThreads with no matching author.

select distinct T.author_id, A.Username
from tblThread T left join tblAuthor A on A.author_id=T.Author_id
where username is null

Posted By: MortiOli
Date Posted: 27 March 2007 at 6:37pm

Bingo!

There's 103 replies which belong to an Author_ID of 2, but no author exists.

124,177 + 103 = 124,280

Now, is it a simple case of deleting those replies, or will that knock something out somewhere? I've already checked to see if they're showing on the forum, which they're not. I don't think there's anyway of inserting a user of Author_ID = 2 into the database, as Guest uses that number...I think.

EDIT - I created a new user, and edited the Author_ID = 2 to reflect the new user's ID. The posts have appeared on the forum now, so looks like that'll solve the problem. I've done all this on a test forum, so please let me know if you can think of anything that this will affect, before running on my live forum.

Cheers Ric!

Posted By: MortiOli
Date Posted: 27 March 2007 at 7:03pm

Tell a lie on the above, there was 104 replies. But I ran the post resync and it seems to have sorted it out.

Posted By: MrMellie
Date Posted: 27 March 2007 at 7:04pm

Yeah, Guest is ID 2. You could look on the old Snotz user table and see whose ID that was. Did you assign them a new ID or just dump them? If they've got a new ID, just replace the 2's in the thread table with his/her new ID.

Edit: Excellent.