|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
You don't need a fax machine to get faxes. Get a fax-to-email fax number from CallWave. Try it free.
|
|
#1
March 17th, 2004, 01:29 PM
|
|||
|
|||
|
SQL problem
Can anybody please help with the following sql problem.
I getthe following error msg when i search for "levin" (without the quotes)of course. Can you help with whats wrong please Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression '1 = 1 OR DEALER LIKE ''%levin%'' OR LOCATION LIKE ''%levin%'''. The Error Occurred in C:\CFusionMX\wwwroot\TwoWord\search-2.cfm: line 27 25 : #dealer_string# 26 : #location_string# 27 : </cfquery> 28 : 29 : SQL SELECT * FROM AllDealers WHERE 1 = 1 OR DEALER LIKE ''%levin%'' OR LOCATION LIKE ''%levin%'' DATASOURCE CarWanted VENDORERRORCODE -3100 SQLSTATE 42000 My page code is: <!-----search.cfm-----> <html> <head> <title>Search Results</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <cfset dsn="CarWanted"> <!--- get all the keywords and always treat the keyword as a list. ---> <cfset dealer_string = "OR "> <cfset location_string = " "> <cfloop list="#form.keyword#" index="i" delimiters="+"> <cfscript> dealer_string = dealer_string & " DEALER LIKE '%" & trim(i) & "%' OR "; location_string = location_string & " LOCATION LIKE '%" & trim(i) & "%'"; if (i NEQ listlast(form.keyword,"+")){ location_string = location_string & " OR "; } </cfscript> </cfloop> <cfquery name="qUsers" datasource="#dsn#"> SELECT * FROM AllDealers WHERE 1 = 1 #dealer_string# #location_string# </cfquery> <cfoutput query="qUsers"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="17%"> </td> <td>#qUsers.dealer# - #qUsers.ADDRESS#, #qUsers.location#</td> </tr> <tr> <td> </td> <td><em>#qUsers.COMMENTS#</em></td> </tr> </table> </cfoutput> </body> </html>
|
|
#2
March 17th, 2004, 03:16 PM
|
|||
|
|||
|
Try using single quotes in the LIKE clause. Also, does it work if you just run the basic query directly:
SELECT * FROM AllDealers WHERE DEALER LIKE '%levin%' OR LOCATION LIKE '%levin%'
|
|
#3
March 17th, 2004, 09:13 PM
|
|||
|
|||
|
sql problem
Hi Kiteless
I guess i should explain just what im trying to do: I have the following columns in a database area, dealer, location. address & comments. I want to be able to search for any combination of these columns ie, dealer + location. area+comments etc etc. I want to be able to insert the search criteria into one text area on a form with the values seperated by a + sign. I am only a newbie to this sort of thing and i have had all sorts of wierd answers from all sorts of different people on different forums that only make me more and more confused - Can you PLEASE help with a solution cheers Thanks in advance
|
|
#4
March 17th, 2004, 09:48 PM
|
|||
|
|||
|
Did either of these allow the query to run?
Quote:
|
|
#5
March 17th, 2004, 11:36 PM
|
|||
|
|||
|
sql problem
No neither of them ran without throwing an error in the sql statement.
This code was given to me as another way of doing it by someone on "easycfm" when i asked my original question i had done a tutorial and finally got the following code to work: This is the code (following at the end of this post) i started with and finally got to work and search for both the entered criteria ie car+levin returned all instances of car in the dealer column and all instances of levin in the location column thus giving me the result i wanted - all "car" dealers in Levin (a local town). It was when I asked a question on how to extend it someone came up with the code that i posted originally.. My original query was as follows: It looks like i have run out of room in this post can i email you the full code please?
|
|
#6
March 17th, 2004, 11:50 PM
|
|||
|
|||
|
sql problem
No neither of them ran without throwing an error in the sql statement.
This code was given to me as another way of doing it by someone on "easycfm" Pls forget the code in the last post: and lets start anew When i asked my original question i had done a tutorial (on easycfm)and finally got the following code to work: This is the code (following at the end of this post) i started with and finally got to work and search for both the entered criteria ie car+levin returned all instances of car in the dealer column and all instances of levin in the location column thus giving me the result i wanted - all "car" dealers in Levin (a local town). It was when I asked a question on how to extend it someone came up with the code that i posted originally.. my code and question from the original query was: how can i extend this code to search for any column +any column ie area+dealer, dealer+comments, location+dealer etc etc etc form page: form.cfm <html> <head> <title>Search Form</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <!---set form to clear when loaded---> <body onload="document.form.reset()"> Insert two keywords separated by "+" sign.<br> <form action="search.cfm" method="post" name="form" id="form"> <input name="keyword" type="text" id="keyword"> <input type="submit" name="Submit" value="GO"> </form> </body> </html> Search page: <!-----search.cfm-----> <html> <head> <title>Search Results</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <cfset dsn="CarWanted"> <cfif isDefined ("form.keyword")> <!--- // IF THE KEYWORD SUBMITTED DOES NOT CONTAIN + SIGN THEN JUST DO A BASIC QUERY // ---> <cfif form.keyword DOES NOT CONTAIN "+"> <cfquery name="qUsers" datasource="#dsn#"> SELECT * FROM AllDealers WHERE DEALER LIKE '%#form.keyword#%' OR LOCATION LIKE '%#form.keyword#%' or comments like '%#form.keyword#%' or area like '%#form.keyword#%' </cfquery> <p>The following results match your search criteria:</p> <cfoutput query="qUsers"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td colspan="2"><font color="##CC0000">#qUsers.CurrentRow#</font> - #qUsers.dealer# - #qUsers.ADDRESS#, #qUsers.location#</td> </tr> <tr> <td width="11%"> </td> <td width="89%"><em>#qUsers.COMMENTS#</em></td> </tr> </table> <br> </cfoutput> <!--- // IF THE KEYWORD DOES CONTAIN A + THEN BREAK THE KEYWORD INTO TWO VARIABLES AND SEARCH USERS USING "LIKE" & "OR" FUNCTIONS // ---> <cfelse> <cfset search_1 = trim(ListFirst(form.keyword, '+'))> <cfset search_2 = trim(Listlast(form.keyword, '+'))> <cfoutput>Your Search for: #search_1#</cfoutput> and <cfoutput>#search_2#</cfoutput><br> <br> <cfquery name="qUsers" datasource="#dsn#"> SELECT * FROM AllDealers WHERE 0+1 and DEALER LIKE '%#search_1#%' and location like '%#search_2#%' or comments like '%#search_2#%' or area like '%#search_2#%' </cfquery> <cfoutput>Returned #qUsers.RecordCount# </cfoutput> matches<br> <cfoutput query="qUsers"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td colspan="2"><font color="##CC0000">#qUsers.CurrentRow#</font>- #qUsers.dealer# - #qUsers.ADDRESS#, #qUsers.location#</td> </tr> <tr> <td width="17%"> </td> <td><em>#LCase(qUsers.COMMENTS)#</em></td> </tr> </table> </cfoutput> </cfif> </cfif> </body> </html> This code does actually work so all i originally asked was how to extend it to do as i set out at the top of this post cheers and thanks in advance for any help you can give me Last edited by grabit : March 17th, 2004 at 11:54 PM.
|
|
#7
March 18th, 2004, 09:00 AM
|
|||
|
|||
|
I'm still unclear as to what you want to do. You said you want to search:
Quote:
What keyword are you searching for? I don't see what point there would be to saying "search the database for this column". What might make sense is "search the database for this VALUE in some column".
|
|
#8
March 18th, 2004, 12:25 PM
|
|||
|
|||
|
sql problem
Hi Kiteless.
OK I will start again. I want to be able to search for match entries in the following columns - dealer, location, comments, & area. In the following code lines 10 - 17 will search for a single word input and return all the matches it finds of that word in any of the above columns. Lines 29 - 40 is set up (at the moment) to search for two input criteria seperated by a + sign. the code (as it is now) will search for the criteria "dealer+location, comments or area. and only works if the input "dealer" is the first word in the search string. What i would like to do is be able to enter the search string in the input form, in any order ie, location+dealer, area+dealer etc, or any combination of the columns i want searched. This means that people can search for any columns information they want. ie, "what location (suburbs or towns) have car dealers in them (dealer)" this would mean an input of location+dealers" or maybe "what dealers have after hours phone numbers in an area this input would be comments(the column where the after hours numbers are stored) + dealers (the column where the dealers names are stored). The following code actually works but only in the order stated ie. and DEALER LIKE '%#search_1#%' and location like '%#search_2#%' or comments like '%#search_2#%' or area like '%#search_2#%' as you can see in the statement above '%#search_1#%' is only set up to search the dealer column. What i want to do is make an sql statement that will execute the following: DEALER LIKE '%#search_1#%' OR '%#search_2#%' LOCATION LIKE '%#search_1#%' OR '%#search_2#%' COMMENTS LIKE '%#search_1#%' OR '%#search_2#%' AREA LIKE '%#search_1#%' OR '%#search_2#%' i have tried adding the 'and' statement to the front of the location, comments & area lines but that doesnt work This is what i would like some help with (remembering i am a newbie) CODE FOLLOWS: form page: form.cfm <html> <head> <title>Search Form</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <!---set form to clear when loaded---> <body onload="document.form.reset()"> Insert two keywords separated by "+" sign.<br> <form action="search.cfm" method="post" name="form" id="form"> <input name="keyword" type="text" id="keyword"> <input type="submit" name="Submit" value="GO"> </form> </body> </html> search page: <!-----search.cfm-----> <html> <head> <title>Search Results</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <cfset dsn="CarWanted"> <cfif isDefined ("form.keyword")> <!--- // IF THE KEYWORD SUBMITTED DOES NOT CONTAIN + SIGN THEN JUST DO A BASIC QUERY // ---> <cfif form.keyword DOES NOT CONTAIN "+"> <cfquery name="qUsers" datasource="#dsn#"> SELECT * FROM AllDealers WHERE DEALER LIKE '%#form.keyword#%' OR LOCATION LIKE '%#form.keyword#%' or comments like '%#form.keyword#%' or area like '%#form.keyword#%' </cfquery> <p>The following results match your search criteria:</p> <cfoutput query="qUsers"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td colspan="2"><font color="##CC0000">#qUsers.CurrentRow#</font> - #qUsers.dealer# - #qUsers.ADDRESS#, #qUsers.location#</td> </tr> <tr> <td width="11%"> </td> <td width="89%"><em>#qUsers.COMMENTS#</em></td> </tr> </table> <br> </cfoutput> <!--- // IF THE KEYWORD DOES CONTAIN A + THEN BREAK THE KEYWORD INTO TWO VARIABLES AND SEARCH USERS USING "LIKE" & "OR" FUNCTIONS // ---> <cfelse> <cfset search_1 = trim(ListFirst(form.keyword, '+'))> <cfset search_2 = trim(Listlast(form.keyword, '+'))> <cfoutput>Your Search for: #search_1#</cfoutput> and <cfoutput>#search_2#</cfoutput><br> <br> <cfquery name="qUsers" datasource="#dsn#"> SELECT * FROM AllDealers WHERE 0+1 and DEALER LIKE '%#search_1#%' and location like '%#search_2#%' or comments like '%#search_2#%' or area like '%#search_2#%' </cfquery> <cfoutput>Returned #qUsers.RecordCount# </cfoutput> matches<br> <cfoutput query="qUsers"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td colspan="2"><font color="##CC0000">#qUsers.CurrentRow#</font>- #qUsers.dealer# - #qUsers.ADDRESS#, #qUsers.location#</td> </tr> <tr> <td width="17%"> </td> <td><em>#LCase(qUsers.COMMENTS)#</em></td> </tr> </table> </cfoutput> </cfif> </cfif> </body> </html> This code is a modified version of a tutorial on "easycfm", and as an aside, something i dont understand here is (at the start of the statement is) "WHERE 0+1" what does this do? Hope this makes things a bit clearer for you and I hope you can help. Thanks in advance
|
|
#9
March 18th, 2004, 01:28 PM
|
|||
|
|||
|
The 0+1 just guarantees that there is something to run in the WHERE clause, because that will always evaluate to 1. You could also do "WHERE 1=1".
Your search system still confuses me. Unless the user somehow lets you know (via their search statement) what type of information each keyword is, aren't you going to end up searching all the columns (dealer, location, comments, area) for all of the keywords the user enters? Is that what you want to do? Wouldn't potentially cause a lot of false hits if the user enters "new" into the search, maybe meaning "new jersey" or "new york", but the query also tries to match "new" in the comments field, which may return numerous results that have no relation to new york or new jersey (like a new store)? However, if this IS what you want to do (search all 4 columns for every keyword the user enters), it can be done. Before I post code though I want to be sure that is what you mean. So if the user enters: new york+suburbs+discount what you want to do is look for any record in the database that has either "new york", "suburbs", or "discount" in ANY of the columns location, area, comments, or dealer. Is that right?
|
|
#10
March 18th, 2004, 02:20 PM
|
|||
|
|||
|
sql problem
Hi Kiteless
Thanks for the quick reply Yes that is what i want to do. Probably the most common search would be "dealer+location" or "dealer+area" (they can enter this to find all the dealers listed in a particular location (town) or area (like a state to you) I do however want the ability for them to be able to search the comments column because that is where they can lokk for info such as dealers staff names, after hours details etc thanks for that cheers Grabit
|
|
#11
March 18th, 2004, 03:47 PM
|
|||
|
|||
|
This seems to generate the correct SQL statement based on the test values I set in the lists:
<cfset form.keyword = "Joes Dealership+New Jack City" /> <cfset colList = "dealer,area,comments,location" /> Code:
<cfset form.keyword = "Joes Dealership+New Jack City" /> <cfset colList = "dealer,area,comments,location" /> <cfquery name="qUsers" datasource="#dsn#"> SELECT * FROM AllDealers WHERE 1=1 AND <cfloop index="thisColumn" list="#colList#" delimiters=","> <cfloop index="thisKeyword" list="#form.keyword#" delimiters="+"> #thisColumn# LIKE '%#thisKeyword#%' <cfif listFindNoCase( form.keyword, thisKeyword, '+' ) neq listLen( form.keyword, '+' )> OR </cfif> </cfloop> <cfif listFindNoCase( colList, thisColumn, ',' ) neq listLen( colList, ',' )> OR </cfif> </cfloop> </cfquery>
|
|
#12
March 18th, 2004, 06:48 PM
|
|||
|
|||
|
sql problem
Hi Kiteless
I must appear to be the thickest individual in the universe, but as i said i am very new to this and the only way i seem to have learned anything is by asking questions. So here i go "the code in your last post goes into the page right!? if so do i need the first line of cfset? just where in the page do i put it (in the code below is it in the right place or not? just how do i now write my queryin the form, is it still in the style dealer+location? my code now: <!-----search.cfm-----> <html> <head> <title>Search Results</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <cfset dsn="CarWanted"> <cfset form.keyword = "Joes Dealership+New Jack City" /> <cfset colList = "dealer,area,comments,location" /> <cfquery name="qUsers" datasource="#dsn#"> SELECT * FROM AllDealers WHERE 1=1 AND <cfloop index="thisColumn" list="#colList#" delimiters=","> <cfloop index="thisKeyword" list="#form.keyword#" delimiters="+"> #thisColumn# LIKE '%#thisKeyword#%' <cfif listFindNoCase( form.keyword, thisKeyword, '+' ) neq listLen( form.keyword, '+' )> OR </cfif> </cfloop> <cfif listFindNoCase( colList, thisColumn, ',' ) neq listLen( colList, ',' )> OR </cfif> </cfloop> </cfquery> <cfoutput query="qUsers"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="17%"> </td> <td>#qUsers.dealer# - #qUsers.ADDRESS#, #qUsers.location#</td> </tr> <tr> <td> </td> <td><em>#qUsers.COMMENTS#</em></td> </tr> </table> </cfoutput> </body> </html> Also would it be a real pain if i asked you just what your code is doing cheers and thanks a lot
|
|
#13
March 18th, 2004, 09:20 PM
|
|||
|
|||
|
All you need on the target page (the query page, which the form posts to) is the cfset for the list of column names, and the query itself. The cfset with the form post was just a sample of what might be being posted to the page. So all you really need is something like this in search.cfm:
Code:
<html> <head> <title>Search Results</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <cfset dsn="CarWanted"> <cfparam name="form.keyword" /> <cfset colList = "dealer,area,comments,location" /> <cfquery name="qUsers" datasource="#dsn#"> SELECT * FROM AllDealers WHERE 1=1 AND <cfloop index="thisColumn" list="#colList#" delimiters=","> <cfloop index="thisKeyword" list="#form.keyword#" delimiters="+"> #thisColumn# LIKE '%#thisKeyword#%' <cfif listFindNoCase( form.keyword, thisKeyword, '+' ) neq listLen( form.keyword, '+' )> OR </cfif> </cfloop> <cfif listFindNoCase( colList, thisColumn, ',' ) neq listLen( colList, ',' )> OR </cfif> </cfloop> </cfquery> <cfoutput query="qUsers"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="17%"> </td> <td>#qUsers.dealer# - #qUsers.ADDRESS#, #qUsers.location#</td> </tr> <tr> <td> </td> <td><em>#qUsers.COMMENTS#</em></td> </tr> </table> </cfoutput> </body> </html> The cfparam makes sure that form.keywords was posted to the page. In your real app, that variable would be coming in from the form post that you did on the form page. So your form would look like: Code:
<html> <head> <title>Search Form</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <!---set form to clear when loaded---> <body onload="document.form.reset()"> Insert two keywords separated by "+" sign.<br> <form action="search.cfm" method="post" name="form" id="form"> <input name="keyword" type="text" id="keyword"> <input type="submit" name="Submit" value="GO"> </form> </body> </html> So if you enter "Ben's Dealership+New Jersey" in the form and post it, the target page (search.cfm) will run the query and search all of the columns in the list for any record that has either "Ben's Dealership" or "New Jersey" in it.
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
