Solved

PHP MYSQL Unique values

Posted on 2009-06-28
8
440 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

These days socially coordinated efforts have turned into a critical requirement for enterprises.
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.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now