Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Help Groups for Snitz Forums 2000 Users
 Help: MOD Implementation
 pmcount for unread messages		  New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

MarkJH
Senior Member

United Kingdom
1722 Posts
Posted - 29 November 2004 :  13:05:16  Show Profile  Visit MarkJH's Homepage
In inc_header, the following code:

' Get Private Message count for display
	if strDBType = "access" then
		strSqL = "SELECT count(M_TO) as [pmcount] "
	else
        	strSqL = "SELECT count(M_TO) as pmcount "
    	end if
	strSql = strSql & " FROM " & strMemberTablePrefix & "MEMBERS , " & strTablePrefix & "PM "
	strSql = strSql & " WHERE " & strMemberTablePrefix & "MEMBERS.M_NAME = '" & strDBNTUserName & "'"
	strSql = strSql & " AND " & strMemberTablePrefix & "MEMBERS.MEMBER_ID = " & strTablePrefix & "PM.M_TO "
	strSql = strSql & " AND " & strTablePrefix & "PM.M_READ = 0 "

	Set rsPM = my_Conn.Execute(strSql)
	pmcount = rsPM("pmcount")

	rsPM.close
	set rsPM = nothing


Now changing the line:

strSql = strSql & " AND " & strTablePrefix & "PM.M_READ = 0 "


to

strSql = strSql & " AND " & strTablePrefix & "PM.M_READ = 1 "


gives me the total amount of unread messages.

Is there a way I can set another variable (say, pmread) in that code block so I end up with a total for unread and a total for read messages? If not, do I have to open a second connection to the database, like this?

' Get Private Message count for display
	if strDBType = "access" then
		strSqL = "SELECT count(M_TO) as [pmcount] "
	else
        	strSqL = "SELECT count(M_TO) as pmcount "
    	end if
	strSql = strSql & " FROM " & strMemberTablePrefix & "MEMBERS , " & strTablePrefix & "PM "
	strSql = strSql & " WHERE " & strMemberTablePrefix & "MEMBERS.M_NAME = '" & strDBNTUserName & "'"
	strSql = strSql & " AND " & strMemberTablePrefix & "MEMBERS.MEMBER_ID = " & strTablePrefix & "PM.M_TO "
	strSql = strSql & " AND " & strTablePrefix & "PM.M_READ = 0 "

	Set rsPM = my_Conn.Execute(strSql)
	pmcount = rsPM("pmcount")

	rsPM.close
	set rsPM = nothing

	if strDBType = "access" then
		strSqL = "SELECT count(M_TO) as [pmread] "
	else
        	strSqL = "SELECT count(M_TO) as pmread "
    	end if
	strSql = strSql & " FROM " & strMemberTablePrefix & "MEMBERS , " & strTablePrefix & "PM "
	strSql = strSql & " WHERE " & strMemberTablePrefix & "MEMBERS.M_NAME = '" & strDBNTUserName & "'"
	strSql = strSql & " AND " & strMemberTablePrefix & "MEMBERS.MEMBER_ID = " & strTablePrefix & "PM.M_TO "
	strSql = strSql & " AND " & strTablePrefix & "PM.M_READ = 1 "

	Set rsPM = my_Conn.Execute(strSql)
	pmread = rsPM("pmread")

	rsPM.close
	set rsPM = nothing
Bandlink.net - http://www.bandlink.net/
Bandlink Music Forums - http://www.bandlink.net/forum/
Edited by - MarkJH on 29 November 2004 13:09:23

laser
Advanced Member

Australia
3859 Posts
Posted - 29 November 2004 :  15:19:20  Show Profile
I think that's the only way to do it.
Go to Top of Page

MarkJH
Senior Member

United Kingdom
1722 Posts
Posted - 30 November 2004 :  18:57:08  Show Profile  Visit MarkJH's Homepage
Okay. Thanks, Laser.
Bandlink.net - http://www.bandlink.net/
Bandlink Music Forums - http://www.bandlink.net/forum/
Go to Top of Page

dayve
Forum Moderator

USA
5820 Posts
Posted - 30 November 2004 :  20:06:30  Show Profile  Visit dayve's Homepage
It can be done in 1 statement... I did a quick Access query using Group By/Having to get the desired result. Just convert this to your forum code above. I'd do it but I'm in a rush. If you need additional help, I'll provide the forum code later, but you do not have to do 2 separate queries.


SELECT FORUM_PM.M_READ, Count(FORUM_PM.M_ID) AS totalReadUnread
FROM FORUM_PM
GROUP BY FORUM_PM.M_READ, FORUM_PM.M_TO
HAVING (((FORUM_PM.M_TO)=2))  this is the member id
ORDER BY FORUM_PM.M_READ;


More help... http://www.w3schools.com/sql/sql_groupby.asp
Edited by - dayve on 30 November 2004 20:10:09
Go to Top of Page

MarkJH
Senior Member

United Kingdom
1722 Posts
Posted - 01 December 2004 :  12:10:45  Show Profile  Visit MarkJH's Homepage
quote:
If you need additional help, I'll provide the forum code later, but you do not have to do 2 separate queries.
That would be great if you could. Thanks, Dayve.
Bandlink.net - http://www.bandlink.net/
Bandlink Music Forums - http://www.bandlink.net/forum/
Go to Top of Page

dayve
Forum Moderator

USA
5820 Posts
Posted - 03 December 2004 :  18:42:48  Show Profile  Visit dayve's Homepage
Tested on my forum, this should work:

        Dim strUnread, strRead, strPM
        strUnread = 0:strRead = 0

	strPM  = "SELECT " & strTablePrefix & "PM.M_READ, Count(" & strTablePrefix & "PM.M_ID) AS totalReadUnread " &_
                 "FROM " & strTablePrefix & "PM " &_
	         "GROUP BY " & strTablePrefix & "PM.M_READ, " & strTablePrefix & "PM.M_TO " &_
	         "HAVING ((" & strTablePrefix & "PM.M_TO)=" & MemberID & ") " &_
	         "ORDER BY " & strTablePrefix & "PM.M_READ "

	Set rsPM = my_Conn.Execute(strPM)
	 Do While NOT rsPM.EOF
	  if rsPM("m_read") = "0" then strUnread = rsPM("totalReadUnread")
	  if rsPM("m_read") = "1" then strRead = rsPM("totalReadUnread")
	   rsPM.MoveNext
	 Loop
	rsPM.close
	set rsPM = nothing

strUnread will be the result for Unread Messages, strRead will be the result for Read messages. You can pop
these variables in basically anwhere you want. let me know if you need more help.

The nice thing about my query is that it only hits 1 table. Not really sure why the older method used 2 tables.
I check the member id whereas the older one checks the members name... not the best way to do it.
Edited by - dayve on 03 December 2004 18:48:07
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.37 seconds. Powered By: Snitz Forums 2000 Version 3.4.07