Building Browsergames: DRYing out our database connections (PHP)

In the interest of DRYing out our code a bit, there’s another change we can make: our database connection code.

Every time that we want to work with the database, we need to connect to it in order to do anything - and so far, we’ve just been copying and pasting the same code over and over and over:

1
2
3
4

require_once 'config.php';		// our database settings
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
	or die('Error connecting to mysql');
mysql_select_db($dbname);

While that works, it means that we have the exact same code in way too many places- which is a Bad Thing™. Instead of copying and pasting the code into each page or function that needs to interact with the database, we’re going to break the code off into its own file, named database.php:

1
2
3
4
5
6

<?php
require_once 'config.php';		// our database settings
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
	or die('Error connecting to mysql');
mysql_select_db($dbname);
?>

And now, because we’ve made that change we can stop having to paste out all those lines - this is the only line you need in a piece of code that interacts with the database:

include 'database.php';

This means that we can modify our DRY stats code from this:

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

<?php
 
function getStatDRY($tableName,$columnName,$statName,$trackingID) {
	include 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql:');
	mysql_select_db($dbname);
	createIfNotExistsDRY($tableName,$columnName,$statName,$trackingID);
	$query = sprintf("SELECT value FROM %s WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND %s = '%s'",
		mysql_real_escape_string($tableName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($columnName),
		mysql_real_escape_string($trackingID));
	$result = mysql_query($query);
	list($value) = mysql_fetch_row($result);
	return $value;		
}
 
function setStatDRY($tableName,$columnName,$statName,$trackingID,$value) {
	include 'config.php';
	$conn = mysql_connect($dbhost,$dbuser,$dbpass)
		or die ('Error connecting to mysql');
	mysql_select_db($dbname);
	createIfNotExistsDRY($tableName,$columnName,$statName,$trackingID);
	$query = sprintf("UPDATE %s SET value = '%s' WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND %s = '%s'",
		mysql_real_escape_string($tableName),
		mysql_real_escape_string($value),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($columnName),
		mysql_real_escape_string($trackingID));
	$result = mysql_query($query);
}
 
function createIfNotExistsDRY($tableName,$columnName,$statName,$trackingID) {
	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 %s WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND %s = '%s'",
		mysql_real_escape_string($tableName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($columnName),
		mysql_real_escape_string($trackingID));
	$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 %s(stat_id,%s,value) VALUES ((SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s'),'%s','%s')",
		mysql_real_escape_string($tableName),
		mysql_real_escape_string($columnName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($trackingID),
		'0');
		mysql_query($query);
	}	
}
 
?>

Into this:

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

<?php
 
include 'database.php';
 
function getStatDRY($tableName,$columnName,$statName,$trackingID) {
	createIfNotExistsDRY($tableName,$columnName,$statName,$trackingID);
	$query = sprintf("SELECT value FROM %s WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND %s = '%s'",
		mysql_real_escape_string($tableName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($columnName),
		mysql_real_escape_string($trackingID));
	$result = mysql_query($query);
	list($value) = mysql_fetch_row($result);
	return $value;		
}
 
function setStatDRY($tableName,$columnName,$statName,$trackingID,$value) {
	createIfNotExistsDRY($tableName,$columnName,$statName,$trackingID);
	$query = sprintf("UPDATE %s SET value = '%s' WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND %s = '%s'",
		mysql_real_escape_string($tableName),
		mysql_real_escape_string($value),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($columnName),
		mysql_real_escape_string($trackingID));
	$result = mysql_query($query);
}
 
function createIfNotExistsDRY($tableName,$columnName,$statName,$trackingID) {
	$query = sprintf("SELECT count(value) FROM %s WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND %s = '%s'",
		mysql_real_escape_string($tableName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($columnName),
		mysql_real_escape_string($trackingID));
	$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 %s(stat_id,%s,value) VALUES ((SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s'),'%s','%s')",
		mysql_real_escape_string($tableName),
		mysql_real_escape_string($columnName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($trackingID),
		'0');
		mysql_query($query);
	}	
}
 
?>

Can you see what we did there to reduce repetition? Because the variable $conn from database.php ends up in the root scope, we can access it from anywhere within our code - which means we only need to add that single line to the beginning of our code, and it will just work - without us having to repeat even that one line anywhere other than at the top of our code. Nice!

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.

Wednesday, July 9th, 2008 DRY, buildingbrowsergames, code, design, php

No comments yet.

Leave a comment

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

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.

Write for us!

Have you built a browsergame before, or do you have an opinion to share on the subject? Send an e-mail to buildingbrowsergames@gmail.com!