Solved

PHP MYSQL Unique values

Posted on 2009-06-28
8
459 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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
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.

728 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