Before digging into the details of PHP design patterns (including the Singleton pattern), I used to define my database classes as abstract to avoid instantiation. This is a rough solution and it's not really suitable for a MVC environment. However, in this post I'm going to show you a really basic implementation of a basic class that uses a MySQL database.
Defining the class
The definition of this class is as follows:
/** @name Database
* @author Gabriele Romanato
* @category Database handler
* @abstract Connect to a MySQL database
*/
abstract class Database
{
}
An abstract class can be extended by another class but not instantiated using the new operator. Example:
class User extends Database
{
public function __construct()
{
parent::__construct();
}
}
Connection details
Sometimes I get lazy and I don't want to waste time with creating external configuration files. In the case of our class, I've stored the database connection details as class constants (too bad):
abstract class Database
{
const DB_USER = 'user';
const DB_PASS = 'pass';
const DB_HOST = 'localhost';
const DB_DATABASE = 'test';
}
Connecting to the database
Another bad habit that I had is to use the low-level MySQL functions instead of MySQLI functions or PDO. In this case the connection to the database is handled in a very simple way:
abstract class Database
{
const DB_USER = 'user';
const DB_PASS = 'pass';
const DB_HOST = 'localhost';
const DB_DATABASE = 'test';
private $_connection;
public function __construct()
{
$this->_connection = mysql_connect(self::DB_HOST, self::DB_USER, self::DB_PASS);
$this->_connect(self::DB_DATABASE, $this->_connection);
}
/** @access private
*
*
* @param mixed $database
* @param mixed $connection
*/
private function _connect($database, $connection)
{
mysql_select_db($database, $connection);
}
}
Our connection handler is declared as private because we don't want it to be accessible from outside our class. Within the __construct() method we use both the mysql_connect() function and the mysql_select_db() function so that connecting and selecting our working database happen almost at the same time, just at the beginning of our class.
Disconnecting from the database
When our class is no longer required, we need to disconnect from the database:
abstract class Database
{
const DB_USER = 'user';
const DB_PASS = 'pass';
const DB_HOST = 'localhost';
const DB_DATABASE = 'test';
private $_connection;
public function __construct()
{
$this->_connection = mysql_connect(self::DB_HOST, self::DB_USER, self::DB_PASS);
$this->_connect(self::DB_DATABASE, $this->_connection);
}
/** @access private
*
*
* @param mixed $database
* @param mixed $connection
*/
private function _connect($database, $connection)
{
mysql_select_db($database, $connection);
}
/** @access private */
private function _disconnect()
{
@mysql_close($this->_connection);
}
}
Class destructor
When a class has finished its task, we need to free memory. For that reason, we define a __destruct() method at the end of our class code and we call our _disconnect() method within it:
public function __destruct()
{
$this->_disconnect();
}
Fetching data
Now we can start fetching data from our database. The first, obvious method that we're going to create will actually fetch all the data from a table:
/** @access public
*
*
* @param mixed $tablename
* @return array
*/
public function fetchAll($tablename)
{
$query = "SELECT * FROM {$tablename}";
if($results = mysql_query($query, $this->_connection)) {
$rows = array();
while($result = mysql_fetch_array($results)) {
$rows[] = $result;
}
return $rows;
} else {
return mysql_error($this->_connection);
}
}
This method simply accepts a table name as its sole parameter and returns an associative array or a MySQL error (if something went wrong).
Inserting data
Inserting data into our database is quite simple:
/** @access public
*
*
* @param mixed $tablename
* @param mixed $values
* @return string
*/
public function insert($tablename, $values)
{
$query = "INSERT INTO {$tablename} VALUES(" . $values . ")";
if($results = mysql_query($query, $this->_connection))
{
return true;
} else {
return mysql_error($this->_connection);
}
}
This method accepts two parameters: a table name and a string containing the values to be inserted. It returns true or a MySQL error (if something went wrong).
Generic queries
We also need to define a method for more generic queries:
/** @access public
*
*
* @param mixed $query
* @return array
*/
public function query($query)
{
if($results = mysql_query($query, $this->_connection)) {
$rows = array();
while($result = mysql_fetch_array($results)) {
$rows[] = $result;
}
return $rows;
} else {
return mysql_error($this->_connection);
}
}
This method returns an associative array containing the query results or a MySQL error.
Cleaning and filtering data
Finally, we need a method to clear and filter data before inserting it into our database:
/** @access public
*
*
* @param mixed $string
* @return string
*/
public function clear($string)
{
return mysql_real_escape_string($string);
}
Conclusions
This is a very, very basic class that can be significantly improved by using design patterns and a more flexible database interface such as PDO. However, the aim of this class is to show you what are the underlying mechanisms behind a MySQL PHP implementation.