| Author |
 Topic  |
|
|
GauravBhabu
Advanced Member
4288 Posts |
 Posted - 27 January 2002 : 12:21:09
|
Just noticed when sorting by Title the display on members page is not sorted correctly.
The reason as I found is that Titles are not being saved in the Members Table rather they are being fetched at the time of display from the Application variables populated with the values saved in the config table.
Though seems to be a behaviour by design but to a user it appears to be a bug. When someone clicks on "Title" on the members page, it would be an expectation that Member names will display sorted by Title. But it is not happening so.
A workaround will be to update the members table with the appropriate title when messages are posted. But that at times will/may be in conflict with the custom titles which are assigned to Members and that will also need to be taken into account.
Is it a Bug? or A Topic for Development Discussions.
www.forumSquare.com - GauravBhabu - It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying. |
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 27 January 2002 : 18:41:57
|
I added another column to Order By Clause that seems to have helped to some extent.
Changes are marked in red
case "levelasc"
strSql = strSql & " ORDER BY Trim(" & strMemberTablePrefix & "MEMBERS.M_TITLE) ASC, " & strMemberTablePrefix & "MEMBERS.M_POSTS DESC, " & strMemberTablePrefix & "MEMBERS.M_NAME ASC"
case "leveldesc"
strSql = strSql & " ORDER BY Trim(" & strMemberTablePrefix & "MEMBERS.M_TITLE) DESC, " & strMemberTablePrefix & "MEMBERS.M_POSTS ASC, " & strMemberTablePrefix & "MEMBERS.M_NAME ASC"
www.forumSquare.com - GauravBhabu - It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying. |
 |
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 29 January 2002 : 22:37:26
|
| How about just removing the option to sort by title, since the title (at least the ranking titles) are not available in the database query. |
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 29 January 2002 : 22:42:13
|
Pehaps have the sort by title filter out non cutom titles?
Nathan Bales - Romans 15:13
---------------------------------
Snitz Exchange | Mod Resource |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 30 January 2002 : 00:39:27
|
Removing the option to sort by Title is a different approach.
Here is one Option and IMO it will keep this option available:
1. Store the Ranking Titles in the database. (Column is anyways there and make use of it)
2. The Title column gets updated when member posts a message and new post results in a new Rank
3. Custom titles are already stored in the database
To avoid the Custom Titles from being reset to Ranking Title use the Status Column.
Status Column
0 = Locked Member
1 = Active Member
2 = Member with Custom Title
4. Instead of calling the function to determine the Title, Use the Title display from the database, this column is included in most queries to the database.
www.forumSquare.com - GauravBhabu - It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying. |
|
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 30 January 2002 : 00:56:35
|
| we'll just leave it as is. I don't see changing the way Titles are displayed just to allow sorting by title on the members page. |
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 30 January 2002 : 01:20:49
|
I have successfully sorted out non custom titles using this code.
case "namedesc"
strSql = strSql & " ORDER BY " & strMemberTablePrefix & "MEMBERS.M_NAME DESC"
case "levelasc"
strSql = strSql & "AND " & strMemberTablePrefix & "MEMBERS.M_TITLE <> '' ORDER BY " & strMemberTablePrefix & "MEMBERS.M_TITLE ASC, " & strMemberTablePrefix & "MEMBERS.M_NAME ASC"
case "leveldesc"
strSql = strSql & "AND " & strMemberTablePrefix & "MEMBERS.M_TITLE <> '' ORDER BY " & strMemberTablePrefix & "MEMBERS.M_TITLE DESC, " & strMemberTablePrefix & "MEMBERS.M_NAME ASC"
case "lastpostdateasc"
strSql = strSql & " ORDER BY " & strMemberTablePrefix & "MEMBERS.M_LASTPOSTDATE ASC, " & strMemberTablePrefix & "MEMBERS.M_NAME ASC"
case "lastpostdatedesc"
You can see the results here -->
http://www.ls3k.com/dsa/forum/members.asp?UserName=&FirstName=&LastName=&INITIAL=&link=sort&mode=search&M_NAME=&method=levelasc
Nathan Bales - Romans 15:13
---------------------------------
Snitz Exchange | Mod Resource |
|
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 30 January 2002 : 02:13:12
|
I added the highlighted code that Nathan posted.
GauravBhabu, does this work ok for you? |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 30 January 2002 : 02:50:20
|
This code will show only the members with custom titles. All other members will not be fethed from the database and will not be displayed.
Sorting of custom titles is not a problem.
When sorting by Title, currently it shows something like this.
New Member
Starting Member
Starting Member
Starting Member
Junior Member
Starting Member
Starting Member
Starting Member
Starting Member
Notice Junior Member appearing between Starting members
This happens because if the Title field if empty the list is sorted by Names instead.
The one option is as I posted above to use the Status Column etc. Another can be to use the one Posted in my second post above.
I have also used the one below at my site.
case "levelasc"
strSql = strSql & " ORDER BY" & _
" " & strMemberTablePrefix & "MEMBERS.M_LEVEL DESC," & _
" " & strMemberTablePrefix & "MEMBERS.M_TITLE ASC," & _
" " & strMemberTablePrefix & "MEMBERS.M_POSTS DESC," & _
" " & strMemberTablePrefix & "MEMBERS.M_NAME ASC"
case "leveldesc"
strSql = strSql & " ORDER BY" & _
" " & strMemberTablePrefix & "MEMBERS.M_LEVEL DESC," & _
" " & strMemberTablePrefix & "MEMBERS.M_TITLE DESC," & _
" " & strMemberTablePrefix & "MEMBERS.M_POSTS ASC," & _
" " & strMemberTablePrefix & "MEMBERS.M_NAME ASC"
The two methods posted with code by me will not exactly sort in alphabetical order (because the ranking Titles are not always assigned in alphabatic order depending on the post count)
But these will atleast keep the titles together
NEW Member
NEW Member
NEW Member
NEW Member
Starting Member
Starting Member
Starting Member
Starting Member
Junior Member
Junior Member
Junior Member
Junior Member
and so on...
www.forumSquare.com - GauravBhabu - It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying.
Edited by - GauravBhabu on 30 January 2002 02:52:11 |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 03 February 2002 : 06:12:18
|
This will fix the Sorting by Title for the most part. Remaining is the custom titles with M_LEVEL = 1. But otherwise is Okay. Check out if something skipped my notice.
Modify the following case statements as shown
case "levelasc"
strSql = strSql & " ORDER BY "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_LEVEL DESC,"
strSql = strSql & GetSqlSortTitles("ASC")
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_NAME ASC"
case "leveldesc"
strSql = strSql & " ORDER BY "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_LEVEL DESC,"
strSql = strSql & GetSqlSortTitles("DESC")
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_NAME ASC"
Add the following function to Members.asp
function GetSqlSortTitles(strOrder)
dim strTitleSql,intVar,strSortSql
'If a new level is added to the Ranks this array must also be modified.
arrRankLevels = array(intRankLevel0,intRankLevel1,intRankLevel2,intRankLevel3,intRankLevel4,intRankLevel5)
'Get all the titles from database in ascending or descending order
strTitlesSql = "" & "SELECT" & _
" " & strTablePrefix & "CONFIG_NEW.C_VARIABLE," & _
" " & strTablePrefix & "CONFIG_NEW.C_VALUE" & _
" FROM " & strTablePrefix & "CONFIG_NEW" & _
" WHERE " & strTablePrefix & "CONFIG_NEW.C_VARIABLE" & _
" LIKE '%STRRANKLEVEL%'" & _
" ORDER BY " & strTablePrefix & "CONFIG_NEW.C_VALUE " & strOrder & ";"
set rstSort = my_conn.execute(strTitlesSql)
'Match the titles with the number of posts level and build order by sql for titles
do while not rstSort.eof
if strSortSql <> "" then strSortSql = strSortSql & ","
intVar = cint(Right(rstSort("C_VARIABLE"),1))
strSortSql = strSortSql & strMemberTablePrefix & "MEMBERS.M_POSTS >= " & arrRankLevels(intVar)
if intVar < ubound(arrRankLevels) then
strSortSql = strSortSql & " AND " & strMemberTablePrefix & "MEMBERS.M_POSTS < " & arrRankLevels(intVar+ 1)
end if
'Keep the custom titles with Level = 1 out of Rank Titles
strSortSql = strSortSql & " AND " & strMemberTablePrefix & "MEMBERS.M_TITLE = ''"
rstSort.Movenext
loop
set rstSort = nothing
GetSqlSortTitles = strSortSql & ","
end function
www.forumSquare.com - GauravBhabu - It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying.
Edited by - GauravBhabu on 03 February 2002 08:32:58 |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 03 February 2002 : 07:56:42
|
Modified the function to get both Post Levels and Rank Titles from the database. Used GetRows.
function GetSqlSortTitles(strOrder)
dim strTitleSql,intVar,strSortSql,intArrCount
'Get Post Levels from the database
strRanksSql = "" & "SELECT" & _
" " & strTablePrefix & "CONFIG_NEW.C_VALUE" & _
" FROM " & strTablePrefix & "CONFIG_NEW" & _
" WHERE " & strTablePrefix & "CONFIG_NEW.C_VARIABLE" & _
" LIKE '%INTRANKLEVEL%'" & _
" ORDER BY " & strTablePrefix & "CONFIG_NEW.C_VARIABLE ASC;"
set rstRanks = my_conn.execute(strRanksSql)
arrRankLevels = rstRanks.GetRows(-1)
set rstRanks = nothing
arrRankLevelsSize = ubound(arrRankLevels,2)
'Get Rank Titles from the database in ascending or descending order
strTitlesSql = "" & "SELECT" & _
" " & strTablePrefix & "CONFIG_NEW.C_VARIABLE" & _
" FROM " & strTablePrefix & "CONFIG_NEW" & _
" WHERE " & strTablePrefix & "CONFIG_NEW.C_VARIABLE" & _
" LIKE '%STRRANKLEVEL%'" & _
" ORDER BY " & strTablePrefix & "CONFIG_NEW.C_VALUE " & strOrder & ";"
set rstTitles = my_conn.execute(strTitlesSql)
arrRankTitles = rstTitles.GetRows(-1)
set rstTitles = nothing
arrRankTitlesSize = ubound(arrRankTitles,2)
if arrRankTitlesSize <> arrRankLevelsSize then GetSqlSortTitles = "" : Exit function
'Build Order By part of sql to sort the records by rank Title
for intArrCount = 0 to arrRankTitlesSize
if strSortSql <> "" then strSortSql = strSortSql & ","
intVar = cint(Right(arrRankTitles(0,intArrCount),1))
strSortSql = strSortSql & strMemberTablePrefix & "MEMBERS.M_POSTS >= " & arrRankLevels(0,intVar)
if intVar < arrRankLevelsSize then
strSortSql = strSortSql & " AND " & strMemberTablePrefix & "MEMBERS.M_POSTS < " & arrRankLevels(0,(intVar + 1))
end if
'Keep the custom Titles for M_LEVEL = 1 separate
strSortSql = strSortSql & " AND " & strMemberTablePrefix & "MEMBERS.M_TITLE = ''"
next
GetSqlSortTitles = strSortSql & ","
end function
www.forumSquare.com - GauravBhabu - It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying.
Edited by - GauravBhabu on 03 February 2002 08:33:45 |
|
|
| |
Topic |
|
Topic Locked
