IP tracking for counter script

[Cheap Bastard]

i've got a counter script, using one MySQL table for

row 1: total count of visitors
all rows below: $day, $daycount

if i wanted to implement IP tracking, would the best way to do this be with another table?

[lucifer]

yip,

[Cheap Bastard]

i had a pretty weird idea but it could work with only a minor error margin.

say the table is created at install...
every new day the entire table gets dropped and recreated. That way all IP's are blocked for the rest of that day (once they're added), and the table is cleared every day. The only problem is at midnight it might count a few without IP tracking, if the visitor views the counter right before and after midnight...

would this be a good idea? (resource-wise and speed-wise and just the idea)


If not, is there a way to "clean" the table, making sure it doesn't get too long? or something like that...

if so, would it be best to insert new values at the beginning of the table (not sure but i think this is possible)...

[lucifer]

Originally posted by Cheap Bastard
i had a pretty weird idea but it could work with only a minor error margin.

say the table is created at install...
every new day the entire table gets dropped and recreated.

just empty it

"delete from tablename"


That way all IP's are blocked for the rest of that day (once they're added), and the table is cleared every day. The only problem is at midnight it might count a few without IP tracking, if the visitor views the counter right before and after midnight...

would this be a good idea? (resource-wise and speed-wise and just the idea) why not have the IP 'blocked' for x hours rather than a specific cut-off time



If not, is there a way to "clean" the table, making sure it doesn't get too long? or something like that...

"delete from tablename where date<value" kill old records


if so, would it be best to insert new values at the beginning of the table (not sure but i think this is possible)...

does it matter where they are? I wouldn't bother

[Cheap Bastard]

since deleting is a pretty... 'sensitive' thing (at best), let me make sure i got this right

if i query MySQL using

DELETE FROM table WHERE date < $date-24;

this would like delete all records that are 24 hours old (for example)

[lucifer]

it depends on the date format

I use unix date stamps (number of seconds since Jan 1 1970 - or something) - they are an int.

these are different than dates in mySQL

so date is datestamp for php/perl but mySQL stores it as type INT


$killdate = date() - ( 24*60*60); // now - 1 day

"delete from table where date<$killdate"

there are other ways but that's how I do it

test it first :D

[Cheap Bastard]

using the seconds since epoch

that's simply brilliant...
i was just gonna do it using
date(YmdHis)
(YYYYMMDDHHmmSS)
year month day hour minutes seconds

then... errr... figure out some complex how to make it work (probly see if it was like 1,000,000 smaller or not)

doing it with the epoch really simplifies it...

thanks lucifer!

[Cheap Bastard]

:rolleyes: i wonder how long it'll take me to start thinking like that ;)

[Cheap Bastard]

... what would be the most efficient way to see if it's there?


$result = @mysql_query("SELECT ip FROM track0ip",$dbconn) or die("Couldn't read IP info...");

... okay so i know that's not it... but it's a start


also, how do i acquire the IP?
is that set somewhere in HTTP_something_VARS ?

[zazoo]

why would you need to know your users IP address anyways?

[Cheap Bastard]

so i'd have a count of unique visitors, not of pageviews?

[lucifer]

get the php manual from www.php.net

it's all in there in chapter 7

$REMOTE_ADDR

select timelastvisit from table where ip=$usersip

may need quotes round $userip if it's a string

you can then check if timelastvisit is 'valid' or not

when someone's counted

update table set timelastvisit=$now, ip=$userip where ip=$userip

will add new record if needed


if you ..... or die
users get an error

if(@mysql_query(....)){.....}
else { do my custom error thing }

I find more 'clean'

[Cheap Bastard]

i installed IP blocking into my counter, and i got the counter back to working form, but no IP blocking... (although the IP's are being logged. in fact, i viewed the page/counter 5 times and it's logged 5 times)

here's the important part from the install.php
$creation = "CREATE TABLE track0ip (sec INT,ip TINYTEXT)";
@mysql_query($creation,$dbconn) or die("Couldn't create database table track0ip");

here's the counter script ip blocking part
$unisec = date(U);
$killsec = $unisec - ($unique * 3600);
$result = @mysql_query("DELETE FROM track0ip WHERE sec < $killsec",$dbconn) or die("Couldn't delete useless uniques");
$sql = "SELECT ip FROM track0ip WHERE ip==\"$REMOTE_ADDR\"";
if(@mysql_query("$sql",$dbconn)) {
$sql = "SELECT dvc FROM track0 WHERE day = \"0\"";
$resulttot = @mysql_query("$sql",$dbconn) or die("Couldn't read database info");
$rowtot = mysql_fetch_array($resulttot);
$total = $rowtot['dvc'];
} else {
$sql = "INSERT INTO track0ip (sec,ip) VALUES (\"$unisec\",\"$REMOTE_ADDR\")";
$result = @mysql_query($sql,$dbconn) or die("Couldn't insert values");

[atlas]

Actually, mySQL's date functionality really is quite good. You can specify time intervals -- so you can easily just tell it to get things within the last 2 days, 10 hours, and 34 minutes if you want :)

You can do the same thing with the seconds since epoch, but generally using the built-in functionality will yield a more efficient process.

-mk

[Cheap Bastard]

never mind i got it working.
apparently

$sql = "SELECT ip FROM track0ip WHERE ip==\"$REMOTE_ADDR\"";
if(@mysql_query("$sql",$dbconn))

is always wrong (so it always goes to ELSE), and
$sql = "SELECT ip FROM track0ip WHERE ip = \"$REMOTE_ADDR\"";
if(@mysql_query("$sql",$dbconn)) is always right, so it'll always go to IF.

found some way around it though :)