Finding unique IP addresses

[sotusotusotu]

Hey,

 

I have a column in my database called adverts_user_ip and has lots of records of ip addresses in there. I would like to be able to show unique ip addresses, so in order to do this I am guessing I need to somehow go through them  all and determine if there are any repeats.

 

Does anyone have any ideas how to do this or am I going about this is the wrong way?

 

thanks

[Jessica]

You'll want to use the DISTINCT keyword.

SELECT DISTINCT ip from adverts_user_ip GROUP BY ip

[sotusotusotu]

Thanks for the reply. I am not sure if I explained myself properly. I need to find out the amount in numbers, rather than the ip addresses themselves.

 

For example my adverts table looks like this:

 

adverts_id   

adverts_img   

adverts_company   

adverts_url   

adverts_user_ip   

adverts_day   

adverts_month   

adverts_year

 

In the adverts_user_ip row there a lots of records of addresses, but a there are lots of duplicates. I want to be able to distinguish between clicks and unique clicks?

 

[cooldude832]

select count(distinct field name) from Table

[sotusotusotu]

Hey,

 

Still no luck, not returning any errors just not returning any results either. Here is the code I am using:

 

$query = "SELECT DISTINCT count(adverts_user_ip) FROM adverts";
								$result = mysql_query($query);

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

									echo "UNIQUE IP :{$row['adverts_user_ip']} <br />";
								}

[Jessica]

You said you need to find the number of them, not the IP, then complain that you can't print the IP....

[sotusotusotu]

Yes I do need to to find just the number of unique ip's, not the address itself. Sorry if I sound dumb, have only just started out with PHP.

[MmmVomit]

$sql = "SELECT adverts_user_ip FROM adverts GROUP BY advers_user_ip;";

$sqlresult = mysql_query($sql);

$num_ip_addr = mysql_num_rows($sqlresult);

That should get what you need.  Each row of the query is going to have a unique ip address.  Count the number of rows and you'll have the number of unique ip addresses.

[sotusotusotu]

Thanks mate, I realized I was being dumb by not displaying $result variable and trying to display the row. Stupid me, it has been a long day. I edited my code and got a weird result: "Unique IP :Resource id #2"

 

 

$query = "SELECT DISTINCT count(adverts_user_ip) FROM adverts";
								$result = mysql_query($query);

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

									echo "Unique IP :$result <br>";
								}

 

I also tried your code and it returned nothing.

 

When i put your sql statement into mine it returned returned:

 

"Unique IP :Resource id #2

Unique IP :Resource id #2

Unique IP :Resource id #2"

 

 

[Jessica]

<?php
$query = "SELECT DISTINCT count(adverts_user_ip) AS numIPs FROM adverts";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
echo "Unique IP :$row['numIPs']<br>";
?>

[hvle]

Can you give an example?

[MmmVomit]

When i put your sql statement into mine it returned returned:

 

"Unique IP :Resource id #2

Unique IP :Resource id #2

Unique IP :Resource id #2"

 

You're getting that because you're trying to output the resource returned by mysql_query().  You should be using the array returned by mysql_fetch_assoc() or mysql_fetch_array().

 

Here's some code that should work using my query.

 

$query = "SELECT adverts_user_ip FROM adverts GROUP BY advers_user_ip;";

$result = mysql_query($query);
$num_ip_addr = mysql_num_rows($sqlresult);

while($row = mysql_fetch_assoc($result))
{
  echo "Unique IP : $row[adverts_user_ip] <br>";
}

echo "Total number of IPs: $num_ip_addr<br>";

 

 

[sotusotusotu]

With Jesirose code it at least returns "16"

$query = "SELECT DISTINCT count(adverts_user_ip) AS numIPs FROM adverts";
								$result = mysql_query($query);

								$row = mysql_fetch_array($result);

								//$num_ip_addr = mysql_num_rows($result);

								echo $row['numIPs'];


								mysql_query( $sql, $dbConn );

								mysql_close($dbConn);

 

I still need to find the unique IP's though, not the total

[Jessica]

Well you know how to get out the unique IPs, and you know how to get the total, why don't you try doing BOTH.

[sotusotusotu]

I haven't been able to get my unique ones to return a result yet. This is all my code:

mysql_select_db( "andy", $dbConn) or die("unable to select database");

								$query = "SELECT DISTINCT count(adverts_user_ip) AS numIPs FROM adverts";
								$result = mysql_query($query);

								$row = mysql_fetch_array($result);

								//$num_ip_addr = mysql_num_rows($result);

								echo $row['numIPs'];


								mysql_query( $sql, $dbConn );

								mysql_close($dbConn);

[MmmVomit]

Try this.

 

mysql_select_db( "andy", $dbConn) or die("unable to select database");

$query = "SELECT adverts_user_ip FROM adverts GROUP BY advers_user_ip;";

$result = mysql_query($query);
$num_ip_addr = mysql_num_rows($sqlresult);

while($row = mysql_fetch_assoc($result))
{
  echo "Unique IP : $row[adverts_user_ip] <br>";
}

echo "Total number of IPs: $num_ip_addr<br>";

mysql_close($dbConn);

 

BTW, why do you have so many tabs in your code?