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
• The debugging system has been improved
• Some additional functions have been added:
  • $db->error()
  • $db->fetch()
  • $db->fetchAll()
  • $db->getColumns()
  • $db->getColumnsNames()
  • $db->getTables()
  • $db->rowCount()
  • $db->selectCount()
  • $db->setDebugMode()
  • $db->getDebugContent()
• 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

About the code examples in this documentation

We use a customized version of the "classicmodels" database as a MySQL sample database.

Download the SQL file at https://www.phpformbuilder.pro/documentation/phpformbuildersampledatabase.sql.zip

Original source: https://www.mysqltutorial.org/mysql-sample-database.aspx

__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.

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 the function doesn't return anything.

// 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 all the encountered errors automatically
$db = new DB(true);

// or connect, then test the connection and retrieve the error if the database is not connected
$db = new DB();
if (!$db->isConnected()) {
    $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)

// Execute a SQL query and return whether it was successful or not
// (FYI: You can also use $db->safe() to return a safe quoted string for SQL)
$sql = "INSERT INTO productlines (id, name, description) VALUES (null, 'bikes', 'Lorem ipsum')";
$id = $db->execute($sql);

// Execute a SQL query with placeholders (better because it stops SQL Injection hacks)
$sql = 'DELETE FROM productlines WHERE name = :name AND description = :description';
$values = array('name' => 'bikes', 'description' => 'Lorem ipsum');
$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)

returns true, or false if there is an error executing the SQL.

// Execute a SQL query to return an object containing all rows
$sql = "SELECT * FROM customers WHERE country = 'Indonesia'";
$db->query($sql);

// Execute the same query in debug mode
$db->query($sql, array(), true);

// Execute the same query using placeholders (better because it stops SQL Injection hacks)
$sql = "SELECT id, first_name, last_name FROM customers WHERE country = :country";
$values = array('country' => 'Indonesia');
$db->query($sql, $values);

// Execute the same query in debug mode
$db->query($sql, $values, true);

// loop through the results
while ($row = $db->fetch()) {
    echo $row->first_name . ' ' . $row->last_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->first_name . ' ' . $row->last_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)

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 first_name, last_name FROM customers WHERE id = :id LIMIT 1';
$row = $db->queryRow($sql, array('id' => 5));

echo $row->first_name . ' - ' . $row->last_name;

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)

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 last_name FROM customers WHERE id = 1';
$value = $db->queryValue($sql);

// Show the value
echo $value;

select

Retrieves data from a specified table with the possible joins 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($from, $values = '*', $where = false, $extras = array(), $debug = false)

returns true, or false if there is an error executing the SQL.

// Select rows without using SQL
$values = array('id', 'first_name', 'last_name');
$where = array('country' => 'Indonesia');
$db->select('customers', $values, $where);

// We can make more complex where clauses in the Select, Update, and Delete methods
$values = array('id', 'first_name', 'last_name');
$where = array(
    'zip_code IS NOT NULL',
    'id >' => 10,
    'last_name LIKE' => '%Ge%'
);
$db->select('customers', $values, $where);

// Let's sort by descending ID and run it in debug mode
$extras = array('order_by' => 'id DESC');
$db->select('customers', $values, $where, $extras, true);


// loop through the results
while ($row = $db->fetch()) {
    echo $row->first_name . ' ' . $row->last_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->first_name . ' ' . $row->last_name . '<br>';
}

selectCount

Count the number of records in a table, or the number of not-null values in one or several fields.

Retrieves data the same way $db->select does except only one row is returned. The syntax is nearly identical.

$row = $db->selectCount($from, $values = array('*' => 'rows_count'), $where = false, $debug = false);

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 alias of the field name.

// Count the number of records in the 'customers' table
$row = $db->selectCount('customers');
echo '
Customers count: ' . $row->rows_count . '
';

// Count the number of customers zip codes and states that are not null
$values = array(
    'zip_code' => 'zip_code_count',
    'state'    => 'state_count'
);
$row = $db->selectCount('customers', $values);
echo '
' . $row->zip_code_count . ' customers zip codes are not null.
';
echo '
' . $row->state_count . ' customers states are not null.
';

selectRow

Retrieves data from a specified table with the possible joins 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($from, $values = '*', $where = false, $debug = false, $fetch_parameters = \PDO::FETCH_OBJ)

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
$row = $db->selectRow('customers', '*', array('id' => 12));
// Show some of the values
echo $row->first_name . ' ' . $row->last_name;

selectValue

Retrieves data from a specified table with the possible joins 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($from, $field, $where = false, $debug = false)

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 email of the customer in the record with ID 32
$value = $db->selectValue('customers', 'email', array('id' => 32));

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)

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('id' => null, 'customers_id' => 5, 'payment_date' => '2022-05-11', 'amount' => 2224.5);
$success = $db->insert('payments', $values);

// Show the last insert id
if ($success) {
    echo 'Last insert id is: ' . $db->getLastInsertId();
}

// Try it in debug mode
// In debug mode, the record will not be saved
$success = $db->insert('payments', $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)

If there is an error updating a record, false is returned. Otherwise, true is returned on success.

// Update an existing record
$update = array('amount' => 3565);
$where  = array('customers_id' => 5, 'payment_date' => '2022-05-11');
$success = $db->update('payments', $update, $where);

// Try it in debug mode
// In debug mode, the record will not be updated
$success = $db->update('payments', $update, $where, true);

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)

If there is an error deleting a record, false is returned. Otherwise, true is returned on success.

// Delete records
$where  = array('active' => false);
$where  = array('customers_id' => 5, 'payment_date' => '2022-05-11');
$success = $db->delete('payments', $where);

// Try it in debug mode
// In debug mode, the record will not be deleted
$success = $db->delete('payments', $where, true);

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)

Returns the next row from a result set

// Select rows first
$values = array('id', 'first_name', 'last_name');
$where = array('country' => 'Indonesia');
$db->select('customers', $values, $where);

// 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)

Returns the all the rows from a result set

// Select rows first
$values = array('id', 'first_name', 'last_name');
$where = array('country' => 'Indonesia');
$db->select('customers', $values, $where);

// 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>';
}

getColumns

Gets the information about the columns in a given table

Call this method to retrieve information about the columns in a given table.

$db->getColumns($table, $fetch_parameters = \PDO::FETCH_OBJ)

Returns associative array that contains the columns data or false if the table doesn't have any column.

$columns = $db->getColumns('payments');

if (!$columns) {
    echo 'No column found.';
} else {
    // loop the columns
    foreach ($columns as $column) {
        var_dump($column);
    }
}

/* will output something like this:

object(stdClass)[4]
  public 'Field' => string 'id' (length=2)
  public 'Type' => string 'int(10) unsigned' (length=16)
  public 'Null' => string 'NO' (length=2)
  public 'Key' => string 'PRI' (length=3)
  public 'Default' => null
  public 'Extra' => string 'auto_increment' (length=14),

    // etc.
*/

getColumnsNames

Gets the columns names of the target table

Call this method to retrieve the columns names in a given table.

$db->getColumnsNames($table)

Returns an array that contains the columns names or false if the table doesn't have any column.

$columns_names = $db->getColumnsNames('payments');

if (!$columns_names) {
    echo 'No column found.';
} else {
    var_dump($columns_names);
}

/* will output something like this:

array (size=4)
  0 => string 'id' (length=2)
  1 => string 'customers_id' (length=12)
  2 => string 'payment_date' (length=12)
  3 => string 'amount' (length=6)
*/

getTables

Selects all the tables into the database

Call this method to retrieve all the tables of the database into an array

$db->getTables()

Returns the all the tables from the active database, or false if no table is found.

// Retrieve the tables from the database into an array
            $tables = $db->getTables();

if (!$tables) {
    echo 'No table found.';
} else {
    echo '
';
    // loop the tables
    foreach ($tables as $table) {
        echo $table . '
';
    }
    echo '
';
}

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. You have to call the $db->transactionRollback(); function by yourself.

$db->transactionBegin();
$db->transactionCommit();
$db->transactionRollback();

If there is an error, false is returned. Otherwise, true is returned on success.

Example 1 - single request

// Begin a transaction
$db->transactionBegin();
try {
    // Perform any action on the database here
    // such as insert, update, and delete
    $success = $db->insert($table, $values);

    // If success, commit and save the transaction
    if ($success) {
        $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 if the DB class don't show it by itself
    if (!$db->show_errors) {
        echo $e->getMessage();
    }
}

Example 2 - multiple conditional requests

// Begin a transaction
$db->transactionBegin();
try {
    if ($db->insert($table, $values)) {
        if ($db->delete($table, $where)) {
            // 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 if the DB class doesn't show it by itself
    if (!$db->show_errors) {
        echo $e->getMessage();
    }
}

Example 3 - multiple conditional requests with negative logic

// Begin a transaction
$db->transactionBegin();
try {
    if (!$db->insert($table, $values)) {
        throw new \Exception($db->error());
    }

    // continue if no error has been thrown before
    if (!$db->insert($table_2, $values_2)) {
        throw new \Exception($db->error());
    }

    // commit and save the entire transaction
    // only if no error has been thrown before
    $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 if the DB class doesn't show it by itself
    if (!$db->show_errors) {
        echo $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)

This method will convert an array that looks like this:

$db->select('customers', 'city, country', false, array('order_by' => 'country', 'limit' => 5));
$result = $db->fetchAll(\PDO::FETCH_ASSOC);
var_dump($result);

// output:
array (size=5)
  0 =>
    array (size=2)
      'city' => string 'Chahār Burj'
      'country' => string 'Afghanistan'
  1 =>
    array (size=2)
      'city' => string 'Derjan'
      'country' => string 'Albania'
  2 =>
    array (size=2)
      'city' => string 'Cintra'
      'country' => string 'Argentina'
  3 =>
    array (size=2)
      'city' => string 'Empedrado'
      'country' => string 'Argentina'
  4 =>
    array (size=2)
      'city' => string 'Jardín América'
      'country' => string 'Argentina'

To an array that looks like this:

$array = $db->convertQueryToSimpleArray($result, 'city', 'country');
var_dump($array);

// output:
array (size=3)
  'Afghanistan' => string 'Chahār Burj'
  'Albania' => string 'Derjan'
  'Argentina' => string 'Jardín América'
)

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)

If there is an error executing the SQL, false is returned. Otherwise, the data is returned as HTML.

$html = $db->getHTML('SELECT * FROM customers LIMIT 3');
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)

Returns null if the value is empty, otherwise the original value is returned.

$values = array('amount' => $db->emptyToNull($amount));

error

Returns the last encountered error, or an empty string if no error.

$db->error()

// Create a new DB object with $show_errors turned off
$db = new DB();

// make some requests, then show the errors when you want to
echo $db->error();

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();

isConnected

Returns the status of the database connection (true/false).

$db->isConnected()

Example of use:

if (!$db->isConnected()) {
    // the connection failed ...
    echo $db->error();
} else {
    // we are connected...
}
            

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)

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');

Debugging with the DB class

The DB class allows you to activate debugging at two different levels:

• The $show_errors argument of the __construct function
• The $debug argument of the requests functions

1. The $show_errors argument of the __construct function

   If you activate it, all the encountered errors will be displayed on screen (connection failure, unsuccessful requests, ...).

2. The $debug argument

   All the request functions have a $debug argument.
   This argument enables full debugging of the performance, queries and arguments used by the PDO object.
   Depending on the debugging mode being used, the results will either be displayed on screen or registered in the $db object.
   You can choose the debugging mode using the setDebugMode($debug_mode) function
   You can get the debug content using the getDebugContent() function

setDebugMode

Sets the active debugging mode

$db->setDebugMode($debug_mode)

getDebugContent

Returns the registered debugging information when the debug mode has been set to 'register'.

$db->getDebugContent($mode = 'html')