Forum Moderators: open
User Id, Term, IP Address, Timestamp
What I'm trying to do now is pull from this resource information about top keywords.
I'm interested in knowing what my top five keywords are for the last seven days, as well as how those five keywords did each day. From this information, I could then pull the data out for reports and charts.
Here's what I have so far, but its not working, and I don't even think I'm on the right path:
$result=mysql_query("SELECT term, COUNT(term) FROM search_terms WHERE timestamp>='$seven_days_ago' GROUP BY term");
$row=mysql_fetch_array($result);
$terms=array();
while($row=mysql_fetch_array($result)) array_push($terms,$row['term']." - ".$row['COUNT(term)']);
This gives my an array with all the search terms within a specified period, and their count.
My next step was to then analyze this array for the top five terms, and then another seven queries in the table for seven data ranges for each five terms.
This seems really inefficient.
Any advice?
Thanks
I don't even think I'm on the right path
You are very close.
There are just a couple of things to do/change.
The query could do all the work for you if you add a 'between' for your date range and span it across 7 days. In order to get the correct counts for those days you will need to either make sure that the column only has the date and not date/time or use something like to_days(timestamp) to get the day. This is what you want to group by now so you can remove the group by term and add a group by to_days(timestamp).
That should be it but you won't have the total for the entire time period so use whatever script is calling the query to total it for you.
JAG
SELECT top 5 count(term) as total, Term
FROM search_terms
WHERE timestamp > = '7daytimestamp'
ORDER BY count(term) desc
totals by day for any one term:
SELECT count(term) AS total, Convert(varchar(10),timestamp, 101) AS requestDate
FROM search_terms
WHERE timestamp BETWEEN 'date1' AND 'date2' AND term = 'searchterm'
GROUP BY Convert(varchar(10),timestamp,101), day(timestamp)
ORDER BY Convert(varchar(10),timestamp,101) ASC
JAG
Yes I am using just MySQL. The limit feature looks very useful, but it seems like it would only be useful if I could order the terms by counts.
This is what I have:
$result=mysql_query("SELECT term, COUNT(term) FROM search_terms WHERE timestamp>='$seven_days_ago' GROUP BY term LIMIT 5");
When I try to add a ORDER BY count(term) command I get errors.
Also, JAG, your talking about the BETWEEN command. Are you saying I should use the between command seven times (for each day) for each of the five search terms I have after I have found out what my five most popular search terms are for the week?
Thanks
$result=mysql_query("SELECT term, COUNT(term) as term_count FROM search_terms WHERE timestamp between to_days(now()) and to_days('$seven_days_ago') GROUP BY to_days(timestamp) order by term_count desc LIMIT 5");
JAG
