Buying Items (Perl)

We’ve already run the initial database code to get our items system prepared, and now it’s time to write the code that is responsible for interacting with it. Today, we’ll build the items shop.

In order to get started, check out a copy of our code from the Google Code Project:

svn checkout http://building-browsergames-tutorial.googlecode.com/svn/trunk/perl/pbbg tutorial -r 36

With that done, we’re ready to get started building our item system!

For starters, we’ll build an item shop – because we need players to be able to get items into their inventory before they can interact with them. We’ll start off with just about the same template code as we had for our armor and weapons shops, inside of item-shop.tmpl:

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
<html>
<head>
	<title>The Item Shop</title>
</head>
<body>
	<p>Welcome to the Item Shop.</p>
	<p><a href='index.cgi'>Back to main</a></p>
	<h3>Current Inventory:</h3>
	<ul>
		<tmpl_loop name='inventory'>
		<li>
			<!--tmpl_var name='name'--> x <!--tmpl_var name='quantity'-->
			<form action='item-shop.cgi' method='post'>
				<input type='hidden' name='sell' value='<!--tmpl_var name="item_id"-->' />
				<input type='submit' value='Sell' />
			</form>
		</li>
		</tmpl_loop>
	</ul>
	<p>You may purchase any of the items listed below.</p>
	<tmpl_if name='error'>
		<p style='color:red'><!--tmpl_var name='error'--></p>
	</tmpl_if>
	<tmpl_if name='message'>
		<p style='color:green'><!--tmpl_var name='message'--></p>
	</tmpl_if>
	<ul>
		<tmpl_loop name='items'>
			<li>
				<strong><!--tmpl_var name='name'--></strong> - <em><!--tmpl_var name='price'--> gold coins</em>
				<form action='item-shop.cgi' method='post'>
					<input type='hidden' name='item-id' value='<!--tmpl_var name="id"-->' />
					<input type='submit' value='Buy' />
				</form>
			</li>
		</tmpl_loop>
	</ul>
</body>
</html>

This is essentially the same template as our other shops – the only difference is the ‘Current Inventory’ display. The current inventory is pretty self-explanatory; it will display the user’s current inventory whenever they visit the shop. Let’s start writing the code for our shop, inside item-shop.cgi:

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
#!/usr/bin/perl -w
 
use strict;
use CGI qw(:cgi);
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);
use HTML::Template;
use DBI;
use config;
 
my $query = new CGI;
my %arguments = $query->Vars;
 
my $dbh = DBI->connect("DBI:mysql:$config{dbName}:$config{dbHost}",$config{dbUser},$config{dbPass},{RaiseError => 1});
my $sth;
my %parameters;
 
use stats;
 
my $cookie = $query->cookie('username+password');
my ($username) = split(/\+/,$cookie);
$sth = $dbh->prepare("SELECT id FROM users WHERE UPPER(username) = UPPER(?)");
$sth->execute($username);
my $userID;
$sth->bind_columns(\$userID);
$sth->fetch;
 
$sth = $dbh->prepare("SELECT DISTINCT(id), name, price FROM items WHERE type = 'Usable' LIMIT 5");
$sth->execute();
my @items = ();
while(my $row = $sth->fetchrow_hashref) {
	push @items, $row;
}
 
$sth = $dbh->prepare("SELECT item_id, quantity FROM user_items WHERE user_id = ?");
$sth->execute($userID);
my @inventory = ();
while(my $row = $sth->fetchrow_hashref) {
	my $sth2 = $dbh->prepare("SELECT name FROM items WHERE id = ?");
	$sth2->execute($row->{item_id});
	$sth2->bind_columns(\$row->{name});
	$sth2->fetch;
	push @inventory, $row;
}
 
$parameters{inventory} = \@inventory;
$parameters{items} = \@items;
 
my $template = HTML::Template->new(
		filename	=>	'item-shop.tmpl',
		associate	=>	$query,
	);
$template->param(%parameters);
print $query->header(), $template->output;

For the most part, this is all code we’ve written before. The only ‘new’ code here is the inventory retrieval code, on lines 36-43. We retrieve all of the rows in user_items that are linked to the current user, and then store them into our @inventory array so that we can display them. We also retrieve and store the name of the item, so that it will display with something a little easier for the player to understand than ‘18′(which is currently a ‘Red Potion’ in the sample game).

Now that we can display items, we should write some code so users can buy them:

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
55
56
if(%arguments) {
	if($arguments{'item-id'}) {
		my $itemID = $arguments{'item-id'};
		$sth = $dbh->prepare("SELECT price FROM items WHERE id = ?");
		$sth->execute($itemID);
		my $price;
		$sth->bind_columns(\$price);
		$sth->fetch;
		my $gold = stats::getStat('gc',$userID);
		if($gold >= $price) {
			stats::setStat('gc',$userID,($gold - $price));
			$sth = $dbh->prepare("SELECT count(id) FROM user_items WHERE user_id = ? AND item_id = ?");
			$sth->execute($userID,$itemID);
			my $count;
			$sth->bind_columns(\$count);
			$sth->fetch;
			if($count > 0) {
				# already has one of the item
				$sth = $dbh->prepare("UPDATE user_items SET quantity = quantity + 1 WHERE user_id = ? AND item_id = ?");
			} else {
				# has none of the item - new row
				$sth = $dbh->prepare("INSERT INTO user_items(quantity,user_id,item_id) VALUES (1,?,?)");
			}
			$sth->execute($userID,$itemID);
			$parameters{message} = 'You purchased the item.';
		} else {
			$parameters{error} = 'You cannot afford that item!';
		}		
	}
}

If you’re comparing this to the code from our weapons or armor shops from earlier, you might notice some differences – like the fact that we’re running two queries to add items to the player’s inventory, instead of just one. This is because our user inventory has been built in such a way that items can ’stack’ – meaning you can have more than one of any particular item. Because we only need to keep track of the fact that a player has <x> of an item, we just use a column inside user_items to keep track of the quantity of the item that the player currently has in their inventory. Because we’re storing our data in this way, whenever we want to add an item to a player’s inventory, we need to first check and see if a row exists – if it does, we increment the quantity of the item. If it doesn’t, we’ll insert a new row.

Now that that’s finished, there’s only one more thing our item shop is missing – the ability to sell items! We’ll quickly add our item selling code to item-shop.cgi:

55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
} elsif($arguments{sell}) {
	my $itemID = $arguments{sell};
	$sth = $dbh->prepare("SELECT price FROM items WHERE id = ?");
	$sth->execute($itemID);
	my $price;
	$sth->bind_columns(\$price);
	$sth->fetch;
	my $gold = stats::getStat('gc',$userID);
	stats::setStat('gc',$userID,($gold + $price));
	$sth = $dbh->prepare("SELECT quantity FROM user_items WHERE user_id = ? AND item_id = ?");
	$sth->execute($userID,$itemID);
	my $quantity;
	$sth->bind_columns(\$quantity);
	$sth->fetch;
	if($quantity > 1) {
		$sth = $dbh->prepare("UPDATE user_items SET quantity = quantity - 1 WHERE user_id = ? AND item_id = ?");
	} else {
		$sth = $dbh->prepare("DELETE FROM user_items WHERE user_id = ? AND item_id = ?");
	}
	$sth->execute($userID,$itemID);
	$parameters{message} = 'You sold the item.';
}

This code is pretty similar to the code from our other shops, except for the different method used to update the information in the database. In the ‘buy item’ code(2 blocks above), we used ‘SELECT COUNT’ to check to see if a row existed – in this case, because we need to see how many of the item the player has, we just use a ‘SELECT’ statement to retrieve the quantity of the item that the player has. Based on the value we retrieve, we perform one of two queries – an UPDATE query if they have more than 1, and a DELETE query if they have only 1 of the item. Performing the DELETE query removes the row from the user_items table, which helps us make sure that we are only storing data we need.

With that code written, we’re done! We have a working item shop now, and our inventory system is beginning to take shape. Don’t forget to link to it from your main page:

18
<p><a href='item-shop.cgi'>The Item Shop</a></p>

And that’s that! Here’s the code from item-shop.tmpl:

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
<html>
<head>
	<title>The Item Shop</title>
</head>
<body>
	<p>Welcome to the Item Shop.</p>
	<p><a href='index.cgi'>Back to main</a></p>
	<h3>Current Inventory:</h3>
	<ul>
		<tmpl_loop name='inventory'>
		<li>
			<!--tmpl_var name='name'--> x <!--tmpl_var name='quantity'-->
			<form action='item-shop.cgi' method='post'>
				<input type='hidden' name='sell' value='<!--tmpl_var name="item_id"-->' />
				<input type='submit' value='Sell' />
			</form>
		</li>
		</tmpl_loop>
	</ul>
	<p>You may purchase any of the items listed below.</p>
	<tmpl_if name='error'>
		<p style='color:red'><!--tmpl_var name='error'--></p>
	</tmpl_if>
	<tmpl_if name='message'>
		<p style='color:green'><!--tmpl_var name='message'--></p>
	</tmpl_if>
	<ul>
		<tmpl_loop name='items'>
			<li>
				<strong><!--tmpl_var name='name'--></strong> - <em><!--tmpl_var name='price'--> gold coins</em>
				<form action='item-shop.cgi' method='post'>
					<input type='hidden' name='item-id' value='<!--tmpl_var name="id"-->' />
					<input type='submit' value='Buy' />
				</form>
			</li>
		</tmpl_loop>
	</ul>
</body>
</html>

And here’s the (slightly tweaked) code from item-shop.cgi:

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
#!/usr/bin/perl -w
 
use strict;
use CGI qw(:cgi);
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);
use HTML::Template;
use DBI;
use config;
 
my $query = new CGI;
my %arguments = $query->Vars;
 
my $dbh = DBI->connect("DBI:mysql:$config{dbName}:$config{dbHost}",$config{dbUser},$config{dbPass},{RaiseError => 1});
my $sth;
my %parameters;
 
use stats;
 
my $cookie = $query->cookie('username+password');
my ($username) = split(/\+/,$cookie);
$sth = $dbh->prepare("SELECT id FROM users WHERE UPPER(username) = UPPER(?)");
$sth->execute($username);
my $userID;
$sth->bind_columns(\$userID);
$sth->fetch;
 
if(%arguments) {
	my $price;
	my $itemID;
	$itemID = $arguments{'item-id'} ? $arguments{'item-id'} : $arguments{sell};
	$sth = $dbh->prepare("SELECT price FROM items WHERE id = ?");
	$sth->execute($itemID);
	$sth->bind_columns(\$price);
	$sth->fetch;
	my $gold = stats::getStat('gc',$userID);
	if($arguments{'item-id'}) {
		if($gold >= $price) {
			stats::setStat('gc',$userID,($gold - $price));
			$sth = $dbh->prepare("SELECT count(id) FROM user_items WHERE user_id = ? AND item_id = ?");
			$sth->execute($userID,$itemID);
			my $count;
			$sth->bind_columns(\$count);
			$sth->fetch;
			if($count > 0) {
				# already has one of the item
				$sth = $dbh->prepare("UPDATE user_items SET quantity = quantity + 1 WHERE user_id = ? AND item_id = ?");
			} else {
				# has none of the item - new row
				$sth = $dbh->prepare("INSERT INTO user_items(quantity,user_id,item_id) VALUES (1,?,?)");
			}
			$sth->execute($userID,$itemID);
			$parameters{message} = 'You purchased the item.';
		} else {
			$parameters{error} = 'You cannot afford that item!';
		}		
	} elsif($arguments{sell}) {
		stats::setStat('gc',$userID,($gold + $price));
		$sth = $dbh->prepare("SELECT quantity FROM user_items WHERE user_id = ? AND item_id = ?");
		$sth->execute($userID,$itemID);
		my $quantity;
		$sth->bind_columns(\$quantity);
		$sth->fetch;
		if($quantity > 1) {
			$sth = $dbh->prepare("UPDATE user_items SET quantity = quantity - 1 WHERE user_id = ? AND item_id = ?");
		} else {
			$sth = $dbh->prepare("DELETE FROM user_items WHERE user_id = ? AND item_id = ?");
		}
		$sth->execute($userID,$itemID);
		$parameters{message} = 'You sold the item.';
	}
}
 
$sth = $dbh->prepare("SELECT DISTINCT(id), name, price FROM items WHERE type = 'Usable' LIMIT 5");
$sth->execute();
my @items = ();
while(my $row = $sth->fetchrow_hashref) {
	push @items, $row;
}
 
$sth = $dbh->prepare("SELECT item_id, quantity FROM user_items WHERE user_id = ?");
$sth->execute($userID);
my @inventory = ();
while(my $row = $sth->fetchrow_hashref) {
	my $sth2 = $dbh->prepare("SELECT name FROM items WHERE id = ?");
	$sth2->execute($row->{item_id});
	$sth2->bind_columns(\$row->{name});
	$sth2->fetch;
	push @inventory, $row;
}
 
$parameters{inventory} = \@inventory;
$parameters{items} = \@items;
 
my $template = HTML::Template->new(
		filename	=>	'item-shop.tmpl',
		associate	=>	$query,
	);
$template->param(%parameters);
print $query->header(), $template->output;

Extra Credit

  • Can you modify the item shop so that it sells all types of items?

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.

Tags: , , ,

Tuesday, October 14th, 2008 buildingbrowsergames, medieval
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