View Full Version : Re sorting search results from within the results

01-10-2005, 11:54 PM
I built a search engine that queries an access database.
It works perfectly, but I want to enhance it and I do not know how.
It searches a database of music cds and shows Title, Artist, Disc Number, Track Number and price. I would to be able to click the disk number and show what is on that cd.

If you could help me out I would appreciate it.

Here is my code:

option explicit

function iif(byval c, byval t, byval f)
if c then
iif = t
iif = f
end if
end function

const maxRecords = 800
dim dbPath

dbPath = server.MapPath("/") & iif(instr(server.MapPath("/"),"")>0,"","/")
dbPath = dbPath & "/Search/db.mdb"

Dim FieldSelected, SortFieldSelected

FieldSelected = request.form("Field") : if FieldSelected = "" then FieldSelected = "Title"
SortFieldSelected = request.form("SortField") : if SortFieldSelected = "" then SortFieldSelected = "Artist"


<title>Karaoke Search Engine</title>
<meta name="GENERATOR" content="LuTechASP">

<h3><font face="Arial">

<span style="font-weight: 400"><font size="3"><a target="_top" href="http://www.mysite.com">Back to home page.</a></font></span>&nbsp;&nbsp;
<a target="_top" href="http://www.mysite.com"><img border="0" src="back-arrow.jpg" width="64" height="40"></a>&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CD+G Karaoke Search</font></h3>

<p>When searching by artist, please use LastName, FirstName (including comma).<br>
If you would like to see what is on a certain disk, Search In DiskNo and enter the disk number in the search field.</P>

<form method="POST" action="search.asp">
<table border="0" height="29">
<td width="81" height="25" valign="middle"><font face="Arial">
Search In:</TD><TD width="297">
Search For:</TD>
<TD width="119">Sort List By:</TD>
<TD width="257">Click To Search:</TD>
<td width="81" height="25" valign="middle"><font face="Arial"><select name="Field" size="1">
<option <% = iif(FieldSelected="Title", "selected", "") %> value="Title">Title</option>
<option <% = iif(FieldSelected="Artist", "selected", "") %> value="Artist">Artist</option>
<option <% = iif(FieldSelected="DiscNo", "selected", "") %> value="DiscNo">DiscNo</option>
<option <% = iif(FieldSelected="Description", "selected", "") %> value="Description">Manufacturer</option>
<td height="25" width="297" valign="middle"><font face="Arial"><input type="text"
name="SearchWord" value="<% = iif(request.form("SearchWord")="", "enter search term here...", request.form("SearchWord")) %>" size="25"></TD>
<TD width="119"><small>
<select name="SortField" size="1">
<option <% = iif(SortFieldSelected="Title", "selected", "") %> value="Title">Title</option>
<option <% = iif(SortFieldSelected="Artist", "selected", "") %> value="Artist">Artist</option>
<option <% = iif(SortFieldSelected="Description", "selected", "") %> value="Description">Manufacturer</option>
<option <% = iif(SortFieldSelected="DiscNo", "selected", "") %> value="DiscNo">DiscNo</option>
<td width="257" height="25" valign="middle"><font face="Arial"><input type="submit" value="Search" name="Lu2"></font></td>

if request.form("SearchWord") <> "" then
dim conn, rs, sql

conn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & dbPath

set rs = createobject("ADODB.Recordset")

sql = "SELECT TOP " & (maxRecords + 1) & " * " & _
" FROM Lu2 " & _
" WHERE InStr (" & request.form("Field") & ", " & chr(34) & replace(replace(request.form("SearchWord"),"*","%"), "'", "''") & chr(34) & ") > 0 " & _
" ORDER BY " & request.form("SortField")
'response.write sql

rs.open sql, conn, 1, 1

if not rs.EOF then

<table border="1" width="100%">
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>Title</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>Artist</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>Description</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>DiscNo</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>TrackNo</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>Price</small></small></strong></font></td>
Do While not rs.EOF
<td width="20%" valign="top"><small><font face="Arial"><% = "" & rs.fields("Title") %></font></small> &nbsp;</td>
<td width="20%" valign="top"><small><font face="Arial"><% = "" & rs.fields("Artist") %></font></small> &nbsp;</td>
<td width="20%" valign="top"><small><font face="Arial"><% = "" & rs.fields("Description") %></font></small> &nbsp;</td>
<td width="15%" valign="top"><small><font face="Arial"><a href="search.asp?Field=DiscNo&SearchWord=<% = "" & rs.fields("DiscNo") %>&SortField=TrackNo" request.form("SearchWord")><% = "" & rs.fields("DiscNo") %></a></font></small> &nbsp;</td>
<td width="15%" valign="top"><small><font face="Arial"><% = "" & rs.fields("TrackNo") %></font></small> &nbsp;</td>
<td width="15%" valign="top"><small><font face="Arial"><A href=http://www.mysite.com/shopaddtocartnodb.asp?productname=<% = "" & rs.fields("DiscNo") %>&quantity=1&Price=<% = "" & rs.fields("Price") %> target="_top"><% = "" & rs.fields("Price") %></font></small> &nbsp;</td>
<font face="Arial"><small><b><p>No matching records found.</b></small></font>
end if

if rs.recordcount > maxRecords then
%> <br>
<font face="Arial"><small><b>Too many matching records. Please narrow your search criteria.</b></small></font> <%
end if

end if
%> </p>

01-11-2005, 08:57 AM
If you want to show the results on same page then here are some options.

One thing you can do is set the title attribute of an anchor tag to be the info you want to show. This works like a tooltip in windows on mouseover.

Another is to use a hidden div that again onMouseover will show the info in this div. problem here is if the list is long unless you float the div, this may be offscreen. so best to use a floating div that keys off x and y coordinates of mouse on screen.

or you can generate a popup window to list the songs problem here is popup blocker will block this

Remember thought that if it is a long list of results returned then this could be slow loading on dialup connections

01-11-2005, 02:45 PM
this is more javascript but the div idea can be used with block/inline in the style, or the visibility.. this way it does not have to be actaully moved.. i would just use the popup idea though :thumbsup: