Building Browsergames: Retrieving Items (Perl)

The other day, we set up the initial database structure for our items system – and today, we’re going to write the code we use to interact with that system and retrieve item data.

For the most part, you’ve seen all of this code before – it’s a slightly modified version of our stats code, along with one extra function. We don’t even have to include any functionality for setting up item information – because our players shouldn’t ever be able to(although if you want them to in your game, you should be able to figure out how to make the change after this). We’ll start off with our one new function, getItem, which will return a hash with the item’s attributes inside:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package items;
use DBI;
 
sub getItem {
	my ($itemID) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	my $sth = $dbh->prepare("SELECT name, type FROM items WHERE id = ?");
	$sth->execute($itemID);
	my %item;
	$sth->bind_columns(\@item{qw(name type)});
	$sth->fetch;
	$item{id} = $itemID;
	return %item;
}
 
1;

This is just some simple database querying – the only thing that’s fancy is that we used our %item hash as an array slice so that we could bind keys directly to columns returned by our query. I find that it’s easier to deal with a hash of attributes than a bunch of variables, which is why it’s being done this way.

Unfortunately, we won’t be building anything that takes advantage of this code today – but we can at least build some testing code. To start off, run this SQL to insert a testing item into the database:

INSERT INTO items(name,type) VALUES ('Wooden Sword','Weapon');

Once that’s finished, create a file named test.cgi with this code inside:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#!/usr/bin/perl -w
use strict;
use CGI qw(:cgi);
use items;
 
my $query = new CGI;
my %item = items::getItem(1);
 
print $query->header();
print qq ~
Name: $item{name}<br />
Type: $item{type}<br />
ID: $item{id}
~;

Once you’ve uploaded test.cgi to your server, visit that page – you should see all the information on the Wooden Sword, which means that our item retrieval code is working properly.

That’s really the only ’special’ part of our items code – the rest is just a customization of our stats code, so that we retrieve stats for items:

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
49
50
51
52
sub getStat {
	my ($statName,$itemID) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	createIfNotExists($statName,$itemID);
	my $sth = $dbh->prepare("SELECT value FROM item_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND item_id = ?");
	$sth->execute($statName,$statName,$itemID);
	my $value;
	$sth->bind_columns(\$value);
	$sth->fetch;
	return $value;
}
sub setStat {
	my ($statName,$itemID,$statValue) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	createIfNotExists($statName,$itemID);
	my $sth = $dbh->prepare("UPDATE item_stats SET value = ? WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND item_id = ?");
	$sth->execute($statValue,$statName,$statName,$itemID);
}
 
sub createIfNotExists {
	my ($statName, $itemID) = @_;	
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	my $sth = $dbh->prepare("SELECT count(value) FROM item_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND item_id = ?");
	$sth->execute($statName,$statName,$itemID);
	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 item_stats(stat_id,item_id,value) VALUES ((SELECT id FROM stats WHERE display_name = ? OR short_name = ?),?,?)");
		$sth->execute($statName,$statName,$itemID,0);
	}	
}

If we want to test our stats code to make sure that it’s working, we’ll first need to add a stat to our wooden sword:

INSERT INTO item_stats(item_id,stat_id,value) VALUES ((SELECT id FROM items WHERE name = 'Wooden Sword'),(SELECT id FROM stats WHERE short_name = 'atk'),2);

Then, we’ll modify our testing page so that it retrieves the ‘atk’ stat for our sword:

8
$item{atk} = items::getStat('atk',1);
15
16
Attack: $item{atk}
~;

And with that, we’ve written the code to retrieve our item information! Once you run your test page, you’ll be able to see all of the information for the Wooden Sword. Here’s all the code inside items.pm in one place:

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
49
50
51
52
53
54
package items;
use DBI;
 
sub getItem {
	my ($itemID) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	my $sth = $dbh->prepare("SELECT name, type FROM items WHERE id = ?");
	$sth->execute($itemID);
	my %item;
	$sth->bind_columns(\@item{qw(name type)});
	$sth->fetch;
	$item{id} = $itemID;
	return %item;
}
 
sub getStat {
	my ($statName,$itemID) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	createIfNotExists($statName,$itemID);
	my $sth = $dbh->prepare("SELECT value FROM item_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND item_id = ?");
	$sth->execute($statName,$statName,$itemID);
	my $value;
	$sth->bind_columns(\$value);
	$sth->fetch;
	return $value;
}
sub setStat {
	my ($statName,$itemID,$statValue) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	createIfNotExists($statName,$itemID);
	my $sth = $dbh->prepare("UPDATE item_stats SET value = ? WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND item_id = ?");
	$sth->execute($statValue,$statName,$statName,$itemID);
}
 
sub createIfNotExists {
	my ($statName, $itemID) = @_;	
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	my $sth = $dbh->prepare("SELECT count(value) FROM item_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND item_id = ?");
	$sth->execute($statName,$statName,$itemID);
	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 item_stats(stat_id,item_id,value) VALUES ((SELECT id FROM stats WHERE display_name = ? OR short_name = ?),?,?)");
		$sth->execute($statName,$statName,$itemID,0);
	}	
}
 
1;

And here’s the tester page code, which lets us verify that our items code is working:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#!/usr/bin/perl -w
use strict;
use CGI qw(:cgi);
use items;
 
my $query = new CGI;
my %item = items::getItem(1);
$item{atk} = items::getStat('atk',1);
 
print $query->header();
print qq ~
Name: $item{name}<br />
Type: $item{type}<br />
ID: $item{id}<br />
Attack: $item{atk}
~;

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.

Friday, July 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