database insert statement in PHP
I've simple script that store time/IP/PATH just how many online visitors script
$timeoutseconds= 50;
$timestamp=time();
$tim开发者_如何学JAVAeout=$timestamp-$timeoutseconds;
it will insert the following informations for any visitor time $timestamp
IP $REMOTE_ADDR
and Main Path $PHP_SELF
$sql= "insert into online VALUES ('$timestamp','$REMOTE_ADDR','$PHP_SELF')";
executeupdate($sql);
it will delete only if timestamp<$timeout
$sql= "delete from online WHERE timestamp<$timeout";
executeupdate($sql);
But this means if someone start making Zillions of reload for the page before $timeout
ends will be counted over and over and over again !
so how can i modify it so that it depends on IP $REMOTE_ADDR
and does not insert it if exist in order to repent repeating for same visitor since i'm willing to put it in footer which is in all pages of my website ! ~ thanks
Assuming you're using MySQL, make the ip field the primary key, and use this query:
$sql= "
INSERT INTO
online
VALUES
('$REMOTE_ADDR','$timestamp','$PHP_SELF')
ON DUPLICATE KEY UPDATE
`timestamp` = '$timestamp',
`page` = '$PHP_SELF'
";
(This is assuming the following column names: ip
, timestamp
, page
)
By the way, you should protect against SQL injection, if you aren't doing so already. Don't insert external data verbatim in your database. But use proper escaping mechanisms; preferably parameterized prepared statements. This can be done with PDO for instance.
You should add UNIQUE constraint on $REMOTE_ADDR.
- If you need update some fields for this key you can use INSERT INTO ... ON DUPLICATE KEY UPDATE timestamp=CURRENT_TIMESTAMP.
- If you needn't to update anything run INSERT IGNORE INTO ....
use UPDATE instead of INSERT
$sql= "UPDATE online SET timestampColumnName ='$timestamp' WHERE remoteColumnName ='$REMOTE_ADDR'";
I think more detail would be helpful, but you can influence what gets stored using indexes. If you only want one hit per IP, you can add a unique index on the ip column. To store the most recent hit from the IP you can then add on duplicate key update to the end of the query. For a situation where you want to force an interval, the best option would probably be to do a select first - select max(timestamp) where ip = '$ip' - if there is no result, log the hit, otherwise check the time.
精彩评论