Dealing with databases

Database’s are everywhere. Your favourite website probably uses them, and any developer is going to have to ultimately use them. As such it is important to have a safe, reliable method of connecting them. Below I am going to talk about how I tend to do database management on my projects.

What Database

Databases can be loosely categorised into two categories:

  • Relational e.g.
    • MySQL
    • PostgreSQL
    • SQLite
  • NoSQL e.g.
    • Mongo DB
    • Google BigTable
    • NEO4J

The main difference between these two types is how you get at the data stored in them. In a relational database Structured Query Language (SQL) is used. SQL consists of plain-text instruction’s: e.g. Select * FROM Users. That code would return the details of every user in the database, usually as a PHP array.
By comparison a NoSQL database usually uses a more object oriented approach to data persistance. These generally implement their own data entry/ retrieval methods, rather than use a standard language.
I use MySQL on all my projects. This is because it is free and feature rich. It is also in active development, used by major websites and very popular, meaning if I do have trouble I can usually find an answer online.

Database Management Software

During development it is often nice to be able to manage a database without having to pop-into the command prompt and write some SQL. For this I use MySQL Workbench. It is available as a a cross-platform application. As well as all the usual features it also contains a very good visual editors, which has become one of my favourite features of the software. However, I have found this software can be slow to open and run operations.
PHPMyAdmin is another popular choice, and is usually pre-installed on shared hosting environments. It is less advanced than Workbench, but might be a good choice for beginners. However, when I have tried installing it myself in the past I have been unsuccessful, and was greeted by a page of errors.
ChromeMyAdmin and Sequal Pro are other options, although I have personally not used either.

Using MySQL with PHP

In the early day’s on the web we used the MySQL PHP module, but this is now deprecated. Now there are two options – PHP Data Module (PDO) or MySQLi. I use and recommend the use of PDO because it provides a more object oriented interface and supports multiple database engines meaning that I can move to another database system in future. On most projects I make my life easier by using the following abstaction layer.

class DB {

	private $dbh;
	private $stmt;

	public function __construct($user, $pass, $dbname) {
		$this->dbh = new PDO(
			"mysql:host=localhost;dbname=$dbname",
			$user,
			$pass,
			array( PDO::ATTR_PERSISTENT => true )
		);
	}

	public function query($query) {
		$this->stmt = $this->dbh->prepare($query);
		return $this;
	}

	public function bind($pos, $value, $type = null) {

		if( is_null($type) ) {
			switch( true ) {
				case is_int($value):
					$type = PDO::PARAM_INT;
					break;
				case is_bool($value):
					$type = PDO::PARAM_BOOL;
					break;
				case is_null($value):
					$type = PDO::PARAM_NULL;
					break;
				default:
					$type = PDO::PARAM_STR;
			}
		}

		$this->stmt->bindValue($pos, $value, $type);
		return $this;
	}

	public function execute() {
		return $this->stmt->execute();
	}

	public function resultset() {
		$this->execute();
		return $this->stmt->fetchAll();
	}

	public function single() {
		$this->execute();
		return $this->stmt->fetch();
	}
}
	

Originally posted by Mike on Stack Overflow

I can then write:

// Establish a connection.
$db = new DB('user', 'password', 'database');

// Create query, bind values and return a single row.
$row = $db->query('SELECT * FROM Users WHERE email = :email')
   ->bind('email', :email)
   ->single();
	

That code could be used as part of a login script to get the users details from the database. One key concept to note is that I have created a prepared statement. This means that I have created the query with no user data entered into it. Instead I have used a placeholder :email, and bound the data afterwards with the bind function. As a result of this, the user data and SQL is sent to the database server separately meaning I am protected against SQL Injection.

By using these tools I can effectively manage my databases, and am protected against SQL injection when I use the database in my applications.