Count Unique Records

[koolgirl]

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.

::)

[Orio]

You need to use DISTINCT.

A tutorial:
http://www.1keydata.com/sql/sqldistinct.html

Orio.

[redarrow]

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.

[koolgirl]

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.

[sabadell]

"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 ";

[koolgirl]

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!!!!

[Barand]

SELECT date, COUNT (DISTINCT IP) FROM UserRecords
GROUP BY date

[koolgirl]

THANKKKSSSSSSSSSSSSSSSSS

Barand, this is all I needed. Thanks again