how to group duplicate entries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
craginweb
Forum Newbie
Posts: 13
Joined: Fri Jul 05, 2002 12:49 am
Location: FWB Florida

how to group duplicate entries

Post by craginweb »

I have a user log that gathers user information and sends it to a mysql database, then using php I can get the results of each type of entry. The problem I have is that I would like to group the duplicate entries and show the entry along with the number of times it was entered.

For example if I was tracking users IP Addresses, when I query the database I get the IP Addresses like so:

IP Address: 127.0.0.1
IP Address: 127.0.0.1
IP Address: 66.77.73.254
IP Address: 144.160.5.81
IP Address: 144.160.5.81

Is there a way I can summerize the results and group the duplicate IP addreses and show the Totals and Averages?
Something like:

IP Address: 127.0.0.1 entry = 2
IP Address: 66.77.73.254 entry = 1
IP Address: 144.160.5.81 entry = 2

If anyone has any ideas please point me in the right direction.
Thanks
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

I think this is what you're looking for:
http://www.mysql.com/doc/G/r/Group_by_functions.html

Mac
craginweb
Forum Newbie
Posts: 13
Joined: Fri Jul 05, 2002 12:49 am
Location: FWB Florida

count(Distinct )

Post by craginweb »

Thanks twigletmac, that's what I was loking for.
Only problem now is that when I run the script I get an error:

Undefined index: IP in ip_stats.php on line 25

This is my code:

Code: Select all

<?php 
  // Connect to the database server 
  $dbcnx = @mysql_connect("localhost", "aaaaa", "aaaaaa"); 
  if (!$dbcnx) &#123; 
    echo( "<p>Unable to connect to the database server at this time.</p>" );
    exit(); 
  &#125; 
  // Select the database 
  if (! @mysql_select_db("aaaaa") ) &#123; 
    echo( "<p>Unable to locate the database at this time.</p>" ); 
    exit(); 
  &#125; 
  // Request the data from the database
  $result = @mysql_query("SELECT COUNT(DISTINCT IP) FROM users");
  if (!$result) &#123; 
    echo("<p>Error performing query: " . mysql_error() . "</p>" ); 
    exit(); 
  &#125; 

  // Display the IP Address List 
  while ( $row = mysql_fetch_array($result) ) &#123;  
  	echo("\t<li>\n");
	echo("\t\t<p style="background-color:#b0e0e6;width:100%;">\n");
	echo("\t\t<b>IP Address:</b> " . $row&#1111;"IP"] . "\n");
	echo("\t\t</p>\n");
	echo("\t</li>\n");
  &#125; 
?>
When I changed this line

Code: Select all

$result = @mysql_query("SELECT IP FROM users");
to

Code: Select all

$result = @mysql_query("SELECT COUNT(DISTINCT IP) FROM users");
I started to recieve the error Undefined index: IP. Which refers to $row["IP"].
Now I am really confused.
samscripts
Forum Commoner
Posts: 57
Joined: Tue Apr 23, 2002 4:34 pm
Location: London, UK

Post by samscripts »

Hi, the problem is that you are no longer selecting a field called IP from mysql, you are now selecting a field called 'COUNT(DISTINCT IP)'.

Try

Code: Select all

echo $row&#1111;'COUNT(DISTINCT IP)'];
and you should get the IP's.

Simplest way around this is to use the following:

Code: Select all

$result = mysql_query("SELECT COUNT(DISTINCT IP) AS IP FROM users");
(note the AS IP). Check your mysql manual - I can't remember the term for this - might be aliases or something like that.

hope this helps
craginweb
Forum Newbie
Posts: 13
Joined: Fri Jul 05, 2002 12:49 am
Location: FWB Florida

Post by craginweb »

Thanks sam you solved my problem.
Post Reply