Building Browsergames: Retrieving Items (PHP)

Yesterday, we worked through setting up the initial database structure for our items system – today, we’re going go write the code that we’ll be using to retrieve our items.

The code to retrieve our items is relatively simple – it’s just a couple of functions for retrieving information on items. We don’t even need to include anything for actually setting item information – because our players should never be in a situation where they are 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 a simple getItem function, which will return an associative array with the item’s attributes:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php
 
function getItem($itemID) {
	include 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql:');
	mysql_select_db($dbname);
	$query = sprintf("SELECT name, type FROM items WHERE id = '%s'",
		mysql_real_escape_string($itemID));
	$result = mysql_query($query);
	$item = mysql_fetch_assoc($result);
	$item['id']	= $itemID;
	return $item;
}
 
?>

There isn’t really anything fancy to this code – the only thing that’s different from most of our other database code is that we are using mysql_fetch_assoc instead of mysql_fetch_row to retrieve our information. By using mysql_fetch_assoc, we can store the retrieved data into an associative array, which is easier to deal with than a large collection of random variables.

We aren’t going to be building anything to take advantage of this code today, but we can at least build some testing code. To start off, run this SQL to insert an item into the database:

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

And once that’s done, create a file named test.php with this code inside:

1
2
3
4
5
6
7
8
9
10
11
<?php
 
require_once 'items.php';
 
$item = getItem(1);
 
echo 'name: ' .$item['name'];
echo '<br />type: ' .$item['type'];
echo '<br />ID: ' .$item['id'];
 
?>

Once you’ve uploaded test.php to your server, if you visit it you should see the information on the Wooden Sword – which shows us that our item retrieval code works.

This is really the only ’special’ part of the item code – all that’s left now is to customize our stats code so that we can retrieve stats for a specific item:

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
function getItemStat($statName,$itemID) {
	include 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql:');
	mysql_select_db($dbname);
	createIfNotExistsItem($statName,$itemID);
	$query = sprintf("SELECT value FROM item_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND item_id = '%s'",
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($itemID));
	$result = mysql_query($query);
	list($value) = mysql_fetch_row($result);
	return $value;		
}
function setItemStat($statName,$itemID,$value) {
	include 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql');
	mysql_select_db($dbname);
	createIfNotExistsItem($statName,$itemID);
	$query = sprintf("UPDATE item_stats SET value = '%s' WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND item_id = '%s'",
		mysql_real_escape_string($value),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($itemID));
	$result = mysql_query($query);
}
 
function createIfNotExistsItem($statName,$itemID) {
	include 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql:');
	mysql_select_db($dbname);
	$query = sprintf("SELECT count(value) FROM item_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND item_id = '%s'",
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($itemID));
	$result = mysql_query($query);
	list($count) = mysql_fetch_row($result);
	if($count == 0) {
		// the stat doesn't exist; insert it into the database
		$query = sprintf("INSERT INTO item_stats(stat_id,item_id,value) VALUES ((SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s'),'%s','%s')",
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($itemID),
		'0');
		mysql_query($query);
	}	
}

There isn’t anything at all that you haven’t seen before, here – it’s essentially our code to retrieve stats, modified so that it can retrieve stats for items.

If we want to test it, we’ll 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);

And then we’ll modify our testing page to retrieve the ‘atk’ stat for our sword:

10
11
12
 
$item['atk'] = getItemStat('atk',1);
echo '<br />Attack: ' . $item['atk'];

And that’s all there is to it! Once you upload both pieces of code, you’ll be able to see all of the information for the Wooden Sword. Here’s the item retrieval code all 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
55
56
57
58
59
60
61
62
63
64
65
66
<?php
 
function getItem($itemID) {
	include 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql:');
	mysql_select_db($dbname);
	$query = sprintf("SELECT name, type FROM items WHERE id = '%s'",
		mysql_real_escape_string($itemID));
	$result = mysql_query($query);
	$item = mysql_fetch_assoc($result);
	$item['id']	= $itemID;
	return $item;
}
 
function getItemStat($statName,$itemID) {
	include 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql:');
	mysql_select_db($dbname);
	createIfNotExistsItem($statName,$itemID);
	$query = sprintf("SELECT value FROM item_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND item_id = '%s'",
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($itemID));
	$result = mysql_query($query);
	list($value) = mysql_fetch_row($result);
	return $value;		
}
function setItemStat($statName,$itemID,$value) {
	include 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql');
	mysql_select_db($dbname);
	createIfNotExistsItem($statName,$itemID);
	$query = sprintf("UPDATE item_stats SET value = '%s' WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND item_id = '%s'",
		mysql_real_escape_string($value),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($itemID));
	$result = mysql_query($query);
}
 
function createIfNotExistsItem($statName,$itemID) {
	include 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql:');
	mysql_select_db($dbname);
	$query = sprintf("SELECT count(value) FROM item_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND item_id = '%s'",
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($itemID));
	$result = mysql_query($query);
	list($count) = mysql_fetch_row($result);
	if($count == 0) {
		// the stat doesn't exist; insert it into the database
		$query = sprintf("INSERT INTO item_stats(stat_id,item_id,value) VALUES ((SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s'),'%s','%s')",
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($itemID),
		'0');
		mysql_query($query);
	}	
}
 
?>

And here’s the tester page code, to make sure that our items code is working:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
 
require_once 'items.php';
 
$item = getItem(1);
 
echo 'name: ' .$item['name'];
echo '<br />type: ' .$item['type'];
echo '<br />ID: ' .$item['id'];
 
$item['atk'] = getItemStat('atk',1);
echo '<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.

Thursday, July 3rd, 2008 buildingbrowsergames, code, php
  • Oh, you're right - it looks like PHP 4's end of life has come and gone. I only thought that it had been announced - not that it had happened.

    Whoops! Thanks for pointing that out, sepp.

  • By the way: Don't take PHP4 as your base. It's obsolete and will be vanished in the near future. Version 5 gives much more powerful tools, especially for object orientated programming. Give it a try ;) . "A lot of hosts" doesn't have to be "the most" ;) .

  • Switching to an OOP system is a pretty good idea - when I first started writing this tutorial code, I didn't realize that I'd be repeating myself quite this much.

    I'll have to work on designing an OOP version of our database access code - unfortunately, I won't be able to use PDO as I'm aiming at keeping the PHP code given here compatible with PHP 4, which is what a lot of hosts are still using.

  • So what about an oop approach?! For example you could use a class to encapsulate database access? That would prevent doing mysql_real_escape_string() everywhere! Maybe PDO is worth a glance ;) .

    And: DRY! Dont repeat yourself.

    et cetera pp

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