· 8 years ago · Dec 13, 2017, 05:46 AM
1<?php
2/* Assumed SQL table structure:
3CREATE TABLE IF NOT EXISTS `player_logins` (
4 `id` int(11) NOT NULL AUTO_INCREMENT,
5 `player_id` int(11) NOT NULL,
6 `date` int(11) NOT NULL,
7 `type` tinyint(4) NOT NULL,
8 PRIMARY KEY (`id`)
9) ENGINE=InnoDB;
10*/
11
12require_once 'engine/init.php';
13include 'layout/overall/header.php';
14
15// One week is 7 days
16$week = 7 * 24 * 60 * 60;
17
18// This is the timestamp of one week ago.
19$weekAgo = time() - $week;
20
21// Lets grab all login data from a week ago to today
22$insomniacs = mysql_select_multi("SELECT `player_id`, `date`, `type` FROM `player_logins` WHERE `date` > '{$weekAgo}' ORDER BY `date` ASC;");
23
24// Lets create a unique list sorted by player ID, and query for their names.
25$player_data = array();
26foreach ($insomniacs as $ins) {
27 // Avoid duplicate player ids.
28 if (!in_array($ins['player_id'], array_keys($player_data))) {
29 $player_data[$ins['player_id']] = false;
30 }
31}
32
33$playernames = mysql_select_multi("SELECT `id`, `name` FROM `players` WHERE `id` IN(". implode(',', array_keys($player_data)) .");");
34// Populate the player_data list with their names.
35foreach ($playernames as $player) {
36 if (isset($player_data[$player['id']]) && $player_data[$player['id']] === false) {
37 $player_data[$player['id']] = $player['name'];
38 }
39}
40
41// Lets save insomniacs data to player_data
42for ($i = 0; $i < count($insomniacs); $i++) {
43 $player_data[$insomniacs[$i]['player_id']]['records'][] = $insomniacs[$i];
44}
45
46// Lets start calculating online session durations of the players
47for ($i = 0; $i < count($player_data); $i++) {
48 //$player_data[$i]
49 $player_data[$i]['total_online'] = 0;
50 for ($x = 0; $x < count($player_data[$i]['records']); $x++) {
51
52 // If this is the first record, and it is an logout event, count the seconds since timestamp of a week ago.
53 if ($x === 0 && $player_data[$i]['records'][$x]['type'] == 0) {
54 $player_data[$i]['total_online'] += $player_data[$i]['records'][$x]['date'] - $weekAgo;
55 }
56
57 // If this is a login event, check if the next index is a logout event, and calculate the time between them
58 if ($player_data[$i]['records'][$x]['type'] == 1) {
59 $login = $player_data[$i]['records'][$x]['date'];
60 // next event should be logout if it exist, else grab today (they are considered still online):
61 $logout = (isset($player_data[$i]['records'][$x+1])) ? $player_data[$i]['records'][$x+1]['date'] : time();
62
63 $player_data[$i]['total_online'] += $logout - $login;
64 }
65
66 }
67}
68
69// Todo: Sort the array by $player_data[array][total_online]
70?>
71
72<h1>Insomniacs</h1>
73<table>
74 <tr>
75 <td>Player name</td>
76 <td>Seconds online past 7 days</td>
77 </tr>
78 <?php foreach($player_data as $player): ?>
79 <tr>
80 <td><?php echo $player['name']; ?></td>
81 <td><?php echo $player['total_online']; ?></td>
82 </tr>
83 <?php endforeach; ?>
84</table>
85
86<?php include 'layout/overall/footer.php';
87// Alternative query for insomniacs which includes the player names
88// $insomniacs = mysql_select_multi("SELECT `pl`.`player_id`, `pl`.`date`, `pl`.`type`, `p`.`name` FROM `player_logins` as `pl` INNER JOIN `players` AS `p` ON `p`.`id` = `pl`.`player_id` WHERE `pl`.`date` > '{$weekAgo}'");
89?>