Jump to content

Count Unique Records


koolgirl

Recommended Posts

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.

::)
Link to comment
https://forums.phpfreaks.com/topic/13369-count-unique-records/
Share on other sites


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.
Link to comment
https://forums.phpfreaks.com/topic/13369-count-unique-records/#findComment-51637
Share on other sites

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


Link to comment
https://forums.phpfreaks.com/topic/13369-count-unique-records/#findComment-51650
Share on other sites

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!!!!
Link to comment
https://forums.phpfreaks.com/topic/13369-count-unique-records/#findComment-51664
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.