how do i perform a search query on 2 tables?

E

eric4511999

I have an ASP page that performs a Search in a MS Access Database. The
Search originally only looked at one table (links) in my database. I
added another table with identical columns but different data called
(bandlinks) This script was already made and I altered it to work for
several tables, but when somebody searches it only looks at the "links"
table. I need the query to search BOTH tables and show ALL the info in
one listing. In the script i am altering, there are a lot of extra
codes that maybe you guys know but the confusing part is the 4 or 5
times that they repeat the SELECT query. Thats not my concern yet but
here is the LONG page of code....


***********************************************
<%
'Link List
Dim selStat

selStat = "links.linkid, links.title, links.url, links.description,
links.hits, links.datein, "_
& "links.catid, links.popular, links.votes, links.rating,
links.posts, links.threads, links.linktype, links.picture"

Dim currentpage
Dim pagecount
Dim linkcount
Dim SortSQL
Dim lastcat
Dim catlist
Dim errorstatus
Dim SQLquery
Dim chcolor
Dim lastx
Dim popsort
Dim retCats

retCats = LV_retcats

if dbtype = 1 then
popsort = "ORDER BY popular ASC"
else
popsort = "ORDER BY popular DESC"
end if

' This is the Highlight Color for Categories:
chcolor = "#FFFF00"
'Edit ^^^^^^^ To change the color

Set catlist = Server.CreateObject("ADODB.RecordSet")

linkcount = 0
lastcat = 0
errorstatus = 0

If Request.QueryString("page") = "" Then
currentpage = 1
else
currentpage = Cint(Request.QueryString("page"))
end if

'-----------------------------------
SortSQL = "ORDER BY "

SELECT Case SortOrder
Case 0
SortSQL = popsort
Case 1
SortSQL = SortSQL & " title ASC"
Case 2
SortSQL = SortSQL & " title DESC"
Case 3
SortSQL = SortSQL & " hits ASC"
Case 4
SortSQL = SortSQL & " hits DESC"
Case 5
SortSQL = SortSQL & " votes ASC"
Case 6
SortSQL = SortSQL & " votes DESC"
Case 7
SortSQL = SortSQL & " posts ASC"
Case 8
SortSQL = SortSQL & " posts DESC"
Case 9
SortSQL = SortSQL & " datein DESC"
Case 10
SortSQL = SortSQL & " datein ASC"
Case 11
SortSQL = SortSQL & " rating DESC"
Case 12
SortSQL = SortSQL & " rating ASC"
End SELECT

SortSQL = SortSQL & ", catid ASC"

'-----------------------------------

if request.querystring("func") = "search" then

Dim strText
Dim wCount
Dim lastpos
Dim dist
Dim words(100)

lastpos = 1
dist = 1
wCount = 0
strText = myquery

SQLquery = "(" &parsethis(strText ,words ,wCount, 2) & ")"
SQLquery = Replace(SQLquery, "<FIELD1>", "title")
SQLquery = Replace(SQLquery, "<FIELD2>", "description")

If NOT mytype = "" then
SQLquery = SQLquery & " AND catid = " & tree
end if
if len(trim(strText)) = 0 then
errorstatus = 2
else
SQLstr = "SELECT " & selStat & " FROM links WHERE " & SQLquery & "
AND active = " & myTrue & " " & SortSQL & ";"
end if
end if
if Request.QueryString("func") = "new" then
Dim newdate
newdate = (date - newcutoff)
if mydbtype = 1 then
SQLstr = "SELECT " & selStat & " FROM links WHERE active = " &
myTrue & " AND datein > #" & newdate & "# ORDER BY datein DESC, catid
ASC;"
end if
if mydbtype = 2 then
SQLstr = "SELECT " & selStat & " FROM links WHERE active = " &
myTrue & " AND datein > '" & newdate & "' ORDER BY datein DESC, catid
ASC;"
end if
end if
if Request.QueryString("func") = "hot" then
if whsort = 1 then
SQLstr = "SELECT " & selStat & " FROM links WHERE active = " &
myTrue _
& " AND hits >= " & whhits & " AND rating >= " & whrating & "
ORDER BY hits DESC, rating DESC;"
else
SQLstr = "SELECT " & selStat & " FROM links WHERE active = " &
myTrue _
& " AND hits >= " & whhits & " AND rating >= " & whrating & "
ORDER BY rating DESC, hits DESC;"
end if
end if
if Request.QueryString("func") = "referrers" then
SQLstr = "SELECT " & selStat & " FROM links WHERE active = " & myTrue
& " AND ldayin = " & day(date()) & " ORDER BY dhitsin DESC;"
end if

'&*( Return categories if applicable
)&*()&*(&*()&*(&*()&*()&*()&*()&*(&*()&*()&*&*()&*(&*()&*()

if mytype = "" AND currentpage = 1 AND Request.QueryString("func") =
"search" AND errorstatus = 0 then
Set catsRec = Server.CreateObject("ADODB.RecordSet")
catsRec.Open "SELECT TOP " & retCats & " catid, subname FROM cats
WHERE " & Replace(parsethis(strText ,words ,wCount, 1), "<FIELD1>",
"name") & " AND display LIKE '%default%';", objConn, 3, 3, 1
Response.Write "<font face=""" & deffont & """ size=""" & deffsize &
""">" & sercats & "</font><BR>"

If catsRec.EOF then
Response.Write "<font face=""" & deffont & """ size=""" & deffsize &
""">" & sercatsnone & "</font>"
end if
Dim myCat
While NOT catsRec.EOF
myCat = highlight(catsRec("subname"), words, wCount, chcolor)
Response.Write "<font face=""" & deffont & """ size=""" & deffsize &
""">" & Replace(catsertemp,"<CAT>", "<a href=""catredir.asp?cat=" &
catsRec("catid") & """>" & myCat & "</a>") & "</font><BR>"
catsRec.MoveNext
Wend
Set catsRec = Nothing
Response.Write "<BR><BR>"
end if

'
&*(&*()&*()&*()&*()&*(&*()&*()&*&*()&*(&*()&*()&*(&*()&*()&*()&*()&*(&*()&*()&*&*()&*(&*()&*()
if errorstatus = 0 then
linkRec.PageSize = pagesize
linkRec.CacheSize = pagesize
linkRec.Open SQLstr, ObjConn, adOpenStatic, adLockReadOnly,
adCmdText
pagecount = linkRec.pagecount
If currentpage > pagecount then
currentpage = pagecount
end if
If currentpage < 1 then
currentpage = 1
end if
else
pagecount = 0
end if
if errorstatus = 2 then
Response.Write "<font face=""" & deffont & """ size=""" & deffsize &
""">" & noquery & "</font>"
elseif pagecount = 0 then
Response.Write "<font face=""" & deffont & """ size=""" & deffsize &
""">" & nolinks & "</font>"
else
%><!-- #INCLUDE FILE="paging.asp" --><%
linkRec.AbsolutePage = currentpage
Response.Write "<p><font face=""" & deffont & """ size=""" & deffsize
& """>" & Replace(Replace(Replace(linksfound ,"<LINKSFOUND>",
linkRec.recordcount), "<PGA>", currentpage ), "<PGB>", pagecount ) &
"</font></p>"
While linkcount < pagesize AND Not linkRec.EOF
if lastcat <> linkRec(Links_catid) then
Set catlist = Server.CreateObject("ADODB.RecordSet")
catlist.Open "SELECT catid, subname FROM cats WHERE catid = " &
linkRec(Links_catid) & ";", objConn, 3, 3, 1
lastcat = linkRec(Links_catid)
Response.Write "<font face=""" & deffont & """ size=""" & deffsize
& """>" & Replace(Replace(catTemp, "<CATNAME>", catlist(1)),
"<CATLINK>", "catredir.asp?cat=" & catlist(0)) & "</font>"
end if
%><!-- #INCLUDE FILE="linktemp.inc" --><%
linkRec.MoveNext
linkcount = linkcount + 1
Wend
end if
if errorstatus = 0 then
linkRec.Close
end if
Set catlist = Nothing
Response.Write pgHTML
%>

Thank you for any help with this.. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I set focus (and select text) on a text box on a modeless userform? 0
How to query 2 tables and then perform a wildcard search using a 3 1
How do I FindNext in Recordset.clone 0
Linking 3 auto filters to a pivot table on another worksheet 2
calender problem 0
How do I run routine for each cell in column and wait for response 1
Search that creates a dynamic query 1
how can I use Word find and Replace to search inside a stringvariable (in a macro) 1

Top