I saw a function on another forum that I would like to have... In all users profiles there was a percentage of how many post that had been deleted of the total post count on that user. I did a brain fart and had a quick look at pop_delete.asp. Is it as simple as adding this (please check the code for errors...) to pop_delete.asp to add "1" to M_DELPOST for the user that did the post/reply?
Under case "DeleteReply":

strSql = " UPDATE " & strMemberTablePrefix & "MEMBERS M" & _
"SET M.M_DELPOST = M.M_DELPOST + 1 "" & _
" WHERE M.MEMBER_ID = T.R_AUTHOR "

my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords

Under case "DeleteTopic":

strSql = " UPDATE " & strMemberTablePrefix & "MEMBERS M" & _
"SET M.M_DELPOST = M.M_DELPOST + 1 "" & _
" WHERE M.MEMBER_ID = T.R_AUTHOR "

my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords

Then just get that figure from the db and run it against total post count on the user and show the percentage in his/her profile.
Am I totally in the blue here? =)<

It can be done without the need for an additional field; grab the number of posts a member has made from the TOPICS and REPLY tables, subtract that from M_POSTS and use the resulting figure to calculate your percentage. The drawback to this solution is that it requires an extra database hit.
<

It would only be used when viewing a profile so one extra hit wouldn't be that much of a problem.
So, something like:

COUNT("SELECT TOPICS FROM FORUM_TOPICS WHERE T_AUTHOR = ppMEMBER_ID" + "SELECT REPLIES FROM FORUM_REPLIES WHERE R_AUTHOR = ppMEMBER_ID") AS MEMBER_POST_COUNT
to count the number of posts?
Then like:
COUNT("M_POSTS - MEMBER_POST_COUNT") AS MEMBER_POST_PERCENTAGE

I have no clue how the syntax should be, I'm just having another brainfart..
(I really like that word =))
<

What database are you using again so's I can give you the necessary code?
<

MySQL, sorry =)
<

You don't know what version, do you? Going to try and do it with a nested query but older versions of MySQL don't support them.
<

DBMS Name MySQL
DBMS Version 4.0.20a-nt

I suppose this is the mysql version, found it in admin home under "server information". <

Hmm ... think that version should support nested queries so give the following a try:

Code:

set rscount=my_conn.execute("SELECT COUNT(*) AS inttcount, (SELECT COUNT(*) AS intrcount FROM "&strTablePrefix&"REPLY WHERE R_AUTHOR="&ppMember_ID&") FROM "&strTablePrefix&"TOPICS WHERE T_AUTHOR="&ppMember_ID)
intpcount=clng(rscount("inttcount"))+clng(rs("intrcount"))
rscount.close
set rscount=nothing
intdpercentage=abs(round(((intTotalMemberPosts-intpcount)/intTotalMemberPosts)*100))

<

heh, that query looks more cool than mine ;)

Thanks alot! I'll give it a try! <

You're welcome
<

Oops, I got this:

Microsoft OLE DB Provider for ODBC Drivers fel '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*) AS intrcount FROM FORUM_REPLY WHERE R_AUTHOR=50

/pop_profile.asp, line 839

also, this won't count archived posts I suppose? <

Shaggy: you think there is a problem with the mysql version? I get the same error when running the sql directly into the db.
<

No luck on this one yet, anyone have any ideas? <

bumpetibump... Shaggy: do you think it has something to do with the db version? <

I just noticed that this error only show if the user has done any posts... <

Any MySQL-guru out there that can have a look at the sql-string?<

I solved this by splitting the sql-string like this:

if intTotalMemberPosts > 0 then
set rscount1=my_conn.execute("SELECT COUNT(*) AS inttcount FROM "&strTablePrefix&"TOPICS WHERE T_AUTHOR="&ppMember_ID&"")
set rscount2=my_conn.execute("SELECT COUNT(*) AS intrcount FROM "&strTablePrefix&"REPLY WHERE R_AUTHOR="&ppMember_ID&"")
intpcount=clng(rscount1("inttcount"))+clng(rscount2("intrcount"))
rscount1.close
rscount2.close

set rscount1=nothing
set rscount2=nothing
intdpercentage=abs(round(((intTotalMemberPosts-intpcount)/intTotalMemberPosts)*100))

Is there any reason I can't do it like this? I get a number and it might be correct.. I can't really tell =)
<

Nope, no reason at all; it does the same thing. Curious as to why the nested query didn't work, though, maybe that version of MySQL just doesn't support them - can you response.write it and post it here 'til we double check?
<

What do you want response write:d? =)
The splitted string or the first string? <

My original, nested query.
<

I can't do a response.write since I get an error on the sql-string before the response.write is done.. Or can I do it in another way? <