MySQL viewing saved searches Oct31 '05

A screen shot of a search box

One of the most useful functions in MySQL is the COUNT() function, which collectively grabs the total number of just about any 'result combination' you can think of.

For example, on a corporate site I am maintaining, I like to keep track of all searches submitted at the site, through our search box. I do this by having each search request entered into a database, at the time of the search.

This allows us to see common phrases being searched for, detect patterns, and adjust the site accordingly.

With the search terms all stored in a database - there are many different ways to view those search terms, for our own studies.

With SQL, we can query for those search terms, using useful criteria.

Below are some examples of how we can use SQL to view our saved searches database.

The most recent search requests:

SELECT * FROM saved_searches ORDER BY datetime DESC

The top 20, most recent search requests:

SELECT * FROM saved_searches ORDER BY datetime DESC LIMIT 20

The most common search requests:

SELECT searchterm, COUNT(*) AS 'Number of times searched'
FROM saved_searches 
GROUP BY searchterm
ORDER BY 'Number of times searched' DESC

The most common search requests, by a specific IP address:

SELECT searchterm, COUNT(*) AS 'Number of times searched', ip_address
FROM saved_searches
WHERE ip_address = '32.433.12.5'
GROUP BY searchterm
ORDER BY 'Number of times searched' DESC

As you can see, there are many different ways to view our saved search reqests. Depending on the extensiveness of the database table structure - the possibilities are almost endless.

Categories: Search , SQL

Add Feedback (view all)

Leave feedback

Feedback

Input format: The editor controls below will assist with Markdown syntax.

Status

Sub-status

Your info

matthom is published and produced by Matt Thommes - an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from Chicago. Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.

Contact Matt

Similar Entries

Stats

22 unique visits since August 2008

Syndicate

Advertisements