SQL Help

[ColdFusionKing]

<cffile
action=&quot;read&quot;
file=&quot;C:\Inetpub\

http://wwwroot\logReport\dtSearchWeb2003-09.log&quot;

variable=&quot;fOutput&quot;>

<cfset qTmp = QueryNew(&quot;DateTime,UserName,SearchString,IndexName,ResultsFound,Status&quot>
<cfloop list=&quot;#fOutput#&quot; index=&quot;fileLine&quot; delimiters=&quot;#Chr(10)#&quot;>
<cfif listLen(fileLine) eq 6>
<cfscript>
tmp = QueryAddRow(qTmp,1);
tmp=QuerySetCell(qTmp,&quot;DateTime&quot;, Replace(listGetAt(fileLine,1),&quot;&quot;&quot;&quot;,&quot;&quot;,&quot;All&quot);
tmp=QuerySetCell(qTmp,&quot;UserName&quot;, Replace(LCase(listGetAt(fileLine,2)),&quot;&quot;&quot;&quot;,&quot;&quot;,&quot;All&quot);
tmp=QuerySetCell(qTmp,&quot;SearchString&quot;, Replace(listGetAt(fileLine,3),&quot;&quot;&quot;&quot;,&quot;&quot;,&quot;All&quot);
tmp=QuerySetCell(qTmp,&quot;IndexName&quot;, Replace(ListLast(listGetAt(fileLine,4),'\'),&quot;&quot;&quot;&quot;,&quot;&quot;,&quot;All&quot);
tmp=QuerySetCell(qTmp,&quot;ResultsFound&quot;, Replace(listGetAt(fileLine,5),&quot;&quot;&quot;&quot;,&quot;&quot;,&quot;All&quot);
tmp=QuerySetCell(qTmp,&quot;Status&quot;, Replace(listGetAt(fileLine,6),&quot;&quot;&quot;&quot;,&quot;&quot;,&quot;All&quot);
</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 &quot;Fusion&quot;, the log file stores the information like this:

Date/Time Keyword Category Results Returned Status UserName
------------------------------------------------------------------------------------
2003/10/08 18:40:47 Fusion ColdFusion Sites 10 OK testusername

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=&quot;lQuery&quot; dbtype=&quot;query&quot;>
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

 

[r937]

since this is query-of-query i would not count on cf's sql engine to be very robust

therefore, write your CFQUERY to return all keywords in descending order of frequency, then use MAXROWS in CFOUTPUT to chop off the top 5 or 10

the query is

select keyword, count(*) as freq
from logtable
group by keyword
order by count(*) desc


rudy

http://r937.com/

 

[ColdFusionKing]

Hi Rudy, Can you show me how to do that, I'm afraid I've never used MAXROWS before

 

[r937]

sure, here --

http://livedocs.macromedia.com/coldfusion/5.0/CFML_Reference/Tags72.htm#1101659

;-)

 

[ColdFusionKing]

Thanks Rudy, I've got it all working