CODEWITHSUNDEEP
sqlcoldfusiongroup-by
Banny
Banny

Reputation: 841

Searching through more than one column in a query

I'm currently working on a search function where users can search for gigs either by the name of the band who are hosting the gig, or the location of the gig itself. The issue I am having is for the location part of the search, I want to search through the town, county AND country fields I have stored so that say if a gigs location is "Leeds, Yorkshire, England" and the user searched for "Eng" it would match because it found it in the country field. Same would apply if they searched for "eds" because of the match in Leeds.

I know this can be achieved by using the Group By method within the query, however, I was looking for advice on best practices and exactly how to apply it as I have not used Group By before.

Here is the incorrect query I started with:

<cfquery datasource="#datasource#" name="findgigs">
    Select artist_id, gig_id, gig_artistid, gig_town, gig_county, gig_country
    From artists, gigs
    Where artist_id = gig_artistid
    <cfif IsDefined("URL.search")>
        And gig_town LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%>
        Or gig_county LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%>
        Or gig_country LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%>
    </cfif>
</cfquery>

As I discovered after running the query (should of known before hand) it returns many duplicates. However, looking at that query you can see what I am trying to obtain it is just done in completely the wrong way. Any knowledge or advice on how to improve this would be appreciated.

Upvotes: 0

Views: 127

Answers (2)

Mike Oliver
Mike Oliver

Reputation: 251

Your WHERE clause does not contain any grouping around your ORs. This is causing the initial criteria to be ignored when checking for county or country. Since you are using ANSI 89 syntax to handle your join (not specifying INNER JOIN), this will break your join.

There are a few ways to fix this. If you wanted to continue using ANSI 89:

<cfquery datasource="#datasource#" name="findgigs">
    Select artist_id, gig_id, gig_artistid, gig_town, gig_county, gig_country
    From artists, gigs
    Where artist_id = gig_artistid
    <cfif IsDefined("URL.search")>
        And (gig_town LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">
        Or gig_county LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">
        Or gig_country LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">)
    </cfif>
</cfquery>

As you can see, all I did there is add a "(" After the "And" and a ")" after the last cfparam. This will group those criteria together so the artist_id criteria is used against all three (preventing the extra results).

If you wanted to change the SQL to explicitly define your join (ANSI 92):

<cfquery datasource="#datasource#" name="findgigs">
    Select artist_id, gig_id, gig_artistid, gig_town, gig_county, gig_country
    From artists
    Inner Join gigs ON artist_id = gig_artistid
    <cfif IsDefined("URL.search")>
        Where gig_town LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">
        Or gig_county LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">
        Or gig_country LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#%">
    </cfif>
</cfquery>

Upvotes: 2

steve
steve

Reputation: 1490

<cfquery datasource="#datasource#" name="findgigs">
    SELECT  artist_id
    FROM    artists
    WHERE   artist_id IN 
      (    

           SELECT  gig_artistid 
           FROM    gigs 
           WHERE   1 = 0 
           <cfif structKeyExists(url,'search')>
             OR  gig_town LIKE  <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#">
             OR  gig_county LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#">
             OR  gig_country LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#">
         </cfif>
      )
</cfquery>

  1. If you're worried about duplicated values, this sub query would help remedy that without grouping.

  2. Added cfqueryparam in as a best practice to help against sql injections

The logic for the query is "show nothing to start" and then if your search is defined, the OR will kick in allowing the results to be shown.

Option #2

<cfquery datasource="#datasource#" name="findgigs">
    SELECT artist_id
    FROM   artists
   <cfif structKeyExists(url,'search')>
    WHERE  artist_id IN 
        ( 
           SELECT  gig_artistid 
           FROM    gigs
           WHERE   gig_town LIKE  <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#">
           OR      gig_county LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#">
           OR      gig_country LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.search#">
       )
   </cfif>
</cfquery>

Starts by showing all. If a search criteria is added it will start filtering out the results to include your search criteria.

Upvotes: 3

Related Questions