[RESOLVED] GROUP BY trouble...

middleman666

hey people,

I have a list 4000+ of IP address' and I'm trying to group the IP address' together so I can find the number of unique visitors to my site.

I have:

//Total Unique Hits
$result=mysql_query("SELECT COUNT(*) FROM $TableName GROUP BY IP"); 
$num=mysql_fetch_row($result); 
if ($num[0] != 0) { $totaluniquehits = $num[0]; }

But that returns '2' even though there are more than 2 different IP address' in the database.

Anyone know why that may be?

I'm using version 4.0.27 of MySQL.

Thanks

dagon

2 is the number of times the first ip appears

your results will look like this

COUNT( * )
2
1
3
4
6
etc

you want to use mysql_num_rows()

dagon

SELECT COUNT( DISTINCT ip ) FROM $TableName

is a better idea

middleman666

That works perfectly, thanks!

//Total Unique Hits
$result=mysql_query("SELECT COUNT(*) FROM $TableName GROUP BY IP"); 
$totaluniquehits=mysql_num_rows($result);

EDIT:

And so does:

//Total Unique Hits
$result=mysql_query("SELECT COUNT(DISTINCT IP) FROM $TableName"); 
$num=mysql_fetch_row($result); 
if ($num[0] != 0) { $totaluniquehits = $num[0]; }

Cheers!

dagon

I think the latter would be faster.

middleman666

Yea, I tried both and I'm sticking with the latter one. Will have to remember the (DISTINCT ) code for the future.

Thanks again.