|
<?php |
|
// здесь id_banner всегда 1 |
|
|
|
function getRealIP() |
|
{ |
|
if (!empty($_SERVER['HTTP_CLIENT_IP'])) { |
|
$ip = $_SERVER['HTTP_CLIENT_IP']; |
|
} elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) { |
|
$ip = $_SERVER['HTTP_X_FORWARDED_FOR']; |
|
} else { |
|
$ip = $_SERVER['REMOTE_ADDR']; |
|
} |
|
return $ip; |
|
} |
|
// localhost settings |
|
$settings = array( |
|
'hostname' => 'localhost', |
|
'database' => 'kwdb', |
|
'username' => 'kwdb', |
|
'password' => 'password', |
|
'port' => 3306, |
|
); |
|
|
|
/* |
|
CREATE TABLE `banner_hits` ( |
|
`id` int(11) NOT NULL AUTO_INCREMENT, |
|
`id_banner` int(11) DEFAULT NULL, |
|
`dayvisit` date DEFAULT NULL, |
|
`ipv4` int(10) unsigned DEFAULT NULL, |
|
`hits` int(11) DEFAULT NULL, |
|
PRIMARY KEY (`id`), |
|
UNIQUE KEY `date+ipv4` (`dayvisit`,`ipv4`), |
|
KEY `ipv4` (`ipv4`), |
|
KEY `id_banner` (`id_banner`) |
|
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 |
|
*/ |
|
|
|
|
|
$stdout = ''; |
|
|
|
$dsl = "mysql:host={$settings['hostname']};port={$settings['port']};dbname={$settings['database']}"; |
|
|
|
// подключаемся к БД |
|
try { |
|
$dbh = new \PDO($dsl, $settings['username'], $settings['password']); |
|
$dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); |
|
$dbh->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC); |
|
} catch (\PDOException $e) { |
|
die ($e); |
|
} |
|
|
|
// вставка баннера |
|
$query = "INSERT INTO banner_hits (id_banner, dayvisit, ipv4, hits) VALUES(1, CURDATE(), INET_ATON(:ipv4), 1) |
|
ON DUPLICATE KEY UPDATE hits = hits+1"; |
|
|
|
$sth = $dbh->prepare($query); |
|
try { |
|
$sth->execute( array('ipv4' => getRealIP()) ); |
|
} catch (\PDOException $e) { |
|
echo $e->getMessage(); |
|
} |
|
$dbh = null; |
|
|
|
// можно использовать не PDO, а более быстрый нативный процедурный mysqli, но нужно использовать фильтр для айдишника баннера (intval) |
|
|
|
// рисуем прозрачную точку: |
|
// https://stackoverflow.com/a/30664750/5127037 |
|
|
|
header('Content-Type: image/gif'); |
|
die("\x47\x49\x46\x38\x39\x61\x01\x00\x01\x00\x90\x00\x00\xff\x00\x00\x00\x00\x00\x21\xf9\x04\x05\x10\x00\x00\x00\x2c\x00\x00\x00\x00\x01\x00\x01\x00\x00\x02\x02\x04\x01\x00\x3b"); |
|
|
|
/* ========================================================= */ |
|
/* |
|
Количество хитов на определенную дату: |
|
|
|
SELECT SUM(hits) FROM banner_hits WHERE dayvisit = ... AND id_banner = ... |
|
|
|
Количество уникальных хитов на определенную дату: |
|
|
|
SELECT COUNT(ipv4) FROM banner_hits WHERE dayvisit= ... AND id_banner = ... |
|
|
|
|
|
*/ |
|
/* |
|
Использованные материалы: |
|
https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html |
|
https://stackoverflow.com/questions/3203354/php-script-to-render-a-single-transparent-pixel-png-or-gif |
|
https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists |
|
http://jeka.by/ask/124/mysql-ip-address/#answer-75 |
|
*/ |
SELECT dayvisit, ipv4, INET_NTOA(ipv4), hits FROM banner_hits
Просмотр посещений.