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
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) {
echo( "<p>Unable to connect to the database server at this time.</p>" );
exit();
}
// Select the database
if (! @mysql_select_db("aaaaa") ) {
echo( "<p>Unable to locate the database at this time.</p>" );
exit();
}
// Request the data from the database
$result = @mysql_query("SELECT COUNT(DISTINCT IP) FROM users");
if (!$result) {
echo("<p>Error performing query: " . mysql_error() . "</p>" );
exit();
}
// Display the IP Address List
while ( $row = mysql_fetch_array($result) ) {
echo("\t<li>\n");
echo("\t\t<p style="background-color:#b0e0e6;width:100%;">\n");
echo("\t\t<b>IP Address:</b> " . $rowї"IP"] . "\n");
echo("\t\t</p>\n");
echo("\t</li>\n");
}
?>
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ї'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.