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:

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:

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)
  1. Define the DBUSER, DBPASS, DBNAME and DBHOST constants in phpformbuilder/database/db-connect.php
  2. 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:

  1. Log errors into the database
  2. Send an email with the error message
  3. Save out to some type of log file
  4. Make a RESTful API call
  5. Run a script or program
  6. Set a session or global variable
  7. 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');