Snitz Forums 2000 - Update and Looping Question

Snitz Forums 2000

Username:	Password:
Save Password
Forgot your Password?

All Forums

Community Forums

Code Support: ASP (Non-Forum Related)

Update and Looping Question

New Topic

Topic Locked

Printer Friendly

Author

Topic

cripto9t
Average Member

USA
881 Posts

Posted - 15 December 2004 : 09:41:29

I need to update the post count in the forum table with a number of replies. I've come up with 2 ways.
I'd like to know if one would have an advantage over the other.

example 1 (Add replies one at a time)

for i = 0 to ReplyCount
        '## do other things
        strSql = "UPDATE " & strTablePrefix & "FORUM"
        strSql = strSql & " SET F_COUNT = F_COUNT + 1"
        strSql = strSql & ", F_A_COUNT = F_A_COUNT - 1"
        strSql = strSql & " WHERE FORUM_ID = "ForumID
next

example 2 (Count replies and add all at once)

intReply = 0
for 1 = 0 to ReplyCount
        '## do other things
        intReply = intReply + 1
next

strSql = "UPDATE " & strTablePrefix & "FORUM"
strSql = strSql & " SET F_COUNT = F_COUNT + " & intReply
strSql = strSql & ", F_A_COUNT = F_A_COUNT - " & intReply
strSql = strSql & " WHERE FORUM_ID = "ForumID

I hope that's clear

.
Thanks, Brian

_-/Cripto9t\-_

dayve
Forum Moderator

USA
5820 Posts

Posted - 15 December 2004 : 10:40:10

it might be more clear if you explain what your goal is. the replies count already exists in the FORUM_TOPICS table so based on what it is you're trying to accomplish, you can pull the summary of those results.

cripto9t
Average Member

USA
881 Posts

Posted - 15 December 2004 : 11:42:53

I am moving a topic and its replies from the forum archive back to the active forum.
I have to update the post count with the replies in the FORUM and TOTALS tables.

Heres what I have now


                                        for iReply = 0 to recReplyCount

                                                RCatID = allReplyData(rCat_ID,iReply)
                                                RForumID = allReplyData(rForum_ID,iReply)
                                                RTopicID = allReplyData(rTopic_ID,iReply)
                                                RReplyID = allReplyData(rReply_ID,iReply)
                                                RStatus = allReplyData(rR_Status,iReply)
                                                RAuthor = allReplyData(rR_Author,iReply)
                                                RMessage = allReplyData(rR_Message,iReply)
                                                RDate = allReplyData(rR_Date,iReply)
                                                RIp = allReplyData(rR_IP,iReply)
                                                RLastEdit = allReplyData(rR_Last_Edit,iReply)
                                                tmpRLastEditby = allReplyData(rR_Last_Editby,iReply)
                                                RSig = allReplyData(rR_Sig,iReply)

                                                if isnull(tmpRLastEditby) then
				                        RLastEditby = "NULL"
			                        else
				                        RLastEditby = tmpRLastEditby
			                        end if
                                                strRValues = "" & RCatID & ", " & RForumID & ", " & RTopicID & ", " & RReplyID & ", " & RStatus & ", " & RAuthor & ", '" & chkString(RMessage,"archive") & "', '" & RDate & "', '" & RIp & "', '" & RLastEdit & "', " & RLastEditby & ", " & RSig & " "
		                                
                                                strsql = "INSERT INTO " & strTablePrefix & "REPLY (CAT_ID"
                                                strSql = strSql & ", FORUM_ID"
                                                strSql = strSql & ", TOPIC_ID"
                                                strSql = strSql & ", REPLY_ID"
                                                strSql = strSql & ", R_STATUS"
                                                strSql = strSql & ", R_AUTHOR"
                                                strSql = strSql & ", R_MESSAGE"
                                                strSql = strSql & ", R_DATE"
                                                strSql = strSql & ", R_IP"
                                                strSql = strSql & ", R_LAST_EDIT"
                                                strSql = strSql & ", R_LAST_EDITBY"
                                                strSql = strSql & ", R_SIG)"
		                                strsql = strsql & " VALUES (" & strRValues & ")"
                                                
                                                my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords

			                        '#### Delete From A_REPLY
                                                strSql = "DELETE FROM " & strTablePrefix & "A_REPLY "
					        strSql = strSql & " WHERE REPLY_ID = " & cLng(RReplyID)
					        my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords

                                                '## Update forum table count with reply
			                        strSql = "UPDATE " & strTablePrefix & "FORUM"
			                        strSql = strSql & " SET F_COUNT = F_COUNT + 1"
                                                strSql = strSql & ", F_A_COUNT = F_A_COUNT - 1"   
			                        strSql = strSql & " WHERE FORUM_ID = " & Forum_ID
			                        my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords

                                                '## Update totals table with reply
                                                strSql = "UPDATE " & strTablePrefix & "TOTALS "
                                                strSql = strSql & "SET P_COUNT = P_COUNT + 1"
                                                strSql = strSql & ", P_A_COUNT = P_A_COUNT - 1"
                                                
                                                set rs = nothing
                                                my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords

                                                intReply = intReply + 1
                                        next

As you can see I'm updating the counts as I move the reply and this code does work.
Would it be better to count the replies and then do the count updates after all the replies are moved?

_-/Cripto9t\-_

pdrg
Support Moderator

United Kingdom
2897 Posts

Posted - 16 December 2004 : 05:03:48

as a rule, yes. Databases are best at doing batches of things, and every db connection carries an overhead - so in general it is better to make the least number of hits on the db possible, but make those hits as encompassing as possible (for instance, if you can get all the data for an asp page in a single db query it is better than having 5 queries as the page loads)

However, there is an interestin g transactional problem here - what would happen if you hit a glitch partway through - the figures would be out-of-sync with the #replies

If I were starting out to do what you're doing here is how I'd personally approach it:

begin a transaction

Move x records (ie update them with some where clause - at this stage x is unknown)

we now know x = @@rowcount (for sql server - other db's may have similar syntaxes)

increment/decrement counts by x

commit transaction

Now you can guarantee you will not miscount rows, nothing will happen in-between page refreshes, if the code bums out, the counts stay good

hth
EDIT: [ul][li][/ul] was a good, but wrong guess at forum code for an unnumbered list ;-)

Edited by - pdrg on 16 December 2004 05:05:39

cripto9t
Average Member

USA
881 Posts

Posted - 16 December 2004 : 07:24:20

Thanks pdrg for the answer and explanation. That's what I wanted to know.
It helps me alot