| Author |
 Topic  |
|
|
RichLondoner
Starting Member
United Kingdom
43 Posts |
 Posted - 02 December 2003 : 07:10:42
|
Right, not 100% sure where to put this, but on MS SQL and I have been banging my head against the table on this.
Basicially just want to extract the last 5 posts with their topic_id and reply_id identical to the "Jump to Last Post" icon, but just in a simple list, so that I can include it on a different page and so the user can just click on the topic and it will jump to the last post.
But I can't get the SQL SELECT query right.
"SELECT TOP 5 Forum_Topics.T_Last_Post, Forum_Topics.T_Subject, Forum_Topics.T_Date, Forum_Topics.Topic_ID FROM Forum_Topics ORDER BY Forum_Topics.T_Last_Post DESC"
Works for the first part, which is selecting the latest 5 posts and sorting them in descending order, but as soon as I try and join/whatever the Forum_Reply table it loses the previous order which is numbing my mind to the state of veggies...
Any help would be great...
Thanks
Richard
|
Richard Mills
http://www.mysector.co.uk |
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 02 December 2003 : 08:17:56
|
| Have you looked at the slash mod at all? It may be doing what you're looking for already... |
Dave Maxwell
Barbershop Harmony Freak |
 |
|
|
RichLondoner
Starting Member
United Kingdom
43 Posts |
Posted - 02 December 2003 : 09:12:13
|
Nope that didn't work at all, just gave me :
"Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
?"
on a blank page with just the include file, as instructed by readme... |
Richard Mills
http://www.mysector.co.uk |
|
|
|
Brainiac
Starting Member
USA
24 Posts |
Posted - 02 December 2003 : 15:54:36
|
What is the syntax of your join? What kind of join are you attempting?
You can simplify your syntax a little:
"SELECT TOP 5 ft.T_Last_Post, ft.T_Subject, ft.T_Date, ft.Topic_ID FROM Forum_Topics as ft ORDER BY ft.T_Last_Post DESC"
Helps me keep track of things when I start joining 10 tables together while cutting down on the length of the statement.. |
Abashed the Devil stood and felt how awful goodness is. |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 02 December 2003 : 16:06:13
|
strSql = "SELECT"
strSql = strSql & " T.TOPIC_ID,"
strSql = strSql & " T.T_SUBJECT"
strSql = strSql & " FROM " & strTablePrefix & "TOPICS T," & strTablePrefix & "FORUM F"
strSql = strSql & " WHERE T.FORUM_ID = F.FORUM_ID"
strSql = strSql & " AND F.F_PRIVATEFORUMS = 0"
strSql = strSql & " AND T.T_STATUS = 1"
strSql = strSql & " ORDER BY T_LAST_POST DESC"
is what I use on my homepage |
The UK MkIVs Forum |
|
|
|
Brainiac
Starting Member
USA
24 Posts |
Posted - 02 December 2003 : 16:27:30
|
I ran this in query analyzer for a quick look:
SELECT *
--T.TOPIC_ID,
--T.T_SUBJECT
FROM FORUM_TOPICS T, FORUM_FORUM F
WHERE T.FORUM_ID = F.FORUM_ID
AND (F.F_PRIVATEFORUMS = 0)
AND (T.T_STATUS = 1)
ORDER BY T_LAST_POST DESC
And it sorted exactly as it should have as far as I can tell. |
Abashed the Devil stood and felt how awful goodness is. |
|
|
|
RichLondoner
Starting Member
United Kingdom
43 Posts |
Posted - 03 December 2003 : 05:45:31
|
Sorry guys not what I needed, don't think I explained it very well...
So here goes again.
I have the simplified SQL Query:
SELECT TOP 5 T_Last_Post, T_Subject, T_Date, Topic_ID FROM Forum_Topics ORDER BY T_Last_Post DESC
Which does order the last posts in desending order, but I also need the Reply_Id from each of those last 5 topics so instead of going to the top of each of those topics when I code in the results from the sql query, I'd jump down to the last post. This would be like the last post (details stats) on the front page, or the blue arrow "Jump to last post".
This is the code i'm working in, probably not formatted right, i'm new to asp/sql...
<%
Dim iCharCount
Dim strT_Sub
Dim strTopId
Dim strLastP
set Conn = server.createobject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB;Data Source=uhltest;database=snitz;uid=iusr_snitz;pwd=smurf;"
set rs=Conn.execute ("SELECT TOP 5 Forum_Topics.T_Last_Post, Forum_Topics.T_Subject, Forum_Topics.T_Date, Forum_Topics.Topic_ID FROM Forum_Topics ORDER BY Forum_Topics.T_Last_Post DESC")
response.write ("Last 5 posts with reply id's<p>")
while not rs.eof
strT_Sub = rs("T_Subject")
strTopId = rs("Topic_ID")
strLastP = rs("T_Last_Post")
'strRepId = rs("Reply_Id")
iCharCount = Len(strT_Sub)
if iCharCount > 25 Then
response.write ("<a href=""http://lewinet/dp/apps/snitz/topic.asp?whichpage=-1&TOPIC_ID=" & rs("Topic_ID") & "&REPLY_ID=" & strRepId & """>" & Left(strT_Sub,22) & "...</a> " & mid(strLastP,7,2) & "/" & mid(strLastP,5,2) & "/" & left(strLastP,4) & "<br>")
Else If iCharCount =< 25 Then
response.write ("<a href=""http://lewinet/dp/apps/snitz/topic.asp?whichpage=-1&TOPIC_ID=" & rs("Topic_ID") & "&REPLY_ID=" & strRepId & """>" & rs("T_Subject") & "</a> " & mid(strLastP,7,2) & "/" & mid(strLastP,5,2) & "/" & left(strLastP,4) & "<br>")
End if
End if
rs.movenext
wend
Conn.close
Conn=null
%> |
Richard Mills
http://www.mysector.co.uk |
|
|
|
Brainiac
Starting Member
USA
24 Posts |
Posted - 03 December 2003 : 08:49:08
|
Question: Why are you selecting everything from the *_Forum table? Isn't that pulling in unwanted rows that aren't relative to your topic/reply?
For instance:
SELECT *
FROM FORUM_TOPICS
JOIN FORUM_FORUM ON FORUM_TOPICS.FORUM_ID = FORUM_FORUM.FORUM_ID
JOIN FORUM_REPLY ON FORUM_TOPICS.TOPIC_ID = FORUM_REPLY.TOPIC_ID
WHERE FORUM_TOPICS.FORUM_ID = FORUM_FORUM.FORUM_ID
AND (FORUM_FORUM.F_PRIVATEFORUMS = 0)
AND (FORUM_TOPICS.T_STATUS = 1)
ORDER BY T_LAST_POST DESC
Then you would just get your relevant rows from the two joined tables. It seems that you are doing something like what I did at www.execlan.net (left menu). |
Abashed the Devil stood and felt how awful goodness is. |
|
|
|
RichLondoner
Starting Member
United Kingdom
43 Posts |
Posted - 03 December 2003 : 09:47:48
|
Thanks, But that didn't work as it did not get the latest reply_id's that are needed to jump to the last post in each topic... However, I do now have a working version, apart from it doesn't select topics with zero replies and is a major workaround as you can see below. All I need now is to get the latest topics included in the list!
I'm starting to believe it's impossible.
<%
Dim strTopicCompare
Dim Count
Dim Same
set Conn = server.createobject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB;Data Source=uhltest;database=snitz;uid=iusr_snitz;pwd=smurf;"
set rs=Conn.execute ("SELECT TOP 50 Forum_Topics.T_Last_Post, Forum_Topics.T_Subject, Forum_Topics.Topic_ID, Forum_Reply.Topic_ID, Forum_Reply.Reply_ID FROM Forum_Topics INNER JOIN Forum_Reply ON Forum_Topics.Topic_ID=Forum_Reply.Topic_ID ORDER BY Forum_Topics.T_Last_Post DESC, Forum_Reply.Reply_ID DESC")
TopicCompare = "null"
Count = 0
while not count = 5
If rs("Topic_ID") = strTopicCompare Then
Same = True
End If
If Same = False Then
strTopicCompare = rs("Topic_ID")
strLastP = rs("T_Last_Post")
Response.write ("<a href=""http://lewinet/dp/apps/snitz/topic.asp?whichpage=-1&TOPIC_ID=" & rs("Topic_ID") & "&REPLY_ID=" & rs("Reply_ID") & """>" & rs("T_Subject") & "</a> " & mid(strLastP,7,2) & "/" & mid(strLastP,5,2) & "/" & left(strLastP,4) & "<br>")
Count = Count +1
End If
Same = False
rs.movenext
wend
Conn.close
Conn=null
%> |
Richard Mills
http://www.mysector.co.uk |
|
|
|
RichLondoner
Starting Member
United Kingdom
43 Posts |
Posted - 03 December 2003 : 11:41:27
|
Its been sorted now  If anybody wants to see the code please yell... Basically the code displays the last 5 topics and posts combined, then order by the last post date and displayed on the forum/wherever by the subject line followed by the date. If the topic has no replies it will just go to the topic, if the topic has replies it will go to the last reply. Also included is the date converted into English format without the time. The Subject is truncated down to 25 charactes (if over 22 characters) and "..." added. No demo as this is on an Intranet site and no external Snitz site available currently. Phew... |
Richard Mills
http://www.mysector.co.uk |
|
|
|
Brainiac
Starting Member
USA
24 Posts |
Posted - 03 December 2003 : 13:19:14
|
| Yea, I would like to see what was happening there. |
Abashed the Devil stood and felt how awful goodness is. |
|
|
|
RichLondoner
Starting Member
United Kingdom
43 Posts |
Posted - 04 December 2003 : 04:33:02
|
Well this was my code, obviously you'll have to change the connection string etc but it worked for me, now have a list of latest 5 topics or posts in a topic, both displayed using the topic subject followed by the date (English format)... Hope this helps...
<%
Dim iCharCount
Dim strT_Sub
Dim strTopId
Dim strLastP
set Conn = server.createobject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB;Data Source=uhltest;database=snitz;uid=iusr_snitz;pwd=smurf;"
set rs=Conn.execute ("SELECT TOP 5 T_Last_Post, T_Subject, T_Last_Post_Reply_Id, Topic_ID FROM Forum_Topics ORDER BY T_Last_Post DESC")
while not rs.eof
strT_Sub = rs("T_Subject")
strTopId = rs("Topic_ID")
strLastP = rs("T_Last_Post")
strRepId = rs("T_Last_Post_Reply_Id")
If rs("T_Last_Post_Reply_Id") = 0 Then
AddWhichPage = ""
AddReply = ""
AddRs = ""
End If
If rs("T_Last_Post_Reply_Id") <> 0 Then
AddWhichPage = "whichpage=-1&"
AddReply = "&REPLY_ID="
AddRs = rs("T_Last_Post_Reply_Id")
End If
iCharCount = Len(strT_Sub)
if iCharCount > 25 Then
response.write ("<a href=""http://lewinet/dp/apps/snitz/topic.asp?" & AddWhichPage & "TOPIC_ID=" & rs("Topic_ID") & AddReply & AddRs & """>" & Left(strT_Sub,22) & "...</a> " & mid(strLastP,7,2) & "/" & mid(strLastP,5,2) & "/" & left(strLastP,4) & "<br>")
Else If iCharCount =< 25 Then
response.write ("<a href=""http://lewinet/dp/apps/snitz/topic.asp?" & AddWhichPage & "TOPIC_ID=" & rs("Topic_ID") & AddReply & AddRs & """>" & rs("T_Subject") & "</a> " & mid(strLastP,7,2) & "/" & mid(strLastP,5,2) & "/" & left(strLastP,4) & "<br>")
End if
End if
rs.movenext
wend
Conn.close
Conn=null
%> |
Richard Mills
http://www.mysector.co.uk |
|
|
|
cicciput
Starting Member
19 Posts |
|
|
cicciput
Starting Member
19 Posts |
Posted - 16 March 2004 : 14:57:32
|
Bof, no answers so I did it myself... 
Here's the code, maybe could help someone.
<%
Dim iCharCount
Dim strT_Sub
Dim strTopId
Dim strLastP
set Conn = server.createobject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/mdb-database/snitz_forums_2000.mdb")
set rs=Conn.execute ("SELECT TOP 5 T_Last_Post, T_Subject, T_Last_Post_Reply_Id, Topic_ID, F_Subject, Forum_Forum.Forum_Id FROM Forum_Topics, Forum_Forum WHERE Forum_Forum.Forum_Id=Forum_Topics.Forum_Id ORDER BY T_Last_Post DESC")
while not rs.eof
strF_Sub = rs("F_Subject")
strT_Sub = rs("T_Subject")
strForId = rs("Forum_Id")
strTopId = rs("Topic_ID")
strLastP = rs("T_Last_Post")
strRepId = rs("T_Last_Post_Reply_Id")
If rs("T_Last_Post_Reply_Id") = 0 Then
AddWhichPage = ""
AddReply = ""
AddRs = ""
End If
If rs("T_Last_Post_Reply_Id") <> 0 Then
AddWhichPage = "whichpage=-1&"
AddReply = "&REPLY_ID="
AddRs = rs("T_Last_Post_Reply_Id")
End If
response.write ("<a href=""forum.asp?FORUM_ID=" & strForId &""">" & strF_Sub & "</a> - ")
iCharCount = Len(strT_Sub)
if iCharCount > 25 Then
response.write ("<a href=""topic.asp?" & AddWhichPage & "TOPIC_ID=" & rs("Topic_ID") & AddReply & AddRs & """>" & Left(strT_Sub,22) & "...</a> " & mid(strLastP,7,2) & "/" & mid(strLastP,5,2) & "/" & left(strLastP,4) & "<br>")
Else If iCharCount =< 25 Then
response.write ("<a href=""topic.asp?" & AddWhichPage & "TOPIC_ID=" & rs("Topic_ID") & AddReply & AddRs & """>" & rs("T_Subject") & "</a> " & mid(strLastP,7,2) & "/" & mid(strLastP,5,2) & "/" & left(strLastP,4) & "<br>")
End if
End if
rs.movenext
wend
Conn.close
Conn=null
%>
|
Never say D'oh!
http://www.blanksheetmusic.net |
|
|
| |
Topic |
|
|
|
Topic Locked
