search multiple tables in database [Archive]

View Full Version : search multiple tables in database

reubenb

02-13-2005, 01:16 AM

hiya,

i have the script below but i want to make it so it searches more than one table, ie. NEWS, EVENTS, DAYS etc.
and then get the news_desc or events_desc ... and news_url, events_url etc. so they can click on it

how would i go about doing this?

thanks

<%
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = &H0001
Const PAGE_SIZE = 5
Dim strURL
Dim cnnSearch
Dim rstSearch
Dim strDBPath
Dim strSQL
Dim strSearch
Dim iPageCurrent
Dim iPageCount
Dim iRecordCount
Dim I
strURL = Request.ServerVariables("URL")
strSearch = Request.QueryString("search")
strSearch = Replace(strSearch, "'", "''")

If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Search For News In The Database</title>
<style type="text/css">

</style>
</head>

<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" bgcolor="#F7F7F7" text="#666666" link="#993300" vlink="#993300" alink="#0066CC">
<form name="form1" method="get" action="<%= strURL %>">
<input name="search" type="text" value="<%= strSearch %>">
<br>
<input type="submit" name="Submit" value="Search">
</form>

<%
If strSearch <> "" Then
strDBPath = "db.mdb"
Set cnnSearch = Server.CreateObject("ADODB.Connection")
cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
strSQL = "SELECT content, header, newsID " _
& "FROM news " _
& "WHERE header LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR content LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY newsID DESC;"
Set rstSearch = Server.CreateObject("ADODB.Recordset")
rstSearch.PageSize = PAGE_SIZE
rstSearch.CacheSize = PAGE_SIZE
rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText
iRecordCount = rstSearch.RecordCount
iPageCount = rstSearch.PageCount
If iRecordCount = 0 Then

%>
<p class="style2">
No news articles found. Please try again.
</p>
<%
Else
rstSearch.AbsolutePage = iPageCurrent
%>

<hr />
<p>
<span class="style2"><%= iRecordCount %> Records Found.
Displaying page <%= iPageCurrent %>
of <%= iPageCount %>:
</span></p>

<span class="style2">
<%

%>
</span>

<%
Do While Not rstSearch.EOF And rstSearch.AbsolutePage = iPageCurrent
%>

<span class="style3">
<%Response.Write("<a href=""displayNews.asp?newsID="& rstSearch.Fields("newsID") & """" & ">")%>
<%= rstSearch.Fields("header").Value %><% Response.Write("</a>")%></span><br>

<%

rstSearch.MoveNext
Loop
%>
<p>
<%

If iPageCurrent > 1 Then
%>
<span class="style2"><a href="<%= strURL %>?search=<%= Server.URLEncode(strSearch) %>&page=<%= iPageCurrent - 1 %>">[<< Prev]</a>
<%
End If

For I = 1 To iPageCount
If I = iPageCurrent Then
%>
<%= I %>
<%
Else
%>
<a href="<%= strURL %>?search=<%= Server.URLEncode(strSearch) %>&page=<%= I %>"><%= I %></a>
<%
End If
Next 'I

If iPageCurrent < iPageCount Then
%>
<a href="<%= strURL %>?search=<%= Server.URLEncode(strSearch) %>&page=<%= iPageCurrent + 1 %>">[Next >>]</a>
<%
End If
%>
</span> </p>
<%
End If

rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>
<p>
</body>
</html>

miranda

02-14-2005, 05:28 AM

Is there a relationship between the tables you want to search on? If so use a join. If not, then what about looping through the different tables running the query on each one.

reubenb

02-14-2005, 05:39 AM

OK
This is what I have.
A database with tables like
articles, photos, songs etc.
in each of them they contain
articles_id, articles_category, articles_subcategory, articles_title, description (etc.)
and then in photos
photos_id, photos_category, photos_subcategory, photos_title, description (etc.)
i want it so they can search through those tables and then give the combines results
ie

I search for 'asp'
Title: ASP coding is for you (from tblSongs)
Description: a great article

Title: We won the game (from tblSongs)
Description: i wrote this song in ASP

How do I do that?Like an example, can you give me of what i just said?

Thanks

miranda

02-14-2005, 06:30 AM

does articles_id correspond to the photos_id field? if so that is your relationship now to build a join in your sql statements.
SELECT articles.*, photos.*
FROM articles
INNER JOIN photos ON articles.articles_id = photos.photos_id

thats a join

Now if there is no relationship then put the name of all database tables into an array and loop through the array my additions are in blue

<%
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = &H0001
Const PAGE_SIZE = 5
Dim strURL
Dim cnnSearch
Dim rstSearch
Dim strDBPath
Dim strSQL
Dim strSearch
Dim iPageCurrent
Dim iPageCount
Dim iRecordCount
Dim I
strURL = Request.ServerVariables("URL")
strSearch = Request.QueryString("search")
strSearch = Replace(strSearch, "'", "''")

If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Search For News In The Database</title>
<style type="text/css">

</style>
</head>

<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" bgcolor="#F7F7F7" text="#666666" link="#993300" vlink="#993300" alink="#0066CC">
<form name="form1" method="get" action="<%= strURL %>">
<input name="search" type="text" value="<%= strSearch %>">
<br>
<input type="submit" name="Submit" value="Search">
</form>

<%
If strSearch <> "" Then
strDBPath = "db.mdb"
Set cnnSearch = Server.CreateObject("ADODB.Connection")
cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
Set rstSearch = Server.CreateObject("ADODB.Recordset")

Dim tableArray, x
tableArray = (articles, photos, songs)
For x = 0 to uBound(tableArray) strSQL = "SELECT content, header, " & tableArray(x) & "ID " _
& "FROM " & tableArray(x) _
& "WHERE header LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR content LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY " & tableArray(x) & "ID DESC;"

rstSearch.PageSize = PAGE_SIZE
rstSearch.CacheSize = PAGE_SIZE
rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText
iRecordCount = rstSearch.RecordCount
iPageCount = rstSearch.PageCount
If iRecordCount = 0 Then

%>
<p class="style2">
No news articles found. Please try again.
</p>
<%
Else
rstSearch.AbsolutePage = iPageCurrent
%>

<hr />
<p>
<span class="style2"><%= iRecordCount %> Records Found.
Displaying page <%= iPageCurrent %>
of <%= iPageCount %>:
</span></p>

<span class="style2">
<%

%>
</span>

<%
Do While Not rstSearch.EOF And rstSearch.AbsolutePage = iPageCurrent
%>

<span class="style3">
<%Response.Write("<a href=""displayNews.asp?newsID="& rstSearch.Fields(""" & tableArray(x) & "ID") & """" & ">")%>
<%= rstSearch.Fields("header").Value %><% Response.Write("</a>")%></span><br>

<%

rstSearch.MoveNext
Loop
%>
<p>
<%

If iPageCurrent > 1 Then
%>
<span class="style2"><a href="<%= strURL %>?search=<%= Server.URLEncode(strSearch) %>&page=<%= iPageCurrent - 1 %>">[<< Prev]</a>
<%
End If

For I = 1 To iPageCount
If I = iPageCurrent Then
%>
<%= I %>
<%
Else
%>
<a href="<%= strURL %>?search=<%= Server.URLEncode(strSearch) %>&page=<%= I %>"><%= I %></a>
<%
End If
Next 'I

If iPageCurrent < iPageCount Then
%>
<a href="<%= strURL %>?search=<%= Server.URLEncode(strSearch) %>&page=<%= iPageCurrent + 1 %>">[Next >>]</a>
<%
End If
%>
</span> </p>
<%
End If

rstSearch.Close
Next
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>
<p>
</body>
</html>

reubenb

02-14-2005, 06:42 AM

They are not related - no. They are just AutoNumbers that are in the order they come.

I uploaded that script and it returned this error below.

Microsoft VBScript compilation error '800a03ee'

Expected ')'

C:/Client Webs/internal/internal.lynxas.com/int.RBolaffi/test.asp, line 63

tableArray = (articles, photos, songs)
----------------------^

waolly

02-14-2005, 12:44 PM

I'm assuming each of your tables has the same number of fields (thats what it looks like). In this case it might have been better to use one table and add an extra field called type where, for example, type=1 means photo, type=2 means article, etc -- the same goes for the category table. This would have been more general. However lets look at the problem with your current structure.

Again, assuming the same number of fields, you can use the UNION operator. If you just want to search the title and description fields you can use the following code. It uses the UNION operator to combine the two tables and then search the appropriate fields. Obviously you can extend the code below to combine any number of tables using the UNION operator.

select * from
(select articles_title title, description from articles
union
select photos_title, description from photos) A
where title=<search term> or description=<search term>

miranda

02-15-2005, 05:40 AM

oops should have been
tableArray = Array(articles, photos, songs)

reubenb

02-15-2005, 06:40 AM

Miranda - that still doesn't work.
If i write the query it gives me
SELECT * FROM WHERE _title LIKE '%test%' OR _keywords LIKE '%test%' ORDER BY ID DESC;

i.e., it's not getting the table name (tableArray).

Waolly - don't exactly understand what you said in the first paragraph but I have to maintain my structure for the moment.

Please help!!!

THANKS! :thumbsup:

miranda

02-15-2005, 07:17 AM

try setting up your array like this then

tableArray(0) = "articles"
tableArray(1) = "photos"
tableArray(2) = "songs"

Then run it

reubenb

02-15-2005, 07:40 AM

Microsoft VBScript runtime error '800a000d'

Type mismatch

test.asp, line 63

Line 63: tableArray(0) = "articles"

waolly

02-15-2005, 09:25 AM

Hey, the first paragraph was just a thought - the code I gave (which I've posted again below) works for your current structure.

I often find that it is better to contain this sort of logic on the database (query) side because it easier to maintain when you do updates in the future.

select * from
(select articles_title title, description from articles
union
select photos_title, description from photos) A
where title=<search term> or description=<search term>

reubenb

02-17-2005, 06:48 AM

OK
well i got it to work sort of with Waolly's suggestion.

strSQL = "SELECT articles_desc, articles_title, type, articles_id, articles_keywords, articles_level FROM articles WHERE articles_level <= " & author_status & " AND articles_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR articles_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR articles_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT songs_desc, songs_title, type, songs_id, songs_keywords, songs_level FROM songs WHERE songs_level <= " & author_status & " AND songs_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR songs_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR songs_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%'UNION SELECT photos_desc, photos_title, type, photos_id, photos_keywords, photos_level FROM photos WHERE photos_level <= " & author_status & " AND photos_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR photos_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR photos_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT videos_desc, videos_title, type, videos_id, videos_keywords, videos_level FROM videos WHERE videos_level <= " & author_status & " AND videos_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR videos_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR videos_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT weblinks_desc, weblinks_title, type, weblinks_id, weblinks_keywords, weblinks_level FROM weblinks WHERE weblinks_level <= " & author_status & " AND weblinks_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR weblinks_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR weblinks_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT peulot_desc, peulot_title, type, peulot_id, peulot_keywords, peulot_level FROM peulot WHERE peulot_level <= " & author_status & " AND peulot_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR peulot_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR peulot_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT quotations_quotation, quotations_author, type, quotations_id, quotations_keywords, quotations_level FROM quotations WHERE quotations_level <= " & author_status & " AND quotations_quotation LIKE '%" & Replace(strSearch, "'", "''") & "%' OR quotations_author LIKE '%" & Replace(strSearch, "'", "''") & "%' OR quotations_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT generalchinuch_desc, generalchinuch_title, type, generalchinuch_id, generalchinuch_keywords, generalchinuch_level FROM generalchinuch WHERE generalchinuch_level <= " & author_status & " AND generalchinuch_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR generalchinuch_title LIKE '%" & Replace(strSearch, "'", "''") & "%' OR generalchinuch_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT keythemesquestions_keythemesquestions, keythemesquestions_desc, type, keythemesquestions_id, keythemesquestions_keywords, keythemesquestions_level FROM keythemesquestions WHERE keythemesquestions_level <= " & author_status & " AND keythemesquestions_keythemesquestions LIKE '%" & Replace(strSearch, "'", "''") & "%' OR keythemesquestions_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR keythemesquestions_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT relatedtopics_url, relatedtopics_topic, type, relatedtopics_id, relatedtopics_keywords, relatedtopics_level FROM relatedtopics WHERE relatedtopics_level <= " & author_status & " AND relatedtopics_topic LIKE '%" & Replace(strSearch, "'", "''") & "%' OR relatedtopics_url LIKE '%" & Replace(strSearch, "'", "''") & "%' OR relatedtopics_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%' UNION SELECT programideas_programideas, programideas_desc, type, programideas_id, programideas_keywords, programideas_level FROM programideas WHERE programideas_level <= "& author_status & "AND programideas_programideas LIKE '%" & Replace(strSearch, "'", "''") & "%' OR programideas_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' OR programideas_keywords LIKE '%" & Replace(strSearch, "'", "''") & "%'"

NOTE: I know its a big query - but just bare with me.

Why doesn't the WHERE articles_level <= " & author_status & " AND part work properly?