I want to display the top 5 or 10 search keywords that users regularly search for and because this is logged
in the log file I'm sure it is possible to get this information. Currently I'm displaying all the results with a
simple sql statment.
<cfquery name="lQuery" dbtype="query">
SELECT DISTINCT SearchString, DateTime, UserName, IndexName, ResultsFound, Status
FROM qTmp
ORDER BY IndexName asc
</cfquery>
My sql skills are not very good and was wondering if someone could show me how to do this. I would really appreciate your help.
Best Regards,
Allan
>What is your question?
>-----Original Message-----
>From: Allan Clarke [mailto:[EMAIL PROTECTED]
>Sent: donderdag 9 oktober 2003 13:32
>To: CF-Talk
>Subject: SQL Help - Correct Post
>
>
><cffile
> action=""> >
>
>file="C:\Inetpub\wwwroot\logReport\dtSearchWeb2003-09.log"
> variable="fOutput">
>
><cfset qTmp =
>QueryNew("DateTime,UserName,SearchString,IndexName,ResultsFound,Status")
>>
><cfloop list="#fOutput#" index="fileLine"
>delimiters="#Chr(10)#">
><cfif listLen(fileLine) eq 6>
> <cfscript>
> tmp = QueryAddRow(qTmp,1);
> tmp=QuerySetCell(qTmp,"DateTime",
>Replace(listGetAt(fileLine,1),"""","","All"));
> tmp=QuerySetCell(qTmp,"UserName",
>Replace(LCase(listGetAt(fileLine,2)),"""","","All"));
> tmp=QuerySetCell(qTmp,"SearchString",
>Replace(listGetAt(fileLine,3),"""","","All"));
> tmp=QuerySetCell(qTmp,"IndexName",
>Replace(ListLast(listGetAt(fileLine,4),'\'),"""","","All"));
> tmp=QuerySetCell(qTmp,"ResultsFound",
>Replace(listGetAt(fileLine,5),"""","","All"));
> tmp=QuerySetCell(qTmp,"Status",
>Replace(listGetAt(fileLine,6),"""","","All"));
> </cfscript>
></cfif>
></cfloop>
>
>Hi Everybody,
>
>The above code reads the log file and inserts the file
>content in an empty query with a set of columns.
>
>The log file stores a log of what the user has been
>searching on the site search. For example if the user
>searches for the keyword "Fusion", the log file stores
>the information like this:
>
>Date/Time Keyword Category Results Returned
>Status UserName
>
> _____
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
