| Author |
 Topic  |
|
tribaliztic
Senior Member
Sweden
1532 Posts |
 Posted - 30 March 2006 : 03:56:13
|
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? =)< |
/Tribaliztic
- www.gotlandrace.se -
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 30 March 2006 : 06:21:46
|
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.
< |
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
 |
|
|
tribaliztic
Senior Member
Sweden
1532 Posts |
Posted - 30 March 2006 : 06:28:01
|
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 =))
< |
/Tribaliztic
- www.gotlandrace.se -
|
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 30 March 2006 : 06:45:43
|
What database are you using again so's I can give you the necessary code?
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
tribaliztic
Senior Member
Sweden
1532 Posts |
Posted - 30 March 2006 : 06:46:33
|
MySQL, sorry =)
< |
/Tribaliztic
- www.gotlandrace.se -
|
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 30 March 2006 : 06:52:21
|
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.
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
tribaliztic
Senior Member
Sweden
1532 Posts |
Posted - 30 March 2006 : 06:59:13
|
DBMS Name MySQL
DBMS Version 4.0.20a-nt
I suppose this is the mysql version, found it in admin home under "server information".
< |
/Tribaliztic
- www.gotlandrace.se -
|
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 30 March 2006 : 07:09:42
|
Hmm ... think that version should support nested queries so give the following a try: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))< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
tribaliztic
Senior Member
Sweden
1532 Posts |
Posted - 30 March 2006 : 07:14:41
|
heh, that query looks more cool than mine ;)
Thanks alot! I'll give it a try!
< |
/Tribaliztic
- www.gotlandrace.se -
|
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 30 March 2006 : 07:15:40
|
You're welcome 
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
tribaliztic
Senior Member
Sweden
1532 Posts |
Posted - 30 March 2006 : 08:35:27
|
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?
< |
/Tribaliztic
- www.gotlandrace.se -
|
|
|
|
tribaliztic
Senior Member
Sweden
1532 Posts |
Posted - 03 April 2006 : 06:23:24
|
Shaggy: you think there is a problem with the mysql version? I get the same error when running the sql directly into the db.
< |
/Tribaliztic
- www.gotlandrace.se -
|
|
|
|
tribaliztic
Senior Member
Sweden
1532 Posts |
Posted - 13 April 2006 : 03:05:55
|
No luck on this one yet, anyone have any ideas?
< |
/Tribaliztic
- www.gotlandrace.se -
|
|
|
|
tribaliztic
Senior Member
Sweden
1532 Posts |
Posted - 18 April 2006 : 10:20:47
|
bumpetibump...
Shaggy: do you think it has something to do with the db version?
< |
/Tribaliztic
- www.gotlandrace.se -
|
|
|
|
tribaliztic
Senior Member
Sweden
1532 Posts |
Posted - 03 May 2006 : 04:56:53
|
I just noticed that this error only show if the user has done any posts...
< |
/Tribaliztic
- www.gotlandrace.se -
|
|
|
|
tribaliztic
Senior Member
Sweden
1532 Posts |
Posted - 09 May 2006 : 09:55:01
|
| Any MySQL-guru out there that can have a look at the sql-string?< |
/Tribaliztic
- www.gotlandrace.se -
|
|
|
|
Topic |
|
save percentage deleted posts/user
