Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Пример реализации баннера для тестового задания
<?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
*/
@KarelWintersky

This comment has been minimized.

Copy link
Owner Author

commented Sep 5, 2017

SELECT dayvisit, ipv4, INET_NTOA(ipv4), hits FROM banner_hits

Просмотр посещений.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.