PHP MYSQL Unique values

I have a PHP report showing page hits on a CMS
SELECT *, count(CMSID) AS CNT1 FROM tblvisitorLog GROUP BY CMSID ORDER BY CNT1

The table includes a column storing visitors IP addresses

Can you show me how to differentiate the number of unique IPs in the report?
Ideally I want to show the two figures side by side
thanks
joomlaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

navinbabuCommented:
This should Help :)

$query = "SELECT category, COUNT(*) AS cat_num FROM tablename GROUP BY category";
 
$result = mysql_query($query);
 
while($row = mysql_fetch_array($result))
 
{
 
echo "$row[category] = $row[cat_num]<br>";
 
} 

Open in new window

0
joomlaAuthor Commented:
Hi navinbabu,
thanks for the reponse, however I must not have been clear in my question
using my query, I can get the number of hits for each page
where CMSID is the identifer of each page.

my table also stores the IP address of each visitor

I specifically want to show the the total number of hits per page  (which I currently have)
but also, the number of unique IP addresses against each page.

so for example
Page10 had 50 hits, however only 45 unique IP addresses

thanks
Michael
0
joomlaAuthor Commented:
Hi
was hoping I'd hear about this today?

I'd rather create a sleak solution than write heaps of code to achieve it.
thanks
Michael
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

navinbabuCommented:
Hey

Yes So we are searching for unique IP address that is the discint

SELECT count(distinct value) as 'uniquevalues',count(value) as 'values' FROM database

Values - Hits

uniquevalues - Unique Visitors


0
navinbabuCommented:
like SELECT count(distinct IP) as 'uniquevisitors',count(CMSID) as 'hits' FROM database


   
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
navinbabuCommented:
" Page10 had 50 hits, however only 45 unique IP addresses "

Sorry for above solutions I must be total drunk .

Here we go  :

This is the example table  
                token      value  ( Token is your CMS ID , value is your IP )
         1        1111        aaaa
      2       1111       aaaa
      3       1111       aaaa
       4       1111       bbbb
      5       1111       bbbb
       6       2222       cccc
       7       2222       cccc

Here go the code

 $query = "SELECT count(distinct token , value) as cut,count(token) as cnt , token FROM test GROUP BY token";
 
 
     $result = mysql_query($query);
 
      while($row = mysql_fetch_array($result))
     {
 
      echo "Name : $row[token]<br>";
        echo " Unique : $row[cut]<br>";
       echo " Total : $row[cnt]<br>";
         }



The result

Name : 1111
Unique : 2
Total : 5
Name : 2222
Unique : 1
Total : 2


    I think this is what you are expecting

change the columns accordingly mind me for the idiotic variables , :)


SELECT count(distinct token , value) as cut,count(token) as cnt , token FROM test GROUP BY token

Open in new window

0
navinbabuCommented:
Here we go the complete code :
<?php
 
 
$hostname = "localhost";
$database = "ip";
$dbusername = "root";
$password = "";
 
$login = mysql_connect($hostname, $dbusername, $password) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database);
 
 
 
     
//	  $query = "SELECT token, COUNT(*) AS cat_num , count(distinct token , value) as cut FROM test GROUP BY token";
 
  $query = "SELECT count(distinct token , value) as cut,count(token) as cnt , token FROM test GROUP BY token";
 
 
     $result = mysql_query($query);
 
      while($row = mysql_fetch_array($result))
     {
 
      echo "Name : $row[token]<br>";
	  echo " Unique : $row[cut]<br>";
       echo " Total : $row[cnt]<br>";
	   } 
 
 
 
 
?>

Open in new window

0
joomlaAuthor Commented:
Thanks navinbabu
The query statement is exactly what I needed
Sorry I was unavailable due to timezones to reply earlier
but apreciate the level of support you were prepared to offer.
Its was very much appreciated.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.