Dropping Items (Perl)

Now that we’ve built a way for users to buy items, we need a way for users to get items – by killing monsters. Today, we’ll be adding drops to our combat system.

To begin with, check out a recent copy of the source code for our tutorial:

svn checkout http://building-browsergames-tutorial.googlecode.com/svn/trunk/perl/pbbg tutorial -r 42
In order to keep track of the items that our monsters drop, we need to add another table to our database:

CREATE TABLE monster_items (
	id int NOT NULL AUTO_INCREMENT,
	monster_id int,
	item_id int,
	rarity int,
	PRIMARY KEY(id)
);

The monster_items table will be what we use to keep track of the items that monsters can drop after users kill them. Most of this table explains itself – but you might be wondering about what the ‘rarity’ column is there for. Essentially, the ‘rarity’ column is there for exactly what it says it’s there for; defining the rarity of a drop! We will use the value in the column to randomly determine which items drop from a monster – allowing us to have item A at 10% rarity, and item B at 90% rarity from the same monster.

With our table created, let’s move on to writing some code. We’ll get the ball rolling with a slight tweak to forest.tmpl:

<p>You found a <strong><!--tmpl_var name='item'--></strong>!</p>

We will be using that extra piece of code in our template to display the item that the user gains after killing the monster.

With our template tweaked, we can move on to writing some actual code inside forest.cgi:

my $rand = int(rand(100));
$sth = $dbh->prepare("SELECT item_id FROM monster_items WHERE monster_id = ? AND rarity >= ? ORDER BY RAND() LIMIT 1");
$sth->execute($monsterID,$rand);
my $itemID;
$sth->bind_columns(\$itemID);
$sth->fetch;
$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);
$sth = $dbh->prepare("SELECT name FROM items WHERE id = ?");
$sth->execute($itemID);
$sth->bind_columns(\$parameters{item});
$sth->fetch;

If you’ve been paying attention, you might have noticed that this code is (for the most part), an exact copy of the code from our items shop from earlier. The only ‘new’ part of this code is the first 5 lines or so; it is there that we retrieve the new loot for the user. To begin with, we generate a random number between 0, and 100. While we don’t really need to pass the value through int(), it makes the number a lot simpler to work with(as it’s now 20, instead of 20.020859). Next, we use a single line of SQL to retrieve a random item for the player that is A) dropped by the monster, and B) has a rarity greater than or equal to the value that we randomly generated earlier. We use the ORDER BY RAND() clause to randomize within our results, and finally add LIMIT 1 to make sure that we only get one back. Once we’ve retrieved the random item to add to the player’s inventory, we’re finished – users can now received random items from the monsters that they kill in combat.

Here’s the updated forest.cgi:

#!/usr/bin/perl -w
 
use strict;
use CGI qw(:cgi);
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;
 
if(%arguments) {
	if($arguments{action} eq 'Attack') {
		# fighting the monster	
		use stats;
		use monsterstats;
		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;
		my %player = (
			name		=>	$username,
			attack		=>	stats::getStat('atk',$userID),
			defence		=>	stats::getStat('def',$userID),
			curhp		=>	stats::getStat('curhp',$userID)
		);
		my $phand = stats::getStat('phand',$userID);
		$player{attack} += weaponstats::getWeaponStat('atk',$phand);
		use armorstats;
		my @armor = qw(atorso ahead alegs aright aleft);
		foreach my $key(@armor) {
			my $id = stats::getStat($key,$userID);
			my $defence = armorstats::getArmorStat('defence',$id);
			$player{defence} += $defence;
		}		
		$sth = $dbh->prepare("SELECT id FROM monsters WHERE name = ?");
		$sth->execute($arguments{monster});
		my $monsterID;
		$sth->bind_columns(\$monsterID);
		$sth->fetch;
		my %monster = (
			name		=>	$arguments{monster},
			attack		=>	monsterstats::getMonsterStat('atk',$monsterID),
			defence		=>	monsterstats::getMonsterStat('def',$monsterID),
			curhp		=>	monsterstats::getMonsterStat('maxhp',$monsterID)
		);
		my @combat;
		my $turns = 0;
		my ($attacker,$defender);
		while($player{curhp} > 0 && $monster{curhp} > 0) {
			my %attack;
			if($turns % 2 != 0) {
				$attacker = \%monster;
				$defender = \%player;
			} else {
				$attacker = \%player;
				$defender = \%monster;
			}
			my $damage = 0;
			if($attacker->{attack} > $defender->{defence}) {
				$damage = $attacker->{attack} - $defender->{defence};	
			}
			my %attack = (
				attacker => $attacker->{name},
				defender => $defender->{name},
				damage => $damage
			);
			$defender->{curhp} -= $damage;
			push @combat, \%attack;
			$turns++;
		}
		stats::setStat('curhp',$userID,$player{curhp});
		if($player{curhp} > 0) {
			# player won
			stats::setStat('gc',$userID,stats::getStat('gc',$userID) + monsterstats::getMonsterStat('gc',$monsterID));
			$parameters{won} = 1;
			$parameters{gold} = monsterstats::getMonsterStat('gc',$monsterID);
			my $rand = int(rand(99))+1;
			$sth = $dbh->prepare("SELECT item_id FROM monster_items WHERE monster_id = ? AND rarity >= ? ORDER BY RAND() LIMIT 1");
			$sth->execute($monsterID,$rand);
			my $itemID;
			$sth->bind_columns(\$itemID);
			$sth->fetch;
			$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);
			$sth = $dbh->prepare("SELECT name FROM items WHERE id = ?");
			$sth->execute($itemID);
			$sth->bind_columns(\$parameters{item});
			$sth->fetch;
		} else {
			# monster won	
			$parameters{lost} = 1;
		}
		$parameters{combat} = \@combat;
	} else {
		# running away - back to the index page!
		print $query->redirect('index.cgi');
	}	
} else {
	$sth = $dbh->prepare("SELECT name FROM monsters ORDER BY RAND() LIMIT 1");
	$sth->execute();
	my $monster;
	$sth->bind_columns(\$monster);
	$sth->fetch;
	$parameters{monster} = $monster;
}
 
 
my $template = HTML::Template->new(
		filename	=>	'forest.tmpl',
		associate	=>	$query,
	);
$template->param(%parameters);
print $query->header(), $template->output;

Extra Credit

• Change the ‘you found an item’ message to also display the total quantity of the item that is in the user’s inventory.