Buying Items (PHP)

Today, we’re going to build the items shop for our game. We’ve already run the initial database code to get everything prepared, and now it’s just a matter of writing the code to interact with it.

To begin with, check out a copy of our code from the Google Code Project:

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

And now we’re ready to get started on building our items system!

The first thing that we will need to build is our items shop. We’ll start off with the same template as our armor and weapons shops, but put this one inside item-shop.tpl:

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

As you can see, this is largely the same template as with our other shops – with the one difference being the ‘Current Inventory’ display(as opposed to current weapons/armor in the other shops). When the user visits this shop, we will retrieve and display the contents of their inventory as well. With that being said, let’s start writing the code for the item shop inside item-shop.php:

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
<?php
 
require_once 'smarty.php';
 
session_start();
 
require_once 'config.php';		// our database settings
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
	or die('Error connecting to mysql');
mysql_select_db($dbname);
// retrieve player's ID
$query = sprintf("SELECT id FROM users WHERE UPPER(username) = UPPER('%s')",
			mysql_real_escape_string($_SESSION['username']));
$result = mysql_query($query);
list($userID) = mysql_fetch_row($result);
require_once 'stats.php';	// player stats
$query = "SELECT DISTINCT(id), name, price FROM items WHERE type = 'Usable' ORDER BY RAND() LIMIT 5;";
$result = mysql_query($query);
$items = array();
while($row = mysql_fetch_assoc($result)) {
	array_push($items,$row);
}
$inventory = array();
$query = sprintf("SELECT id, item_id, quantity FROM user_items WHERE user_id = '%s'",
		mysql_real_escape_string($userID));
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
	 $item_query = sprintf("SELECT name FROM items WHERE id = '%s'",
		mysql_real_escape_string($row['item_id']));
	$item_result = mysql_query($item_query);
	list($row['name']) = mysql_fetch_row($item_result);
	array_push($inventory,$row);
}
$smarty->assign('inventory',$inventory);
$smarty->assign('items',$items);
$smarty->display('item-shop.tpl');
 
?>

For the most part, this is all review – the only interesting and new code that we’ve written is the code appearing between lines 23 and 33 – our inventory retrieval code. We retrieve all rows from our user_items table that are linked to the User ID of the player who is currently logged in, and then just loop through them and put them into our $inventory array – but not before also retrieving the name of the item(so that we can display it a little nicer).

Now that we’ve got displaying items down, we’ll want to write some code so that users can actually buy them:

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
if($_POST) {
	if($_POST['item-id']) {
		$itemID = $_POST['item-id'];
		$query = sprintf("SELECT price FROM items WHERE id = '%s'",mysql_real_escape_string($itemID));
		$result = mysql_query($query);
		list($cost) = mysql_fetch_row($result);
		$gold = getStat('gc',$userID);
		if($gold >= $cost) {
			setStat('gc',$userID,($gold - $cost));
			$query = sprintf("SELECT count(id) FROM user_items WHERE user_id = '%s' AND item_id = '%s'",
				mysql_real_escape_string($userID),mysql_real_escape_string($itemID));
			$result = mysql_query($query);
			list($count) = mysql_fetch_row($result);
			if ($count > 0) {
				# already has one of the item
				$query = sprintf("UPDATE user_items SET quantity = quantity + 1 WHERE user_id = '%s' AND item_id = '%s'",
					mysql_real_escape_string($userID),
					mysql_real_escape_string($itemID));
			} else {
				# has none - new row
				$query = sprintf("INSERT INTO user_items(quantity,user_id,item_id) VALUES (1,'%s','%s')",
					mysql_real_escape_string($userID),
					mysql_real_escape_string($itemID));
			}
			mysql_query($query);
			$smarty->assign('message','You purchased the item.');
		} else {
			$smarty->assign('error','You cannot afford that item!');
		}
	}
}

While that’s definitely a lot more code than we wrote to purchase weapons or armor, it’s still pretty simple once you take a look at it – essentially, once we get past the generic ‘do you have enough gold for this?’ check, we add the item to the user’s inventory. Because usable items can stack, we need to check if there is already a row in place for them or not. If there is no row in place, we insert a new one with the proper values(user ID, itemID, and quantity of 1). Otherwise, we just run an UPDATE statement on our table, to increment the quantity of the item for the player by 1.

Now that that’s finished, we only have one more thing to add to our item shop – the ability to sell items! We’ll quickly add that code to item-shop.php:

46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
} else if($_POST['sell-id']) {
	$itemID = $_POST['sell-id'];
	$query = sprintf("SELECT price FROM items WHERE id = '%s'",mysql_real_escape_string($itemID));
	$result = mysql_query($query);
	list($cost) = mysql_fetch_row($result);
	$gold = getStat('gc',$userID);
	setStat('gc',$userID,($gold + $cost));		
	$query = sprintf("SELECT quantity FROM user_items WHERE user_id = '%s' AND item_id = '%s'",
		mysql_real_escape_string($userID),mysql_real_escape_string($itemID));
	$result = mysql_query($query);
	list($quantity) = mysql_fetch_row($result);
	if ($quantity > 1) {
		$query = sprintf("UPDATE user_items SET quantity = quantity - 1 WHERE user_id = '%s' AND item_id = '%s'",
			mysql_real_escape_string($userID),
			mysql_real_escape_string($itemID));
	} else {
		$query = sprintf("DELETE FROM user_items WHERE user_id = '%s' AND item_id = '%s'",
			mysql_real_escape_string($userID),
			mysql_real_escape_string($itemID));
	}
	mysql_query($query);		
	$smarty->assign('message','You sold the item.');
}

This code is almost the exact same as the ‘buy item’ code, except for some small differences:

  • First, we are selecting quantity instead of COUNT(id) from user_items. Selecting the COUNT will tell us if a row exists or not – but it won’t tell us how many of the item the player has. user_items.quantity will.
  • Based on how many of the item the user has left, we perform one of two different queries: either an UPDATE query to decrement the quantiy of the item by 1, or a DELETE query – to remove the row from the user_items table if it will no longer be needed.

With all that code written, we’re finished! We now have a working item shop, and the beginnings of our inventory system up and running. Don’t forget to add a link to the item shop from the main page:

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

And that’s it! Here’s the code for item-shop.tpl:

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

And item-shop.php:

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
<?php
 
require_once 'smarty.php';
 
session_start();
 
require_once 'config.php';		// our database settings
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
	or die('Error connecting to mysql');
mysql_select_db($dbname);
// retrieve player's ID
$query = sprintf("SELECT id FROM users WHERE UPPER(username) = UPPER('%s')",
			mysql_real_escape_string($_SESSION['username']));
$result = mysql_query($query);
list($userID) = mysql_fetch_row($result);
require_once 'stats.php';	// player stats
if($_POST) {
	if($_POST['item-id']) {
		$itemID = $_POST['item-id'];
		$query = sprintf("SELECT price FROM items WHERE id = '%s'",mysql_real_escape_string($itemID));
		$result = mysql_query($query);
		list($cost) = mysql_fetch_row($result);
		$gold = getStat('gc',$userID);
		if($gold >= $cost) {
			setStat('gc',$userID,($gold - $cost));
			$query = sprintf("SELECT count(id) FROM user_items WHERE user_id = '%s' AND item_id = '%s'",
				mysql_real_escape_string($userID),mysql_real_escape_string($itemID));
			$result = mysql_query($query);
			list($count) = mysql_fetch_row($result);
			if ($count > 0) {
				# already has one of the item
				$query = sprintf("UPDATE user_items SET quantity = quantity + 1 WHERE user_id = '%s' AND item_id = '%s'",
					mysql_real_escape_string($userID),
					mysql_real_escape_string($itemID));
			} else {
				# has none - new row
				$query = sprintf("INSERT INTO user_items(quantity,user_id,item_id) VALUES (1,'%s','%s')",
					mysql_real_escape_string($userID),
					mysql_real_escape_string($itemID));
			}
			mysql_query($query);
			$smarty->assign('message','You purchased the item.');
		} else {
			$smarty->assign('error','You cannot afford that weapon!');
		}
	} else if($_POST['sell-id']) {
		$itemID = $_POST['sell-id'];
		$query = sprintf("SELECT price FROM items WHERE id = '%s'",mysql_real_escape_string($itemID));
		$result = mysql_query($query);
		list($cost) = mysql_fetch_row($result);
		$gold = getStat('gc',$userID);
		setStat('gc',$userID,($gold + $cost));		
		$query = sprintf("SELECT quantity FROM user_items WHERE user_id = '%s' AND item_id = '%s'",
			mysql_real_escape_string($userID),mysql_real_escape_string($itemID));
		$result = mysql_query($query);
		list($quantity) = mysql_fetch_row($result);
		if ($quantity > 1) {
			$query = sprintf("UPDATE user_items SET quantity = quantity - 1 WHERE user_id = '%s' AND item_id = '%s'",
				mysql_real_escape_string($userID),
				mysql_real_escape_string($itemID));
		} else {
			$query = sprintf("DELETE FROM user_items WHERE user_id = '%s' AND item_id = '%s'",
				mysql_real_escape_string($userID),
				mysql_real_escape_string($itemID));
		}
		mysql_query($query);		
		$smarty->assign('message','You sold the item.');
	}
}
$query = "SELECT DISTINCT(id), name, price FROM items WHERE type = 'Usable' ORDER BY RAND() LIMIT 5;";
$result = mysql_query($query);
$items = array();
while($row = mysql_fetch_assoc($result)) {
	array_push($items,$row);
}
$inventory = array();
$query = sprintf("SELECT id, item_id, quantity FROM user_items WHERE user_id = '%s'",
		mysql_real_escape_string($userID));
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
	 $item_query = sprintf("SELECT name FROM items WHERE id = '%s'",
		mysql_real_escape_string($row['item_id']));
	$item_result = mysql_query($item_query);
	list($row['name']) = mysql_fetch_row($item_result);
	array_push($inventory,$row);
}
$smarty->assign('inventory',$inventory);
$smarty->assign('items',$items);
$smarty->display('item-shop.tpl');
 
?>

Extra Credit

  • Right now, we’re just displaying generic messages when a user buys an item. Change the messages so that they also tell the user what item they bought.
  • Add a box to the shop page that lets the user enter the quantity of the item they want to buy – so that users can buy 1 or more of any item.
  • Right now, our ’sell’ code is not very secured – a malicious user could simply pass in the ID of any item they wanted to sell that was in our game(whether it was in their inventory or not). Modify the item selling code so that it uses the ID of the row in user_items instead.

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: , , ,

Monday, October 13th, 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