How to Create an Asynchronous Multiplayer Game Part 3: Loading Games from the Database

Asynchronous multiplayer games are awesome because players can enjoy social gaming experiences without having to commit to long sit-down sessions. This convenient style of play is made possible by online servers that deliver updates on games being played directly to a player’s device. This article will explain how games previously saved to a database are loaded by the server and made accessible within the game client’s user interface.

Hero Mages Asynchronous Multiplayer Preview

This article will explain:

How to query a list of game records stored in a MySQL database and send the results set to the game client

How to interpret the query results on the client side and design a meaningful games list user interface the player can use to resume playing

How to recreate a live and synchronous multiplayer experience from an async game

How to replay animations to represent the moves made by the player’s opponent while they were away

Generating the Player’s Games List

Popular async games like Hero Academy utilize a “games list” user interface that allow players to access and resume playing their asynchronous game sessions.

Multiplayer game list in Hero Academy

When building my games list for Hero Mages, I started by creating a new user interface screen class called “HM_GamesList”. I wanted to focus on the data and code components first, so the initial design is limited to a header and a scrollable list component that will be used to populate information retrieved from the server:

A basic game list UI

This interface is populated by a database query that gets a list of the active player’s game records. All of the MySQL query generation takes place on the server side, and the process for communicating with our online server looks like this:

Step 2: Handle Game List Request

The server side code handles the request and sends the client back a response. In Part 2: Saving the Game State to Online Database, I explain how games are saved to a MySQL database using two tables, hm_games and hm_gameresults. The function loadGameList will build a MySQL query that returns the relevant data we need to populate the game list.

123456789101112131415161718192021222324252627282930313233

//CODE EXAMPLE 2: Handle Game List Request on Serverfunction handleRequest(cmd, params, user, fromRoom, protocol){if(protocol == "xml"){//....CODE FOR OTHER EXTENSION COMMANDS OMITTED....elseif(cmd == "Game List"){if(params.hmId != null){//THE FOLLOWING MYSQL STATEMENT GATHERS A LIST OF GAMES PLAYER HAS PLAYED BY JOINING//THE GAME AND GAME RESULTS TABLES CREATED IN PART 2var sql = "SELECT ID_GAME from hm_games JOIN hm_gameresults using (ID_GAME) WHERE ID_MEMBER =1"+params.hmId;//WE CREATE AN ARRAY TO STORE THE GAME LISTvar gameList = [];//WE EXECUTE THE QUERYvar queryRes = dbase.executeQuery(sql);//IF THE QUERY RETURNS RESULTS, POPULATE TO ARRAYif(queryRes != null&& queryRes.size()>0){for(var i = 0; i < queryRes.size(); i++){//GET THE ACTIVE ROWvar dataRow = queryRes.get(i);//CREATE GAME RECORD OBJECTvar gameRecord = {};//STORE THE GAME ID IN THE RECORD
gameRecord.ID_GAME = dataRow.getItem("ID_GAME");//ADD RECORD TO ARRAY
gameList.push(gameRecord);}}//STORE THE GAME LIST IN THE SERVER RESPONSE
response.gameList = getGameList(params.hmId);}}//....CODE FOR OTHER EXTENSION COMMANDS OMITTED....}}

Step 4: Populate Game List

//CODE EXAMPLE 4: Populate Game Listprivatefunction receiveGameList(gameList:Object):void{//The game list is returned from server as arrayvar gameList:Array = dataObj.gameList;//Create a new data provider to store the listvar dp:DataProvider = new DataProvider();//Iterate through the list to add new items to data providerfor(var i:int = 0; i < gameList.length; i++){var gameRecord:Object = gameList[i];//Add a label property to object so it shows up in list cell
gameRecord.label = gameRecord.ID_GAME;//Add item to data provider
dp.addItem(gameRecord);}//Set our UI list's data provider
list.dataProvider = dp;}

And here’s our result:

Basic Game List Populated with Game Id's

Advanced Game List Query

While functional, the basic game list created above lacks key information needed for a solid user experience. Players need to know when the game was created, when the last turn was, whose turn it is, and most importantly who their opponents are for the game to be loaded.

Merging the Games and Games Results Tables

The ideal query needs to return all of the relevant information back to the client using the least resources and bandwidth as possible. This query, designed by Marco Rousonelos, programmer at Reflection Software, and laptop alias of the MySQL help forums uses ranking and derived tables to generate the desired results set:

Limiting the Results

It’s important to note that this query returns ALL game record results for the provided member id. As the game becomes popular and player starts to accrue games, this can result in a very large set of data. To ensure the server, network, and the user’s device are not overloaded, it is best to include a LIMIT statement so that only a targeted portion of the results are returned:

12

#CODE EXAMPLE 8: Limit StatementLimit0,30

Customizing the Query

Queries can be tweaked and customized as needed for inpidual games and controlled by packaging additional parameters in the server request. For example, you could store a “lowerLimit” property and a “limitSpan” property to control the limits of the query.

With a solid query capable of returning the necessary results set, we’re ready to generate a more effective user experience to display the results.

Designing an Asynchronous Multiplayer UI

The player’s game list is the core of the async multiplayer experience. The list is used to navigate, check the status of, and join games in progress. Additionally, the game list is a great leaderboard/logging tool that can be used as a means to review past battles, opponents, and more!

Hero Mages Game List User Interface

Relevant Game Record Information

A good game list starts with a good game record cell. Each game record should contain the following information for the player:

These properties are helpful to the player in choosing the desired game to load. It’s always possible to add more details, and some other possibilities might include:

Unique game record id

Whether or not match is ranked

Name of the map

Game objective

Game record cells should ideally be designed to adapt to the size of the list so they can display appropriately on any size mobile device. Hero Mages makes use of player avatars to display character portraits for a more interesting visual display:

One versus one matches are displayed with larger avatars for a more dramatic effect

Team games use colored bars to separate the players grouped on the same team.

Populating the List

Once the game record cell layout has been designed, instances of the game record class can be added to a list component that the player can use to access their game sessions. The process of adding records to the list is similar to adding items to our basic UI list above, except instead of adding simple cells, we’re adding our own custom designed cells.

Hero Mages leverages AURA multiscreen component UIs. AURA stands for Animations, Utilities, and Resources for ActionScript 3.0. It’s a library of classes and components I wrote to speed of tasks like managing listeners, resources, and designing UI. The list seen in the screenshot below is advanced component that adapts to the screen size and input controls of the user’s device. For example, if you’re playing on a touch-based input mobile device, the list is operated using swipes. The same list running on a desktop is navigated using a standard scroll bar. The list is also optimized for mobile GPU’s and can render cells at 60fps on devices like first generation iPads.

The implementation of the component list is beyond the scope of this article. I will be releasing an article that explains the mechanisms of the component that will include downloadable examples in the near future.

Loading a Game

The primary function of the games list is to allow the player to load saved game sessions by selecting an item in the list. Similar to the process of saving games, loading games will require both client-side and server-side code to make a load game request, retrieve game state from the database, and fire up the game engine to restore the desired game. Once again, we follow our 4 communication steps:

Game Client: Request data from server

Server: Handle request, send client response

Game Client: Receive server response

Game Client: Perform desired task with data

Note: While it would be possible to gather the game state data in the game list query, I recommend using a separate server request for the inpidual game record as outlined below in order to conserve bandwidth.

Step 1: Request Load Game

Each cell in our game list will use the following code to make the request to the server for the desired game record:

12345678910111213141516171819202122232425262728

//CODE EXAMPLE 9: Client Side Load Game Request//In the Game List constructor, add an event listener to our list for when a cell is clickedpublicfunction HM_GameList(){//...CODE OMITTED
list.addEventListener(ListEvent.ITEM_CLICK, gameSelected,false,0,true);}//The game selected function handles our server requestprivatefunction gameSelected(evt:Event):void{//First ensure a valid cell is selectedif(list.selectedIndex != -1){/*
It's possible that older games may not be compatible with newer versions of the engine.
So, It's a good idea to store the required game version in the game record data.
You can write an compatability check function to ensure the version is compatible.
*/if(HM_App.isCompatibleVersion(list.selectedItem.v) == false){
HM_Main.HMMain.showPrompt("HM_MessagePrompt","Version Mismatch","Your game version '"+ HM_App.appVersion +"' is not compatible with this recorded game's version '"+ list.selectedItem.v+"'.");return;}//Once again, create a new params object to store request parametersvar params = newObject();
params.gId = list.selectedItem.ID_GAME;//Show our request prompt to the user
showPrompt("ProcessingRequestPrompt");//And send the message to server
smartFox.sendXtMessage("HMServer","Load Game", params,"xml");}}

Step 2: Handle Load Game Request

On the server side, we add a condition for “Load Game” request. Since Hero Mages games can also be played synchronously using Smart Fox, why not transform an async game to a live online match if the players are both online? The room loop iterates through the list of rooms on servers and checks to see if any room’s game id matches the record the player is attempting to load asynchronously. If a match is found, the server returns the room id so they can connect immediately with live player. For live matches, players load game data directly from the game room host. If a live match is not found, the server loads the game state from the database.

NOTE: In order for this loop to work, it’s necessary to store the game record id as a room variable when creating a live game room. You do not need to do this if you’re only interested in asynchronous gameplay.

The load game function will handle the retrieval of the necessary information for loading the game.

12345678910111213141516171819202122

//CODE EXAMPLE 11: Server Side Load Game Functionfunction loadGame(ID_GAME, response){//Generate a MySQL statement to load the record for the provided game record id
sql = "SELECT cmdLog, timeCreated, timeRecorded, timeLastTurn, status from hm_games WHERE ID_GAME = "+ ID_GAME;
queryRes = dbase.executeQuery(sql);//If the query was unsuccessful, add an error message to the prompt to inform userif(queryRes == null|| queryRes.size()<= 0){
response.error = "Unable to load game";return"";}else{//Hero Mages can be played synchronously and asynchronously//Whenever an unfinished game is loaded as an async game, we change isAsync property to reflectif(queryRes.get(0).getItem("status")!= 2){
dbase.executeCommand("Update hm_games set isAsync = 1 WHERE ID_GAME = "+ ID_GAME);}//Package our response with the cmdLog, which is where we store game state in PART 2var gameRecord = {};
gameRecord.cL =queryRes.get(0).getItem("cmdLog")return gameRecord;}}

Step 3: Receive Load Game Response

Back on the client side, our Smart Fox Server extension response listener needs a new condition to listen for “Load Game” response from the server. Depending on the response type, we’ll either join the existing live game or create a new game with the returned parameters:

//Check to see if server provided a room idif(dataObj.rId == -1){//Room id not provided, so we load the game state directly from response object
loadGame(dataObj);}else{/*
The server provided a room id. This means there is a
live game for this session already created by another player
so all we have to do is join the game
*/
joinRoom(dataObj.rId,"",false);}}//....CODE OMITTED....}

Step 4: Load Game State

The load game function will be different for each game engine, but here are some tasks the function will need to handle:

Convert the returned game state data string back into an object (See Part 2)

Run the list of commands through the engine to effectively “play the game” in the background up to the last command. Your engine code should check the isRunningCommandList flag to ensure any automatic responses to commands (such as counter attack actions) are not fired if they are already included in the command list.

Replaying Animations

Following the above steps will allow you to load any game from your games list and restore the game state so that it matches the last recorded move. Consider, however, that an asynchronous multiplayer opponent will make changes to the game state while the other player is away. Simply loading the current game state will be confusing to the player because they won’t know what commands were carried out by their opponent. For an effective asynchronous multiplayer experience, we need to make some modifications to the command recording process and the game loading code.

Recording the Last Move Witnessed

The last move witnessed must be recorded separately for each player, so we need to add an additional property to the table hm_gameresults first created in Part 2 called “lastCmd”. This property is an integer value designed to store the index of the last command this player witnessed for the game.

When sending new game commands, simply pass the index of the command log along with the command. Then, in our code block for handling updates to the game state (created in Part 2) we’re going to add the following code just below the error response:

//GENERATE LIST OF RECIPIENTS THE SERVER WILL SEND THIS COMMAND TO//....CODE OMITTED....

//params[2] stores game record id//If this game record id is included, we need to write this command to stored game log if(params[2]!= undefined){if(params[1].indexOf("cT=eT")!= -1){//If this is an end turn command//Convert notated command into objectvar cmdObj = stringToObject(params[1]+"","|","=");//Get the id of player whose turn is nextvar nextTurnId = cmdObj.nId;//Write update to game record in database
sql = "UPDATE hm_games set cmdLog = CONCAT(cmdLog, '<c>"+ params[1]+"</c>'), timeRecorded = "+Math.floor(getTimer()/1000)+", timeLastTurn = "+Math.floor(getTimer()/1000)+", whoseTurn = "+nextTurnId+" WHERE ID_GAME = "+ params[2];}else{//Write update to game record in database
sql = "UPDATE hm_games set cmdLog = CONCAT(cmdLog, '<c>"+ params[1]+"</c>'), timeRecorded = "+Math.floor(getTimer()/1000)+" WHERE ID_GAME = "+ params[2];}
success = dbase.executeCommand(sql);if(success == false){//THE DATABASE DID NOT RECORD THE MOVE CORRECTLY//CREATE A NEW RESPONSE TO NOTIFY GAME CLIENT OF THE ERROR}//***NEW CODE BEGIN***//Get list of all users in this room and update the lastCmd property in game results for everyone who witnessed this move livevar lastCmd = cmdObj.lC;//Store the lastCmd to record last witnessed move in live players' gameresult recordsvar allUsers = room.getAllUsers();for(i = 0; i < allUsers.length; i++){var memberId = allUsers[i].getVariable("hmId").getValue();
sql = "UPDATE hm_gameResults set lastCmd ="+lastCmd+" WHERE ID_MEMBER = "+memberId+" and ID_GAME ="+gId;trace("GAME RECORD UPDATE: "+ sql);
dbase.executeCommand(sql);}//***NEW CODE END***}
_server.sendResponse([params[1]],-1,null, recipients,"str");return;}}

Show Animations for Unseen Moves

In our load game handler on the server, we’ll add an additional item to the response to store the player’s lastCmd witnessed as follows:

Here’s what the complete load game response looks like (combining Step 2: Handle Game Load Request with our new code)

1234567891011121314151617181920212223242526272829303132

//CODE EXAMPLE 15: Revised Game Load Handlerfunction loadGame(ID_GAME, response){//Generate a MySQL statement to load the record for the provided game record id
sql = "SELECT cmdLog, timeCreated, timeRecorded, timeLastTurn, status from hm_games WHERE ID_GAME = "+ ID_GAME;
queryRes = dbase.executeQuery(sql);//If the query was unsuccessful, add an error message to the prompt to inform userif(queryRes == null|| queryRes.size()<= 0){
response.error = "Unable to load game";return"";}else{//Hero Mages can be played synchronously and asynchronously//Whenever an unfinished game is loaded as an async game, we change isAsync property to reflectif(queryRes.get(0).getItem("status")!= 2){
dbase.executeCommand("Update hm_games set isAsync = 1 WHERE ID_GAME = "+ ID_GAME);}//Package our response with the cmdLog, which is where we store game state in PART 2var gameRecord = {};
gameRecord.cL =queryRes.get(0).getItem("cmdLog")

With the last command index stored, the trick to playing the appropriate animations is to reactivate the useAnimations flag once the lastCmd index is reached.

Other Considerations

Going beyond the steps I’ve explained, there are many ways to customize the asynchronous experience and add additional features and functionality. For instance, what might happen if a player is given unlimited time to respond to an asynchronous game? A losing player with bad sportsmanship might decide to suspend taking their turn indefinitely, preventing the winning player from claiming victory. One way to solve this problem is to enable a “maxWait” period that allows players to drop opponents if they haven’t taken their turn in so much time. For Hero Mages, I allow players to drop their opponents if they haven’t taken their turn in 3 days.

Other features that might be helpful would be search filters for displaying only active games, finding games against particular opponents, the ability to look up the stats of your opponents, etc.

Coming Next

This article explained the process of building an asynchronous multiplayer game list user interface, loading stored game sessions, and replaying the opponent’s last move animations. The next article will focus on the aspect of the concept of asynchronous multiplayer match-making so that players can start new games or join existing ones without requiring a live online connection.

About Ross Przybylski

Ross Przybylski is a game designer, Flash developer, and technical consultant. He is the founder of D20Studios, LLC and the creator of Hero Mages, a cross-platform, multiplayer strategy game. He is also the Director of Flash Development for Reflection Software, an innovative eLearning services provider company. You can learn more about Ross on his website and follow him on Twitter @RossD20Studios.