Is it possible to create a "most popular" search?

[Dan Gaffey]

In my website, it would be very useful to have a most popular search. But i have know idea how I would do it. When a song is searched, a MySQL query saves the song's name, artist and a link to buy it into a database table. But would thre any way to have some kind of hit counter that could increment every time a song is searched instead of creating duplicates? Or any other way I could get a most popular search?

Here is the query that saves the data if it helps:

//Most popular
mysql_query("INSERT INTO MostPopular (song_name, artist, purchase_link)
VALUES ('$title', '$artist', '$Buy')");
 

[JKing]

Hi again,

If you are saving each search, you could use the COUNT() keyword along with GROUP BY to generate a sum of the searches for each song. Once again if you use ORDER BY and DESC you will have your results from most to least.

Here's an example:

SELECT COUNT(song_name) as song_count, song_name, artist 
FROM MostPopular 
GROUP BY song_name 
ORDER BY song_count DESC
 

You could also modify this to find the most popular artists, most popular songs by a specific artist etc.

If at all possible you may want to store songs by their ID as opposed to their name.

The more data you have, the more queries you can produce. For example you may want to start storing timestamps with your searches. This allows you to pull stats by month, week, day or year.

[Dan Gaffey]

Okay, so with these timestamps, would it be posible to do a "top ten" kind of thing that could refresh every day?

[Dan Gaffey]

I've tried it out and the order of each one works fine, but the purchase link doesn't. It comes up with "Notice: Undefined index: purchase_link". Is this to do with the grouping?

[JKing]

I don't think that has to do with grouping without seeing the code that goes along with the error I can't tell you too much else.

But yes with timestamps you could do a daily top ten.

[Dan Gaffey]

It wasn't working because I overlooked actually selecting it in the query in the first place. All sorted now (y)