SQL Statement for top searched keywords.

[virtuexru]

OK, so when someone searches on my site, I add the keyword to a DB. Now I want to show the results as by the "Top 10 most searched keywords".. so my SQL to insert is this:

 

			   				   			
$kcount = "INSERT INTO search 
(id, keyword) 
VALUES 
('', '$keyword')";

mysql_query($kcount);					

 

so say I have "hummer" 3 times and "porsche" 4 times and "toyota" once.

 

How would I make an SQL statement to see the most used keyword (in this case, it would be porsche)?

 

I know this is probably really simple but I'm drawing a blank.

[Mchl]

SELECT DISTINCT keyword, COUNT(*) AS count FROM search GROUP BY keyword ORDER BY count DESC LIMIT 1

 

Although it would be better if your table was like this

table search
id, keyword, count
1, hummer, 3
2, porsche, 4
3, toyota, 1

 

 

 

[fenway]

First, you don't need distinct, since it's already group-by'ed.

Second, you can easily accomplish "hit counters" with and INSERT... ON DUPLICATE KEY... UPDATE...

[Mchl]

I'm always lost with distinct... I tested this query in my database and it seemed to me that DISTINCT was necessary

[fenway]

I'm always lost with distinct... I tested this query in my database and it seemed to me that DISTINCT was necessary

Shouldn't be... one count per keyword.