count ip numbers to specific member.

Jawn · 10-24-2006, 04:19 PM

Heya all im in need of some help.
Im currently rebuilding my toplist software.
I have over 1200 members, each one got a tracker (image) which stores every unique(IP) that visits their blog. the toplist ranks blogs on unique visitors.

Now im trying to create a php file which should be executed every 30 minutes to rerank the websites on the toplist.

I want this to be so optimized as possible, someone told me about DESTINCT in sql but not sure what he meant.

Got two tables one for members and one for unique ips which are set to be unique.
Not sure how to build it.

Would appreciate if someone could help me.

Best Regards
Jawn

BillyConnite · 10-24-2006, 06:28 PM

Hey John,

DISTINCT is where SQL will grab only 1 of each value in a table, for example:
Say we had these entries in a table:
1.0.0.0
1.0.0.0
1.2.0.0
1.0.0.0
1.2.6.3

Using DISTINCT would bring up only these results:
1.0.0.0
1.2.0.0
1.2.6.3

Then you can simply use mysql_num_rows to count the number of results given, hence giving you the number of unique IP's.

So, using sql, to get the number of unique IP's could be done like this:

PHP Code:

  // grab unique IPs from the table, where the username is 'username'

$sql="select distinct ip from ip_table where username='username' limit 1";

// make sql do it

$result=mysql_query($sql);

// grab the number of rows given (number of unique ip addresses)

$uniques=mysql_num_rows($result);

Untested, but that should grab the number of unique IP's from a table.

Not sure what topsites you're using, so you would have to alter that obviously for it to work. Not sure how you would rerank the sites though, as I haven't bothered looking into how any of the topsites systems work.

All the best,
Rhett.

Dan · 10-24-2006, 06:45 PM

1.0.0.0 is a unique IP many times.. it should still count as a person (or unique IP). [According to what you wrote, it doesn't.]

Also, the last line in your code should be rows, not rwos.

BillyConnite · 10-24-2006, 07:05 PM

Quote:

Originally Posted by Dan

1.0.0.0 is a unique IP many times.. it should still count as a person (or unique IP). [According to what you wrote, it doesn't.]

Also, the last line in your code should be rows, not rwos.

Oops, meant to add 1.0.0.0 in there lol :P Thanks Dan.

Dan · 10-24-2006, 07:11 PM

Alrighty, that was confusing me.

Jawn · 10-25-2006, 03:12 AM

Hey guys thanks for taking the time, this is what i have done so far but i very doubt this is the best/fastest way.

$

PHP Code:

  count = 0;

$data = mysql_db_query($databaseName, "SELECT site_id, totalunique, uniquehits FROM evots_site WHERE site_id > 0");

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

    $count ++;

    $totalUnique    = $row['totalunique'];

    $Unique     = $row['uniquehits'];

    $siteID        = $row['site_id'];

 
    $data2 = mysql_db_query($databaseName, "SELECT ip FROM evots_tracker WHERE site_id=$siteID ORDER BY site_id");

    $Visits = mysql_num_rows($data2);

    Print $userID . "$count,    TotalUnique $totalUnique     Unique $Unique,     Visits $Visits     SiteID $siteID<br>\n";

    $updatedUnique = $Visits;

    $totalUnique = $totalUnique - $Unique + $Visits;

    mysql_db_query($databaseName, "UPDATE evots_site SET `uniquehits`='$updatedUnique', totalunique='$totalUnique' WHERE site_id = $siteID");

 
}

Jawn · 10-27-2006, 05:15 AM

no one?

10-24-2006, 04:19 PM	#1 (permalink)
Jawn Straight from Sweden Join Date: Oct 2006 Location: Sweden Posts: 206 100.00 NP$ (Donate)	count ip numbers to specific member. Heya all im in need of some help. Im currently rebuilding my toplist software. I have over 1200 members, each one got a tracker (image) which stores every unique(IP) that visits their blog. the toplist ranks blogs on unique visitors. Now im trying to create a php file which should be executed every 30 minutes to rerank the websites on the toplist. I want this to be so optimized as possible, someone told me about DESTINCT in sql but not sure what he meant. Got two tables one for members and one for unique ips which are set to be unique. Not sure how to build it. Would appreciate if someone could help me. Best Regards Jawn

10-24-2006, 06:28 PM	#2 (permalink)
BillyConnite Join Date: Jul 2005 Location: Coffs H, Australia Posts: 3,107 47.00 NP$ (Donate)	Hey John, DISTINCT is where SQL will grab only 1 of each value in a table, for example: Say we had these entries in a table: 1.0.0.0 1.0.0.0 1.2.0.0 1.0.0.0 1.2.6.3 Using DISTINCT would bring up only these results: 1.0.0.0 1.2.0.0 1.2.6.3 Then you can simply use mysql_num_rows to count the number of results given, hence giving you the number of unique IP's. So, using sql, to get the number of unique IP's could be done like this: PHP Code: `// grab unique IPs from the table, where the username is 'username' $sql="select distinct ip from ip_table where username='username' limit 1"; // make sql do it $result=mysql_query($sql); // grab the number of rows given (number of unique ip addresses) $uniques=mysql_num_rows($result);` Untested, but that should grab the number of unique IP's from a table. Not sure what topsites you're using, so you would have to alter that obviously for it to work. Not sure how you would rerank the sites though, as I haven't bothered looking into how any of the topsites systems work. All the best, Rhett. __________________ <?php if(1===1){ $computer="fine."; }else{ $computer="broken."; } echo "Your computer is ".$computer; ?> Last edited by BillyConnite; 10-24-2006 at 07:05 PM.

10-25-2006, 03:12 AM	#6 (permalink)
Jawn Straight from Sweden Join Date: Oct 2006 Location: Sweden Posts: 206 100.00 NP$ (Donate)	Hey guys thanks for taking the time, this is what i have done so far but i very doubt this is the best/fastest way. $ PHP Code: count = 0; $data = mysql_db_query($databaseName, "SELECT site_id, totalunique, uniquehits FROM evots_site WHERE site_id > 0"); while($row = mysql_fetch_array($data)) { $count ++; $totalUnique = $row['totalunique']; $Unique = $row['uniquehits']; $siteID = $row['site_id']; $data2 = mysql_db_query($databaseName, "SELECT ip FROM evots_tracker WHERE site_id=$siteID ORDER BY site_id"); $Visits = mysql_num_rows($data2); Print $userID . "$count, TotalUnique $totalUnique Unique $Unique, Visits $Visits SiteID $siteID<br>\n"; $updatedUnique = $Visits; $totalUnique = $totalUnique - $Unique + $Visits; mysql_db_query($databaseName, "UPDATE evots_site SET `uniquehits`='$updatedUnique', totalunique='$totalUnique' WHERE site_id = $siteID"); }

10-24-2006, 06:45 PM	#3 (permalink)
Dan Buy my domains. Join Date: Feb 2006 Posts: 2,801 56.00 NP$ (Donate)	1.0.0.0 is a unique IP many times.. it should still count as a person (or unique IP). [According to what you wrote, it doesn't.] Also, the last line in your code should be rows, not rwos.

10-24-2006, 07:11 PM	#5 (permalink)
Dan Buy my domains. Join Date: Feb 2006 Posts: 2,801 56.00 NP$ (Donate)	Alrighty, that was confusing me.

10-27-2006, 05:15 AM	#7 (permalink)
Jawn Straight from Sweden Join Date: Oct 2006 Location: Sweden Posts: 206 100.00 NP$ (Donate)	no one?

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)