[SOLVED] how to query for top 10 keywords in mysql

[dsaba]

i am trying to make statistics for what people search on my search engine with mysql

its very complicated thing to wrap your mind around but i will try to explain it well

 

lets say i have a table named "keywordcount" in my mysql database

 

here is a look at some sample fields and the rows in the fields:

 

KEYWORD    FILEID    FILEGENERALTYPE  FILESPECIFICTYPE  USERID

boats            24            Videos                    Divx Movies      4

boats            36              Music                      Hip Hop            4

boats            22            Videos                    Ipod Movies      3

water            12              Software                    Linux          16

spring            88              Software                  Linux            230

spring            104            Videos                    Divx Movies    230

 

now from this table I want to do the following things:

1. the top 2 keywords from everything

2. the top 2 keywords from videos (generaltype)

3. the top 2 keywords from linux (specifictype)

 

how would I go about doing that?

 

this is what I have brainstorms but it has problems as you will see:

1. for top 2 keywords from everything i would query where the same keyword shows up multiple times??

    I don't know how to type that query out

2. for top 2 keywords from videos i could query where generaltype= videos

    but how would i determine which keywords are used the most?

3. the same for finding in specific type

 

-thanks

[Barand]

SELECT keyword, COUNT(*) as howmany

FROM keywordcount

GROUP BY keyword

ORDER BY howmany DESC

LIMIT 2

 

 

SELECT keyword, COUNT(*) as howmany

FROM keywordcount

WHERE filegeneraltype = ''Videos'

GROUP BY keyword

ORDER BY howmany DESC

LIMIT 2

 

SELECT keyword, COUNT(*) as howmany

FROM keywordcount

WHERE filespecifictype = ''Linux'

GROUP BY keyword

ORDER BY howmany DESC

LIMIT 2

[dsaba]

ok thank you for telling me how to query I didn't know you could do that with mysql

 

HOWEVER

when you query for keyword and do COUNT(*) howmany

 

the keyword will not be unique in the table so it may find that boats is the most used keyword

 

but when i list it in a table as top 10 keywords i dont want to to display

 

boats

boats

boats

boats

boats

 

do you get what i'm saying? i want to identify the most used keywords, and then list the keywords that are unique among that list that I identified

[Barand]

Don't knock it till you've tried it.

 

Those queries will give something like

 

boats  24

spring  20

[dsaba]

ok so you're saying when you use "GROUP BY"

 

it will find all the unique keywords in the rows I counted

 

EDIT*  and also COUNT(*) howmany

 

howmany here is not a fieldname it is a mysql command, so it should be in caps as well right?

i know mysql don't have to be in caps, but for organizational matters, that is a command right?

 

[Barand]

Yes, it gets the unique values and the COUNT(*) gets a count of each of those values

[dsaba]

i edited previous post thought i could beat u to the reply well look at previous post

 

you CAPS all mysql commands, and u didn't do it to "howmany" so i'm unsure what it is referring to, whether it is a command or a fieldname

[Barand]

As a convention I use CAPS for SQL keywords and nocaps for tables, fields etc.

 

"howmany" is an alias for COUNT(*) and is same as a fieldname.

 

EG

<?php
$sql = "SELECT keyword, COUNT(*) as howmany
          FROM keywordcount
          GROUP BY keyword
          ORDER BY howmany DESC
          LIMIT 2";
$res = mysql_query ($sql);
while ($row = mysql_fetch_assoc($res)) {
        echo $row['keyword'] . ' : ' . $row['howmany'] . '<br/>';
}
?>

 

[dsaba]

ok i think i understand it better, because i am used to the idea that everything in an array refers to fieldnames that exist in a table

 

well you just pulled "howmany" out of the blue, it is NOT a fieldname in my keywords table

 

so what I get from this is when I use this command I am making a temporary fieldname with data in it for my array that I can call on, but it is not actually a fieldname in my table, and will not be entered in there

[Barand]

Anything you use as an alias IS a fieldname as far as the query is concerned.

 

You can pull them out of the blue, as in

 

SELECT NOW() as today.

 

or you can give an alias to an existing column

 

SELECT keyword as kw

 

in which case you have to refer to $row['kw'] when processing the results.