Sharing a database connection handler between different objects - PHP

As I explained in the introduction, my goal here is to show how a factory method can be used for sharing a database connection handler between a couple of objects. With that premise in mind, I’m going to create an example that will do that in a “bad” way, and once you realize why that approach is wrong, I'm going to introduce some improvements to it via the aforementioned factory method.

That being explained, first we need to define a class that abstracts accesses to a database server, which in this particular case will be MySQL. So, here’s the definition of the class that accomplishes this task:

class MySQL

{

private $result = NULL;

private $link = NULL;

// connect to MySQL

public function __construct($host, $user, $password, $database)

{

if (FALSE === ($this->link = mysqli_connect($host, $user, $password, $database)))

{

throw new Exception('Error : ' . mysqli_connect_error());

}

}

// perform query

public function query($query)

{

if (is_string($query) AND empty($query) === FALSE)

{

if (FALSE === ($this->result = mysqli_query($this->link, $query)))

{

throw new Exception('Error performing query ' . $query . ' Error message :' .mysqli_error($this->link));

}

}

}

// fetch row from result set

public function fetch()

{

if (FALSE === ($row = mysqli_fetch_assoc($this->result)))

{

mysqli_free_result($this->result);

return FALSE;

}

return $row;

}

// get insertion ID

public function getInsertID()

{

return mysqli_insert_id($this->link);

}

// count rows in result set

public function countRows()

{

if ($this->result !== NULL)

{

return mysqli_num_rows($this->result);

}

}

// implement destructor to close the database connection

function __destruct()

{

mysqli_close($this->link);

}

}

Certainly, there’s nothing difficult to understand with the above “MySQL” class, since it only defines some simple methods that allow you to run queries against a selected database, fetch and count rows in a result set, and find insertion IDs. So far, the way that this class works is pretty understandable, right?

Well, now that we've created a basic MySQL abstraction class, suppose for a moment that there’s a another one that needs to use an instance of it to get access to a specified database table. In this typical situation, the class that will use the database handler will be a simple one, called “User,” whose definition will be based partially on the class built by Martin Jansen in his excellent article on Object Overloading in PHP 5.

Having given the corresponding credits, here’s how this brand new class looks:

class User

{

private $data = array();

private $id = NULL;

private $db = NULL;

// constructor

public function __construct($id = NULL)

 {

$this->db = new MySQL('host', 'user', 'password', 'database');

 if ($id !== NULL)

{

$this->id = $id;

$this->db->query('SELECT * FROM users WHERE id=' . $this->id);

$this->data = $this->db->fetch();

}

}

// set undeclared property

public function __set($property, $value)

{

if ($property !== 'name' and $property !== 'email')

{

return;

}

$this->data[$property] = $value;

}

// get undeclared property

public function __get($property)

{

if (isset($this->data[$property]) === TRUE)

{

return $this->data[$property];

}

}

// save object to session variable

public function __destruct()

{

if ($this->id === NULL)

{

$this->db->query("INSERT INTO users (id, name, email) VALUES (NULL, '$this->name', '$this->email')");

}

else

{

$this->db->query("UPDATE users SET name = '$this->name', email = '$this->email' WHERE id = $this->id");

}

}

}

As shown above, this brand new “User” class allows you to build objects whose properties persist through different HTTP requests. On one hand, the constructor fetches those properties when applicable, while on the other hand, the destructor is responsible for saving them to a “users” database table.

At this point, I've created the scenario mentioned in the introduction: there’s a database handler that is used by another class to access a MySQL table.

So far, all seems to be right with this example, except for one tiny detail. As you may have noticed, the “User” class’s constructor is entirely responsible for creating an instance of the database handler. This is the wrong approach, since in this case each user object must take care only of retrieving and saving its own properties, and not for creating other objects. Period.

This issue can be addressed by implementing the Dependency Injection pattern, but for the moment this topic is out of the scope of this tutorial. So, returning to the previous classes, here’s a script that shows how to use them for building two persistent user objects. Take a look at it:

// create first user object

$user1 = new User();

$user1->name = 'Susan Norton';

$user1->email = 'susan@domain.com';

// create second user object

$user2 = new User();

$user2->name = 'Mary Smith';

$user2->email = 'mary@domain.com';

If you try the above example, you’ll see that it works very well; the information about Susan and Mary will be neatly stored in the “users” MySQL table. However, due to the reasons given before, this isn’t the correct way to put all these classes to work together. Things will get even worse if multiple user objects need to be spawned, since each of them will create a new instance of the MySQL class. Really ugly, right?

Thus, is it possible to do this any better? Of course it is, and to prove this, in the section to come I’m going to add a factory method inside the “MySQL” class, which will help to reduce the number of database handlers used by the user objects.

To see how this factory method will be defined, click on the link below and keep reading.