doowle
08-04-2004, 09:38 PM
Hi,
I am working on a sort of bulletin board/delayed-viewing blog. Much like this board, really. Users make a primary 'blurb' entry, other uses can post comments on those entries. All users must be logged in to post anything, but all entries must be approved for public viewing by an admin before going live.
Users see subject, author, original blurb, other people's comments, date.
A few of the goals were:
1. When the user is not logged in, they can view all posts, grouped by date. (WORKING)
2. When the user logs in, they see theirs first, then all others still grouped by date but also sorted by authorID within date. (NOT WORKING)
3. The users need to be able to sort by user, subject, date, most active, and we were also supposed to sort by comment authorID but I think we are scrapping that last one. (SOMEWHAT SORT OF WORKING)
I asked a coworker to work on the SQL for me. I don't know SQL, so I am lost trying to fix it. I have been at it all day, and am back to the original code with no improvement. I hope you can help me.
home page (parts with related code, anyway)
Dim item, count, lastdate, holdblurb, findid,Conntemp,rsBlurb,rsComment,mySQL, username,AuthorName,AuthorID,userlog, index
AuthorName=session("AuthorName")
AuthorID = session("AuthorID")
userlog=session("userloggin")
sorttype=Lcase(Request("sorttype"))
index=Request("index")
' ================ sort condition string
' call function for the sort type
fnSortOrder(sorttype)
sortorder=session("sortorder")
' =======
mySQL = "SELECT BlurbTable.BlurbID, BlurbTable.Blurbdate,BlurbTable.BlurbTitle, BlurbTable.Blurbtime, AuthorTable.AuthorID, "
mySQL= mySQL & " AuthorTable.AuthorQuery, SubjectTable.Sub_id, SubjectTable.SubjectName,
BlurbTable.Blurbtext,BlurbTable.BlurbTextPublic "
mySQL= mySQL & " FROM SubjectTable INNER JOIN (AuthorTable INNER JOIN BlurbTable "
mySQL= mySQL & " ON AuthorTable.AuthorID = BlurbTable.AuthorID) "
mySQL= mySQL & " ON SubjectTable.Sub_id = BlurbTable.Sub_id "
'*****************************
'******HERE IS WHERE I AM GETTING ONLY THE USER'S ENTRIES, INSTEAD OF SORTED BY USER GROUPED BY DATE, WITH USER ENTRIES FIRST*******
'*****************************
If (session("AuthorName")<> "" and session ("PreviousPage") ="") Then
' user went through the login page, but did not went through the enter blog/comment : display only his blog
mySQL=mySQL & " WHERE (AuthorTable.AuthorID ="& session("AuthorID") &" AND BlurbTable.Public='yes') "
mySQL=mySQL & " ORDER BY BlurbTable.Blurbdate DESC , BlurbTable.Blurbtime DESC;"
Else
mySQL= mySQL & " WHERE ((BlurbTable.Public='yes')) "
mySQL= mySQL & sortorder
End If
'response.write mySQL
CommentTable where CommentTable.Public='yes' GROUP BY (CommentTable.commentblurbid);",conntemp,adOpenStatic
%>
<!--*****HERE IS THE SORT OPTIONS CODE, WHICH ALSO NEEDS WORK BUT MAYBE IT WILL BE BETTER IF I AT LEAST GET THE SQL STMT FIXED*****-->
<script language="JavaScript">
<!--
function SelectItem(idx)
{
var idx=idx;
var sortname="";
sortname=document.sort.selectedtype.options[idx].value;
window.open("default.asp?sorttype=" + escape(sortname) +"&index=" +idx, "_self");
}
function loaddropdwpnlist()
{
var index='<%=index%>';
document.sort.selectedtype.selectedIndex=index;
}
// -->
</script>
</HEAD>
<BODY onLoad="loaddropdwpnlist()">
<form name="sort">
<table>
<tr><td>
<select name="selectedtype" size="1" Onchange="SelectItem(this.selectedIndex)">
<option value=" ">--- Select Sort Option --- </option>
<option value="Sort By User Blog" > Sort By User Blog </option>
<option value="Sort By User Comment" > Sort By User Comment </option>
<option value="Sort By User Blog and Comment" > Sort By User Blog and Comment </option>
<option value="Sort By Most Active" > Sort By Most Active </option>
</select>
</td></tr>
</table>
</form>
<!--************DISPLAY CODE WORKS FINE-->
<!--***********SORT FUNCTION CODE FROM ANOTHER PAGE-->
Public Function fnSortOrder(sorttype)
Dim sortorder
Select Case sorttype
Case "sort by user blog"
sortorder="ORDER BY AuthorTable.AuthorName;"
'session("sort")="sortuserblog"
Case "sort by user comment"
sortorder=" ORDER BY AuthorTable.AuthorName;"
'session("sort")="sortusercomment"
Case "sort by user blog and comment"
sortorder="ORDER BY AuthorTable.AuthorName;"
'session("sort")="sortusercomment"
Case "sort by most active"
sortorder=" ORDER BY BlurbTable.Blurbdate DESC , BlurbTable.Blurbtime DESC;"
'session("sort")="sortmostactive"
Case ""
'sort default , user did not click on sort by button
sortorder=" ORDER BY BlurbTable.Blurbdate DESC , BlurbTable.Blurbtime DESC;"
End Select
session("sortorder")=sortorder
End Function
----------------------------------------------------------------------
When I try to rewrite this, I either break the sort code altogether, or I get 4 results for every entry.
I need, after that first if-then, to see the session(authorid) items first, then all other entries as well, grouped by date. Right now it does everything okay, except no other entries except the user's once the user is logged in. But whenever I try to fix it, I either lose the session (authorID) entries being first, or the grouping by date, or I get everythign duplicating 4 times, or.....AAAAAAAHHHH I have been workikng on this all day. I haven't even really had a chance to try to fix the sorting code, other than to briefly break it a few times and put it back the way it was.
Can anyone at least tell me how to fix the SQL statement to view the logged in user's items first (all dates, newest first), then all the others still grouped by date but also sorted by user within the grouping?
I am working on a sort of bulletin board/delayed-viewing blog. Much like this board, really. Users make a primary 'blurb' entry, other uses can post comments on those entries. All users must be logged in to post anything, but all entries must be approved for public viewing by an admin before going live.
Users see subject, author, original blurb, other people's comments, date.
A few of the goals were:
1. When the user is not logged in, they can view all posts, grouped by date. (WORKING)
2. When the user logs in, they see theirs first, then all others still grouped by date but also sorted by authorID within date. (NOT WORKING)
3. The users need to be able to sort by user, subject, date, most active, and we were also supposed to sort by comment authorID but I think we are scrapping that last one. (SOMEWHAT SORT OF WORKING)
I asked a coworker to work on the SQL for me. I don't know SQL, so I am lost trying to fix it. I have been at it all day, and am back to the original code with no improvement. I hope you can help me.
home page (parts with related code, anyway)
Dim item, count, lastdate, holdblurb, findid,Conntemp,rsBlurb,rsComment,mySQL, username,AuthorName,AuthorID,userlog, index
AuthorName=session("AuthorName")
AuthorID = session("AuthorID")
userlog=session("userloggin")
sorttype=Lcase(Request("sorttype"))
index=Request("index")
' ================ sort condition string
' call function for the sort type
fnSortOrder(sorttype)
sortorder=session("sortorder")
' =======
mySQL = "SELECT BlurbTable.BlurbID, BlurbTable.Blurbdate,BlurbTable.BlurbTitle, BlurbTable.Blurbtime, AuthorTable.AuthorID, "
mySQL= mySQL & " AuthorTable.AuthorQuery, SubjectTable.Sub_id, SubjectTable.SubjectName,
BlurbTable.Blurbtext,BlurbTable.BlurbTextPublic "
mySQL= mySQL & " FROM SubjectTable INNER JOIN (AuthorTable INNER JOIN BlurbTable "
mySQL= mySQL & " ON AuthorTable.AuthorID = BlurbTable.AuthorID) "
mySQL= mySQL & " ON SubjectTable.Sub_id = BlurbTable.Sub_id "
'*****************************
'******HERE IS WHERE I AM GETTING ONLY THE USER'S ENTRIES, INSTEAD OF SORTED BY USER GROUPED BY DATE, WITH USER ENTRIES FIRST*******
'*****************************
If (session("AuthorName")<> "" and session ("PreviousPage") ="") Then
' user went through the login page, but did not went through the enter blog/comment : display only his blog
mySQL=mySQL & " WHERE (AuthorTable.AuthorID ="& session("AuthorID") &" AND BlurbTable.Public='yes') "
mySQL=mySQL & " ORDER BY BlurbTable.Blurbdate DESC , BlurbTable.Blurbtime DESC;"
Else
mySQL= mySQL & " WHERE ((BlurbTable.Public='yes')) "
mySQL= mySQL & sortorder
End If
'response.write mySQL
CommentTable where CommentTable.Public='yes' GROUP BY (CommentTable.commentblurbid);",conntemp,adOpenStatic
%>
<!--*****HERE IS THE SORT OPTIONS CODE, WHICH ALSO NEEDS WORK BUT MAYBE IT WILL BE BETTER IF I AT LEAST GET THE SQL STMT FIXED*****-->
<script language="JavaScript">
<!--
function SelectItem(idx)
{
var idx=idx;
var sortname="";
sortname=document.sort.selectedtype.options[idx].value;
window.open("default.asp?sorttype=" + escape(sortname) +"&index=" +idx, "_self");
}
function loaddropdwpnlist()
{
var index='<%=index%>';
document.sort.selectedtype.selectedIndex=index;
}
// -->
</script>
</HEAD>
<BODY onLoad="loaddropdwpnlist()">
<form name="sort">
<table>
<tr><td>
<select name="selectedtype" size="1" Onchange="SelectItem(this.selectedIndex)">
<option value=" ">--- Select Sort Option --- </option>
<option value="Sort By User Blog" > Sort By User Blog </option>
<option value="Sort By User Comment" > Sort By User Comment </option>
<option value="Sort By User Blog and Comment" > Sort By User Blog and Comment </option>
<option value="Sort By Most Active" > Sort By Most Active </option>
</select>
</td></tr>
</table>
</form>
<!--************DISPLAY CODE WORKS FINE-->
<!--***********SORT FUNCTION CODE FROM ANOTHER PAGE-->
Public Function fnSortOrder(sorttype)
Dim sortorder
Select Case sorttype
Case "sort by user blog"
sortorder="ORDER BY AuthorTable.AuthorName;"
'session("sort")="sortuserblog"
Case "sort by user comment"
sortorder=" ORDER BY AuthorTable.AuthorName;"
'session("sort")="sortusercomment"
Case "sort by user blog and comment"
sortorder="ORDER BY AuthorTable.AuthorName;"
'session("sort")="sortusercomment"
Case "sort by most active"
sortorder=" ORDER BY BlurbTable.Blurbdate DESC , BlurbTable.Blurbtime DESC;"
'session("sort")="sortmostactive"
Case ""
'sort default , user did not click on sort by button
sortorder=" ORDER BY BlurbTable.Blurbdate DESC , BlurbTable.Blurbtime DESC;"
End Select
session("sortorder")=sortorder
End Function
----------------------------------------------------------------------
When I try to rewrite this, I either break the sort code altogether, or I get 4 results for every entry.
I need, after that first if-then, to see the session(authorid) items first, then all other entries as well, grouped by date. Right now it does everything okay, except no other entries except the user's once the user is logged in. But whenever I try to fix it, I either lose the session (authorID) entries being first, or the grouping by date, or I get everythign duplicating 4 times, or.....AAAAAAAHHHH I have been workikng on this all day. I haven't even really had a chance to try to fix the sorting code, other than to briefly break it a few times and put it back the way it was.
Can anyone at least tell me how to fix the SQL statement to view the logged in user's items first (all dates, newest first), then all the others still grouped by date but also sorted by user within the grouping?