Jump to content

Archived

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

koolgirl

Count Unique Records

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.

::)

Share this post


Link to post
Share on other sites
You need to use DISTINCT.

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

Orio.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
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.

Share this post


Link to post
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 ";


Share this post


Link to post
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!!!!

Share this post


Link to post
Share on other sites
SELECT date, COUNT (DISTINCT IP) FROM UserRecords
GROUP BY date

Share this post


Link to post
Share on other sites

×

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.