Thread: SQL problem

Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    10
    Rep Power
    0

    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. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    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%'
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    10
    Rep Power
    0

    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
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    Did either of these allow the query to run?

    Originally Posted by kiteless
    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%'
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    10
    Rep Power
    0

    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?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    10
    Rep Power
    0

    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%">&nbsp;</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%">&nbsp;</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.
  12. #7
  13. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    I'm still unclear as to what you want to do. You said you want to search:

    to search for any column +any column ie area+dealer, dealer+comments, location+dealer etc etc etc
    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".
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    10
    Rep Power
    0

    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%">&nbsp;</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%">&nbsp;</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
  16. #9
  17. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    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?
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    10
    Rep Power
    0

    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
  20. #11
  21. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    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>
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    10
    Rep Power
    0

    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
  24. #13
  25. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    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.
  26. #14
  27. No Profile Picture
    JDFMSAY
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    1
    Rep Power
    0

    SQL Problem


    I think what you need is to have four columns on your form which gives four search options. Then set up and (If Else ,Else.. Else If End) statement and check each field to see if it contains data. If it does then concatenated that value to the (Select statement).

    Stephen
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    10
    Rep Power
    0

    sql problem


    Hi Kiteless
    Thanks very very much for the help but there is just one last problem.
    The single word search works perfectly, but the multiword (+ seperated) is not quite there yet.
    What happens is when i search for (lets say) "cars+levin" what i get as returned results is every entry in the database that contains the words cars and levin, not as i wanted it, which was to return the entries, filtered out and only containing the search criteria text. ie only "car dealers" in the town of "Levin".
    have i set something up wrong? or is there something missing that will cause this?
    Cheers and Thankyou most sincerely for all this help
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo