Searching and ordering results by relevance

[Outside5 Ltd]

Hi all,

I am building a search function and would like some advice.

I am planning to allow people to search in a single field and am looping through each word in their search terms and returning a struct of IDs, then selecting the records relating to those ID's to output.

If the user searches for John Smith London, I want to search Forename, Surname and City.  This should return any instances of John, Smith and London from the db.  If the db contains a John Smith from London, he will be returned 3 times.  A Dave Jones from London will be returned once and so on.

I have it working up until this point.  What I want to be able to do next is to group the results and count the number of instances of each result, and order by count DESC, to list the record with the most results from the search term at the top of the results.

My code is below.  Any advice greatly appreciated.

Thanks,

Paul

<cfset results = structNew()> <cfset x = 0> <cfloop list="#FORM.q#" delimiters=" " index="q">                 <cfset x = x+1>      <cfquery name="searchResults" datasource="#REQUEST.ds#">           SELECT          userId           FROM          users           WHERE          surname LIKE <cfqueryparam value="%#q#%">           OR          forename LIKE <cfqueryparam value="%#q#%">           OR          town LIKE <cfqueryparam value="%#q#%">      </cfquery>            <cfoutput>           <p>searchResults.regId = #searchResults.regId#</p>      </cfoutput>            <cfset structInsert(results, x, searchResults.regId)>       </cfloop> <cfdump var="#results#">

[Dan_Bracuk]

You forgot to post your code but that's ok.  If you are not familiar with group by and order by clauses in queries then I have heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.

[Outside5 Ltd]

Thanks Dan,

I think either you have misunderstood me, or I have misunderstood you.

The script below is what I have so far...

This will return 3 rows for regId 117 (matches John, Smith and Hampshire)

This will return 2 rows for regId 113 (matches John and Hampshire)

This will return 1 rows for regId 110, 111 and 115 (matches Hampshire)

What I want is to output the results in the order of relevance based on the number of times a regId is picked up during the select (e.g. 117, 113, 110, 111, 115).

<cfset resultsArray = arrayNew(1)>

<cfset FORM.q = "john smith hampshire">

<p><cfoutput>"#FORM.q#"</cfoutput>

<cfloop list="#FORM.q#" delimiters=" " index="q">

    <cfquery name="searchResults" datasource="#REQUEST.ds#">

        SELECT regId

        FROM registrations

        WHERE surname LIKE <cfqueryparam value="%#q#%">

        OR forename LIKE <cfqueryparam value="%#q#%">

        OR county LIKE <cfqueryparam value="%#q#%">

    </cfquery>

    <cfloop query="searchResults"><cfoutput>

        <cfset arrayAppend(resultsArray, searchResults.regId)>

    </cfoutput></cfloop>

</cfloop>

<cfquery name="theResults" datasource="#REQUEST.ds#">

    SELECT *

    FROM registrations

    WHERE regId IN (#arrayToList(resultsArray, ',')#)

</cfquery>

<cfoutput query="theResults">

    <h2>#forename#, #surname#</h2>

</cfoutput>

[Dan_Bracuk]

You misunderstood me.  It's possible to get the results you want, sorted the way you want, with a single query.  I mentioned the necessary clauses in my earlier post.

[Outside5 Ltd]

Thanks again Dan.

-- Sent from my Palm Prē

[insuractive]

What about creating 3 custom columns in your SELECT statement, 1 for each of the search fields (surname_match,

forename_match, county_match) and then using a db find() function to determine whether or not that record matches on

one or more of those fields.  then order your query by surname_match + forename_match + county_match:

SELECT regId,

          surname_match = CASE WHEN find(surname, '#q#') > 0 THEN 1 ELSE 0 END,

          forename_match = CASE WHEN find(forename, '#q#') > 0 THEN 1 ELSE 0 END,

          county_match = CASE WHEN find(county, '#q#') > 0 THEN 1 ELSE 0 END

     FROM registrations

        WHERE surname LIKE <cfqueryparam value="%#q#%">

        OR forename LIKE <cfqueryparam value="%#q#%">

        OR county LIKE <cfqueryparam value="%#q#%">

ORDER BY ...

[Outside5 Ltd]

Thanks very much for the example here insuractive. Obviously, not having done this before an example is a huge help.

I will try this from the office in the morning.

Thanks again.

Paul

-- Sent from my Palm Prē

[Outside5 Ltd]

Oh, one other thing insuractive, this will work for MySQL?

Thanks,

Paul

-- Sent from my Palm Prē

[insuractive]

Not that familiar with MySQL.  Google should be able to tell you whether or not MySQL supports CASE statements.  How you reference those newly created columns in your ORDER BY statement will also be database platform dependent.