How would I log searches on my PHP/MySQL website?

Question

When someone searches my website using a quick search feature I would like to log this into my database

1. Obviously I want to see what my users are searching for
2. I can create a 'tag cloud' based on these searches

I have a table searchterms at the moment with two fields: id and terms, I was wondering if this was the best method of logging this info. Terms is becoming a bit redundant however.

any clues or links to relevant scripts?

Thanks guys

Answer 1

what about a table to store the terms (maybe in some "normalized" way, like all lowercase, no accents, ...), and another table to store when those terms have been searched for ?

Normalization thing is to avoid "Word" and "word" being seen as two different terms in your could ; same for "éléphant" and "elephant", btw.

terms
  - id
  - word

terms_searched
  #id _term
  - time_search

Then, knowing the last searched terms, or the most searched terms, is just a matter of a simple SQL query, like

select id, term, count(*)
from terms
    inner join terms_searched on terms_searched.id_term = terms.id

Maybe with a where clause to specify you only want recent searches to be taken into account ?

And if your tables become really big and the join in the query starts hurting performances, you can envisage denormalization, putting a "num_times_searched" in terms table, that sums up how many times a term has been searched for

Answer 2

I haven't tried but google analytics provides the option to track what users enter in your search box. For displaying there's the developer API.

The caveat is this would all be javascript. The advantage is that it would lessen the load on the server.

Maybe not what you were looking for but worth mentioning.