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

PoorDoggie · 02-22-2006, 02:55 PM

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:

Quote:

USER_ID | NAME
????: NamePros.com http://www.namepros.com/programming/170190-mysql-please-take-look-too-long.html

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 · 02-22-2006, 06:59 PM

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 · 02-23-2006, 12:36 AM

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/...functions.html

sdsinc · 02-23-2006, 08:48 AM

You need something like this:

PHP Code:

  SELECT USER_ID, count(USER_ID) AS count_USER_ID

????: NamePros.com http://www.namepros.com/showthread.php?t=170190
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 · 02-23-2006, 12:10 PM

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

sdsinc · 02-23-2006, 12:54 PM

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:

PHP Code:

  $query = "SELECT USER_ID, count(USER_ID) AS count_USER_ID"

????: NamePros.com http://www.namepros.com/showthread.php?t=170190
." FROM your_table "

." GROUP BY USER_ID "

." ORDER BY count_USER_ID DESC "

." LIMIT 50";

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

????: NamePros.com http://www.namepros.com/showthread.php?t=170190
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 · 02-23-2006, 12:59 PM

great - thanks so much - have some reputation!

02-23-2006, 08:48 AM	#4 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,963	You need something like this: PHP Code: `SELECT USER_ID, count(USER_ID) AS count_USER_ID ????: NamePros.com http://www.namepros.com/showthread.php?t=170190 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) __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

02-23-2006, 12:54 PM	#6 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,963	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: PHP Code: $query = "SELECT USER_ID, count(USER_ID) AS count_USER_ID" ????: NamePros.com http://www.namepros.com/showthread.php?t=170190 ." FROM your_table " ." GROUP BY USER_ID " ." ORDER BY count_USER_ID DESC " ." LIMIT 50"; $link=mysql_pconnect("localhost","root","password"); // connect to db ????: NamePros.com http://www.namepros.com/showthread.php?t=170190 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. __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
HOWTO: Install the Apache Web Server, Perl, PHP, and MySQL on Windows	deadserious	Webmaster Tutorials	96	05-27-2007 01:24 PM
Tutorial: How to Install Apache2 MySQL and PHP on Windows	deadserious	Webmaster Tutorials	35	09-21-2005 09:46 PM
800MB Space, 25GB Bandwidth, Unl. MySQL DB, and more Just $4.00 a month!	resellerlogic	Web Hosting Offers	0	04-05-2005 06:44 PM
Tutorial: Getting Started With MySQL (The Basics)	deadserious	Webmaster Tutorials	3	04-18-2004 01:17 PM
importing data in MYsql	shakim	Programming	6	08-01-2003 03:46 PM

02-22-2006, 06:59 PM	#2 (permalink)
Jeff12088 New Member Join Date: Jan 2006 Location: San Jose, CA Posts: 19	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.

02-23-2006, 12:36 AM	#3 (permalink)
Peter NamePros Expert Join Date: Nov 2003 Location: Scotland Posts: 5,074	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/...functions.html

02-23-2006, 12:10 PM	THREAD STARTER #5 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,408	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

02-23-2006, 12:59 PM	THREAD STARTER #7 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,408	great - thanks so much - have some reputation!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)