[SOLVED] I want to be able to count an item once using COUNT(*)

[jeger003]

i want to be able to count ips once.........i basically log visits for my site cause i get alot of spam from europe....so i want to be able to count all the ips....but only count the once

 

for example:

(not real ips of course)

 

192.59.65.23 visits page /index.html

192.59.65.23 visits page /classified.html

 

172.65.35.98 visits page /index.html

172.65.35.98 visits page /classified.html

 

but i want to be able to count the above ips only once.......so that it tells me there were 2 visits and not 4

 

below code is what i have so far.......i would appreciate any help

 

echo "<b>IP Counts</br></b>";
$query = "SELECT VisIP, COUNT(VisIP) FROM logs GROUP BY VisIP"; 

$result = mysql_query($query) or die(mysql_error());


// Print out result
while($row = mysql_fetch_array($result)){
echo "". $row['COUNT(VisIP)'] ."ips" ;
echo "<br />";

}

[premiso]

<?php
$query = "SELECT COUNT(VisIP) as ipCount FROM logs GROUP BY VisIP"; 
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
   echo "". $row['ipCount'] ."ips" ;
   echo "<br />";

}

 

Try that, having the visIP column in there on it's own essentially throws off the group by and doing an actual count. (I think the above is right but I am not 100% sure)

[sasa]

use COUNT(DISTINCT(VisIP)) in your query

[jeger003]

hey guys thanks for the help....

 

premiso---i used the code worked but it it basically counts the number of ips individually

 

like if i had

 

192.59.65.23 \

                      two ips from one person it displays "2-192.59.65.23" so it only counts that there

                      were two ips showing

192.59.65.23 /

 

what im trying to get it to do is to have it count all the ips but only count the them once....i would think i would have to use the "if" statement like if($ip = $otherip) dont count it.

 

 

Sasa---i tried COUNT(DISTINCT(VisIP))..........it gave me an error i dont think im doing it right....do i just replace all the COUNT(VisIP)? cause thats what i did.

 

 

 

 

thank you guys

[sasa]

what is error

[jeger003]

oh sorry

 

error was:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') FROM logs GROUP BY VisIP' at line 1

 

but i didnt realize that it was tellin me i had an extra ")" so i removed it......it worked but not wat i wanted it to do this is what it displays

 

 

1ips 154.247.714.226

1ips 241.163.123.216

1ips 249.151.1.120

1ips 249.151.1.122

1ips 164.113.13.105

1ips 164.106.16.39

1ips 164.596.59.344    <----same ip      i want it to count all the ips but only once

1ips 164.596.59.344    <----same ip

1ips 165.97.345.30

1ips 174.349.131.149

1ips 205.157.206.225

 

its basically counting ip's sayin heres 1 ip of blah blah.....and here is another of the same ip

 

 

[haku]

Show us your code. Always step one.

[jeger003]

here it is....this is wat i used with the code sasa gave

 

 

<?php
$query = "SELECT VisIP, COUNT(DISTINCT(VisIP)) FROM logs GROUP BY VisIP";
   
$result = mysql_query($query) or die(mysql_error());


// Print out result
while($row = mysql_fetch_array($result)){
   echo "". $row['COUNT(DISTINCT(VisIP))'] ."ips" ;
echo "". $row['VisIP'] ."ips" ;
   echo "<br />";

}

[keyurshah]

Try this:

 

   $query = "SELECT VisIP, COUNT(DISTINCT(VisIP)) AS IPCnt FROM logs GROUP BY VisIP";
   $result = mysql_query($query) or die(mysql_error());

// Print out result
   while($row = mysql_fetch_array($result)){
      echo "". $row['IPCnt'] ."ips" ;
      echo "". $row['VisIP'] ."ips" ;
      echo "<br />";
   }

[jeger003]

i dont think you guys are understanding my question........im sorry if its not clear...im gonna try again

 

 

ok, my script logs visits......and displays ips and what they visited..

 

for example

 

say ip # 123.123.213 came to my site and entered the url.........he would go to my index.php page

 

it would display  123.123.213 /index.php so say he then clicks another page on my site

 

for example he clicks the contact us page...that would display

 

123.123.213 /index.php

123.123.213 /contact_us.php

 

then he goes to back to home and it would be

 

123.123.213 /index.php

123.123.213 /contact_us.php

123.123.213 /index.php

 

so it displays this guys ip and tells me where he visited...and thats how its stored in mysql....what i want to do is count his ip ONLY once........i dont want it to tell me that there are 3 ips from him, i want one....and not include his other ips.

 

i hope it makes sense guys........thank you

[premiso]

i dont think you guys are understanding my question........im sorry if its not clear...im gonna try again

 

 

ok, my script logs visits......and displays ips and what they visited..

 

for example

 

say ip # 123.123.213 came to my site and entered the url.........he would go to my index.php page

 

it would display  123.123.213 /index.php so say he then clicks another page on my site

 

for example he clicks the contact us page...that would display

 

123.123.213 /index.php

123.123.213 /contact_us.php

 

then he goes to back to home and it would be

 

123.123.213 /index.php

123.123.213 /contact_us.php

123.123.213 /index.php

 

so it displays this guys ip and tells me where he visited...and thats how its stored in mysql....what i want to do is count his ip ONLY once........i dont want it to tell me that there are 3 ips from him, i want one....and not include his other ips.

 

i hope it makes sense guys........thank you

 

I am not sure but I know this is possible by using two queries.

 

<?php
$query = "SELECT DISTINCT VisIP FROM logs";

$res = mysql_query($query);

while ($row = mysql_fetch_assoc($res)) {
    $query = "SELECT COUNT(VisIP) as IPcnt FROM logs WHERE VisIP = '" . $row['VisIP'] . "'";
    $res2 = mysql_query($query);
    $row2 = mysql_fetch_assoc($res2);
    $ips[$row['VisIP']] = $row2['IPcnt'];
}

print_r($ips);

?>

 

I am sure there is a way with SQL, but without a test database I won't be able to find it out. That should work.

[jeger003]

premiso--- i put in the code you provided and here is what it displays....

 

Array ( [98.118.263.48] => 40 [71.163.263.153] => 115 [68.163.67.71] => 55 [63.169.163.208] => 179 [69.63.163.34] => 21 [71.163.63.54] => 10 [69.63.179.33] => 20 [69.63.163.35] => 9 [263.80.163.38] => 2 [84.31.163.193] => 4 [68.163.176.93] => 34 [70.79.163.3] => 15 [174.163.131.149] => 1 [68.630.23.242] => 2 [65.213.232.194] => 53 [69.243.4.25] => 2 [72.196.244.182] => 4 [38.163.41.112] => 2 [208.80.194.48] => 2 [208.80.163.33] => 1 [96.241.163.191] => 2 [68.98.138.19] => 49 [65.55.263.161] => 1 [63.55.109.244] => 2)

 

 

its basically counted the number of ips that exist for a specific ip....

 

cant we just get it to count the fields it brings out....that would be like counting individual ips but only once......i tried everything and searched every where and nothing has worked

[premiso]

premiso--- i put in the code you provided and here is what it displays....

 

its basically counted the number of ips that exist for a specific ip....

 

cant we just get it to count the fields it brings out....that would be like counting individual ips but only once......i tried everything and searched every where and nothing has worked

 

Simple modification.

 

<?php
$query = "SELECT DISTINCT VisIP FROM logs";

$res = mysql_query($query);

while ($row = mysql_fetch_assoc($res)) {
    echo $row['VisIP'] . " has entered at least once.<br />";
}

?>

 

The count function is only needed if you actually want to count, this should produce what you want.

[twm]

I used a filed VisPG for the page , so you might want to replace it with actual field name. (in case the IP and page are recorded in the same table)

 

<?PHP

 

$query = "SELECT DISTINCT VisIP, VisPG, COUNT(VisPG) as VisitCount FROM logs GROUP BY VisPG";

 

$result = mysql_query($query) or die(mysql_error());

 

// Print out result

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

      echo "IP: ". $row['VisIP'] ." PG:". $row['VisPG']." Visted x ". $row['VisitCount'];

      echo "<br />";

  }

 

?>

[jeger003]

premiso---i think we are kind of getting there........here is what it displays

 

98.133.240.48 has entered at least once.

71.163.227.133 has entered at least once.

68.133.67.71 has entered at least once.

98.133.163.208 has entered at least once.

69.63.179.33 has entered at least once.

71.133.11.54 has entered at least once.

69.63.133.33 has entered at least once.

 

is there a way to maybe count these....cause i would have to count them myself....for example if only these were the visits, i would count 7...for the ips and say that 7 different ips visited today...is there a way to have it count it by itself? but this is very close

 

 

twm---im not really sure what you mean....which field would i replace VisPG with?

[twm]

well I made a mistake of assuming.

 

If you are storing the page visited in the same table, the VisPG would be that field name (I used it as a placeholder, since I have no idea of your table structure). That way it displays each IP, the page, and how many times the IP hit that page.

 

Wow! That even confused me!

 

Try this:

 

<?PHP

$query = "SELECT DISTINCT VisIP, COUNT(VisIP) as VisitCount FROM logs GROUP BY VisIP";

$result = mysql_query($query) or die(mysql_error());

// Print out result
   while($row = mysql_fetch_array($result)){
      echo "IP: ". $row['VisIP'] ." Visted ". $row['VisitCount']." times<BR />";
   }

?>

 

[premiso]

premiso---i think we are kind of getting there........here is what it displays

 

98.133.240.48 has entered at least once.

71.163.227.133 has entered at least once.

68.133.67.71 has entered at least once.

98.133.163.208 has entered at least once.

69.63.179.33 has entered at least once.

71.133.11.54 has entered at least once.

69.63.133.33 has entered at least once.

 

is there a way to maybe count these....cause i would have to count them myself....for example if only these were the visits, i would count 7...for the ips and say that 7 different ips visited today...is there a way to have it count it by itself? but this is very close

 

 

twm---im not really sure what you mean....which field would i replace VisPG with?

 

That is an easy one.

 

<?php
$query = "SELECT DISTINCT VisIP FROM logs";

$res = mysql_query($query);

$ipsToday = mysql_num_rows($res);

echo "There have been " . $ipsToday . " unique visitors today.";

?>

 

Just remember that IPs are not a good way of tracking "unique" visits due to networks such as schools or LAN's at home.

[Barand]

DISTINCT isn't a function

 

$query = "SELECT COUNT(DISTINCT VisIP) as tot FROM logs";   
$result = mysql_query($query) or die(mysql_error());
echo mysql_result ($result, 0, 'tot');

 

 

[jeger003]

premiso! you NAILED IT!!

 

it works perfectly! displays "There have been 120 unique visitors today."

 

AWESOME! THANK YOU SOO MUCH!

 

what would you recommend for tracking visits? i use the stat my hosting provides but its not very good cause it includes all sorts of bots.

 

----------------------------------------------------------------------------------------------

 

 

twn---thank you soo much for all your help, your code worked but it counted how many times an ip showed up rather than counting individual ips....

 

 

 

 

Again thank you all for the help....greatly appreciate it.

 

 

 

 

 

[jeger003]

DISTINCT isn't a function

 

$query = "SELECT COUNT(DISTINCT VisIP) as tot FROM logs";   
$result = mysql_query($query) or die(mysql_error());
echo mysql_result ($result, 0, 'tot');

 

Barand i just saw your post......and yours works as well...counts exactly like i need it to..

 

 

thank you for your help