07. oktober 2004 - 09:51Der er
14 kommentarer og 1 løsning
List 5 poster fra db
Hey!!!!
Har tidligere fået lavet dette script.
KODE: <% Set con= Server.CreateObject("ADODB.connection") Provider = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("forum/admin/database/wwForum.mdb") con.Open Provider
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (Select top 5 Topic_ID From tblThread order by thread_ID DESC)) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;" Set rs = con.Execute(SqlStr)
rs.MoveFirst do while not rs.eof forumdate = rs("Last_entry_date") If DateDiff("d", date(), rs("Last_entry_date")) = 0 Then strStyle = "font-weight: bold;" Else strStyle = "" End If Response.Write("<span style=""" & strStyle & """>" & left(right(rs("Last_entry_date"),8),5) & "</span>") %> <br> <a href="forum/forum_posts.asp?TID=<%=rs("Topic_ID")%>" target="_blank"><%=rs("subject")%></a><br><br> <% rs.movenext loop %>
Scriptet virker fint, ingen fejl - men desværre ikke helt efter hensigten.
Problemet er at den henter de 5 nyeste indlæg - Men det kan sagtens være under samme spørgsmål.
Så derfor skrifter listen hele tiden mellem at den lister 5 poster og at den lister 1.
Kan I hjælpe, så den springer posten over, hvis den allerede er nævnt, eller noget i den stil :o)
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (Select DISTINCT top 5 Topic_ID From tblThread order by thread_ID DESC)) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;"
ORDER BY clause (thread_ID) conflicts with DISTINCT.
site.asp, line 10
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (Select distinct top 5 Topic_ID From tblThread order by thread_ID DESC)) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;" Set rs = con.Execute(SqlStr)
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (Select (DISTINCT top 5) Topic_ID From tblThread order by thread_ID DESC)) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;"
Syntax error. in query expression '(((tblTopic.Topic_ID) In (Select (DISTINCT top 5) Topic_ID From tblThread order by thread_ID DESC)) AND ((tblForum.hide)=False))'.
du må lave dette: Select distinct top 5 Topic_ID From tblThread order by thread_ID DESC
sådan her:
select top 5 topic_id from (SELECT max(thread_id) as tmpid, tblThread.Topic_ID FROM tblThread GROUP BY tblThread.Topic_Id) order by tmpid desc
Så din SQL sætning bliver:
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (select top 5 topic_id from (SELECT max(thread_id) as tmpid, tblThread.Topic_ID FROM tblThread GROUP BY tblThread.Topic_Id) order by tmpid desc)) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;"
den kan laves om så det er en SQL som også tager top 5: select topic_id from (SELECT top 5 max(thread_id) as tmpid, tblThread.Topic_ID FROM tblThread GROUP BY tblThread.Topic_Id order by max(thread_id) desc)
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (select topic_id from (SELECT top 5 max(thread_id) as tmpid, tblThread.Topic_ID FROM tblThread GROUP BY tblThread.Topic_Id order by max(thread_id) desc))) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;"
Lige som før du skal bare copy/paste SQL sætningen SqlStr. Så det bliver:
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (SELECT top 5 Topic_ID FROM tblThread GROUP BY Topic_Id order by max(thread_id) desc)) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;" Set rs = con.Execute(SqlStr)
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.