Jump to content

Search box that searches multiple words


ahill
 Share

Recommended Posts

Break up the search text into individual words, and then loop through them to build the query that uses AND or OR to find any or all of them. First write out a query that would work like you want it to for 3 words, then think about how to build that query in code.

Link to comment
Share on other sites

For example if I have a database right now if I search "oil type" it only brings up"what is my oil type"Where "oil and type "are right next to each other.and I want it to bring up"What is my oil type"" what oil goes in my type of engine"Where oil and type can be anywhere in the sentencemy sql looks like thissql="select distinct * from database where mproblem like '" +mproblem+ "' and post='true'"

Edited by ahill
Link to comment
Share on other sites

SELECT * FROM table WHERE field LIKE '%oil%' AND field LIKE '%type%'You could also use OR instead of AND to find any term instead of all terms.

so I only have one DB column that I am searching which is mproblem so you are sayingSelect * from DBtable WHERE mproblem LIKE % & mproblem & % and field LIKE % &mproblem & %
Link to comment
Share on other sites

SELECT * FROM DBtable WHERE mproblem LIKE '%oil%' AND mproblem LIKE '%type%'

so do I actually wana use oil and type in my code or do I want to leave the % % blank or should I actually populate them? The text gets entered in a search box that references the mproblem column on the classic ASP vbscript side. my Dim is mproblem=request("problem") Edited by ahill
Link to comment
Share on other sites

so do I actually wana use oil and type in my code

Do you wana always search for "oil" and "type"?I posted an example of the query that you should send to the server if they type in "oil type" like you said as an example. If you run that query you will notice that it returns every record that contains both "oil" and "type". That's how you tell the database to do that, you need to split up whatever they typed into individual words and then loop through them and build the query to search for each word in whatever field you're searching. If they typed 5 words then there would be 5 LIKE clauses, if they only entered 1 word the query would only contain 1 LIKE clause. The % sign is a wildcard character in standard SQL saying that there can be any other characters there.
Link to comment
Share on other sites

Do you wana always search for "oil" and "type"?I posted an example of the query that you should send to the server if they type in "oil type" like you said as an example. If you run that query you will notice that it returns every record that contains both "oil" and "type". That's how you tell the database to do that, you need to split up whatever they typed into individual words and then loop through them and build the query to search for each word in whatever field you're searching. If they typed 5 words then there would be 5 LIKE clauses, if they only entered 1 word the query would only contain 1 LIKE clause. The % sign is a wildcard character in standard SQL saying that there can be any other characters there.

So I have to try and write a code that generates a new like clause for every word in the searchbox?
Link to comment
Share on other sites

Break up search string using explode, then use foreach to loop through each split up search word to produce sql string that joins each word with prefix of "mproblem LIKE '%".$search_word."%' so when its finished you will have SQL ofSELECT * FROM DBtable WHERE SELECT * FROM DBtable WHERE mproblem LIKE '%oil%' AND mproblem LIKE '%type%' (Where "mproblem LIKE '%oil%' AND mproblem LIKE '%type%'" will be produced by foreach loop)Then use this to search database table

Link to comment
Share on other sites

Just learned the basics of classic asp.

Why? That language has been obsolete for over 10 years. The last release was 15 years ago.The thing to know about ASP is that it is just a framework, not a language. It looks like you're learning it using VBScript as the language, which is common but in my opinion VBScript is one of the worse languages. It would be a lot more straight-forward to do the same thing in Javascript, which you can also use with ASP. Most tutorials use VBScript though. But, you're looking for a tutorial on building strings using VBScript by looping through an array. You can use split to break up the list into individual words, and then loop through the array of words to build the SQL query.http://www.w3schools.com/vbscript/func_split.asp
Link to comment
Share on other sites

Why? That language has been obsolete for over 10 years. The last release was 15 years ago.The thing to know about ASP is that it is just a framework, not a language. It looks like you're learning it using VBScript as the language, which is common but in my opinion VBScript is one of the worse languages. It would be a lot more straight-forward to do the same thing in Javascript, which you can also use with ASP. Most tutorials use VBScript though. But, you're looking for a tutorial on building strings using VBScript by looping through an array. You can use split to break up the list into individual words, and then loop through the array of words to build the SQL query.http://www.w3schools.com/vbscript/func_split.asp

Eh that's what I was told we had to code in right now and will be switching over to .net later into the fall.
Link to comment
Share on other sites

OK, but the code on the backend is the same. The only difference is the Javascript code on the frontend.

ok. Just looking at the layout it looks like it would be a lot easier to have search suggestions as you type so the user knows that there is and I am hoeing that making it like a Google search will be a lot easier and friendlier to navigate.
Link to comment
Share on other sites

Which language do you want to use other than PHP?

only thing I have access to right now is classic ASP. ASP.Net isn't installed on the IIS and they wont get around to it till this fall I guess. So only thing I can really work with is classic ASP and javascript
Link to comment
Share on other sites

You'll need to either just write from scratch or convert PHP to ASP. There wasn't a lot of ajax back when ASP classic was at its peak. IE5 had an early version in 1999 but the proper XHR object wasn't first implemented until 2002 and didn't have a draft specification until 2006.The core of the code is the same though, it's still just getting input from either POST or GET, doing some database work, and outputting a response. The major difference is the format of the response, with ajax it is not a complete HTML document. It could be an HTML fragment, or it could be a data structure like JSON or XML.

Link to comment
Share on other sites

Ok so I was able to find some javascript and then match it to an asp style code. The box drops down with data but it is 1.not filtering 2. filled with every single DB entry for mproblem. any idea where the code is missing to filter? I kind of understand the JS and the asp but it looks somewhat foreign to me index.html---------------------------------- <html><head><script>function showResult(str) {if (str.length==0) {document.getElementById("livesearch").innerHTML="";document.getElementById("livesearch").style.border="0px";return;}if (window.XMLHttpRequest) {// code for IE7+, Firefox, Chrome, Opera, Safarixmlhttp=new XMLHttpRequest();} else { // code for IE6, IE5xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");}xmlhttp.onreadystatechange=function() {if (xmlhttp.readyState==4 && xmlhttp.status==200) {document.getElementById("livesearch").innerHTML=xmlhttp.responseText;document.getElementById("livesearch").style.border="1px solid #A5ACB2";}}xmlhttp.open("GET","livesearch.asp?q="+str,true);xmlhttp.send();}</script></head><body><form><input type="text" size="30" onKeyUp="showResult(this.value)"><div id="livesearch"></div></form></body></html> livesearch.asp--------------------------------------</head><body><%term = Replace( Trim(Request("word")), "'", "''" ) ' protect against SQL injection even here!SQL = "select top 10 * mproblem from DBtable where mproblem like '%" & term & "%'"Set conn = Server.CreateObject("ADODB.Connection")Conn.Open "DSN=test;UID=test;PWD=test1234;Database=test"Set RS = conn.Execute(SQL)Response.Write RS.getString()RS.CloseConn.CloseResponse.End%></body></html>

Edited by ahill
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...