PHP+MySQL game server player database

[jevgienij]

Hello. I just inherited a game stats system but it's not working properly. I'm gonna post the entire script:

<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
$servername = "127.0.0.1";
$username = "";
$password = "";
$dbname = "";
 
$addresses = Array();
$nicknames = Array();
$servers = Array();
$times = Array();
 
$find_ip = $_GET["ip"];
$find_name = $_GET["nick"];
 
if(isset($_GET["format"]))
{
	$format = $_GET["format"];
}
else
{
	$format = "include";
}
 
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error)
{
    ReturnError("NO_CONNECTION");
}
 
function ReturnError($error)
{
	global $format;
	if($format == "mod")
		die("!DATA!ERROR:" .$error. "!DATA!");
	else
		die("ERROR:" . $error);
 
}
 
function ReturnData($data)
{
	global $format;
	if($format == "mod")
		die("!DATA!SUCCESS:" .$data. "!DATA!");
	else
 
		die("SUCCESS:" .$data);
}
 
function GetServerIP()
{
	if (!empty($_SERVER['HTTP_CLIENT_IP']))
		return $_SERVER['HTTP_CLIENT_IP'];
	else if (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))
		return $_SERVER['HTTP_X_FORWARDED_FOR'];
	else
		return $_SERVER['REMOTE_ADDR'];
}
 
function GetTimestamp()
{
	$now = DateTime::createFromFormat('U.u', number_format(microtime(true), 6, '.', ''));
	$now->format("m-d-Y H:i:s.u");
	$local = $now->setTimeZone(new DateTimeZone('Europe/Prague'));
	return $local->getTimestamp();
}
 
function GetFormatedTime()
{
	$now = DateTime::createFromFormat('U.u', number_format(microtime(true), 6, '.', ''));
	$now->format("m-d-Y H:i:s.u");
	$local = $now->setTimeZone(new DateTimeZone('Europe/Prague'));
	return $local->format("Y-m-d H:i:s.u");
}
 
function AddAddress($addr)
{
	global $addresses;
	if(in_array($addr, $addresses, true))
	{
		return false;
	}
	else
	{
		array_push($addresses, $addr);
		return true;
	}
}
 
function AddTime($time)
{
	global $times;
	if(in_array($time, $times, true))
	{
		return false;
	}
	else
	{
		array_push($times, $time);
		return true;
	}
}
 
function AddNickname($nick)
{
	global $nicknames;
	if(in_array($nick, $nicknames, true))
	{
		return false;
	}
	else
	{
		array_push($nicknames, $nick, true);
		return true;
	}
}
 
function AddServer($server)
{
	global $servers;
	if(in_array($server, $servers, true))
	{
		return false;
	}
	else
	{
		array_push($servers, $server, true);
		return true;
	}
}
 
function LookForIPs($ip)
{
	global $conn;
 
	$sql = "SELECT * FROM  `connections` WHERE `IP` = '".$ip."'";
	$result = $conn->query($sql);
 
	if ($result->num_rows > 0)
	{
		while($row = $result->fetch_assoc())
		{
			AddAddress($row["IP"]);
			AddServer($row["serverIP"]);
			AddTime(Array($row["datestamp"], $row["datestring"], $row["serverIP"]));
 
			if(AddNickname($row["nickname"]) && $row["nickname"] != "Player" && $row["nickname"] != "A_Edition_V2")
			{
				LookForNicknames($row["nickname"]);
			}
 
		}
		return true;
	}
	else
	{
		return false;
	}
}
 
function LookForNicknames($nick)
{
	global $conn;
 
	$sql = "SELECT * FROM  `connections` WHERE `nickname` LIKE '".$nick."'";
	$result = $conn->query($sql);
 
	if ($result->num_rows > 0)
	{
		while($row = $result->fetch_assoc())
		{
			AddNickname($row["nickname"]);
			AddServer($row["serverIP"]);
			AddTime(Array($row["datestamp"], $row["datestring"], $row["serverIP"]));
 
			if(AddAddress($row["IP"]))
			{
				LookForIPs($row["IP"]);
			}
		}
		return true;
	}
	else
	{
		return false;
	}
}
 
function SortTimes()
{
	global $times;
	$good = false;
 
	while($good == false)
	{
		$good = true;
		for($i = 0; $i < count($times); $i++)
		{
			if($i+1 == count($times))
				continue;
 
			if($times[$i][0] < $times[$i + 1][0])
			{
				$tmp = $times[$i];
				$times[$i] = $times[$i + 1];
				$times[$i+1] = $tmp;
 
				$good = false;
			}
		}
	}
}
 
function GenerateLastSeenString($fromTime)
{
	$time_now = GetTimestamp();
 
	if($time_now < $fromTime)
	{
		return "N/A";
	}
	if($fromTime == 0 || $fromTime =="")
	{
		return "N/A";
	}
 
	$ss = $time_now - $fromTime;
 
	$seconds = $ss%60;
	$minutes = floor(($ss%3600)/60);
	$hours = floor(($ss%86400)/3600);
	$days = floor(($ss%2592000)/86400);
	$months = floor($ss/2592000);
 
	$str = "";
 
	if($months != 0)
	{
		if($str != "")
			$str = $str . " ";
		if($days == 1)
			$str = $str . $months . " month";
		else
			$str = $str . $months . " months";
	}
 
	if($days != 0)
	{
		if($str != "")
			$str = $str . " ";
		if($days == 1)
			$str = $str . $days . " day";
		else
			$str = $str . $days . " days";
	}
 
	if($hours != 0)
	{
		if($str != "")
			$str = $str . " ";
		if($hours == 1)
			$str = $str . $hours . " hour";
		else
			$str = $str . $hours . " hours";
	}
 
	if($minutes != 0)
	{
		if($str != "")
			$str = $str . " ";
		if($minutes == 1)
			$str = $str . $minutes . " minute";
		else
			$str = $str . $minutes . " minutes";
	}
 
	if($seconds != 0)
	{
		if($str != "")
			$str = $str . " ";
		if($seconds == 1)
			$str = $str . $seconds . " second";
		else
			$str = $str . $seconds . " seconds";
	}
 
	return $str. " ago";
}
 
function ClearResult($arr)
{
	for($i = 0; $i < count($arr); $i++)
	{
		if(gettype($arr[$i]) == "boolean")
		{
			 //echo "Found bool at $i";
			 array_splice($arr, $i, 1);
		}
	}
 
	return $arr;
}
 
function PrintArray($arr)
{
	for($i = 0; $i < count($arr); $i++)
	{
		echo $arr[$i];
		if($i + 1 != count($arr))
		{
			echo ", ";
		}
	}
}
 
if($format == "mod")
{
	LookForNicknames($find_name);
	LookForIPs($find_ip);
 
	$nicknames = ClearResult($nicknames);
	$addresses = ClearResult($addresses);
	$servers   = ClearResult($servers);
	$conn->close();
 
	/*if (!filter_var($find_ip, FILTER_VALIDATE_EMAIL) && $find_ip != "")
	{
	    $find_ip = $addresses[0];
	}*/
 
	if($find_ip == "")
	{
		$find_ip = $addresses[0];
	}
 
	if($find_ip == "")
	{
		$find_ip = "0.0.0.0";
	}
 
 
	if(count($nicknames) == 0 && count($addresses) == 0)
	{
		ReturnError("NOT_FOUND");
	}
 
	echo "!DATA!";
	echo "Nicknames used: "; PrintArray($nicknames); echo "*-*";
	echo "Total IPs used: "; echo count($addresses); echo "*-*";
	echo "Location: "; echo file_get_contents("http://127.0.0.1/db/getlocation.php?ip=$find_ip"); echo "*-*";
	echo "Connected: ";  echo count($times); echo " times*-*";
	SortTimes();
 
	if(GetTimestamp() - $times[0][0] < 60 * 60)
		echo "Last seen: " . GenerateLastSeenString($times[1][0]) . "*-*";
	else
		echo "Last seen: " . GenerateLastSeenString($times[0][0]) . "*-*";
 
	echo "First seen: " . GenerateLastSeenString($times[count($times)-1][0]);
	echo "!DATA!";
}
else if($format == "debug")
{
	LookForNicknames($find_name);
	LookForIPs($find_ip);
 
	$nicknames = ClearResult($nicknames);
	$addresses = ClearResult($addresses);
	$servers   = ClearResult($servers);
	$conn->close();
 
	/*if (!filter_var($find_ip, FILTER_VALIDATE_EMAIL) && $find_ip != "")
	{
	    $find_ip = $addresses[0];
	}*/
 
	if($find_ip == "")
	{
		$find_ip = $addresses[0];
	}
 
	if($find_ip == "")
	{
		$find_ip = "0.0.0.0";
	}
 
	if(count($nicknames) == 0 && count($addresses) == 0)
	{
		ReturnError("NOT_FOUND");
	}
 
	echo "Nicknames used: "; PrintArray($nicknames); echo "<br>";
	echo "IPs used: "; PrintArray($addresses); echo "<br>";
	echo "Location: "; echo file_get_contents("http://127.0.0.1/db/getlocation.php?ip=$find_ip"); echo "<br>";
	echo "Connected: ";  echo count($times); echo " times<br>";
	SortTimes();
	echo "Last seen: " . GenerateLastSeenString($times[0][0]) . "<br>";
	echo "First seen: " . GenerateLastSeenString($times[count($times)-1][0]) . "<br>";
	echo $find_name;
}
?>

The problem is that for some nicknames it returns a proper output which should look like this:

But sometimes it just returns lots and lots of data even if the nickname I ask for is unique enough:

Here's how the database looks:

I don't know what's wrong with the script because I'm a PHP beginner. If someone could take a look at it and tell me what's the issue I'd appreciate.

Edited September 22, 2020 by jevgienij

[gw1500se]

Since you have multiple queries, you don't indicate what 'it' is. Which query is returning the bogus data?

 

As an aside you should not use '*' in a select. List only those columns you actually intend to use.

Edited September 22, 2020 by gw1500se

[requinix]

I'm not sure there even is anything wrong. Not with the code.

You know what this thing does, right? You feed it a nickname and the script looks for IP addresses it used, then for each of those it finds nicknames, then for each of those it finds IP addresses, and so on until it runs out of stuff.

You searched for "Matej_Cizik"? Look in the database table yourself for that nickname. Does any of the data seem to be off? Then try searching according to the IP addresses you just found. Then those nicknames. Does it seem to be spiraling out of control?