so this is the scoop. i have a website in which i want to record ppl logging
onto (ip, timestamp, page surfed on), and it also attaches a sessionid to
their record in the db as well (so not to get duplicates)... now...
its not working 100% the way i want it to... it's causing INSERT errors cuz
of duplicate record and CHANGE errors as well.. here's the data in the db
first of all so you can see what i'm trying to explain here:
Code: Select all
mysql> select * from useronline order by timestamp;
+------------+-----------------+-----------------+----------------------------------+
| timestamp | ip | file | online |
+------------+-----------------+-----------------+----------------------------------+
| 1144853539 | xxx.xx.xx.53 | /index.php | 0 |
| 1144856005 | xxx.xx.xx.53 | /index.php | 0 |
| 1144864184 | xxx.xx.xx.53 | /index.php | 5198c2fd76128e6498b05fe4f58d2b5b |
| 1144864218 | xxx.xx.xx.53 | /index.php | 6b577087bc9505dcbf4cda806eeeccaf |
| 1144864221 | xxx.xx.xx.53 | /contact.php | 6b577087bc9505dcbf4cda806eeeccaf |
+------------+-----------------+-----------------+----------------------------------+
5 rows in set (0.00 sec)and here's the code:
Code: Select all
<?
$server="localhost";
$db_user="xxxxxxxxxx";
$db_pass="xxxxxxxxxx";
$database="xxxxxxxxxx";
$timeoutseconds=300;
$timestamp=time();
$timeout=$timestamp-$timeoutseconds;
$stamp=date("F d, Y \\a\\t g:ia",$timestamp);
session_start();
$_SESSION[name] = $PHPSESSID;
if (!isset($_SESSION[initiated])) {
session_regenerate_id();
$_SESSION[initiated] = true;
}
mysql_connect($server, $db_user, $db_pass);
$query=mysql_db_query($database,"SELECT DISTINCT ip,timestamp,file,online FROM useronline WHERE online!='0' order by timestamp");
if(!($query)) { print "Useronline Select Error > "; }
$res=array();
while($row=mysql_fetch_array($query,MYSQL_ASSOC)) array_push($res,$row);
foreach ($res as $x) {
if ($REMOTE_ADDR==$x[$ip] && $_SESSION[name]==$x[online]) {
$change=mysql_db_query($database,"UPDATE useronline SET timestamp='$timestamp' WHERE online='".$x[online]."'");
if(!($change)) { print "* Useronline Change Failed > <br>"; }
} else {
$insert=@mysql_db_query($database,"INSERT INTO useronline VALUES('$timestamp','$REMOTE_ADDR','$PHP_SELF','$_SESSION[name]')");
if(!($insert)) { print "* Useronline Insert Failed > <br>"; }
}
}
$insert=mysql_db_query($database,"INSERT INTO useronline VALUES('$timestamp','$REMOTE_ADDR','$PHP_SELF','$_SESSION[name]')");
if(!($insert)) { print "** Useronline Insert Failed > "; }
// not deleting records when timeout occurs, rather it saves the records and changes the sessionid to a '0'
// --------------------------------------------------------------------------------------------------------
// $delete=mysql_db_query($database,"DELETE FROM useronline WHERE timestamp<$timeout");
// if(!($delete)) { print "Useronline Delete Failed > "; }
$change=mysql_db_query($database,"UPDATE useronline SET online='0' WHERE timestamp<$timeout");
if(!($change)) { print "** Useronline Change Failed > "; }
mysql_close();
?>and the sql schema if u need it:
Code: Select all
CREATE TABLE useronline (
timestamp int(15) DEFAULT '0' NOT NULL,
ip varchar(40) NOT NULL,
file varchar(100) NOT NULL,
online text NULL,
PRIMARY KEY (timestamp),
KEY ip (ip),
KEY file (file)
);
mysql> describe useronline;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| timestamp | int(15) | | PRI | 0 | |
| ip | varchar(40) | | MUL | | |
| file | varchar(100) | | MUL | | |
| online | text | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)k... and i get these errors:
Code: Select all
when the update error occurs, i get:
ERROR 1062: Duplicate entry '1144864221' for key 1
when the insert error occurs, i get:
ERROR 1062: Duplicate entry '1144864221' for key 1is it cuz i'm working with timestamps and i defined it as the primary key?
).
