| </HEAD> <BODY>
<font face=Arial size=2 color=green><b>Start time : <%=starttime%></b></font><br> <font face=Arial size=2 color=red><b>Finish time : <%=finishtime%></b></font><br> <br>
<table bgcolor=Black border=0 cellpadding=1 cellspacing=0> <tr><td><font face=Arial size=1 color=white><b>Database Search</b></font></td></tr> <tr><td> <table width=100% bgcolor=White border=0 cellpadding=2 cellspacing=0><form> <tr> <td><input type=input name=str size=40 value="<%=server.HTMLEncode(Request.Querystring("str"))%>"></td> <td><input type=submit value="Search"></td> </tr> <tr><td colspan=2> <font face=Arial size=1 color=red><b> + : include filter the search result must include the keyword.<br> - : exclude filter, search result must not contain keyword.<br> & : logical AND the search must contain keyword1 AND keyword2.<br> | : logical OR the search must contain keyword1 OR keyword2.<br> ! : logical NOT the search must contain keyword1 BUT NOT keyword2.<br> </b></font> </td></tr> </form></table> </td></tr></table>
<p><font face=Arial size=2 color=black><u>Generated SQL Statement</u></font><br> <blockquote><font face=Arial size=1 color=blue><%=strSQL%></font></blockquote> </p>
</BODY> </HTML>
Page Two -- The include page..Search.asp<script language="JSCRIPT" RUNAT=Server> //------------------------------------------------------------------------------ //This is a generic Search keyword to SQL statement include. //the library takes search keywords and produces a equivalent SQL statement. //the search syntax accounts for advance symbols // + : include filter (the search result must include the keyword. // - : exclude filter, search result must not contain keyword. // & : logical AND the search must contain keyword1 AND keyword2 // | : logical OR the search must contain keyword1 OR keyword2 // ! : logical NOT the search must contain keyword1 BUT NOT keyword2 //------------------------------------------------------------------------------ //Written By: Salim Naim 26/2/2000 //Note: I'm more than happy to fix any Bugs or answer any questions regarding the //sample,i can be reached at snaim@zfp.com or salimn@yahoo.com //------------------------------------------------------------------------------ //Warning: This sample is assuming some knowlegde of the Structured Query Language(SQL) //------------------------------------------------------------------------------
//------------------------------------------------------------------------------------ //function: _addword //Description: the keyword object. //------------------------------------------------------------------------------------ function _word() { this.phrase=""; this.type=""; }
//------------------------------------------------------------------------------------ //function: _addword //Description: the helper function that adds a keyword object to the KeywordList //------------------------------------------------------------------------------------ function _addword(phrase,type) { var obj = new _word() obj.phrase = phrase; obj.type = type; return(obj); }
//------------------------------------------------------------------------------------ //function: KeywordList //Description: keyword link list. //------------------------------------------------------------------------------------ function KeywordList() { this.count=0; //the word is an object this.word = new _word(); this.add = AddKeyword; return this; }
//------------------------------------------------------------------------------------ //function: AddKeyword //Description: Add unique keywords into the search keyword list. //------------------------------------------------------------------------------------
function AddKeyword(word,type) { if(word.length > 0 ){ for(var i=0;i<this.count;i++) if(this.word[i].phrase == word) return; this.word[this.count++] = _addword(word,type); } }
//------------------------------------------------------------------------------------ //function: normalizeWord //Description: this function normalizes words inorder to increase hit results. //------------------------------------------------------------------------------------ function normalizeWord(keyword) { var esc = keyword.toLowerCase(); var kw = "";
for(var i=0;i < esc.length;i++){ var ch = esc.charAt(i); if(ch == '%') i += 2; else kw += ch; }
var len = kw.length; if (kw.charAt(len-1) == "s" && kw.charAt(len-2) != "s"){ kw = kw.substring(0,len-1); len--; } //acounnt for the " if (kw.charAt(len-1) == "\""){ kw = kw.substring(0,len-1); len--; } if (kw.charAt(0) == "\""){ kw = kw.substring(1,len); len--; }
if(kw == "in" && len == 2){ kw = ""; len -=2; } if(kw.substring(len-2,len) == "ly"){ kw = kw.substring(0,len-2); len -= 2; } if(kw.substring(len-2,len) == "ed"){ kw = kw.substring(0,len-1); len --; } if(kw.substring(len-2,len) == "er"){ kw = kw.substring(0,len-1); len --; } if(kw.substring(len-2,len) == "ie"){ kw = kw.substring(0,len-2) + y; len --; } if(kw.substring(len-3,len) == "ing" && len > 5){ kw = kw.substring(0,len-3); len -= 3; if(isVowel(kw.charAt(len-2)) && !isVowel(kw.charAt(len-3))){ kw += "e"; len++; } } if(kw.charAt(len-1) == "e") if(!isVowel(kw.charAt(len-3))){ kw = kw.substring(0,len-1); len--; } if(len > 1 && (kw.charAt(len-1) == kw.charAt(len-2))){ kw = kw.substring(0,len-1); len--; } return(kw); }
//------------------------------------------------------------------------------------ //function: isStopword //Description: this function excluded words that should not be classified as keywords. //------------------------------------------------------------------------------------ function isStopword(word) { var wd = word.toLowerCase(); if(wd == "a" || wd == "an" || wd == "and" || wd == "or" || wd == "the" || wd == "is" || wd == "this" || wd == "all") return(true); else return(false);
}
//------------------------------------------------------------------------------------ //function: parseKeywords //Description: this function adds unique filenames to the list of database fields that // we want to search. //------------------------------------------------------------------------------------ //Note: this is function was adopted from the BOOK using javascript, modifications have // been added inorder to add keyword search symbols //------------------------------------------------------------------------------------ function parseKeywords(str) { var list = new KeywordList(); var inword = false; var word = ""; var len = str.length; var firstcharacter = true; var Wordtype = "";
if(len <=0 ) return
for(var i=0;i<len;i++){ var ch = str.charAt(i); //check the first character in the keyword if(firstcharacter){ if(isSymbol(ch)) Wordtype = getSymbolType(ch); firstcharacter = false; }
if(isWhitespace(ch) || isDelimiter(ch)){ if(inword){ if(!isStopword(word)) list.add(normalizeWord(word),Wordtype);
word = ""; inword = false; firstcharacter = true } } else{ word += ch; inword = true; }
if( i+1 == len && inword) if(!isStopword(word)){ list.add(normalizeWord(word),Wordtype); } } return list }
//------------------------------------------------------------------------------------ //function: AddFieldName //Description: this function adds unique filenames to the list of database fields that // we want to search. //------------------------------------------------------------------------------------ function AddFieldName(fieldname) { if(fieldname.length > 0 ){ for(var i=0;i<this.count;i++) if(this.fields[i] == fieldname) return; this.fields[this.count++] = fieldname; } }
//------------------------------------------------------------------------------------ //function: MakeSQLString //Description: this function makes the SQL statement based on the list of // keyword being parsed by the string parser. //------------------------------------------------------------------------------------ //Note: this is an internal function use by the SearchSQL class //------------------------------------------------------------------------------------ function MakeSQLString(vbstr)
{ var strSQL = ""; var fieldname = ""; var szlogic = ""; var szNot; var fDonefirst = false; var ifieldstosearch=0;
//okay the string getting passed from //VbScript is a variant, create a String object //with this parameter because we expect a String. var str = new String(vbstr);
if (!(str.length > 0)) Response.Write("must pass a search string");
//Now Parse the keywords var list = parseKeywords(str);
//start of statement if(this.columns.length > 0) strSQL += "SELECT " + this.columns + " FROM " + this.tablename; else strSQL += "SELECT * FROM " + this.tablename;
//account for everything accept filter because filters //should be the last thing that controls the SQL statement
if (list.count > 0) strSQL += " WHERE ("
//now loop through the keywords in the search string. for(var i=0;i<this.count;i++){ //inner loop through the fieldnames that we should //search in the database. for(var j=0;j<list.count;j++){ ifieldstosearch++; //exclude the filter for now. if(!(list.word[j].type == "filerminus" || list.word[j].type == "fileradd" )){ //get the phrase and setup the SQL logic //symbols. szPhrase = list.word[j].phrase szNot = ""
//now the filter can be annoying if the number of fields //getting searched are more than 3,so change logic to OR //so that we can have a better chance of a hit. if(ifieldstosearch>2) szlogic = " OR " else szlogic = " AND "
//now check the frist character of the //keyword is it an OR || NOT if(szPhrase.length > 0){ if(isSymbol(szPhrase.charAt(0))){ if(szPhrase.charAt(0) == '|') szlogic = " OR "; //OR logic else if(szPhrase.charAt(0) == '!'){ szNot = " NOT "; //NOT LOGIC } //trim that character out. szPhrase = szPhrase.substring(1,szPhrase.length-1); } } //now the first time that we add the clause to the SQL //we have to make sure that we don't add the LOGIC if(!fDonefirst){ fieldname += " " + this.fields[i] + szNot + " LIKE \'%" + szPhrase + "%\'"; fDonefirst =true; } else fieldname += szlogic + this.fields[i] + szNot + " LIKE \'%" + szPhrase + "%'";
} } } //now done with the main search statement //lets start with the filters. fieldname += ") "
//filters. //filter are usually : //+{keyord} : to make sure that the result contains the keyword //-{keyowrd} : to make sure that the result excludes the keyword for(var i=0;i<this.count;i++){ for(var j=0;j<list.count;j++){ //only work with the filters. if(list.word[j].type == "filerminus" || list.word[j].type == "fileradd"){ szPhrase = list.word[j].phrase //trim the symbol szlogic = "" if(szPhrase.length > 0){ //Now filters are easy usually they are a Logic AND //incase of - then they should be a NOT AND if(isSymbol(szPhrase.charAt(0))){ if(szPhrase.charAt(0) == '-') szlogic = " NOT " szPhrase = szPhrase.substring(1,szPhrase.length-1); } } //add to critera fieldname += " AND " + this.fields[i] + szlogic + " LIKE \'%" + szPhrase + "%'"; } } }
//Now build the SQL statement //and return it. strSQL += fieldname; return(strSQL); }
//------------------------------------------------------------------------------------ // function: SearchSQL //Description: this is the master class that we instantiate for our search include //------------------------------------------------------------------------------------ //Note: this class is created via the helper function CreateSearchClass //------------------------------------------------------------------------------------ function SearchSQL() { this.tablename = ""; this.innerjoin = false; this.fields = new Object(); this.AddField = AddFieldName; this.count=0; this.execute = MakeSQLString; this.columns = ""; } //------------------------------------------------------------------------------------ // function: CreateSearchClass //Description: this function creates the SearcgSQL class. //--------------------------------------------------------------------------------- function CreateSearchClass(){return new SearchSQL()}
//helper functions function getTime() { var s=""; var c=":"; d = new Date(); s += d.getHours() + c; s += d.getMinutes() + c; s += d.getSeconds() + c; s += d.getMilliseconds(); return(s) }
function isWhitespace(ch){ if(ch == ' ' || ch == '\n' || ch == '\r' || ch == '\t' || ch == '\f' || ch == '\v' || ch == '\b') return(true) else return(false); }
function isSymbol(ch) { if(ch == '-' || ch == '+' || ch == '&' || ch == '|' || ch == '!') return(true); else return(false); }
function getSymbolType(ch) { var szType = ""; if(ch == '-') szType = "filerminus"; else if(ch == '+') szType = "fileradd"; else if(ch == '&') szType = "logicand"; else if(ch == '|') szType = "logicor"; else if(ch == '!') szType = "logicnot"; else szType = "normal";
return(szType); }
function isDelimiter(ch) { if(ch == ',' || ch == '?' || ch == '.' || ch == '\\' || ch == '/') return(true); else return(false); }
function isVowel(ch) { if(ch == 'a' || ch == 'e' || ch == 'i' || ch == 'o' || ch == 'u' || ch == 'y') return(true); else return(false); } </script> |