I’m trying to add a search function to lgsl ( https://github.com/tltneon/lgsl )
What i have done so far is to create a table inside the database where the lgsl is hosted lgsl_player_playtime that has id, player name, server name, last joined time player_seconds and player_minutes.
Inside lgsl i want to add a search function that once i type a name it will search that name on every server added inside lgsl and if it finds something it should display the server where is he playing at and the total time spent on the server.
I managed to create the table and as far as i can see it retrieves the data correctly, however when i try to search for a name even from someone who currently is on the server that it is added in lgsl it tells me that it has 0 minutes although it had 5 minutes since he joined the server.
this is just a snipet of lgsl_details.php
// Database connection
try {
$db = new PDO("mysql:host=localhost;dbname=legendc1_serverlist;charset=utf8", "legendc1_serverlist", "iD.fyD4;72?)MaH");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// Function to format playtime into a human-readable format
function formatPlaytime($seconds) {
$hours = floor($seconds / 3600);
$minutes = floor(($seconds % 3600) / 60);
$seconds = $seconds % 60;
if ($hours > 0) {
return sprintf("%d hours, %d minutes, %d seconds", $hours, $minutes, $seconds);
} elseif ($minutes > 0) {
return sprintf("%d minutes, %d seconds", $minutes, $seconds);
} else {
return sprintf("%d seconds", $seconds);
}
}
// LOG PLAYER ACTIVITY
if (!empty($server['p']) && is_array($server['p'])) {
$current_time = time();
foreach ($server['p'] as $player) {
$player_name = $player['name'] ?? 'Unknown'; // Player name
$server_name = $server['s']['name'] ?? 'Unknown Server'; // Server name
// Check if the player exists in the database
$query = "SELECT id, playtime_minutes, playtime_seconds, last_join_time
FROM lgsl_player_playtime
WHERE player_name = ? AND server_name = ?";
$stmt = $db->prepare($query);
$stmt->execute([$player_name, $server_name]);
$row = $stmt->fetch();
if ($row) {
// Calculate session time
$last_join_time = strtotime($row['last_join_time']);
$session_time = max($current_time - $last_join_time, 0);
// Convert session time to minutes and seconds
$minutes = floor($session_time / 60);
$seconds = $session_time % 60;
// Update playtime
$new_seconds = $row['playtime_seconds'] + $seconds;
$extra_minutes = floor($new_seconds / 60);
$remaining_seconds = $new_seconds % 60;
$new_minutes = $row['playtime_minutes'] + $minutes + $extra_minutes;
// Update the database
$update_query = "UPDATE lgsl_player_playtime
SET playtime_minutes = ?,
playtime_seconds = ?,
last_join_time = ?
WHERE id = ?";
$update_stmt = $db->prepare($update_query);
$update_stmt->execute([$new_minutes, $remaining_seconds, date('Y-m-d H:i:s', $current_time), $row['id']]);
} else {
// Insert new player record
$insert_query = "INSERT INTO lgsl_player_playtime (player_name, server_name, last_join_time, playtime_minutes, playtime_seconds)
VALUES (?, ?, ?, ?, ?)";
$insert_stmt = $db->prepare($insert_query);
$insert_stmt->execute([$player_name, $server_name, date('Y-m-d H:i:s', $current_time), 0, 0]);
}
}
}
//------------------------------------------------------------------------------------------------------------+
//------------------------------------------------------------------------------------------------------------+
// SEARCH FUNCTIONALITY
if (isset($_GET['player_search'])) {
$player_name = trim($_GET['player_search']);
if (!empty($player_name)) {
$query = "
SELECT server_name,
SUM(playtime_minutes * 60 + playtime_seconds) AS total_seconds
FROM lgsl_player_playtime
WHERE LOWER(player_name) = LOWER(?)
GROUP BY server_name";
$stmt = $db->prepare($query);
$stmt->execute([$player_name]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "<div id='search-results'><h3>Search Results for Player: <span style='color: #4079b2;'>$player_name</span></h3>";
echo "<table class='players_table'>
<thead>
<tr>
<th>Server Name</th>
<th>Total Playtime</th>
</tr>
</thead>
<tbody>";
if ($results) {
foreach ($results as $row) {
// Convert total seconds to human-readable format
$formatted_playtime = formatPlaytime($row['total_seconds'] ?? 0);
echo "<tr>
<td>{$row['server_name']}</td>
<td>{$formatted_playtime}</td>
</tr>";
}
} else {
echo "<tr><td colspan='2' style='text-align: center;'>No results found for player <b>$player_name</b>.</td></tr>";
}
echo "</tbody></table></div>";
} else {
echo "<div style='color: red; text-align: center;'>Please enter a player name to search.</div>";
}
}
$output .= "<div style='text-align:center; font-family:tahoma; font-size:9px; padding: 33px 0 11px 0;'><a href='https://github.com/tltneon/lgsl' style='text-decoration:none'>" . lgsl_version() . "</a></div>";
if ($lgsl_config['preloader']) {
echo $output;
}
?>
