Jump to content


Photo

Count Unique Records


  • Please log in to reply
7 replies to this topic

#1 koolgirl

koolgirl
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 01 July 2006 - 09:46 AM

Hi again Everybody,

This is getting more and more interesting and complicated the more I am learning. I am collecting the visitors records such as IP, Host, Browser and OS etc in a MySQL database table. To view the daily hits record I have some administrative pages from where I can check the visitor activity on the site. For example I group the records by date and get the total hits at a specific date. All works perfect for me, even better and more reliable then the free counters etc that I was using before I started to learn PHP myself. Now I have a small question again, probably very easy one. I can get the total hits of the day simply with this script:

<?php

// Request the text of all the URLs

$result = mysql_query(


"SELECT Date, IP, COUNT(*) AS datecount FROM UserRecords WHERE Date='$dte' GROUP BY Date");

if (!$result) {

echo("<P>Error: " .

mysql_error() . "</P>");

exit();

}

echo "<table border='0'>";

echo "<tr>";

while ( $row = mysql_fetch_array($result) ) {

echo "<tr>";

echo "<td>" . "Total Hits Count @ ". $dte . " : ". $row["datecount"] . "</td>";

echo "</tr>";

}

echo "</table>\n";

?>
******************************************************

This works fine for the total hits of the day, or the whole time. I want to count the unique IP addresses of the same date. I was thinking that SELECT DISTINCT will do the job, maybe I am right but my syntax is not right, but anyway with SELECT DISTINCT too I am getting the same result as I am getting with the GROUP BY. I am trying it this way:

"SELECT DISTINCT COUNT(IP) AS uniqs FROM UserRecords WHERE Date='$dte'");

********************************************************

Any help greatly appriciated.

::)

#2 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 01 July 2006 - 09:51 AM

You need to use DISTINCT.

A tutorial:
http://www.1keydata....qldistinct.html

Orio.
Think you're smarty?

(Gone until 20 to November)

#3 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 01 July 2006 - 10:14 AM

orio thank you for that link i just used thats the best cut down version of the whole mysql ever seen thanks a lot.


Have you got a php link like that stright to the point.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#4 koolgirl

koolgirl
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 01 July 2006 - 10:40 AM


Hi Orio and Redarrow,

Thanks alot for the replies. I am sorry to bother again but I didn't actually understand my mistake from the link. The syntax that I used before posting here counts it also for me but the problem is that it does not count the unique IP addresses. For example if I get 200 hits a day from 30 IP addresses. The script that I tried to use counts 200 IP addresses and not 30.
I would be very Thankful if somebody can review the code that I posted before and reply.

#5 sabadell

sabadell
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 01 July 2006 - 11:11 AM

"SELECT Date, IP, COUNT(*) AS datecount FROM UserRecords WHERE Date='$dte' GROUP BY Date");

This is how it works.........

SELECT DISTINCT "column_name"
FROM "table_name"

The column name in your case to shown only unique records would be IP i.e. SELECT DISTINCT (from your table) .`the column you want to be distinctive about` and then the rest of the SQL. Below is an example

$query_Recordset1 = "SELECT DISTINCT mytable.`mydistinctcolumn`, mytable.Anothercolumn, mytable.AnotherColumn2  FROM mytable ";




#6 koolgirl

koolgirl
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 01 July 2006 - 11:37 AM

Hi Sabadell

call me a typical blond  ::) but I still don't actually understand and get the point how to count only the unique IP addresses.

"SELECT Date, IP, COUNT(*) AS datecount FROM UserRecords WHERE Date='$dte' GROUP BY Date");---> is what I am using tocount the total hits of the day. Working perfectly.

"SELECT DISTINCT COUNT(IP) AS uniqs FROM UserRecords WHERE Date='$dte'");---> is what I am trying to use to count the unique IP addresses but it returns the same result as the 1st one.


Now I tried to use what you suggested. Here I couldn't get it work because I need to "COUNT As uniqs" so that I can get the result in the table cell.
$query_Recordset1 = "SELECT DISTINCT mytable.`mydistinctcolumn`, mytable.Anothercolumn, mytable.AnotherColumn2  FROM mytable ";

HelPppppppppppppppp!!!!

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 01 July 2006 - 01:50 PM

SELECT date, COUNT (DISTINCT IP) FROM UserRecords
GROUP BY date
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 koolgirl

koolgirl
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 01 July 2006 - 03:23 PM

THANKKKSSSSSSSSSSSSSSSSS

Barand, this is all I needed. Thanks again




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users