How to track generic hits in product search engine in Coldfusion/MySQL?

Question

I'm using Coldfusion8/MySQL 5.0.88 and have a product search for which I'm trying to add some custom search statistics.

I have a table to hold product search hits and am now looking for the best and least time consuming way to fill it.

I will insert entries into the table like this:

<cfquery datasource="db">
    INSERT INTO statistics_search_hits ( iln, artikelnummer, source, viewed, zeitstempel )
    VALUES(   <cfqueryparam value = "#results.sellerID#" cfsqltype="cf_sql_varchar" maxlength="13">
            , <cfqueryparam value = "#results.style#" cfsqltype="cf_sql_varchar" maxlength="35">
            , <cfqueryparam value = "#Session.skin#" cfsqltype="cf_sql_varchar" maxlength="13">
            , "detail"
            , now() 
            )
</cfquery>

If I'm pulling a single record (detail), this is easy to include, but I'm wondering what and how to store generic searches.

Say a user is doing a search that returns 500 results, I will only be displaying 24 at a time, but in order to have correct statistics, I would have to make 500 entries into my hit counter.

Question:
What would be the best way to do this? I thought about using a CFThread to run along while the user is continuing on the page, but I'm not even sure I want to make 500 trips to the database. Any pointers on a good approach are welcome.

Thanks!

PS: I don't want to use Google Analytics for this.

Answer 1

This may not be what you're looking for but you can of course concatenate MS-SQL statements using semicolons.

INSERT INTO statistics_search_hits ( iln, artikelnummer, source, viewed, zeitstempel )
VALUES(   <cfqueryparam value = "#results.sellerID#" cfsqltype="cf_sql_varchar" maxlength="13">
        , <cfqueryparam value = "#results.style#" cfsqltype="cf_sql_varchar" maxlength="35">
        , <cfqueryparam value = "#Session.skin#" cfsqltype="cf_sql_varchar" maxlength="13">
        , "detail"
        , now() 
        );
INSERT INTO statistics_search_hits ( iln, artikelnummer, source, viewed, zeitstempel )
VALUES(   <cfqueryparam value = "#results.sellerID#" cfsqltype="cf_sql_varchar" maxlength="13">
        , <cfqueryparam value = "#results.style#" cfsqltype="cf_sql_varchar" maxlength="35">
        , <cfqueryparam value = "#Session.skin#" cfsqltype="cf_sql_varchar" maxlength="13">
        , "detail"
        , now() 
        )
INSERT INTO statistics_search_hits ( iln, artikelnummer, source, viewed, zeitstempel )
VALUES(   <cfqueryparam value = "#results.sellerID#" cfsqltype="cf_sql_varchar" maxlength="13">
        , <cfqueryparam value = "#results.style#" cfsqltype="cf_sql_varchar" maxlength="35">
        , <cfqueryparam value = "#Session.skin#" cfsqltype="cf_sql_varchar" maxlength="13">
        , "detail"
        , now() 
        );

You might be able to write the statements to a file and have MySQL import them on a scheduled basis as well. So you could write the files to a logging directory then have a CFSCHEDULE/CFEXECUTE process come in and process the files on a scheduled basis.

You could also try the ever handy INSERT INTO...SELECT statement. If your search results are coming from the database anyway, you could fashion a SELECT statement that rebuilds the same results from the database and bulk insert it with a few constants from CF.

INSERT INTO statistics_search_hits ( iln, artikelnummer, source, viewed, zeitstempel )
SELECT #reseller_id# as iln, searchtable.article, searchtable.source, 1, #now()#
FROM SearchTable
WHERE [search criteria statements here...]

Lastly, you could build a stored procedure to pass the bulk entry off to the SQL server instead.

A CFThread that hands off the query data sounds like a good idea too, but if you have a busy site, you'd better watch out for how many threads that might spawn, especially if users are constantly executing searches. Examine your CF Thread limit in your edition of ColdFusion and the settings in the administrator.

Hope these generic pointers help! ^_^