Mysql: please take a look, too long to explain here

[PoorDoggie]

Hi,

I want to look into a mysql database and get the top 50 most regularly occuring fields.

- the field I am looking in is called user_id
- I want to get a list of the top 50 most frequently occuring user_ids in this table, with the amount of times they occur.

For instance:

lets say my table is like this:

USER_ID | NAME

12345 | Tom
12346 | Mark
12346 | Paul

and I want to get the most occuring user ID I want to get:

"123456" and "2" (most occuring, and number of times occuring)

How would I do this?

Thanks a lot
Tom

 

[Jeff12088]

Do you know the range of number you are dealing with? 1-100, 1-8000 or anything?
I don't know any mySQL parameters to use in this case, but if you know your range, you can probably do a loop and find out.

 

[Peter]

have a look at group by and count() on mysql.org they are what you are looking for.

http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html

 

[Kate]

You need something like this:

SELECT USER_ID, count(USER_ID) AS count_USER_ID
FROM your_table
GROUP BY USER_ID
ORDER BY count_USER_ID DESC 
LIMIT 50

It means: give me the list of the 50 most frequent USER_ID in my table (descending order: larger figures first)

 

[PoorDoggie]

great thanks a lot sdsinc! I will see if it works. If I were to add it to an array named $var would I output it:

$var['USER_ID'] OR $var['count_USER_ID'] ???

Thanks
Tom

 

[Kate]

Well I guess you want to display the results of your query right ?
So you already have an array (using mysql_fetch_array)
For instance consider this code:

$query = "SELECT USER_ID, count(USER_ID) AS count_USER_ID"
." FROM your_table "
." GROUP BY USER_ID "
." ORDER BY count_USER_ID DESC "
." LIMIT 50";

$link=mysql_pconnect("localhost","root","password"); // connect to db
mysql_select_db ("some_db" ,$link); // select db

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
while($row = mysql_fetch_array($result)){
    echo 'USER_ID: '.$row["USER_ID"]. " appears ". $row["count_USER_ID"]. " times<br />";
}

This is a simple PHP script to output the results of the query.
Please note that the count of USER_ID in your table is aliased as count_USER_ID in my example. You could rename this to something else. Then use the same field name when outputting the results of your query.

 

[PoorDoggie]

great - thanks so much - have some reputation! :lol: