While working on the login server for Space Horde I realized that I would need some way to register servers with the game, and I would need to be able to retrieve these on the fly.  This would allow me to dynamically change IPs and ports for my primary servers (login, chat) as needed, and would allow me to let other people host game servers while keeping a central repository for them.

To that end I created two database tables to hold this information:

CREATE TABLE servers
(
id int unsigned auto_increment not null,
typeID int unsigned not null,
name text,
description text,
ip varchar(15) not null,
port int not null,
primary key (id),
foreign key (typeID) references serverTypes(typeID)
);

CREATE TABLE serverTypes
(
typeID int unsigned not null,
type text not null,
primary key (typeID)
);

The first table will hold the server information for server type, ip, and port with an optional server name and description.  The latter two will primarily be used for game servers to allow the hosts to unique identify themselves and provide information about their server.

The second table will hold available server types.  The initial rows here are login, chat, and game, but this design allows me the flexibility to add more later if needed without affecting the first table at all.

Next we want another PHP script to act as our API layer over these tables.  It will take a single parameter named ‘type’ and return a JSON encoded string as a result.  If any servers are found for the given type it will return a list of those under the ‘servers’ key.  Otherwise an error of sometype will be returned.

EDIT:  I updated this script to reflect a lot of debugging from tonight’s session.  My original query was basically wrong, and I fixed up how I passed the data to JSON for encoding to let my server fetch and parse it correctly.

<?php

require_once('scripts/functions.php');
require_once('scripts/credentials.php');
require_once('scripts/PDOWrapper.php');

if(array_key_exists('type', $_POST) && $type = trim(strip_tags($_POST['type'])))
{
	try
	{
		$jsonData = array('Error' => 'No servers found.');
		$pdo = new PDOWrapper('mysql', DB_HOST, DB_NAME, DB_USER, DB_PASS, array(PDO::ATTR_PERSISTENT => true));
		if($pdo->query('SELECT Name, Description, IP, Port
			FROM servers JOIN serverTypes
			ON servers.typeID=serverTypes.typeID
			WHERE serverTypes.type=:type', array(':type' => $type)))
		{
			if($result = $pdo->lastStatement->fetchAll(PDO::FETCH_ASSOC))
				$jsonData = array('Servers' => $result);
			else
				$jsonData = array('Error' => 'No servers of type ' . $type . ' available.');
		}
		echo json_encode($jsonData);
	}
	catch(PDOException $e)
	{
		echo json_encode(array('Error' => 'PDOException: ' . $e->getMessage()));
	}
}
else
{
	echo json_encode(array('Error' => 'Malformed request.'));
}

?>

That’s all there is to that.  Now I can dynamically add servers to my tables and they can be found by anyone accessing this API.  Once I get to game server code I’ll implement the ability for the server to automatically register itself once it goes online, and to remove itself once it shuts down.  Then the player will be able to see a dynamic list of servers to choose from upon login.