Building Browsergames: Implementing a flexible stats system (PHP)

Now that we’ve been over how to design a flexible stats system, let’s look at how to implement it in PHP.

To begin with, we will need some starting table structure and data:

CREATE TABLE users (
	id int NOT NULL AUTO_INCREMENT,
	username varchar(250),
	password varchar(50),
	PRIMARY KEY(id)
);
CREATE TABLE stats (
	id int NOT NULL AUTO_INCREMENT,
	display_name text,
	short_name varchar(10),
	PRIMARY KEY(id)
);
INSERT INTO stats(display_name,short_name) VALUES ('Magic','mag');
INSERT INTO stats(display_name,short_name) VALUES ('Attack','atk');
CREATE TABLE user_stats (
	id int NOT NULL AUTO_INCREMENT,
	user_id int,
	stat_id int,
	value text,
	PRIMARY KEY(id)
);

And now that this is done, we can begin actually writing code.

To begin with, we will need a way to pull back the value for a particular stat. The SQL query looks like this:

SELECT value FROM user_stats WHERE stat_id = <foo> AND user_id = <bar>

That’s pretty handy, but not exactly what we want – why would we want to first retrieve the stat’s ID, and then use it to retrieve the value? It’d be much nicer to do it all with one query:

SELECT value FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = <foo> OR short_name = <foo>) AND user_id = <bar>

By using what’s known as a sub-query to retrieve our stat ID, we can shorten our SQL down to just one query – which means that we only need to interact with the database once to retrieve a stat for a player. This makes writing a function to retrieve stats for a player trivial:

1
2
3
4
5
6
7
8
9
10
11
12
13

function getStat($statName,$userID) {
	require_once 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql');
	mysql_select_db($dbname);
	$query = sprintf("SELECT value FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND user_id = '%s'",
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($userID));
	$result = mysql_query($query);
	list($value) = mysql_fetch_row($result);
	return $value;		
}

You’ve seen all this code before – all we’re doing is creating a specific query, and then returning whatever we retrieve. That’s about as complex as stat retrieval gets, whether you’re using a flexible system or otherwise. And that’s really all there is to implementing this stats system – we take in the name(which could be either the display name or the short name), and we retrieve and then return the value inside the table.

But what if you wanted to update the stat’s value? That’s just as easy:

1
2
3
4
5
6
7
8
9
10
11
12

function setStat($statName,$userID,$value) {
	require_once 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql');
	mysql_select_db($dbname);
	$query = sprintf("UPDATE user_stats SET value = '%s' WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND user_id = '%s'",
		mysql_real_escape_string($value),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($userID));
	$result = mysql_query($query);
}

And that’s how you can update the stat! You can then do any transforming or displaying for your stat in your other logic, and just call those two functions when you need to store/retrieve stats(I’d even recommend splitting it off into its own page – something like stats.php or something).

Here’s all the code in one spot:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

function getStat($statName,$userID) {
	require_once 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql');
	mysql_select_db($dbname);
	$query = sprintf("SELECT value FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND user_id = '%s'",
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($userID));
	$result = mysql_query($query);
	list($value) = mysql_fetch_row($result);
	return $value;		
}
function setStat($statName,$userID,$value) {
	require_once 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql');
	mysql_select_db($dbname);
	$query = sprintf("UPDATE user_stats SET value = '%s' WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND user_id = '%s'",
		mysql_real_escape_string($value),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($userID));
	$result = mysql_query($query);
}

Luke is the primary editor of Building Browsergames, and has written a large portion of the articles that you read here. He generally has no idea what to say when asked to write about himself in the third person.

Tuesday, May 6th, 2008 buildingbrowsergames, code, design, php
  • sHyuAn
    "SELECT value FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = <foo> OR short_name = <foo>) AND user_id = <bar>"

    when i put this into sql query, it shows me the error message as below:

    "MySQL said:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<foo> OR short_name = <foo>) AND user_id = <bar>
    LIMIT 0, 30' at line 1"

    what should i do?
  • You need to actually put values into the <foo> and <bar> portions - if you read a little further, the code to do that is later on.
  • sHyuAn
    "SELECT value FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = <foo> OR short_name = <foo>) AND user_id = <bar>"

    i have inserted this into sql query.. however, the error message came out as below:

    "MySQL said:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<foo> OR short_name = <foo>) AND user_id = <bar>
    LIMIT 0, 30' at line 1"

    what should I do now?
  • You're missing the string formatting that puts arguments into your SQL
    statement - add that, and you should be good to go.
  • Name
    Don't use subqueries, where an inner join is more likely (and probably faster).

    Change this:


    SELECT value
    FROM user_stats
    WHERE stat_id = (
    SELECT id
    FROM stats
    WHERE display_name = <foo> OR short_name = <foo>)
    AND user_id = <bar>


    to this:


    SELECT user_stats.value
    FROM user_stats
    INNER JOIN stats
    ON (user_stats.stat_id = stats.id)
    WHERE stats.display_name = <foo> AND stats.short_name = <foobar> AND user_stats.user_id = <bar>
  • Thanks for the tip, Name!
blog comments powered by Disqus

About

Building Browsergames is a blog about browsergames(also known as PBBG's). It's geared towards the beginner to intermediate developer who has an interest in building their own browsergame.
WooThemes - WordPress themes for everyone Dreamhost

Got Something to Say?

Send an e-mail to buildingbrowsergames@gmail.com!

Follow Building Browsergames on Twitter!

http://twitter.com/bbrowsergames