ASP Avanceret søgning i en Mysql Database
Hej Eksperter!Jeg kunne godt tænke mig at min ASP søgefunktion på min hjemmeside, hvor alt ligger i en MySQL databse, i stedet for bare at søge på "hele strengen" valgfrit og som standard kunne søge på de enkelte ord "AND"´et (Boolean),
så når man f.eks. søgte på "Rene Tusser"
så fik man ikke kun resultater med "Rene Tusser", men også resultater som indeholder f.eks. "Rene blå Tusser"...
Er der nogen der kan finde ud af det, og som gider hjælpe med det? - det er jo lidt af en opgave! (Derfor de 150 point! :-))
Nedenfor er min eksisterende ASP Søgefunktion,
håber på at få et godt svar integreret i dette stykke ASP:
if Request.Querystring("NumberList") = "true" AND Session("Addver") = "DK" then
Searched = "Numre"
elseif Request.Querystring("NumberList") = "true" AND Session("Addver") = "DE" then
Searched = "Nummern"
elseif Request.Querystring("NumberList") = "true" then
Searched = "Numbers"
else
Searched = Request.Querystring("SearchString")
end if
Record.Movenext
loop
set record = nothing
%>
<A Href="java script:history.go(-1)"><img src="left_icon.gif" width="19" height="10" alt="" border="0"> <u>
<%
if Session("Addver") = "DK" then
response.write("Tilbage")
elseif Session("Addver") = "DE" then
response.write("Zurück")
else
response.write("Back")
end if
%>
</u></a><font size="-2"><br></font>
<!-- #include virtual ="/newslist.asp" -->
<%
Dim cmdStr
TableWidth = 500
TDwidth = 100
TDwidth1 = 400
SQLInserted = false
cmd = Request.Querystring("where") & Request.Querystring("field") & Request.Querystring("SearchString")
if cmd <> "" then
Select Case Ucase(Request.Querystring("where"))
Case "START"
likeStr = "'" & Request.Querystring("SearchString") & "%'"
Case "PARTOF"
likeStr = "'%" & Request.Querystring("SearchString") & "%'"
Case "HOLE"
likeStr = "'" & Request.Querystring("SearchString") & "'"
End Select
if Ucase(Request.Querystring("field")) = "ALLE" then
Sql = "SELECT * FROM poster where (titel LIKE "&likeStr&" OR filnavn LIKE "&likeStr&" OR beskrivelse LIKE "&likeStr&") "
else
Sql = "SELECT * FROM poster where "& Request.Querystring("field") &" LIKE "&likeStr
end if
cmdStr = "&where="& Request.Querystring("where") & "&field=" & Request.Querystring("field") & "&SearchString="& Request.Querystring("SearchString")& "&Type="& Request.Querystring("type")& "&Platform="& Request.Querystring("platform")& "&View="& Request.Querystring("View")& "&NumberList="& Request.Querystring("NumberList")
else
Sql = "SELECT * FROM poster"
end if
if (Request.Querystring("type") <> "") AND (Ucase(Request.Querystring("type")) <> "ALLE") then
if Request.Querystring("platform") <> "" then
SQL = SQL & " AND posttype = '"& Request.Querystring("type") &"' AND platform = '"& Request.Querystring("platform") &"'"
else
SQL = SQL & " AND posttype = '"& Request.Querystring("type") &"'"
end if
SQLInserted = true
end if
if (Request.Querystring("platform") <> "") AND not SQLInserted AND (UCASE(Request.Querystring("platform")) <> "ALLE") then
if Request.Querystring("type") <> "" then
SQL = SQL & " AND posttype = '"& Request.Querystring("type") &"' AND platform = '"& Request.Querystring("platform") &"'"
else
SQL = SQL & " AND platform = '"& Request.Querystring("platform") &"'"
end if
end if
if Request.Querystring("field") <> "" then
if Ucase(Request.Querystring("field")) = "ALLE" then
SQL = SQL & " order by id"
else
SQL = SQL & " order by "& Request.Querystring("field")
end if
end if
if Request.QueryString("NumberList") = "true" then
SQL = "SELECT * FROM poster where (titel LIKE '0%' OR titel LIKE '1%' OR titel LIKE '2%' OR titel LIKE '3%' OR titel LIKE '4%' OR titel LIKE '5%' OR titel LIKE '6%' OR titel LIKE '7%' OR titel LIKE '8%' OR titel LIKE '9%') order by titel"
if (Request.Querystring("type") <> "") then SQL = "SELECT * FROM poster where (titel LIKE '0%' OR titel LIKE '1%' OR titel LIKE '2%' OR titel LIKE '3%' OR titel LIKE '4%' OR titel LIKE '5%' OR titel LIKE '6%' OR titel LIKE '7%' OR titel LIKE '8%' OR titel LIKE '9%') AND posttype = '"& Request.Querystring("type") &"' order by titel"
cmdStr = cmdStr & "&NumberList=true"
end if
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQL, Session("db"), 1, 3
antal = 0
do while not rs.eof
antal = antal + 1
rs.movenext
loop
response.write antal
response.write " Hits...</div>"
if Ucase(request.querystring("update")) = "TRUE" then updateStat Request.Querystring("SearchString"),antal
Set RecordSet = Server.CreateObject("ADODB.RecordSet")
if request.querystring("offset") <> "" then
if request.querystring("range") <> "" then
Sql = Sql & " limit "& request.querystring("offset") &","& request.querystring("range")
range = int(request.querystring("range"))
offset = int(request.querystring("offset")) + int(range)
else
Sql = Sql & " limit "& request.querystring("offset") &",30"
range = 30
offset = int(request.querystring("offset"))
end if
else
if request.querystring("range") <> "" then
Sql = Sql & " limit "& request.querystring("range")
offset = int(request.querystring("offset")) + 30
range = int(request.querystring("range"))
else
Sql = Sql & " limit 30"
offset = 30
range = 30
end if
end if %>
<table width="600" align="center">
<tr>
<td align="left" width="250"><nobr>
<% if Session("Addver") = "DK" then
LangBack = "Forrige "
LangNext = "Næste "
elseif Session("Addver") = "DE" then
LangBack = "Rückwärts "
LangNext = "Nächste "
else
LangBack = "Back "
LangNext = "Next "
end if %>
<% if int(offset-range-range) => 0 then %>
<a href="?offset=<% response.write offset-range-range %>&range=<% response.write range %><% response.write server.htmlencode(cmdstr) %>">
<%
if range <> "" then %>
<img src="left_icon.gif" width="19" height="10" alt="" border="0">
<% response.write LangBack & range
else
response.write LangBack & "30"
end if
%>
</a>
<% end if %>
</nobr>
</td>
<td align="right" width="250"><nobr>
<a href="?offset=<% response.write offset %>&range=<% response.write range & server.htmlencode(cmdStr) %>">
<%
if range <> "" then
if (offset + range) > Antal then
if not offset >= Antal then response.write LangNext & antal-offset & "<img src='right_icon.gif' width='19' height='10' alt='' border='0'>"
else
response.write LangNext & range & "<img src='right_icon.gif' width='19' height='10' alt='' border='0'>"
end if
else
if (offset + 30) > Antal then
if not offset >= Antal then response.write LangNext & antal-offset & "<img src='right_icon.gif' width='19' height='10' alt='' border='0'>"
else
response.write LangNext & "30" & "<img src='right_icon.gif' width='19' height='10' alt='' border='0'>"
end if
end if
%>
</nobr>
</a>
</td>
</tr>
</table>
<% RecordSet.open SQL, Session("db"), 1, 3
DO WHILE not RecordSet.EOF
select case UCase(request.querystring("View"))
case "LIST"
%>
<table width="600" border="1" cellspacing="1" cellpadding="1" align=center valign="top">
<tr>
<td align="center" valign=top width="20">
<%
if Session("Addver") = "DK" then
response.write("Titel")
elseif Session("Addver") = "DE" then
response.write("Titel")
else
response.write("Title")
end if
%>
</td>
<td width="400"><nobr>
<a onmouseover='window.status="Cheaters Heaven"; return true' onMouseout='window.status="Cheaters Heaven"; return true' href='?ID=
<% response.write TxtFraDB(RecordSet("id"))
response.write MenuLang & "'><u>"
if TxtFraDB(RecordSet("platform")) = "PS" then
response.write("<img src='ps_icon.gif' width='16' height='16' alt='' border='0'> ")
elseif TxtFraDB(RecordSet("platform")) = "XBOX" then
response.write("<img src='xbox_icon.gif' width='16' height='16' alt='' border='0'> ")
elseif TxtFraDB(RecordSet("platform")) = "C64" then
response.write("<img src='c64_icon.gif' width='16' height='16' alt='' border='0'> ")
elseif TxtFraDB(RecordSet("platform")) = "PC" then
response.write("<img src='pc_icon.gif' width='16' height='16' alt='' border='0'> ")
end if
response.write TxtFraDB(RecordSet("titel"))
response.write " </u></a></nobr>"
%>
<td width="20">
<%
if Session("Addver") = "DK" then
response.write("<nobr> Til: ")
elseif Session("Addver") = "DE" then
response.write("<nobr> Für: ")
else
response.write("<nobr> For: ")
end if
if TxtFraDB(RecordSet("platform")) = "PS" then
response.write(" PlayStation 1/2/PSx")
elseif TxtFraDB(RecordSet("platform")) = "XBOX" then
response.write(" MS XBOX")
elseif TxtFraDB(RecordSet("platform")) = "C64" then
response.write(" Commodore 64")
elseif TxtFraDB(RecordSet("platform")) = "PC" then
response.write(" PC")
else
response.write TxtFraDB(RecordSet("platform"))
end if
%>
</td>
</tr>
<% if TxtFraDB(RecordSet("filnavn")) <> "" then %>
<tr>
<td valign=top width="50">
<%
if Session("Addver") = "DK" then
response.write("Filnavn")
elseif Session("Addver") = "DE" then
response.write("DateiName")
else
response.write("Filename")
end if
%>
</td>
<td width="400">
<a onmouseover='window.status="Cheaters Heaven"; return true' onMouseout='window.status="Cheaters Heaven"; return true' href='http://www.cheaters-heaven.com/download.php?site=35&file=<% response.write TxtFraDB(RecordSet("filnavn"))
response.write "'><img src='zip_icon.gif' width='16' height='16' alt='' border='0'><u>"
response.write TxtFraDB(RecordSet("filnavn"))
response.write "</u></A>"
%>
<% if TxtFraDB(RecordSet("posttype")) = "Cheat" then %>
<% end if %>
</td>
</tr>
<% end if %>
<% if session("UserApproved") = true then %>
<tr>
<td valign=top width="100" align=right colspan=2>
<input type="button" value="Ret" onclick="java script:self.location.href='postAdmin.asp?cmd=RET&ID=<% response.write RecordSet("id") %>'">
<A href='postAdmin.asp?cmd=RET&ID=<% response.write RecordSet("id") %>' target="_newe">Ret</A>
<input type="button" value="Slet" onclick="java script:self.location.href='postAdmin.asp?cmd=SLET&ID=<% response.write RecordSet("id") %>'">
</td>
</tr>
<% end if %>
</table>
<br>
<%
case "DETAIL"
%>
<table width="600" border="1" cellspacing="1" cellpadding="1" align=center valign="top">
<tr>
<td align="center" valign=top width="20">
<%
if Session("Addver") = "DK" then
response.write("Titel")
elseif Session("Addver") = "DE" then
response.write("Titel")
else
response.write("Title")
end if
%>
</td>
<td width="400"><nobr>
<a onmouseover='window.status="Cheaters Heaven"; return true' onMouseout='window.status="Cheaters Heaven"; return true' href='?ID=
<% response.write TxtFraDB(RecordSet("id"))
response.write MenuLang & "'><u>"
if TxtFraDB(RecordSet("platform")) = "PS" then
response.write("<img src='ps_icon.gif' width='16' height='16' alt='' border='0'> ")
elseif TxtFraDB(RecordSet("platform")) = "XBOX" then
response.write("<img src='xbox_icon.gif' width='16' height='16' alt='' border='0'> ")
elseif TxtFraDB(RecordSet("platform")) = "C64" then
response.write("<img src='c64_icon.gif' width='16' height='16' alt='' border='0'> ")
elseif TxtFraDB(RecordSet("platform")) = "PC" then
response.write("<img src='pc_icon.gif' width='16' height='16' alt='' border='0'> ")
end if
response.write TxtFraDB(RecordSet("titel"))
response.write " </u></a></nobr>"
%>
<td width="20">
<%
if Session("Addver") = "DK" then
response.write("<nobr> Til: ")
elseif Session("Addver") = "DE" then
response.write("<nobr> Für: ")
else
response.write("<nobr> For: ")
end if
if TxtFraDB(RecordSet("platform")) = "PS" then
response.write(" PlayStation 1/2/PSx")
elseif TxtFraDB(RecordSet("platform")) = "XBOX" then
response.write(" MS XBOX")
elseif TxtFraDB(RecordSet("platform")) = "C64" then
response.write(" Commodore 64")
elseif TxtFraDB(RecordSet("platform")) = "PC" then
response.write(" PC")
else
response.write TxtFraDB(RecordSet("platform"))
end if
%>
</td>
</tr>
<% if TxtFraDB(RecordSet("filnavn")) <> "" then %>
<tr>
<td valign=top width="50">
<%
if Session("Addver") = "DK" then
response.write("Filnavn")
elseif Session("Addver") = "DE" then
response.write("DateiName")
else
response.write("Filename")
end if
%>
</td>
<td width="400">
<a onmouseover='window.status="Cheaters Heaven"; return true' onMouseout='window.status="Cheaters Heaven"; return true' href='http://www.cheaters-heaven.com/download.php?site=35&file=<% response.write TxtFraDB(RecordSet("filnavn"))
response.write "'><img src='zip_icon.gif' width='16' height='16' alt='' border='0'><u>"
response.write TxtFraDB(RecordSet("filnavn"))
response.write "</u></A>"
%>
</td>
</tr>
<% end if %>
<tr>
<td valign=top width="20">
<%
if Session("Addver") = "DK" then
response.write("Beskrivelse")
elseif Session("Addver") = "DE" then
response.write("Beschreibung")
else
response.write("Description")
end if
%>
</td>
<td class="dostxt" width="500" colspan="2">
<%
response.write "<div style='text-align:left;align:center;width:500px'><font face='Courier New'><Code>" %>
<% if TxtFraDB(RecordSet("posttype")) = "Trainer" then %>
<% response.write "<PRE WIDTH=80>" %>
<% end if %>
<%
response.write TxtFraDBTilHTML(RecordSet("beskrivelse"))
response.write "</code></div>"
%>
<% if TxtFraDB(RecordSet("posttype")) = "Trainer" then %>
<% response.write "</PRE>" %>
<% end if %>
<% if TxtFraDB(RecordSet("posttype")) = "Cheat" then %>
<% end if %>
</td>
</tr>
</table>
<center>
<%
end select
RecordSet.movenext
loop
set RecordSet = nothing
%>
<table width="600" align="center">
<tr>
<td align="left" width="250"><nobr>
<% if int(offset-range-range) => 0 then %>
<a href="?offset=<% response.write offset-range-range %>&range=<% response.write range %><% response.write server.htmlencode(cmdstr) %>">
<%
if range <> "" then %>
<img src="left_icon.gif" width="19" height="10" alt="" border="0">
<% response.write LangBack & range
else
response.write LangBack & "30"
end if
%>
</a>
<% end if %>
</nobr>
</td>
<td align="right" width="250"><nobr>
<a href="?offset=<% response.write offset %>&range=<% response.write range & server.htmlencode(cmdStr) %>">
<%
if range <> "" then
if (offset + range) > Antal then
if not offset >= Antal then response.write LangNext & antal-offset & "<img src='right_icon.gif' width='19' height='10' alt='' border='0'>"
else
response.write LangNext & range & "<img src='right_icon.gif' width='19' height='10' alt='' border='0'>"
end if
else
if (offset + 30) > Antal then
if not offset >= Antal then response.write LangNext & antal-offset & "<img src='right_icon.gif' width='19' height='10' alt='' border='0'>"
else
response.write LangNext & "30" & "<img src='right_icon.gif' width='19' height='10' alt='' border='0'>"
end if
end if
%>
</nobr>
</a>
</td>
</tr>
</table>
<center></u>
<%
Set Record = Server.CreateObject("ADODB.RecordSet")
%>
<form method="post" name="Searchform" action="?cmd=ExecuteSearch<%=MenuLang %>">
<input type="hidden" name="View" value="List">
<table>
<tr>
<td>
<%
if Session("Addver") = "DK" then
response.write("Søg efter:")
elseif Session("Addver") = "DE" then
response.write("Such nach:")
else
response.write("Search for:")
end if
%>
</td>
<td>
<% if (Request.Querystring("ID") <> "") AND (Request.Querystring("NumberList") <> "true") Then %>
<INPUT TYPE="TEXT" NAME="SearchString" SIZE="15" VALUE="<% response.write TxtFraDB(RecordSet("titel")) %>">
<% elseif Request.Querystring("numberlist") <> "true" Then %>
<INPUT TYPE="TEXT" NAME="SearchString" SIZE="15" VALUE="<%=Request.Querystring("SearchString") %>">
<% else %>
<INPUT TYPE="TEXT" NAME="SearchString" SIZE="15" VALUE="
<%
if Session("Addver") = "DK" then
response.write("Numre")
elseif Session("Addver") = "DE" then
response.write("Nummern")
else
response.write("Numbers")
end if
%>">
<% end if %>
</td>
</tr>
<tr>
<td>
<%
if Session("Addver") = "DK" then
Alle = "Alle"
searchcmd = "Søg"
elseif Session("Addver") = "DE" then
Alle = "Alle"
searchcmd = "Such"
else
Alle = "All"
searchcmd = "Search"
end if
%>
<%
if Session("Addver") = "DK" then
response.write("Felt:")
elseif Session("Addver") = "DE" then
response.write("Felt:")
else
response.write("Field:")
end if
%>
</td>
<td>
<SELECT NAME="field">
<%
if Request.Querystring("field") = "titel" Then
fieldtitel = "SELECTED"
elseif Request.Querystring("field") = "filnavn" Then
fieldfilnavn = "SELECTED"
elseif Request.Querystring("field") = "beskrivelse" Then
fieldbeskrivelse = "SELECTED"
elseif Request.Querystring("field") = "type" Then
fieldtype = "SELECTED"
elseif Request.Querystring("field") = "platform" Then
fieldplatform = "SELECTED"
else
fieldalle = "SELECTED"
end if %>
<% if Session("Addver") = "DK" then %>
<OPTION <%=fieldalle %> VALUE='alle'>Alle<OPTION <%=fieldtitel %> VALUE='titel'>Titel<OPTION <%=fieldfilnavn %> VALUE='filnavn'>Filnavn<OPTION <%=fieldbeskrivelse %> VALUE='beskrivelse'>Beskrivelse<OPTION <%=fieldtype %> VALUE='type'>Type<OPTION <%=fieldplatform %> VALUE='platform'>Platform
<% elseif Session("Addver") = "DE" then %>
<OPTION <%=fieldalle %> VALUE='alle'>Alle<OPTION <%=fieldtitel %> VALUE='titel'>Titel<OPTION <%=fieldfilnavn %> VALUE='filnavn'>Filname<OPTION <%=fieldbeskrivelse %> VALUE='beskrivelse'>Bechreibung<OPTION <%=fieldtype %> VALUE='type'>Typ<OPTION <%=fieldplatform %> VALUE='platform'>Platform
<% else %>
<OPTION <%=fieldalle %> VALUE='alle'>All<OPTION <%=fieldtitel %> VALUE='titel'>Title<OPTION <%=fieldfilnavn %> VALUE='filnavn'>Filename<OPTION <%=fieldbeskrivelse %> VALUE='beskrivelse'>Description<OPTION <%=fieldtype %> VALUE='type'>Type<OPTION <%=fieldplatform %> VALUE='platform'>Platform
<% end if %>
</SELECT>
</td>
</tr>
<tr>
<td>
<%
If Session("Addver") = "DE" then
response.write("Typ:")
else
response.write("Type:")
end if
%>
</td>
<td>
<%
Sql = "select * from posttype"
Record.open SQL, Session("db"), 1, 3
%>
<SELECT NAME="Type">
<% if Request.Querystring("type") = "" Then %>
<OPTION SELECTED VALUE="">All
<% else %>
<OPTION VALUE="">All
<% end if %>
<%
Do While Not Record.EOF
if Request.Querystring("type") = TxtFraDB(Record("navn")) Then
Response.Write "<OPTION SELECTED VALUE='" & Record("navn") & "'>" & TxtFraDB(Record("navn"))
else
Response.Write "<OPTION VALUE='" & Record("navn") & "'>" & TxtFraDB(Record("navn"))
end if
Record.MoveNext
Loop
Record.Close
%>
</SELECT>
</td>
</tr>
<tr>
<td>
Platform:
</td>
<td>
<select name="PlatForm" Size="1">
<% if Request.Querystring("platform") = "PS" then %>
<option SELECTED VALUE="PS">PS 1/2/x
<option>XBOX
<option>PC
<option VALUE="C64">C=64
<option VALUE=""><%=Alle %>
<% elseif Request.Querystring("platform") = "XBOX" then %>
<option VALUE="PS">PS 1/2/x
<option SELECTED>XBOX
<option>PC
<option VALUE="C64">C=64
<option VALUE=""><%=Alle %>
<% elseif Request.Querystring("platform") = "C64" then %>
<option VALUE="PS">PS 1/2/x
<option>XBOX
<option>PC
<option SELECTED VALUE="C64">C=64
<option VALUE=""><%=Alle %>
<% elseif Request.Querystring("platform") = "PC" then %>
<option VALUE="PS">PS 1/2/x
<option>XBOX
<option SELECTED>PC
<option VALUE="C64">C=64
<option VALUE=""><%=Alle %>
<% else %>
<option VALUE="PS">PS 1/2/x
<option>XBOX
<option>PC
<option VALUE="C64">C=64
<option SELECTED VALUE=""><%=Alle %>
<% end if %>
</select>
</td>
</tr>
<tr>
<td valign="top">
<%
if Session("Addver") = "DK" then
response.write("Søg på:")
elseif Session("Addver") = "DE" then
response.write("Such auf:")
else
response.write("Search on:")
end if
%>
</td>
<td>
<table>
<tr>
<td>
<input type="radio" name="where" value="hole">
<%
if Session("Addver") = "DK" then
response.write("Hele feltet")
elseif Session("Addver") = "DE" then
response.write("Ganzes felt")
else
response.write("Entire Field")
end if
%>
</td>
</tr>
<tr>
<td>
<input type="radio" name="where" value="start">
<%
if Session("Addver") = "DK" then
response.write("Start af felt")
elseif Session("Addver") = "DE" then
response.write("Start vom felt")
else
response.write("Start of Field")
end if
%>
</td>
</tr>
<tr>
<td>
<input type="radio" name="where" value="partof" Checked>
<%
if Session("Addver") = "DK" then
response.write("Del af felt")
elseif Session("Addver") = "DE" then
response.write("Teil vom felt")
else
response.write("Part of Field")
end if
%>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan="2">
<%
if Mid(LangU, 1, 2) = "da" then
searchcmd = "Søg"
elseif Mid(LangU, 1, 2) = "de" then
searchcmd = "Such"
elseif Mid(LangU, 1, 5) = "en-gb" then
searchcmd = "Search"
elseif Mid(LangU, 1, 2) = "en" then
searchcmd = "Search"
else
searchcmd = "Search"
end if
%>
<INPUT TYPE="SUBMIT" VALUE="<%=searchcmd %>" NAME="Send">
</td>
</tr>
</table>
</form>
<% Set Record = Nothing %>
