| Author |
 Topic  |
|
Andy Humm
Average Member
United Kingdom
908 Posts |
 Posted - 20 April 2008 : 08:34:23
|
Great mod and all seems to work..
However, I have noticed the following:
Member adds a post
post count =0
Moderator approves post
post count =1
Member deletes his/her post
Post Count =0
then go to members.asp
Post Count=0 as it should however, the next column 'Last Post' still shows todays date.
Should this database field be triggered to reflect the reduction in post counts.
Obviously when a member has multiple posts if a post is deleted the last post will; read his last posting..
Any solutions please...
rgds andy
< |
 |
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 20 April 2008 : 10:40:16
|
quote:
Originally posted by modifichicci
I think it is a little complicate to reset lastpost date, as we have to memorize previous date and when post is deleted we have to ripristinate old date..
hard with simply coding and without a db modification..
You'd just need a SQL query to be executed...
< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 20 April 2008 : 11:27:05
|
Am example query good both for MySQL and SQL Server would be:
SELECT MAX(T_DATE) As T_DATE FROM
(
SELECT MAX(T_DATE) AS T_DATE FROM FORUM_TOPICS WHERE T_AUTHOR=1
UNION
SELECT MAX(R_DATE) AS T_DATE FROM FORUM_REPLY WHERE R_AUTHOR=1
UNION
SELECT MAX(R_DATE) AS T_DATE FROM FORUM_A_REPLY WHERE R_AUTHOR=1
UNION
SELECT MAX(T_DATE) AS T_DATE FROM FORUM_A_TOPICS WHERE T_AUTHOR=1
) As T
You'd just need change it to change it to use the forum_prefix and such and to get the member_id from a suitable forum variable. I don't know the context, so can't help much more.
Running the query will give you the last post date for a user. You can then use it to update it in the members table.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 20 April 2008 : 12:26:12
|
No, T is the alias given to the table resulting from the "inner" UNION. I've just changed the query to have the returning value being named T_VALUE, so insert T_VALUE in M_LASTPOSTDATE in the members table.
I could try a syntax do it all in a single query, but I'm not sure all MySQL versions would support it, or even if the syntax would be valid both for MySQL and SQL Server.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 20 April 2008 : 13:18:00
|
quote:
Originally posted by modifichicci
it is possible to add also A_REPLY and A_TOPICS in query? Thanks for help, I will try to implement this function also..
Yeah, it's possible, just changed the SQL for that.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
modifichicci
Average Member
Italy
787 Posts |
Posted - 20 April 2008 : 15:18:17
|
I have tryed this query:
strUpLastPost = Reply_Autore
strSql = "SELECT MAX(T_VALUE) As T_VALUE FROM "
strSql = strSql & "( SELECT MAX(T_DATE) AS T_VALUE FROM " & strTablePrefix & "TOPICS WHERE T_AUTHOR = " & strUpLastPost
strSql = strSql & " UNION "
strSql = strSql & "SELECT MAX(R_DATE) AS T_VALUE FROM " & strTablePrefix & "REPLY WHERE R_AUTHOR = " & strUpLastPost
strSql = strSql & " UNION "
strSql = strSql & " SELECT MAX(R_DATE) AS T_VALUE FROM " & strTablePrefix & "A_REPLY WHERE R_AUTHOR = " & strUpLastPost
strSql = strSql & " UNION "
strSql = strSql & " SELECT MAX(T_DATE) AS T_VALUE FROM " & strTablePrefix & "A_TOPICS WHERE T_AUTHOR = " & strUpLastPost
strSql = strSql & ") As T "
response.write strSql
set rs = my_Conn.Execute (strSql)
DataUltimoPost = rs("T_VALUE")
rs.close
set rs = nothing
strSql = "UPDATE " & strMemberTablePrefix & "MEMBERS "
strSql = strSql & " SET M_LASTPOSTDATE = '" & DataUltimoPost & "'"
strSql = strSql & " WHERE MEMBER_ID = " & strUpLastPost
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
and I get this strSql and the error below..
SELECT MAX(T_VALUE) As T_VALUE FROM ( SELECT MAX(T_DATE) AS T_VALUE FROM FORUM_TOPICS WHERE T_AUTHOR = 1 UNION SELECT MAX(R_DATE) AS T_VALUE FROM FORUM_REPLY WHERE R_AUTHOR = 1 UNION SELECT MAX(R_DATE) AS T_VALUE FROM FORUM_A_REPLY WHERE R_AUTHOR = 1 SELECT MAX(T_DATE) AS T_VALUE FROM FORUM_A_TOPICS WHERE T_AUTHOR = 1) As T
error '80040e14'
some advices?< |
Ernia e Laparocele
Forum di Ernia e Laparocele
Acces - MySql Migration Tutorial
Adamantine forum |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
modifichicci
Average Member
Italy
787 Posts |
Posted - 20 April 2008 : 16:04:34
|
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-4.0.27-standard-log]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 MAX(T_DATE) AS T_DATE FROM FORUM_TOPICS WHERE T_AUTHOR
I have changed T_VALUE to T_DATE, as in original Ruirib query, but the same error with T_VALUE instead of T_DATE
< |
Ernia e Laparocele
Forum di Ernia e Laparocele
Acces - MySql Migration Tutorial
Adamantine forum |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Topic |
|
Decrease members post count on post deletion
