Page 1 of 1

how to group duplicate entries

Posted: Sat Jul 20, 2002 11:33 pm
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

Posted: Sun Jul 21, 2002 6:11 am
by twigletmac
I think this is what you're looking for:
http://www.mysql.com/doc/G/r/Group_by_functions.html

Mac

count(Distinct )

Posted: Sun Jul 21, 2002 4:11 pm
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.

Posted: Sun Jul 21, 2002 7:41 pm
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

Posted: Sun Jul 21, 2002 10:01 pm
by craginweb
Thanks sam you solved my problem.