Thread: SQL problem

Page 2 of 2 First 12
  • Jump to page:
    #16
  1. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,286
    Rep Power
    968
    If you enter "cars+levin", how does the query know that the first keyword is supposed to be a car dealer, and the second keyword is supposed to be a town? Sounds like we're back to this, which I asked about a few posts ago:

    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?
    How is the query supposed know what each keyword is supposed to be? Sounds like you might need multiple input boxes so that it is clear what each keyword is (that is, what column in the database you should be looking in).
  2. #17
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    10
    Rep Power
    0

    sql problem


    Hi Kiteless
    It looks like seperate input field on the form would sort that problem out ok, BUT where do I start to do that sort of coding
    Cheers & Many thanks
  4. #18
  5. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,286
    Rep Power
    968
    Well, you'll have form fields for each type of keyword (dealer, location, etc.). Then on the target page, you can look in each field for keywords, and if there are keywords for that type, you loop over them (just like I did in my previous example). But now you'll know what type of keyword each one is. So you loop over the dealer keywords to populate the SQL that would say something like: "where dealer like '%#thisDealerKeyword#%'.

    You can do the same for location, etc.
  6. #19
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    10
    Rep Power
    0

    sql problem


    Hi Kiteless
    As you know I am a complete beginner at this so if you can please give me an example for one of the form fiels and the resulting search page code I should be able to buildd it up from there
    cheers and thanks a lot for all your help on this
  8. #20
  9. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,286
    Rep Power
    968
    This would be the search.cfm page. I'm skipping the step of creating the form and just using dummy form variables. But in the real applicaiton you would just have a form with these 4 text fields, which posts to search.cfm.

    Code:
    <!--- pretend these were posted from a form with text fields named: "dealer", "area", "comments", and "location". The user would enter the appropriate keywords into the text field. If no keywords were entered, that text field is just empty. --->
    <cfset form.dealer = "Joe's Dealership+Bob's Cars" />
    <cfset form.area = "New Jack City+Levin" />
    <cfset form.comments = "" />
    <cfset form.location = "" />
    
    <!--- Here is a list that has all the different keyword types, these correspond to the columns in the database that will be searched. --->
    <cfset colList = "dealer,area,comments,location" />
    
    <cfoutput>
    <cfquery name="qUsers" datasource="#dsn#">
    SELECT * FROM AllDealers 
    WHERE
    1=1
    <cfloop index="thisColumn" list="#colList#" delimiters=",">
    	<cfif len( trim( form[thisColumn] ) )>
    		AND (
    		<cfloop index="thisKeyword" list="#form[thisColumn]#" delimiters="+">
    			#thisColumn# LIKE '%#thisKeyword#%' <cfif listFindNoCase( form[thisColumn], thisKeyword, '+' ) neq listLen( form[thisColumn], '+' )> OR </cfif>
    		</cfloop>
    		)
    	</cfif>
    </cfloop>
    </cfquery>
    </cfoutput>
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo