7
●130
<?php
$before = array('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'June', 'July', 'Aug', 'Sept', 'Oct', 'Nov', 'Dec');
$after = array('Pazar', 'Pazartesi', 'Salı', 'Çarşamba', 'Perşembe', 'Cuma', 'Cumartesi', 'Ocak', 'Şubat', 'Mart', 'Nisan', 'Mayıs', 'Haziran', 'Temmuz', 'Ağustos', 'Eylül', 'Ekim', 'Kasım', 'Aralık', 'Pzt', 'Sal', 'Çar', 'Per', 'Cum', 'Cmt', 'Paz', 'Oca', 'Şub', 'Mar', 'Nis', 'May', 'Haz', 'Tem', 'Ağu', 'Eyl', 'Eki', 'Kas', 'Ara');
if (isset($_REQUEST['ajax']) || isset($_SERVER['HTTP_X_REQUESTED_WITH']) && $_SERVER['HTTP_X_REQUESTED_WITH'] === 'XMLHttpRequest') {
$host = "localhost";
$dbname = "r10";
$username = "root";
$password = "";
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Bağlantı hatası: " . $e->getMessage());
}
$sql = "
SELECT
CONCAT(YEAR(`created_at`), ', ', MONTHNAME(`created_at`), ' ', FLOOR(((DAY(`created_at`) - 1) / 7) + 1)) `week`,
COUNT(*) AS visitor_count,
SUM(CASE WHEN action_type = 'view' THEN 1 ELSE 0 END) AS view_count,
SUM(CASE WHEN action_type = 'click' THEN 1 ELSE 0 END) AS click_count
FROM
visitor
LEFT JOIN
visitor_log ON visitor.visitor_id = visitor_log.visitor_id
GROUP BY
`week`
ORDER BY month(`created_at`), `week`;
";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$weeklyData = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($weeklyData as $index => $row) {
//$row['start_date'] = get_dateformat($row['start_date'], 'd F');
//$row['end_date'] = get_dateformat($row['end_date'], 'd F');
$row['week'] = str_replace($before, $after, $row['week']);
$weeklyData[$index] = $row;
}
header('Content-Type: application/json');
exit(json_encode($weeklyData, JSON_NUMERIC_CHECK));
}
?>
<!DOCTYPE html>
<html lang="tr">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Haftalık Ziyaretçi Sayısı Grafiği</title>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
<div style="width: 80%; margin: auto;">
<canvas id="visitorChart"></canvas>
</div>
<script>
$.ajax({
url: "",
method: "GET",
dataType: "JSON",
success: function(data) {
var ctx = document.getElementById("visitorChart").getContext("2d");
var visitorChart = new Chart(ctx, {
type: "line",
data: {
labels: data.map(function(item) {
return "(" + item.week + ")";
}),
datasets: [{
label: "Tekil Ziyaretçi",
data: data.map(function(item) {
return item.visitor_count;
}),
borderColor: "rgba(75, 192, 192, 1)",
borderWidth: 2,
fill: false
}, {
label: "Görüntüleme",
data: data.map(function(item) {
return item.view_count;
}),
borderColor: "rgba(255, 99, 132, 1)",
borderWidth: 2,
fill: false
}, {
label: "Tıklama",
data: data.map(function(item) {
return item.click_count;
}),
borderColor: "rgba(54, 162, 235, 1)",
borderWidth: 2,
fill: false
}]
},
options: {
scales: {
y: {
beginAtZero: true
}
},
plugins: {
title: {
display: true,
text: "Haftalık Ziyaretçi Sayısı Grafiği"
}
}
}
});
},
error: function() {
console.error("Veri çekme hatası");
}
});
</script>
</body>
</html>index.php - bunu kendim denemek için yaptımsession_start();
function get_my() {
if (isset($_COOKIE['my'])) {
return $_COOKIE['my'];
} elseif (isset($_SESSION['my'])) {
return $_SESSION['my'];
} else {
$uniq = uniqid();
setcookie('my', $uniq, time() + 3600 * 24 * 365);
$_SESSION['my'] = $uniq;
return $uniq;
}
}
function create_visitor($pdo, $visitor_id) {
$sql = "INSERT INTO visitor (visitor_id, created_at) VALUES (:visitor_id, :created_at)";
$stmt = $pdo->prepare($sql);
$created_at = date('Y-m-d H:i:s');
$stmt->bindParam(':visitor_id', $visitor_id, PDO::PARAM_STR);
$stmt->bindParam(':created_at', $created_at, PDO::PARAM_STR);
$stmt->execute();
}
function log_page_visit($pdo, $visitor_id, $page_url) {
$sql = "INSERT INTO visitor_log (visitor_id, page_url, action_type) VALUES (:visitor_id, :page_url, :action_type)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':visitor_id', $visitor_id, PDO::PARAM_STR);
$stmt->bindParam(':page_url', $page_url, PDO::PARAM_STR);
$action_type = "view"; // Görüntüleme işlemi, tıklama veya başka bir işlem türü olabilir
$stmt->bindParam(':action_type', $action_type, PDO::PARAM_STR);
$stmt->execute();
}
$host = "localhost";
$dbname = "r10";
$username = "root";
$password = "";
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Bağlantı hatası: " . $e->getMessage());
}
$visitor_id = get_my();
// Ziyaretçiyi "visitor" tablosuna ekle (eğer daha önce eklenmediyse)
$sql = "SELECT COUNT(*) FROM visitor WHERE visitor_id = :visitor_id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':visitor_id', $visitor_id, PDO::PARAM_STR);
$stmt->execute();
$count = $stmt->fetchColumn();
if ($count == 0) {
create_visitor($pdo, $visitor_id);
}
$page_url = $_SERVER['REQUEST_URI'];
$parsed_url = parse_url($page_url);
if (isset($parsed_url['path'])) {
$page_url = rtrim($parsed_url['path'], '/');
}
// Aynı sayfa URL'siyle birden fazla kayıt oluşturmayı önlemek için kontrol yap
$sql = "SELECT COUNT(*) FROM visitor_log WHERE visitor_id = :visitor_id AND page_url = :page_url";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':visitor_id', $visitor_id, PDO::PARAM_STR);
$stmt->bindParam(':page_url', $page_url, PDO::PARAM_STR);
$stmt->execute();
$count = $stmt->fetchColumn();
if ($count == 0) {
// Ziyaretçinin sayfa ziyareti kaydedilmediyse, kaydet
log_page_visit($pdo, $visitor_id, $page_url);
}SQL yapısı-- r10.visitor: 16 rows tablosu için veriler indiriliyor
/*!40000 ALTER TABLE `visitor` DISABLE KEYS */;
REPLACE INTO `visitor` (`id`, `visitor_id`, `created_at`) VALUES
(1, 'uniqid1', '2023-09-05 10:00:00'),
(2, 'uniqid2', '2023-09-05 11:30:00'),
(3, 'uniqid3', '2023-09-06 09:45:00'),
(4, 'uniqid4', '2023-09-06 14:15:00'),
(5, 'uniqid5', '2023-09-07 16:20:00'),
(6, 'uniqid6', '2023-09-07 17:45:00'),
(7, 'uniqid7', '2023-09-08 08:30:00'),
(8, 'uniqid8', '2023-09-08 12:00:00'),
(9, 'uniqid9', '2023-09-09 14:10:00'),
(10, 'uniqid10', '2023-09-09 15:30:00'),
(11, 'uniqid11', '2023-09-10 11:20:00'),
(12, 'uniqid12', '2023-09-10 16:45:00'),
(13, 'uniqid13', '2023-09-11 09:15:00'),
(14, 'uniqid14', '2023-09-11 13:40:00'),
(15, 'uniqid15', '2023-09-12 14:50:00'),
(25, '65130d72afc20', '2023-09-26 20:42:55');
/*!40000 ALTER TABLE `visitor` ENABLE KEYS */;
-- tablo yapısı dökülüyor r10.visitor_log
CREATE TABLE IF NOT EXISTS `visitor_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`visitor_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`page_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`action_type` enum('view','click','submit') COLLATE utf8_unicode_ci NOT NULL,
`log_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `visitor_id` (`visitor_id`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- r10.visitor_log: 18 rows tablosu için veriler indiriliyor
/*!40000 ALTER TABLE `visitor_log` DISABLE KEYS */;
REPLACE INTO `visitor_log` (`id`, `visitor_id`, `page_url`, `action_type`, `log_date`) VALUES
(1, 'uniqid1', '/anasayfa', 'view', '2023-09-05 10:05:00'),
(2, 'uniqid2', '/urunler', 'view', '2023-09-05 11:35:00'),
(3, 'uniqid3', '/hakkimizda', 'view', '2023-09-06 09:50:00'),
(4, 'uniqid4', '/urunler', 'click', '2023-09-06 14:20:00'),
(5, 'uniqid5', '/iletisim', 'view', '2023-09-07 16:25:00'),
(6, 'uniqid6', '/urunler', 'view', '2023-09-07 17:50:00'),
(7, 'uniqid7', '/hakkimizda', 'view', '2023-09-08 08:35:00'),
(8, 'uniqid8', '/urunler', 'click', '2023-09-08 12:05:00'),
(9, 'uniqid9', '/iletisim', 'view', '2023-09-09 14:15:00'),
(10, 'uniqid10', '/anasayfa', 'view', '2023-09-09 15:35:00'),
(11, 'uniqid11', '/urunler', 'click', '2023-09-10 11:25:00'),
(12, 'uniqid12', '/hakkimizda', 'view', '2023-09-10 16:50:00'),
(13, 'uniqid13', '/anasayfa', 'view', '2023-09-11 09:20:00'),
(14, 'uniqid14', '/iletisim', 'submit', '2023-09-11 13:45:00'),
(15, 'uniqid15', '/urunler', 'click', '2023-09-12 14:55:00'),
(16, '65130d72afc20', '', 'view', '2023-09-26 20:18:42'),
(18, '65130d72afc20', '/selamsdghsd', 'view', '2023-09-26 20:34:53');
/*!40000 ALTER TABLE `visitor_log` ENABLE KEYS */;