Web Wiz - Green Windows Web Hosting

  New Posts New Posts RSS Feed - Get data from more than one table
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Get data from more than one table

 Post Reply Post Reply
Author
l15aRd View Drop Down
Groupie
Groupie


Joined: 24 May 2002
Location: England
Status: Offline
Points: 121
Post Options Post Options   Thanks (0) Thanks(0)   Quote l15aRd Quote  Post ReplyReply Direct Link To This Post Topic: Get data from more than one table
    Posted: 02 May 2003 at 6:39am

I'm trying to write my own latest topics script so I can view it on the main page of my site, it works fine (used ASPmaker to do it), but I wanted to add in the username of the person who last posted as well, could someone help me with this as I rubbish at SQL and need help? , here what I've got so far:

<!--#include file="db.asp"-->

<%
LCID = 1033
%>

<%
displayRecs = 10
recRange = 10
%>

<%
' Get table name
tablename = "[tblTopic]"
dbwhere = ""
a_search = ""
b_search = ""
%>

<%
' Load Default Order
DefaultOrder = "Last_entry_date"
DefaultOrderType = "Desc"

' Check for an Order parameter
OrderBy = "Last_entry_date"
If Request.QueryString("order").Count > 0 Then
 OrderBy = "Last_entry_date"
 ' Check if an ASC/DESC toggle is required
 If Session("tblTopic_OB") = OrderBy Then
  If Session("tblTopic_OT") = "ASC" Then
   Session("tblTopic_OT") = "DESC"
  Else
   Session("tblTopic_OT") = "ASC"
  End if
 Else
  Session("tblTopic_OT") = "ASC"
 End If
 Session("tblTopic_OB") = OrderBy
 Session("tblTopic_REC") = 1
Else
 OrderBy = Session("tblTopic_OB")
 if OrderBy = "" then
  OrderBy = DefaultOrder
  Session("tblTopic_OB") = OrderBy
  Session("tblTopic_OT") = DefaultOrderType
 End If
End If

' Check for a START parameter
If Request.QueryString("start").Count > 0 Then
 startRec = Request.QueryString("start")
 Session("tblTopic_REC") = startRec
Else
 startRec = Session("tblTopic_REC")
 if not isnumeric(startRec) or startRec = "" then
  'reset start record counter
  startRec = 1
  Session("tblTopic_REC") = startRec
 End If
End If

' Open Connection to the database
set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str

' Build Query
strsql = "select * from [tblTopic]"

If dbwhere <> "" Then
 strsql = strsql & " WHERE " & dbwhere
End If

if OrderBy <> "" then
  strsql = strsql & " ORDER BY [" & OrderBy & "] " & Session("tblTopic_OT")
end if 

'response.write strsql

set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql, conn, 1, 2
totalRecs = rs.RecordCount
%>


<BR>
<table border="1" width="771" align="center"  BGcolor="#000000" cellspacing="0" cellpadding="3" background="/images/barfill.gif">
<tr>
<td>
<font color="#FFFFFF"><font size="-1">&nbsp;Subject&nbsp;</font></font>
</td>
<td>
<font color="#FFFFFF"><font size="-1">Last entry date&nbsp;</font></font>
</td>

</tr>


<%
'Avoid starting record > total records
if clng(startRec) > clng(totalRecs) then
 startRec = totalRecs
end if
'Set the last record to display
stopRec = startRec + displayRecs - 1

'Move to first record directly for performance reason
recCount = startRec - 1
if not rs.eof then
 rs.movefirst
 rs.move startRec - 1
end if

recActual = 0
Do While (NOT rs.EOF) AND (recCount < stopRec)
 recCount = recCount + 1
 If Clng(recCount) >= Clng(startRec) Then
  recActual = recActual + 1 %>

<%
 'set row color
 bgcolor="#f7f8ff"
%>

<% 
 ' Display alternate color for rows
 If recCount mod 2 <> 0 Then
  bgcolor="#F5F5F5"
 End If
%>

<%
 x_Topic_ID = rs("Topic_ID")
 x_Forum_ID = rs("Forum_ID")
 x_Poll_ID = rs("Poll_ID")
 x_Moved_ID = rs("Moved_ID")
 x_Subject = rs("Subject")
 x_Start_date = rs("Start_date")
 x_Last_entry_date = rs("Last_entry_date")
 x_No_of_views = rs("No_of_views")
 x_Locked = rs("Locked")
 x_Priority = rs("Priority")
%>

<tr bgcolor="<%= bgcolor %>">
<td><a href="<% If not isnull(rs("Topic_ID")) Then response.write "/forum/forum_posts.asp?TID=" & Server.URLEncode(rs("Topic_ID")) Else response.write "javascript:alert('Invalid Record! Key is null.');" %>"><font size="-1"><% response.write x_Subject %></font></a></td>
<td><font size="-1">
<% response.write x_Last_entry_date %>&nbsp;
</font></td>
</tr>


<%
 end if

 rs.MoveNext
Loop
%>

<TR><TD background="../images/barfill.gif" colspan="3">
<% If stopRec > recCount Then stopRec = recCount %>
 <font color="#FFFFFF"><font size="-1">There are <%= totalRecs %> Forums</font>
</TD></TR>
</table>
</form>

 


<%
' Close recordset and connection
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing %>

 

to see the code working goto my site, any help would be greatly welcomed

Thanx in advance

 



Edited by l15aRd

DrunkenTechie.net

You can logoff, but you can never leave
Back to Top
MorningZ View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 May 2002
Location: United States
Status: Offline
Points: 1793
Post Options Post Options   Thanks (0) Thanks(0)   Quote MorningZ Quote  Post ReplyReply Direct Link To This Post Posted: 02 May 2003 at 8:35am
Contribute to the working anarchy we fondly call the Internet
Back to Top
l15aRd View Drop Down
Groupie
Groupie


Joined: 24 May 2002
Location: England
Status: Offline
Points: 121
Post Options Post Options   Thanks (0) Thanks(0)   Quote l15aRd Quote  Post ReplyReply Direct Link To This Post Posted: 02 May 2003 at 8:46am
That site could come in useful for me, thanks m8y

DrunkenTechie.net

You can logoff, but you can never leave
Back to Top
MorningZ View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 May 2002
Location: United States
Status: Offline
Points: 1793
Post Options Post Options   Thanks (0) Thanks(0)   Quote MorningZ Quote  Post ReplyReply Direct Link To This Post Posted: 02 May 2003 at 8:50am
i swear by that site, Javascript, XSL, and SQL i all learned from there
Contribute to the working anarchy we fondly call the Internet
Back to Top
l15aRd View Drop Down
Groupie
Groupie


Joined: 24 May 2002
Location: England
Status: Offline
Points: 121
Post Options Post Options   Thanks (0) Thanks(0)   Quote l15aRd Quote  Post ReplyReply Direct Link To This Post Posted: 02 May 2003 at 8:58am
I can see why, it looks like an absolute god send, your a

DrunkenTechie.net

You can logoff, but you can never leave
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.


Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.