Search code examples
mysqlsearchcoldfusionsearch-engine

Writing an optimised and efficient search engine with mySQL and ColdFusion

I have a search page with the following scenarios listed below. I was told using so many conditional statements degrades performance, but I don't know if any other way to achieve the same objective.

  1. Search.cfm will processes a search made from a search bar present on all pages, with one search input (titleName).
  2. If search.cfm is accessed manually (through URL not through using the simple search bar on all pages) it displays an advanced search form with three inputs (titleName, genreID, platformID (dynamically generated, but not in this example code) or it evaluates searchResponse variable and decides what part of the page to show.
    1. If simple search/advanced search query is blank, has no results, or less than 3 characters it displays an error
    2. If any successful search returns results, they come back normally. There is one function handling the queries.

The top-of-page logic is as follows:

    <!---DEFINE DEFAULT STATE--->
<cfparam name="variables.searchResponse" default="">

<!---CHECK TO SEE IF SEARCH A FORM WAS SUBMITTED AND EXECUTE SEARCH IF IT WAS--->
<cfif IsDefined("Form.simpleSearch") AND Len(Trim(Form.titleName)) LTE 2>
    <cfset variables.searchResponse = "invalidString">
<cfelseif IsDefined("Form.simpleSearch") AND Len(Trim(Form.titleName)) GTE 3>
    <cfinvoke component="gz.cfcomp.search" method="searchTitles" titleName="#Form.titleName#" genreID="" platformID="" returnvariable="searchResult">
    <cfset variables.searchResponse = "hasResult">
</cfif>

<!---CHECK IF ADVANCED SEARCH FORM WAS SUBMITTED--->
<cfif IsDefined("Form.advancedSearch") AND (Len(Trim(Form.titleName)) LTE 2 AND Len(Form.genreID) IS 0 AND Len(Form.platformID) IS 0)>
    <cfset variables.searchResponse = "invalidString">
<cfelseif IsDefined("Form.advancedSearch") AND variables.searchResponse IS NOT "invalidString">
    <cfinvoke component="gz.cfcomp.search" method="searchTitles" returnvariable="searchResult" titleName="#Form.titleName#" genreID="#Form.genreID#" platformID="#Form.platformID#">
    <cfset variables.searchResponse = "hasResult">
</cfif>

<!---CHECK IF ANY RECORDS WERE FOUND--->
<cfif IsDefined("variables.searchResult") AND searchResult.RecordCount IS 0>
    <cfset variables.searchResponse = "noResult">
</cfif>

I'm using the searchResponse variable to decide what the the page displays, based on the following scenarios:

<!---ALWAYS DISPLAY SIMPLE SEARCH BAR AS IT'S PART OF THE HEADER--->
<form name="simpleSearch" action="search.cfm" method="post">
<input type="hidden" name="simpleSearch" />
<input type="text" name="titleName" />
<input type="button" value="Search" onclick="form.submit()" />
</form>

<!---IF NO SEARCH WAS SUBMITTED DISPLAY DEFAULT FORM--->
<cfif searchResponse IS "">
    <h1>Advanced Search</h1>
    <!---DISPLAY FORM--->
    <form name="advancedSearch" action="search.cfm" method="post">
        <input type="hidden" name="advancedSearch" />
        <input type="text" name="titleName" />
        <input type="text" name="genreID" />
        <input type="text" name="platformID" />
        <input type="button" value="Search" onclick="form.submit()" />
    </form>
</cfif>

<!---IF SEARCH IS BLANK OR LESS THAN 3 CHARACTERS DISPLAY ERROR MESSAGE--->
<cfif searchResponse IS "invalidString">
    <cfoutput>
        <h1>INVALID SEARCH</h1>
    </cfoutput>
</cfif>

<!---IF SEARCH WAS MADE BUT NO RESULTS WERE FOUND--->
<cfif searchResponse IS "noResult">
    <cfoutput>
        <h1>NO RESULT FOUND</h1>
    </cfoutput>
</cfif>

<!---IF SEARCH MADE AND RESULT WAS FOUND--->
<cfif searchResponse IS "hasResult">
    <cfoutput>
        <h1>Search Results</h1>
    </cfoutput>
    <cfoutput query="earchResult">
        <!---DISPLAY QUERY DATA--->
    </cfoutput>
</cfif>

Is my logic a) not efficient because my if statements/is there a better way to do this? And b) Can you see any scenarios where my code can break? I've tested it but I have not been able to find any issues with it. And I have no way of measuring performance. Any thoughts and ideas would be greatly appreciated.

Here is my function, for reference:

<!---SEARCH--->
<cffunction name="searchTitles" hint="This functions searches for a title based on multiple categories" access="public" output="false">
    <cfargument name="titleName" required="no" type="string" hint="Search by title">
    <cfargument name="genreID" required="no" type="string" hint="Search by genre">
    <cfargument name="platformID" required="no" type="string" hint="Search by platform">
    <!--- DEFINE LOCAL VARIABLES - NOTE VARIABLE NAME IS QUERY NAME --->
    <cfset var searchResult = "">
    <!---GET RESULTS--->
    <cfquery name="searchResult" datasource="myDSN">
        SELECT
            games.gameID,
            games.gameReleaseDate AS rDate,
            titles.titleName AS tName,
            titles.titleShortDescription AS sDesc,
            platforms.platformName AS pName,
            genres.genreName AS gName
        FROM
            games
            INNER JOIN titles ON titles.titleID = games.titleID
            INNER JOIN platforms ON games.platformID = platforms.platformID
            INNER JOIN genres ON games.genreID = genres.genreID
        WHERE
            0=0
            <cfif ARGUMENTS.titleName IS NOT "">
                AND titles.titleName LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#ARGUMENTS.titleName#%">
            </cfif>
            <cfif ARGUMENTS.genreID IS NOT "">
                AND games.genreID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.genreID#">
            </cfif>
            <cfif ARGUMENTS.platformID IS NOT "">
                AND games.platformID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.platformID#">
            </cfif>
        ORDER BY
            rDate DESC,
            tName;
    </cfquery>
    <cfreturn searchResult>
</cffunction>

Many thanks

Solution

  • If you add default values of "" to your genreID and platformID arguments, then I think you can refactor your top code to:

    <cfif StructKeyExists(url, "titleName") and Len(Trim(url.titleName)) lte 2>
    <cfset variables.searchResponse = "invalidString">
<cfelseif StructKeyExists(url, "titleName")>
    <cfinvoke component="gz.cfcomp.search" method="searchTitles" returnvariable="searchResult" argumentcollection="#url#">
    <cfset variables.searchResponse = "hasResult">
</cfif>

<cfif searchResponse eq "hasResult" and searchResult.recordCount eq 0>
    <cfset variables.searchResponse = "noResult">
</cfif>

    Note: I recommend switching your form methods to "get" for a search like this to improve the user's experience navigating your site. I've switched all the form references to url in the code.