blob: 88c971172b2c4f92d84583dbaef4689687af5231 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
<?php
include_once $_SERVER['DOCUMENT_ROOT'] . '/config.php';
include_once $_SERVER['DOCUMENT_ROOT'] . '/partials.php';
$db = new PDO(DB_URL, DB_USER, DB_PASS);
$channel = $_GET['c'] ?? null;
$user = $_GET['u'] ?? null;
if (isset($channel, $user)) {
$stmt = $db->prepare('SELECT w.name, SUM(cw.usage_count) AS usage_count
FROM channel_words cw
INNER JOIN words w ON w.id = cw.word_id
INNER JOIN channels c ON c.alias_name = ?
INNER JOIN users u ON u.alias_name = ?
WHERE cw.channel_id = c.id AND cw.user_id = u.id
GROUP BY cw.word_id
ORDER BY usage_count DESC
LIMIT 100
');
$stmt->execute([$channel, $user]);
} else if (isset($channel)) {
$stmt = $db->prepare('SELECT w.name, SUM(cw.usage_count) AS usage_count
FROM channel_words cw
INNER JOIN words w ON w.id = cw.word_id
INNER JOIN channels c ON c.alias_name = ?
WHERE cw.channel_id = c.id
GROUP BY cw.word_id
ORDER BY usage_count DESC
LIMIT 100
');
$stmt->execute([$channel]);
} else if (isset($user)) {
$stmt = $db->prepare('SELECT w.name, SUM(cw.usage_count) AS usage_count
FROM channel_words cw
INNER JOIN words w ON w.id = cw.word_id
INNER JOIN users u ON u.alias_name = ?
WHERE cw.user_id = u.id
GROUP BY cw.word_id
ORDER BY usage_count DESC
LIMIT 100
');
$stmt->execute([$user]);
} else {
$stmt = $db->prepare('SELECT w.name, SUM(cw.usage_count) AS usage_count
FROM channel_words cw
INNER JOIN words w ON w.id = cw.word_id
GROUP BY cw.word_id
ORDER BY usage_count DESC
LIMIT 100
');
$stmt->execute();
}
$words = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html>
<head>
<title><?= INSTANCE_NAME ?></title>
<link rel="stylesheet" href="/static/style.css">
<link rel="shortcut icon" href="/favicon.ico" type="image/x-icon">
</head>
<body>
<?php html_header(); ?>
<main class="row gap-8">
<form action="/" method="get">
<div class="box">
<div class="tab">Search</div>
<div class="content">
<input type="text" name="c" placeholder="Enter Twitch channel..." value="<?= $channel ?>">
<input type="text" name="u" placeholder="Enter Twitch user..." value="<?= $user ?>">
<button type="submit">Search</button>
</div>
</div>
</form>
<section class="column gap-8 grow">
<div class="scoreboard-showcase">
<div class="box">
<p class="tab">
<img src="/static/img/icons/word.png" alt=""> Words
</p>
<div class="content scoreboard">
<?php if (empty($words)): ?>
<p><i>No words.</i></p>
<?php endif; ?>
<?php foreach ($words as $i => $word): ?>
<div class="scoreboard-item">
<p class="scoreboard-place"><?= $i + 1 ?>.</p>
<p class="scoreboard-name"><?= $word['name'] ?></p>
<p class="scoreboard-counter"><?= $word['usage_count'] ?></p>
</div>
<?php endforeach; ?>
</div>
</div>
</div>
</section>
</main>
</body>
</html>
|
