January 17, 2011 by Christoff Truter PHP
Happy new year to everyone, may this be the best year ever... (and every person that visit this
site make a trillion euro this year)
Okay, now with all the cliches out of the way, I am going to start my blogging year out
with a PHP post.(Originally I decided to abandon this post, but perhaps someone out there can
find some value in this content.)
It is commonplace for PHP developers to use MySQL as their database engine; the database engine of choice for most.
PHP features a number of options/extensions in order to access MySQL, we've got the legacy mysql
extension thats been around since PHP 4, the mysqli extension which was introduced with the advent of PHP 5 and
the PDO extension.
Generally we need to avoid using the legacy mysql extension when we're faced with new development and rather use
the new mysqli extension - since it supports the new functionality (e.g. Stored Procedures, charsets, prepared statements
etc) added to MySQL over the years.
The PDO extension by contrast functions as a common abstraction that uses a driver based
approach, which supports MySQL or any other db engine with a PDO driver (not going to go into too much detail).
Assuming that we decided on using the mysqli extension, returning rows of data from a query will look something like
this:
<?php
// Check if MySQLI Extension exists
if (!function_exists('mysqli_connect')) {
throw new Exception('MySQLI Extension not found!');
}
// Connect to MySQL database
$mysqli = @new mysqli("host", "user", "pass", "db");
if (mysqli_connect_errno()) {
throw new Exception(mysqli_connect_error());
}
// Sanitize values
$id = (int)$_POST['id'];
$title = $mysqli->real_escape_string($_POST['title']);
$body = $mysqli->real_escape_string($_POST['body']);
$query = "SELECT * FROM something WHERE title = '$title' AND body LIKE '%$body%' AND id = $id";
// Execute Query
$result = $mysqli->query($query);
// Throw exception if something goes wrong with the query
// supposed to be default behavior in my opinion
if ($mysqli->error) {
throw new Exception($mysqli->error);
}
// Render rows
if (is_object($result))
{
echo '<table>';
while ($row = $result->fetch_assoc())
{
echo '<tr>'.
'<td>'.$row['id'].'</td>'.
'<td>'.$row['title'].'</td>'.
'<td>'.$row['body'].'</td>'.
'</tr>';
}
echo '</table>';
$result->close();
}
// Close connection - apparently not necessary according to PHP documentation
$mysqli->close();
?>
// Place within config file - Note this won't open an active MySQL Connection
MySQL::Config('host', 'user', 'pass', 'db');
// Open MySQL Connection and Execute Query, Connection closed on object destruct
// Perhaps move this to some kind of datalayer e.g. one for MySQL, one for PostgreSQL etc...
$rows = MySQL::Create('SELECT * FROM something
WHERE title = ?title
AND body LIKE ?body
AND id = ?id')
->Parameter('title', $_POST['title'])
->Parameter('body', '%'.$_POST['body'].'%')
->Parameter('id', $_POST['id'], 'integer')
->Query();
// Consider replacing with datagrid control/class
if (isset($rows))
{
echo '<table>';
foreach($rows as $row)
{
echo '<tr>'.
'<td>'.$row['id'].'</td>'.
'<td>'.$row['title'].'</td>'.
'<td>'.$row['body'].'</td>'.
'</tr>';
}
echo '</table>';
}
<?php
abstract class MySQLStrategy
{
protected $resource;
protected $count;
protected $host;
public function __construct(MySQL $host) {
$this->host = $host;
}
abstract public function AffectedRows();
abstract public function Count();
abstract public function Escape($value);
abstract public function LastId();
abstract public function Open();
abstract public function Query();
abstract public function NonQuery();
abstract public function Scalar();
abstract public function Close();
}
?>
private function __construct(array $settings) {
$this->settings = $settings;
if (function_exists('mysqli_connect')) {
$this->_strategy = new MySQLI_Strategy($this);
} else if (function_exists('mysql_connect')) {
$this->_strategy = new MySQL_Strategy($this);
} else {
throw new Exception("No compatible MySQL strategy found");
}
}