Solved

PHP MYSQL Unique values

Posted on 2009-06-28
8
446 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
  • 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

825 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