ahill Posted July 21, 2015 Share Posted July 21, 2015 Right now my search box is set up to search multiple words but they have to be right next to each other. How do I set it up so it searches for multiple wordsregardless of where they are in the statment Link to comment Share on other sites More sharing options...
justsomeguy Posted July 21, 2015 Share Posted July 21, 2015 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 More sharing options...
ahill Posted July 22, 2015 Author Share Posted July 22, 2015 example? Link to comment Share on other sites More sharing options...
ahill Posted July 22, 2015 Author Share Posted July 22, 2015 (edited) 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 July 22, 2015 by ahill Link to comment Share on other sites More sharing options...
davej Posted July 22, 2015 Share Posted July 22, 2015 You need to split the string and then AND each condition in the query. Link to comment Share on other sites More sharing options...
justsomeguy Posted July 22, 2015 Share Posted July 22, 2015 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. Link to comment Share on other sites More sharing options...
ahill Posted July 22, 2015 Author Share Posted July 22, 2015 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 More sharing options...
justsomeguy Posted July 22, 2015 Share Posted July 22, 2015 SELECT * FROM DBtable WHERE mproblem LIKE '%oil%' AND mproblem LIKE '%type%' Link to comment Share on other sites More sharing options...
ahill Posted July 23, 2015 Author Share Posted July 23, 2015 (edited) 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 July 23, 2015 by ahill Link to comment Share on other sites More sharing options...
justsomeguy Posted July 23, 2015 Share Posted July 23, 2015 so do I actually wana use oil and type in my codeDo 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 More sharing options...
ahill Posted July 23, 2015 Author Share Posted July 23, 2015 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 More sharing options...
dsonesuk Posted July 23, 2015 Share Posted July 23, 2015 Trouble is LIKE '%oil%' would bring up 'boil', 'soil', 'foil', 'coil' so its not restricted to just finding 'oil'. Link to comment Share on other sites More sharing options...
justsomeguy Posted July 23, 2015 Share Posted July 23, 2015 So I have to try and write a code that generates a new like clause for every word in the searchbox?That's right. Link to comment Share on other sites More sharing options...
ahill Posted July 23, 2015 Author Share Posted July 23, 2015 That's right.Anywhere I can look to get an idea of how to do this. Just learned the basics of classic asp. now working on learning the advanced stuf Link to comment Share on other sites More sharing options...
dsonesuk Posted July 23, 2015 Share Posted July 23, 2015 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 More sharing options...
justsomeguy Posted July 23, 2015 Share Posted July 23, 2015 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 More sharing options...
ahill Posted July 24, 2015 Author Share Posted July 24, 2015 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.aspEh 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 More sharing options...
ahill Posted July 24, 2015 Author Share Posted July 24, 2015 was just doing a little research and think I might skip the whole search box and build an AJAX search box. Looks like its more along the lines of what I am wanting to do. Link to comment Share on other sites More sharing options...
justsomeguy Posted July 24, 2015 Share Posted July 24, 2015 OK, but the code on the backend is the same. The only difference is the Javascript code on the frontend. Link to comment Share on other sites More sharing options...
ahill Posted July 28, 2015 Author Share Posted July 28, 2015 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 More sharing options...
ahill Posted July 31, 2015 Author Share Posted July 31, 2015 Does Anyone have any leads on ajax templates with drop-down filtering menus. All i am finding are ones written for php Link to comment Share on other sites More sharing options...
justsomeguy Posted July 31, 2015 Share Posted July 31, 2015 Which language do you want to use other than PHP? Link to comment Share on other sites More sharing options...
ahill Posted July 31, 2015 Author Share Posted July 31, 2015 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 More sharing options...
justsomeguy Posted July 31, 2015 Share Posted July 31, 2015 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 More sharing options...
ahill Posted August 5, 2015 Author Share Posted August 5, 2015 (edited) 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 August 6, 2015 by ahill Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now