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
how to group duplicate entries
Moderator: General Moderators
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
count(Distinct )
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:
When I changed this line
to I started to recieve the error Undefined index: IP. Which refers to $row["IP"].
Now I am really confused.
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");
}
?>Code: Select all
$result = @mysql_query("SELECT IP FROM users");Code: Select all
$result = @mysql_query("SELECT COUNT(DISTINCT IP) FROM users");Now I am really confused.
-
samscripts
- Forum Commoner
- Posts: 57
- Joined: Tue Apr 23, 2002 4:34 pm
- Location: London, UK
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 and you should get the IP's.
Simplest way around this is to use the following:
(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
Try
Code: Select all
echo $rowї'COUNT(DISTINCT IP)'];Simplest way around this is to use the following:
Code: Select all
$result = mysql_query("SELECT COUNT(DISTINCT IP) AS IP FROM users");hope this helps