Building Browsergames: Adding Stats (Perl)

Now that we have the basic skeleton of our game, it’s time to start building the pieces of it that differentiate it from the other browsergames out there. We’re going to start off by modifying our registration code a little bit, so that it will set up a user’s starting stats for them using the code we wrote(but didn’t use) for our stats system earlier.

To begin with, we’re going to adjust our registration page, so that it retrieves the new user’s ID and we can use it to set up their stats. This is achieved using MySQL’s built-in LAST_INSERT_ID() function, like so:

34
35
36
37
38
			$sth = $dbh->prepare("SELECT LAST_INSERT_ID() FROM users");
			$sth->execute();
			my $userID;
			$sth->bind_columns(\$userID);
			$sth->fetch;

Before we can use the user ID we just retrieved in any of our stats code, however, we’ll need to adjust it – although we’ve put a conditional in place to make sure a row is created when we attempt to retrieve a stat that doesn’t have a value, we haven’t put one in to make sure a row is created when we try to set a stat – which is just as important. In order to do that, we’ll just promote the code from the getStat function into it’s own function, and call it from both of them:

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
package stats;
use DBI;
 
sub getStat {
	my ($statName,$userID) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$config{dbName}:$config{dbHost}",$config{dbUser},$config{dbPass},{RaiseError => 1});
	createIfNotExists($statName,$userID);
	my $sth = $dbh->prepare("SELECT value FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND user_id = ?");
	$sth->execute($statName,$statName,$userID);
	my $value;
	$sth->bind_columns(\$value);
	$sth->fetch;
	return $value;
}
sub setStat {
	my ($statName,$userID,$statValue) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$config{dbName}:$config{dbHost}",$config{dbUser},$config{dbPass},{RaiseError => 1});
	createIfNotExists($statName,$userID);
	my $sth = $dbh->prepare("UPDATE user_stats SET value = ? WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND user_id = ?");
	$sth->execute($statValue,$statName,$statName,$userID);
}
 
sub createIfNotExists {
	my ($statName, $userID) = @_;	
	use config;
	my $dbh = DBI->connect("DBI:mysql:$config{dbName}:$config{dbHost}",$config{dbUser},$config{dbPass},{RaiseError => 1});
	my $sth = $dbh->prepare("SELECT count(value) FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND user_id = ?");
	$sth->execute($statName,$statName,$userID);
	my $count;
	$sth->bind_columns(\$count);
	$sth->fetch;
	if($count == 0) {
		# no entry for that stat/user combination - insert one with a value of 0
		$sth = $dbh->prepare("INSERT INTO user_stats(stat_id,user_id,value) VALUES ((SELECT id FROM stats WHERE display_name = ? OR short_name = ?),?,?)");
		$sth->execute($statName,$statName,$userID,0);
	}	
}
 
1;

And this way, anytime we do anything to interact with a user’s stats, we’re guaranteed that the row storing its value will be there – which is a Good Thing™. It also means that we can now add the code to our registration page to set those stats to something:

39
40
41
42
			use stats;
			stats::setStat('atk',$userID,5);
			stats::setStat('def',$userID,5);
			stats::setStat('mag',$userID,5);

And once that change has been made, we’re finished! If you go through your registration page and then check out your database, you should notice some new rows being added to user_stats, with the values for the new user’s stats. Here’s what our registration page looks like now:

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#!/usr/bin/perl -w
 
use strict;
use CGI qw(:cgi);
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);
use DBI;
use config;		# this is our database settings
use HTML::Template;
 
my $query = new CGI;
my %arguments = $query->Vars;
 
my $template = HTML::Template->new(
		filename	=>	'register.tmpl',
		associate	=>	$query,		# for argument memory
	);
my %parameters;
if(%arguments) {
	if($arguments{password} ne $arguments{confirm}) {
		$parameters{error} = 'Those passwords do not match!';
	} else {
		my $dbh = DBI->connect("DBI:mysql:$config{dbName}:$config{dbHost}",$config{dbUser},$config{dbPass},{RaiseError => 1});
		my $sth = $dbh->prepare("SELECT count(id) FROM users WHERE UPPER(username) = UPPER(?)");
		$sth->execute($arguments{username});
		my $count;
		$sth->bind_columns(\$count);
		$sth->fetch;
		if($count >= 1) {
			$parameters{error} = 'That username is taken.';
		} else {
			$sth = $dbh->prepare("INSERT INTO users(username,password) VALUES (?,?)");
			$sth->execute($arguments{username},crypt($arguments{password},$arguments{username}));
			$parameters{message} = 'Congratulations! You registered successfully!';
			$sth = $dbh->prepare("SELECT LAST_INSERT_ID() FROM users");
			$sth->execute();
			my $userID;
			$sth->bind_columns(\$userID);
			$sth->fetch;
			use stats;
			stats::setStat('atk',$userID,5);
			stats::setStat('def',$userID,5);
			stats::setStat('mag',$userID,5);
		}
	}
}
 
$template->param(%parameters);
print $query->header(),$template->output();

Wish there was more?

I'm considering writing an ebook - click here.

.

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.

Wednesday, June 4th, 2008 buildingbrowsergames, code, perl
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.

Sponsors

Got Something to Say?

Send an e-mail to luke@buildingbrowsergames.com, or get in touch through Twitter at http://twitter.com/bbrowsergames