MySQL PDO Database Class
Credits
The DB class and its documentation are both based on the original package from Jeff Williams published in phpclasses.org.
Some important changes have been made:
- Unlike the original, this class is no longer static but must be instantiated with a constructor.
- The rows of records are no longer fetched directly by the functions, but must be fetched with the
fetch()
function or fetched all together with the fetchAll()
function. The fetch()
function saves memory by fetching records one by one instead of storing them in a table or an object.
- All the functions have been renamed from UpperCamelCase to lowerCamelCase to comply with PSR12.
- The connection settings are stored in phpformbuilder/database/db-connect.php as constants.
This allows to connect using $db->connect()
without any argument
- Some additional functions have been added:
$db->error()
$db->fetch()
$db->fetchAll()
$db->rowCount()
- The
protected $db->whereClause($where)
has been edited to accept fields prefixed with their table name (ie: [table.field => value]
) in the $where argument.
- A private function
$db->interpolateQuery($qry, $params)
has been added to show the raw SQL query string from PDO prepared statements when thye query fails and the error is displayed.
- The documentation HTML has been rewrited.
Introduction
All of the code is in a single file to make it incredibly easy to install and learn.
It uses an internal global PDO database connection to make it easier to retrofit into existing projects or use in new projects.
I also made every effort to take care of all the details like:
- Incredibly easy-to-use and detailed debugging
- Automatic SQL generation
- Try/catch error checking
- Error event handling and PHP error logging
- Security
- Full transaction processing
and using as little memory and being as lightweight as possible while still containing a lot of great features.
Some basic knowledge of how PDO "placeholders" work is helpful but not necessary. Every effort to use them is applied to stop SQL injection hacks and also because:
"There is a common misconception about how the placeholders in prepared statements work. They are not simply substituted in as (escaped) strings, and the resulting SQL executed. Instead, a DBMS asked to "prepare" a statement comes up with a complete query plan for how it would execute that query, including which tables and indexes it would use."
Source: http://php.net/manual/en/pdo.prepare.php
__construct
Creates the DB object and & connects to a MySQL PDO database.
$db = new DB($show_errors = false, $username = DBUSER, $password = DBPASS, $database = DBNAME, $hostname = DBHOST)
- Define the DBUSER, DBPASS, DBNAME and DBHOST constants in phpformbuilder/database/db-connect.php
- Then require phpformbuilder/database/db-connect.php and you can connect to both your localhost and production server using
$db = new DB();
without any argument.
Argument |
Type |
Description |
$show_errors |
Boolean |
If false then the errors are registered and can be called using $db->error() but no errors are shown. |
$username |
String |
Database user name. Default: DBUSER |
$password |
String |
Database password Default: DBPASS |
$database |
String |
Database or schema name Default: DBNAME |
$hostname |
String |
Host name of the server Default: DBHOST |
If there is an error connecting and $show_errors is set to true the error will be displayed on screen. If there is no error, true
is returned.
// register the database connection settings
require_once 'phpformbuilder/database/db-connect.php';
// Include the database class
require_once 'phpformbuilder/database/DB.php';
// Then connect to the database
$db = new DB();
// or connect and show errors
$db = new DB(true);
// or connect and register errors in a variable
if ($db = new DB() !== true) {
$error_message = $db->error();
}
execute
Executes a SQL statement using PDO.
If you have a SQL statement that needs to be executed and doesn't return anything (usually an INSERT, UPDATE, or DELETE), use this method. Use $db->insert
, $db->update
, or $db->delete
if you are not using SQL (these methods automatically generate and execute SQL for you on the backend).
$db->execute($sql, $placeholders = false, $debug = false)
Argument |
Type |
Description |
$sql * |
String |
The SQL to be executed |
$placeholders |
Array |
Associative array placeholders for binding to SQL.
array('name' => 'Cathy', 'city' => 'Cape Cod') |
$debug |
Boolean |
If set to true , will output results and query information |
The return result varies depending on the situation. If the SQL was an INSERT statement, the primary key of the record will be returned. Otherwise, if there was an error, false
is returned. If the execution was sucessful, true
is returned.
// Execute a SQL query
// (FYI: You can also use $db->safe() to return a safe quoted string for SQL)
$sql = "INSERT INTO test_table (name, age, active) VALUES ('Sophia', 20, true)";
$id = $db->execute($sql);
// Execute a SQL query with placeholders (better because it stops SQL Injection hacks)
$sql = 'DELETE FROM test_table WHERE name = :name AND age = :age AND active = :active';
$values = array('name' => 'Lucas', 'age' => 45, 'active' => true);
$success = $db->execute($sql, $values);
// Execute the same SQL statement but only in debug mode
// In debug mode, the record will not be saved
$success = $db->execute($sql, $values, true);
query
Executes a SQL statement using PDO
If you have a SQL statement that returns records (usually a SELECT), use this method. Use $db->select
if you are not using or don't want to use SQL.
$db->query($sql, $placeholders = false, $debug = false)
Argument |
Type |
Description |
$sql * |
The SQL to be executed |
|
$placeholders |
Array |
Associative array placeholders for binding to SQL.
array('name' => 'David', 'city' => 'Dallas') |
$debug |
Boolean |
If set to true , will output results and query information |
returns true
, or false
if there is an error executing the SQL.
// Execute a SQL query
$sql = 'SELECT * FROM test_table';
$db->query($sql);
// Execute the same query in debug mode
$rows = $db->query($sql, $values, true);
// Execute a SQL query using placeholders
$sql = 'SELECT id, name, age FROM test_table WHERE active = :active';
$values = array('active' => true);
$db->query($sql, $values);
// loop through the results
while ($row = $db->fetch()) {
echo $row->name . '<br>';
}
// or fetch all the records then loop
// (this function should not be used if a huge number of rows have been selected, otherwise it will consume a lot of memory)
$rows = $db->fetchAll();
foreach($rows as $row) {
echo $row->name . '<br>';
}
queryRow
Executes a SQL statement using PDO and returns a single row as an object.
Executes a SQL statement using PDO exactly the same way $db->query does except only one row is returned. The syntax is identical. If more than more row would be returned by a given SQL query, using this method, only the first row would be returned. Don't forget to try setting debug mode to true.
$db->queryRow($sql, $placeholders = false, $debug = false, $fetch_parameters = PDO::FETCH_OBJ)
Argument |
Type |
Description |
$sql * |
String |
The SQL to be executed |
$placeholders |
Array |
Associative array placeholders for binding to SQL.
array('name' => 'Emma', 'city' => 'Eureka') |
$debug |
Boolean |
If set to true , will output results and query information |
$fetch_parameters |
Function parameter |
PDO fetch style record options (i.e. PDO::FETCH_OBJ , PDO::FETCH_ASSOC , PDO::FETCH_NUM , or PDO::FETCH_BOTH ) |
If there is an error executing the SQL, false
is returned. Otherwise, the data is returned in the format set by $fetch_parameters.
// Execute a SQL query with placeholders to return only one row
$sql = 'SELECT name, age FROM test_table WHERE id = :id LIMIT 1';
$row = $db->queryRow($sql, ['id' => 45]);
echo $row->name . ' - ' . $row->age;
queryValue
Executes a SQL statement using PDO and returns a single value.
Executes a SQL statement using PDO exactly the same way $db->query does except only one value is returned. This is excellent for getting a single value like a name or a primary key. If more than more row or column would be returned by a given SQL query, using this method, only the first value on the first row would be returned.
$db->queryValue($sql, $placeholders = false, $debug = false)
Argument |
Type |
Description |
$sql * |
String |
The SQL to be executed |
$placeholders |
Array |
Associative array placeholders for binding to SQL.
array('name' => 'Fred', 'city' => 'Fargo') |
$debug |
Boolean |
If set to true , will output results and query information |
If there is an error executing the SQL, false
is returned. Otherwise, the data is returned as a single value.
// Execute a SQL query to return only one value
$sql = 'SELECT name FROM test_table WHERE id = 1';
$value = $db->queryValue($sql);
// Show the value
echo $value;
select
Retrieves data from a specified table using PDO.
This is different from $db->query because it does not require any SQL. If you need records from a single table, use this method.
$db->select($table, $values = '*', $where = false, $order = false, $debug = false)
Argument |
Type |
Description |
$table * |
String |
The table containing the records to be retrieved |
$values |
String|Array |
The list of fields to be returned. This can be a string like "name" or "state, zipcode" or an array of columns like array('id', 'amount') . If not specified, all fields will be returned. |
$where |
String|Array |
An array containing the fields and values like array("id" => 1, "active" => true) or array('id >' => 123, 'first_name IS NOT NULL') . This can also be a string containing a where clause without the beginning word WHERE like "id = 1 AND age > 18" |
$order |
String|Array |
An array or string containing field sort order. This can be a string like "name" or "state, zipcode" or an array of columns like array('id', 'amount') |
$debug |
Boolean |
If set to true , will output results and query information |
returns true
, or false
if there is an error executing the SQL.
// Query records from a table
$columns = array('id', 'name', 'age');
$where = array('active' => true);
$db->select('test_table', $columns, $where);
// We can make more complex where clauses in the select, update, and delete methods
$columns = array('id', 'name', 'age');
$where = array(
'active IS NOT NULL',
'id >' => 10,
'UPPER(name) LIKE "%JEN%"'
);
$db->select('test_table', $columns, $where);
// Let's sort by ID and run it in debug mode
$db->select('test_table', $columns, $where, 'id', true);
// loop through the results
while ($row = $db->fetch()) {
echo $row->name . '<br>';
}
// or fetch all the records then loop
// (this function should not be used if a huge number of rows have been selected, otherwise it will consume a lot of memory)
$rows = $db->fetchAll();
foreach($rows as $row) {
echo $row->name . '<br>';
}
selectRow
Retrieves data from a specified table using PDO and returns a single row as an array.
Retrieves data the same way $db->select does except only one row is returned. The syntax is nearly identical. If more than more row would be returned, using this method, only the first row would be returned.
This is different from $db->queryRow because it does not require any SQL. If you need a single row from a table, use this method.
$db->selectRow($table, $values = '*', $where = false, $debug = false, $fetch_parameters = PDO::FETCH_OBJ)
Argument |
Type |
Description |
$table * |
String |
The table containing the record to be retrieved |
$where |
String|Array |
An array containing the fields and values like array("id" => 1, "active" => true) or array('id >' => 123, 'first_name IS NOT NULL') . This can also be a string containing a where clause without the beginning word WHERE like "id = 1 AND age > 18" . |
$debug |
Boolean |
If set to true , will output results and query information |
$fetch_parameters |
Function parameter |
PDO fetch style record options (i.e. PDO::FETCH_OBJ , PDO::FETCH_ASSOC , PDO::FETCH_NUM , or PDO::FETCH_BOTH ) |
If there is an error retrieving the records, false
is returned. Otherwise, the data is returned as an associative array
where the key is the field name.
// If only one row is needed, use the selectRow method
$rows = $db->selectRow('test_table', 'name, age', 'id = 10');
selectValue
Retrieves data from a specified table using PDO and returns a single value.
Retrieves data the same way $db->select does except only one value is returned. This is excellent for getting a single value like a name or a primary key. If more than more row or column would be returned, using this method, only the first value on the first row would be returned.
This is different from $db->queryValue because it does not require any SQL. If you need a single value from the database, use this method.
$db->selectValue($table, $field, $where = false, $debug = false)
Argument |
Type |
Description |
$table * |
String |
The table containing the value to be retrieved |
$field |
String |
The name of the field to be returned |
$where |
String|Array |
An array containing the fields and values like array("id" => 1, "active" => true) or array('id >' => 123, 'first_name IS NOT NULL') . This can also be a string containing a where clause without the beginning word WHERE like "id = 1 AND age > 18" . |
$debug |
Boolean |
If set to true , will output results and query information |
If there is an error retrieving the records, false
is returned. Otherwise, the data is returned as an associative array
where the key is the field name.
// Grab one value - get the name of a person with ID 10
$value = $db->selectRow('test_table', 'name', array('id' => 10));
insert
Inserts a new record into a table using PDO.
This method will insert a row into a table. Behind the scenes, this method actually generates and executes a SQL INSERT statement returning the primary key of the new row. No SQL is required using this method.
$db->insert($table, $values, $debug = false)
Argument |
Type |
Description |
$table * |
String |
The table where the data will be inserted into the database |
$values |
Array |
The list of fields to be saved. This is an associative array of keys of the column names and their respective values like array('name' => 'Ted', 'amount' => 123.45) |
$debug |
Boolean |
If set to true , will output results and query information. Any insert transaction flagged as debug will be rolled back and will not be saved |
If there is an error inserting the record, false
is returned. Otherwise, the primary key is returned for the new row.
// Insert a new record
$values = array('name' => 'Riley', 'age' => 30, 'active' => false);
$success = $db->insert('test_table', $values);
// Try it in debug mode
$success = $db->insert('test_table', $values, true);
update
Updates an existing record into a table using PDO.
This method will update a row in a table. Behind the scenes, this method actually generates and executes a SQL UPDATE statement, but no SQL is required using this method.
$db->update($table, $values, $where = false, $debug = false)
Argument |
Type |
Description |
$table * |
String |
The table where the data will be updated |
$values |
Array |
The list of fields to be saved. This is an associative array of keys of the column names and their respective values like array('name' => 'Vick', 'customer' => true) |
$where |
String|Array |
An array containing the fields and values like array("id" => 1, "active" => true) or array('id >' => 123, 'first_name IS NOT NULL') . This can also be a string containing a where clause without the beginning word WHERE like "id = 1 AND age > 18" . If no where clause is specified, ALL records in the table will be updated. |
$debug |
Boolean |
If set to true , will output results and query information. Any update transaction flagged as debug will be rolled back and will not be saved |
If there is an error updating a record, false
is returned. Otherwise, true
is returned on success.
// Update an existing record
$update = array('age' => 35);
$where = array('name' => 'Riley');
$success = $db->update('test_table', $update, $where);
delete
Deletes a record from a table using PDO.
This method will delete a row in a table. Behind the scenes, this method actually generates and executes a SQL DELETE statement, but no SQL is required using this method.
$db->delete($table, $where = false, $debug = false)
Argument |
Type |
Description |
$table * |
String |
The table where the data will be updated |
$where |
String|Array |
An array containing the fields and values like array("id" => 1, "active" => true) or array('id >' => 123, 'first_name IS NOT NULL') . This can also be a string containing a where clause without the beginning word WHERE like "id = 1 AND age > 18" . If no where clause is specified, ALL records in the table will be deleted. |
$debug |
Boolean |
If set to true , will output results and query information. Any delete transaction flagged as debug will be rolled back and the records will not be deleted |
If there is an error deleting a record, false
is returned. Otherwise, true
is returned on success.
// Delete records
$where = array('active' => false);
$success = $db->delete('test_table', $where);
fetch
Fetches the next row from a result set and returns it according to the $fetch_parameters format.
Call this method to loop through the PDO statements
$db->fetch($fetch_parameters = \PDO::FETCH_OBJ)
Argument |
Type |
Description |
$fetch_parameters |
Function parameter |
PDO fetch style record options (i.e. PDO::FETCH_OBJ , PDO::FETCH_ASSOC , PDO::FETCH_NUM , or PDO::FETCH_BOTH ) |
Returns the next row from a result set
// Execute a SQL query
$sql = 'SELECT * FROM test_table';
$db->query($sql);
// loop through the results
while ($row = $db->fetch()) {
echo $row->name . '<br>';
}
// or retrive an array instead of an object
while ($row = $db->fetch(PDO::FETCH_ASSOC)) {
echo $row['name'] . '<br>';
}
fetchAll
Fetches all rows from a result set and return them according to the $fetch_parameters format.
Call this method to retrieve all the records into a single object and/or array
$db->fetchAll($fetch_parameters = \PDO::FETCH_OBJ)
Argument |
Type |
Description |
$fetch_parameters |
Function parameter |
PDO fetch style record options (i.e. PDO::FETCH_OBJ , PDO::FETCH_ASSOC , PDO::FETCH_NUM , or PDO::FETCH_BOTH ) |
Returns the all the rows from a result set
// Execute a SQL query
$sql = 'SELECT * FROM test_table';
$db->query($sql);
// fetch all the records then loop
// (this function should not be used if a huge number of rows have been selected, otherwise it will consume a lot of memory)
$rows = $db->fetchAll();
foreach($rows as $row) {
echo $row->name . '<br>';
}
// or retrieve an array instead of an object
$rows = $db->fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $row) {
echo $row['name'] . '<br>';
}
Transaction Processing
Allows for full transaction processing using PDO.
Transaction processing is used to maintain database integrity by ensuring that batches of MySQL operations execute completely or not at all.
Transaction processing is a mechanism used to manage sets of MySQL operations that must be executed in batches to ensure that databases never contain the results of partial operations. With transaction processing, you can ensure that sets of operations are not aborted mid-processing they either execute in their entirety or not at all (unless explicitly instructed otherwise). If no error occurs, the entire set of statements is committed (written) to the database tables. If an error does occur, a rollback (undo) can occur to restore the database to a known and safe state.
The key to managing transactions involves breaking your SQL statements into logical chunks and explicitly stating when data should be rolled back and when it should not.
NOTE: When using transaction processing, debugging mode will not rollback any inserts, updates, or deletes.
$db->transactionBegin();
$db->transactionCommit();
$db->transactionRollback();
If there is an error, false
is returned. Otherwise, true
is returned on success.
// Begin a transaction
$db->transactionBegin()
try {
// Perform various actions on the database here
// such as inserts, updates, and deletes
// If success, commit and save the entire transaction
$db->transactionCommit()
} catch (Exception $e) {
// If there was a problem, rollback the transaction
// as if no database actions here had ever happened
$db->transactionRollback()
// Show the error
var_dump($e->getMessage());
}
convertQueryToSimpleArray
Converts the array results from a Query() or Select() into a simple array using only one column or an associative array using another column as a key.
This method is useful for converting a query into a key => value pair.
$db->convertQueryToSimpleArray($array, $value_field, $key_field = false)
Argument |
Type |
Description |
$array * |
Array |
The results from a $db->fetchAll(PDO::FETCH_ASSOC) |
$value_field |
String |
The column to use for the new array |
$key_field |
String |
If used, causes this method to return an associative array using the field name as the key |
This method will convert an array that looks like this:
$db->select('states', 'state_code, state_name');
$result = $db->fetchAll(PDO::FETCH_ASSOC);
print_r($result);
Array
(
[0] => Array
(
[state_code] => AL
[state_name] => Alabama
)
[1] => Array
(
[state_code] => AK
[state_name] => Alaska
)
[2] => Array
(
[state_code] => AZ
[state_name] => Arizona
)
)
To an array that looks like this:
$array = $db->convertQueryToSimpleArray($result, 'state_name', 'state_code');
print_r($array);
Array
(
[AL] => Alabama
[AK] => Alaska
[AZ] => Arizona
)
getHTML
Executes a SQL statement using PDO and returns data as an HTML table.
This method generates HTML code.
$db->getHTML($sql, $placeholders = false, $showCount = true, $styleTable = null, $styleHeader = null, $styleData = null)
Argument |
Type |
Description |
$sql * |
String |
The SQL to be executed |
$placeholders |
Array |
Associative array placeholders for binding to SQL.
array('name' => 'Todd', 'city' => 'Tokyo') |
$showCount |
Boolean |
If set to true , shows a row count above the table |
$styleTable |
String |
Style information for the table |
$styleHeader |
String |
Style information for the header row |
$styleData |
String |
Style information for the cells |
If there is an error executing the SQL, false
is returned. Otherwise, the data is returned as HTML.
$html = $db->getHTML('SELECT * FROM test_table');
if ($html) {
echo $html;
} else {
echo 'There was an error in your SQL.';
}
emptyToNull
Converts empty values to NULL.
This support function will return null for database calls if a variable is empty.
$db->emptyToNull($value, $includeZero = true, $includeFalse = true, $includeBlankString = true)
Argument |
Type |
Description |
$value * |
String |
The value to process |
$includeZero |
Boolean |
If true , any zero value will return null |
$includeFalse |
Boolean |
If true , any false value will return null |
$includeBlankString |
Boolean |
If true , any blank string will return null |
Returns null if the value is empty, otherwise the original value is returned.
$values = array('amount' => $db->emptyToNull($amount));
getLastInsertId
Returns the id of the last inserted record.
$db->getLastInsertId()
This method returns the id of the last record inserted by an INSERT
query.
$id = $db->getLastInsertId();
rowCount
Returns the number of results of the last SQL SELECT statement.
$db->rowCount()
This method returns the number of records returned by a SELECT
query.
$db_count = $db->rowCount();
safe
Returns a quoted string using PDO that is safe to pass into an SQL statement.
This support function internally uses $db->quote() to place quotes around an input string (if required) and escapes special characters within the input string, using a quoting style appropriate to the underlying driver.
If you are using this function to build SQL statements, you are strongly recommended to use placeholders instead. Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries, as both the server and client side can cache a compiled form of the query.
Not all PDO drivers implement this method (notably PDO_ODBC).
$db->safe($value)
$values = array('name' => $db->safe($name));
errorEvent
This is an event function located at the top of the DB.php
file that is called every time there is an error.
If $show_errors is set to true
all the errors will be displayed on screen with the error number, code and details.
You can add code into this function (or override it when inheriting this class) to do things such as:
- Log errors into the database
- Send an email with the error message
- Save out to some type of log file
- Make a RESTful API call
- Run a script or program
- Set a session or global variable
- Or anything you might want to do when an error occurs
protected function errorEvent($error, $error_code = 0)
Argument |
Type |
Description |
$error * |
String |
The error message |
$error_code |
String |
An error code |
This method does not return any value.
MultipleDatabases
Using Multiple Databases
You can create as many different connections as you need by setting the connection informations when you instanciate the $db object.
// Connect to the database 1
$db1 = new DB('user_local', 'pass_local', 'development', 'localhost');
// Connect to the database 2
$db2 = new DB('appuser', 'pass123', 'production', 'my_server');