I have a simple database connection class which is using PDO:
/**
* Class DatabaseConnection
*/
class DatabaseConnection implements Connection
{
/**
* @var null|PDO
*/
private $instance = null;
/**
* @var string The host the Connection should connect to
*/
private $host;
/**
* @var string The database name the connection should connect to
*/
private $database;
/**
* @var string The username to login into the database
*/
private $username;
/**
* @var string The password to login into the database
*/
private $password;
/**
* @var array The credentials from the database connection
*/
private $credentials = [];
/**
* @var array The flags from the database connection
*/
private $flags = [];
/**
* @param float $host
* @param string $database
* @param string $username
* @param string $password
* @param array $credentials
* @param array $flags
*/
public final function __construct($host, $database, $username, $password, $credentials = [], $flags = []) {
$this->host = $host;
$this->database = $database;
$this->username = $username;
$this->password = $password;
$this->credentials = $credentials;
$this->flags = $flags;
}
/**
* @throws ConnectionException
*/
public final function initialize() {
if ($this->isInitialized()) {
throw new ConnectionException("Database connection isnt open yet.");
}
$this->instance = new PDO("mysql:host=$this->host;dbname=$this->database", $this->username, $this->password);
}
/**
* Closes the connection
*
* @throws ConnectionException When the connection isn't open
*/
public final function terminate() {
if (!$this->isInitialized()) {
throw new ConnectionException("Database is closed");
}
$this->instance = null;
}
/**
* @return bool
*/
private function isInitialized() {
return $this->instance !== null;
}
/**
* @param $credentials
*/
public final function setCredentials($credentials) {
$this->credentials = $credentials;
}
/**
* @return null|PDO
*/
public function getInstance() {
return $this->instance;
}
}
For queries, i've made this class:
/**
* User: Bas
* Date: 8-12-2014
* Time: 10:36
*/
class QueryHandler
{
/**
* @var DatabaseConnection $databaseConnection
*/
private $databaseConnection;
/**
* @var PDOStatement The query which is getting executed
*/
private $statement;
/**
* Initializes the variable for the current database connection where the query should run on
*
* @param DatabaseConnection $databaseConnection The database connection
*/
public final function __construct (DatabaseConnection $databaseConnection) {
$this->databaseConnection = $databaseConnection;
}
/**
* Prepares the query and gives the opportunity to already give the parameters
*
* @param PDOStatement $statement The query which is getting executed.
* @param array $parameters The parameters contained in the query
*
* @return $this This class, used for method chaining
*/
public final function build ($statement, array $parameters = []) {
/*
* Prepare the query
*/
$this->statement = $this->databaseConnection->getInstance()
->prepare($statement);
/*
* Check if there are any parameter in the query
*/
if (preg_match_all('/(?<=:)\w+/', $statement, $parameter) && !empty($parameters)) {
/*
* Loop through the parameter names and values
*/
foreach ($parameters as $parameter => $value) {
/*
* Bind the parameters within the query
*/
$this->statement->bindParam($parameter, $value);
}
} elseif (!empty($parameters) && !preg_match_all('/(?<=:)\w+/', $statement, $parameter)) {
unset($parameters);
}
/*
* Return the class used for method chaining
*/
return $this;
}
/**
* Executes the given query
*
* @return $this Executes the given query
*/
public final function execute () {
$this->statement->execute();
return $this;
}
/**
* Fetch a single row out of the database based on the query
*
* @param int $fetchStyle
*
* @return int|string|float|array
*/
public final function getResult ($fetchStyle = PDO::FETCH_ASSOC) {
return $this->statement->fetch($fetchStyle);
}
/**
* Fetches multiple row
*
* @param int $fetchStyle
*
* @return int|string|float|array
*/
public final function getResults ($fetchStyle = PDO::FETCH_ASSOC) {
return $this->statement->fetchAll($fetchStyle);
}
/**
* Sets the parameters of the given query
*
* @param array $parameters
*
* @return $this
*/
public function setParameters (array $parameters) {
foreach ($parameters as $parameter => $value) {
$this->statement->bindParam(":" . $parameter, $value);
}
return $this;
}
}
To use this, i use this code:
$connection = new DatabaseConnection("127.0.0.1", "phpmyadmin", "root", "", array());
try {
$connection->initialize();
$queryHandler = new QueryHandler($connection);
$result = $queryHandler->build("SELECT table_name, column_name, comment FROM pma_column_info")
->execute()
->getResult();
} catch (ConnectionException $e) {
....
}
This is only for getinng a value out of the database, for inserting something into the database i can just do:
$queryHandler->build("INSERT INTO ....")->execute();
Note
- This is for practice