Php Databases
Native drivers are great if you are only using one database in your application, but if, for example, you are using MySQL and a little bit of MSSQL, or you need to connect to an Oracle database, then you will not be able to use the same drivers. You’ll need to learn a brand new API for each database — and that can get silly.
MySQL Extension
The mysql extension for PHP is incredibly old and has superseded by two other extensions:
mysqli
pdo
Not only did development stop long ago on mysql, but it was deprecated as of PHP 5.5.0, and has been officially removed in PHP 7.0.
To save digging into your php.ini settings to see which module you are using, one option is to search for mysql_* in your editor of choice. If any functions such as mysql_connect() and mysql_query() show up, then mysql is in use.
Even if you are not using PHP 7.0 yet, failing to consider this upgrade as soon as possible will lead to greater hardship when the PHP 7.0 upgrade does come about. The best option is to replace mysql usage with mysqli or PDO in your applications within your own development schedules so you won’t be rushed later on.
If you are upgrading from mysql to mysqli, beware lazy upgrade guides that suggest you can simply find and replace mysql_* with mysqli_*. Not only is that a gross oversimplification, it misses out on the advantages that mysqli provides, such as parameter binding, which is also offered in PDO.
PHP: Choosing an API for MySQL
PDO Tutorial for MySQL Developers
PDO Extension
PDO is a database connection abstraction library — built into PHP since 5.1.0 — that provides a common interface to talk with many different databases. For example, you can use basically identical code to interface with MySQL or SQLite:
<?php
// PDO + MySQL
$pdo = new PDO('mysql:host=example.com;dbname=database', 'user', 'password');
$statement = $pdo->query("SELECT some_field FROM some_table");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['some_field']);
// PDO + SQLite
$pdo = new PDO('sqlite:/path/db/foo.sqlite');
$statement = $pdo->query("SELECT some_field FROM some_table");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['some_field']);
PDO will not translate your SQL queries or emulate missing features; it is purely for connecting to multiple types of database with the same API.
More importantly, PDO allows you to safely inject foreign input (e.g. IDs) into your SQL queries without worrying about database SQL injection attacks. This is possible using PDO statements and bound parameters.
Let’s assume a PHP script receives a numeric ID as a query parameter. This ID should be used to fetch a user record from a database. This is the wrong way to do this:
<?php
$pdo = new PDO('sqlite:/path/db/users.db');
$pdo->query("SELECT name FROM users WHERE id = " . $_GET['id']); // <-- NO!
This is terrible code. You are inserting a raw query parameter into a SQL query. This will get you hacked in a heartbeat, using a practice called SQL Injection. Just imagine if a hacker passes in an inventive id parameter by calling a URL like http://domain.com/?id=1%3BDELETE+FROM+users. This will set the $_GET['id'] variable to 1;DELETE FROM users which will delete all of your users! Instead, you should sanitize the ID input using PDO bound parameters.
<?php
$pdo = new PDO('sqlite:/path/db/users.db');
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = :id');
$id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT); // <-- filter your data first (see [Data Filtering](#data_filtering)), especially important for INSERT, UPDATE, etc.
$stmt->bindParam(':id', $id, PDO::PARAM_INT); // <-- Automatically sanitized for SQL by PDO
$stmt->execute();
This is correct code. It uses a bound parameter on a PDO statement. This escapes the foreign input ID before it is introduced to the database preventing potential SQL injection attacks.
For writes, such as INSERT or UPDATE, it’s especially critical to still filter your data first and sanitize it for other things (removal of HTML tags, JavaScript, etc). PDO will only sanitize it for SQL, not for your application.
Learn about PDO
You should also be aware that database connections use up resources and it was not unheard-of to have resources exhausted if connections were not implicitly closed, however this was more common in other languages. Using PDO you can implicitly close the connection by destroying the object by ensuring all remaining references to it are deleted, i.e. set to NULL. If you don’t do this explicitly, PHP will automatically close the connection when your script ends - unless of course you are using persistent connections.
Learn about PDO connections
Interacting with Databases
When developers first start to learn PHP, they often end up mixing their database interaction up with their presentation logic, using code that might look like this:
<ul>
<?php
foreach ($db->query('SELECT * FROM table') as $row) {
echo "<li>".$row['field1']." - ".$row['field1']."</li>";
}
?>
</ul>
This is bad practice for all sorts of reasons, mainly that its hard to debug, hard to test, hard to read and it is going to output a lot of fields if you don’t put a limit on there.
While there are many other solutions to doing this - depending on if you prefer OOP or functional programming - there must be some element of separation.
Consider the most basic step:
<?php
function getAllFoos($db) {
return $db->query('SELECT * FROM table');
}
foreach (getAllFoos($db) as $row) {
echo "<li>".$row['field1']." - ".$row['field1']."</li>"; // BAD!!
}
That is a good start. Put those two items in two different files and you’ve got some clean separation.
Create a class to place that method in and you have a “Model”. Create a simple .php file to put the presentation logic in and you have a “View”, which is very nearly MVC - a common OOP architecture for most frameworks.
foo.php
<?php
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
// Make your model available
include 'models/FooModel.php';
// Create an instance
$fooModel = new FooModel($db);
// Get the list of Foos
$fooList = $fooModel->getAllFoos();
// Show the view
include 'views/foo-list.php';
models/FooModel.php
<?php
class FooModel
{
protected $db;
public function __construct(PDO $db)
{
$this->db = $db;
}
public function getAllFoos() {
return $this->db->query('SELECT * FROM table');
}
}
views/foo-list.php
<?php foreach ($fooList as $row): ?>
<?= $row['field1'] ?> - <?= $row['field1'] ?>
<?php endforeach ?>
This is essentially the same as what most modern frameworks are doing, albeit a little more manual. You might not need to do all of that every time, but mixing together too much presentation logic and database interaction can be a real problem if you ever want to unit-test your application.
PHPBridge has a great resource called Creating a Data Class which covers a very similar topic, and is great for developers just getting used to the concept of interacting with databases.
Abstraction Layers
Many frameworks provide their own abstraction layer which may or may not sit on top of PDO. These will often emulate features for one database system that is missing from another by wrapping your queries in PHP methods, giving you actual database abstraction instead of just the connection abstraction that PDO provides. This will of course add a little overhead, but if you are building a portable application that needs to work with MySQL, PostgreSQL and SQLite then a little overhead will be worth it the sake of code cleanliness.
Some abstraction layers have been built using the PSR-0 or PSR-4 namespace standards so can be installed in any application you like:
Aura SQL
Doctrine2 DBAL
Propel
Zend-db
