boggled mind

crazy select statement

[bobnew32]

Ok I have the table with the following fields.

id ¦¦ file_id ¦¦ time ¦¦ ip_address ¦¦ referrer

The information is added great and I love it, but I want to display it a certain way.

How I want is it to display the top five referrers and top five ip addresses for a certain $file_id. I think it would be a certain kind of for each inside of a while statement? Please help me get to the next level in php programming. Thx

[coopster]

Is this in a flat text file, or in a database, such as MySQL?

[bobnew32]

Oops sorry in my first sentence I meant a table in a mysql database. The info I want can just be printed out.

[jatar_k]

what does the data look like?

one row per hit or is it counted in any given row?

[bobnew32]

One row per hit. So yeah I have like 10,000 rows but thats ok.

[jatar_k]

sounds like some combo of count, order by, group by and limit or somewhere thereabouts, maybe unique too.

how many unique file_id's?

[dcrombie]

SELECT DISTINCT field, COUNT(*) AS count FROM table GROUP BY field ORDER BY count DESC LIMIT 5;

;)

[jatar_k]

nice dcrombie, that was what I was thinking just a little too bogged down with work to manage to make a few moments to figure it out. thx

[bobnew32]

Sorry to nag, but can somebody explain the code dcrombie posted? I can kind of figure it out as I know the entire basics of mysql, but this is a bit out of my league.

[jatar_k]

a slick little select that is

SELECT DISTINCT field, COUNT(*) AS count FROM table GROUP BY field ORDER BY count DESC LIMIT 5;

replace all bolded items with the actual names of cols, fields, tables

distinct
retrieve each unique output record just once by adding the keyword DISTINCT

count

try something like
select count(colname) as count from tablename
that will show you, but it essentially does what you would think counts the fields that match the query and returns an integer

group by [mysql.com]

order by colname == "sort by" column name

desc == descending
asc == ascending

that help? try changing to the cols, tables etc in your particular db and then see if you can get it to work

and you're not nagging

[slade7]

gee willie - if I can make that work with SUM instead of COUNT and maybe add a subselect...and if it will fly on a table with 26,000+ rows...

I'll have to get back to you on this...