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!

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.

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

    Right now, you do not need the config file for anything else but database values.. Right?

    So why not make the config and the database connection file the same file?

  • That sounds like a pretty good idea - the tutorial's finished, but I'll
    definitely have to keep that in mind for next time.

  • MrLollige

    I am just checking if my own ideas are not silly or if I am forgetting something. But of course, you can use them ;)

  • BBBB

    I don't think you really should do this. Later there will be much more values in it and you dont want to connect to mysql always when you include the file. Also I would start searching in the config for the mysql config and not in the database file

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