Solved

PHP MYSQL Unique values

Posted on 2009-06-28
8
455 Views
Last Modified: 2013-12-13
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
0
Comment
Question by:joomla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 2

Expert Comment

by:navinbabu
ID: 24733354
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
 

Author Comment

by:joomla
ID: 24733382
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
 

Author Comment

by:joomla
ID: 24733655
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 2

Expert Comment

by:navinbabu
ID: 24734426
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
 
LVL 2

Accepted Solution

by:
navinbabu earned 500 total points
ID: 24735890
like SELECT count(distinct IP) as 'uniquevisitors',count(CMSID) as 'hits' FROM database


   
0
 
LVL 2

Expert Comment

by:navinbabu
ID: 24736754
" 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
 
LVL 2

Expert Comment

by:navinbabu
ID: 24736792
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
 

Author Closing Comment

by:joomla
ID: 31597771
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question