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

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

PHP Form Builder Sample database schema
PHP Form Builder Sample database schema

__construct

Creates the DB object and & connects to a MySQL PDO database.

$db = new DB($show_errors = false, $driver = PDO_DRIVER, $hostname = DB_HOST, $database = DB_NAME, $username = DB_USER, $password = DB_PASS, $port = DB_PORT);
  1. Define the PDO_DRIVER, DB_HOST, DB_NAME, DB_USER, DB_PASS, and DB_PORT 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.
$driver String An available PDO driver which can be used in DSN parameter of PDO::__construct().
Default: PDO_DRIVER
$hostname String Host name of the server
Default: DB_HOST
$database String Database or schema name
Default: DB_NAME
$username String Database user name.
Default: DB_USER
$password String Database password
Default: DB_PASS
$port String The port for the connection. Default port if empty.

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.

About Oracle databases (PDO_OCI driver) and date formatting

Oracle manages the dates according to its internally configured format.
To ensure consistent date management the DB class automatically sets the date management format to the MySQL standard with the following query: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'

// 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)
Argument Type Description
$sql * String The SQL to be executed
$placeholders Array|Boolean 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. In debug mode the insert, update and delete requests are simulated, no change is made to your database.

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 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)
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 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)
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 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)
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 last_name FROM customers WHERE id = 1 LIMIT 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)
Argument Type Description
$from * String The table with the possible joins 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"
$extras Array An array containing the optional following pairs of key => values:
Key Values Default
'select_distinct' If set to true the query will use SELECT DISTINCT instead of SELECT. false
'order_by' Array or string containing field order, or null to not specify any order. This can be a string like "name" or "state, zipcode" or an array of columns like array('id', 'amount') null
'limit' Integer, or string containing the offset and maximum number of results, or null to not specify any limit. E.g:
'limit' => 10 or 'limit' => '10, 20'
Use this syntax regardless of your PDO driver.
null
$debug Boolean If set to true, will output results and query information

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);
Argument Type Description
$from * String The table with the possible joins containing the record to be retrieved
$values String|Array Array of fieldnames => aliases to be returned. Default will count the number of records and return it with the rows_count alias.
If you choose one or several fields it'll return the number of NOT NULL values for each one of them.
$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 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)
Argument Type Description
$from * String The table with the possible joins 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
$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)
Argument Type Description
$from * String The table with the possible joins 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 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. 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

Returns true on success, or false if there is an error inserting the record.

// Insert a new record
$values = array('id' => null, 'customers_id' => 5, 'payment_date' => '2022-05-11', 'amount' => 2224.5);
if ($db->insert('payments', $values)) {
    // Warning: getLastInsertId() doesn't work with some PDO drivers. If so, use $db->getMaximumValue($table, $field, $debug) instead.
    $last_insert_id = $db->getLastInsertId();
    echo 'Last insert id is: ' . $last_insert_id;
} else {
    echo $db->error();
}

// Try it in debug mode
// In debug mode, the record will not be saved
$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)
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('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($from, $where = false, $debug = false)
Argument Type Description
$from * String The table with the possible joins containing the records to be deleted
$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);
$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)
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

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

// 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, $debug = false)
Argument Type Description
$table String The name of the target table
$fetch_parameters Function parameter PDO fetch style record options (i.e. \PDO::FETCH_OBJ, \PDO::FETCH_ASSOC, \PDO::FETCH_NUM, or \PDO::FETCH_BOTH)
$debug Boolean If set to true, will output results and query information.

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, $debug = false)
Argument Type Description
$table String The name of the target table
$debug Boolean If set to true, will output results and query information.

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($debug = false)
Argument Type Description
$debug Boolean If set to true, will output results and query information.

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 '

'; }

inTransaction

Checks if inside a transaction.

Some SQL statements cause an Implicit Commit (https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html).
These are basically the CREATE ..., DROP ..., ALTER ... requests.

Call this method to check if a transaction has been started

$db->inTransaction()

Returns true or false.

// Checks if inside a transaction
if ($db->inTransaction()) {
    $db->transactionRollback();
}

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.

NOTE: Some statements cause an Implicit Commit. You can check if a transaction is active with the inTransaction() function.

$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
    $last_insert_id = $db->insert($table, $values);

    // If success, commit and save the transaction
    if ($last_insert_id !== false) {
        $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) !== false) {
        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 {
    $last_insert_id = $db->insert($table, $values);
    if ($last_insert_id === false) {
        throw new \Exception($db->error());
    }

    // continue if no error has been thrown before
    if ($db->insert($table_2, $values_2) === false) {
        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)
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('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

Returns a records set as an HTML table.

This method generates HTML code.

$db->getHTML($records, $show_count = true, $table_attr = null, $th_attr = null, $td_attr = null)
Argument Type Description
$records * Array The records set - can be an array or array of objects according to the fetch parameters.
$show_count Boolean If set to true, shows a row count above the table
$table_attr String Comma separated attributes for the table. e.g: 'class=my-class, style=color:#222'
$th_attr String Comma separated attributes for the header row. e.g: 'class=my-class, style=font-weight:bold'
$td_attr String Comma separated attributes for the cells. e.g: 'class=my-class, style=font-weight:normal'

The data is returned as HTML.

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

// fetch all the records
$rows = $db->fetchAll();

// then pass them to the getHTML function to build the HTML table
$html = $db->getHTML($rows);

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, $include_zero = true, $include_false = true, $include_blank_string = true)
Argument Type Description
$value * String The value to process
$include_zero Boolean If true, any zero value will return null
$include_false Boolean If true, any false value will return null
$include_blank_string 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));

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

getPdo

Returns the PDO object for external use

$db->getPdo()

getPdoDriver

Returns the active PDO driver

$driver = $db->getPdoDriver()

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.

Warning:getLastInsertId() doesn't work with some PDO drivers. If so, use $db->getMaximumValue($table, $field, $debug) instead.

$id = $db->getLastInsertId();

getMaximumValue

Returns the maximum value of a table field.

$db->getMaximumValue($table, $field, $debug)

This method is useful mainly for retrieving the last identifier of a record, for PDO drivers that do not support getLastInsertId().

Argument Type Description
$table String The name of the target table.
$field String The name of the target field.
$debug Boolean If set to true, will output results and query information.
$id = $db->getMaximumValue($table, $field, $debug);

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

Debugging with the DB class

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

  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)
Argument Type Description
$debug_mode * String The debugging mode to enable. 'echo' or 'register'
  • If 'echo' the debugging information will be displayed directly on the screen on each database request.
  • If 'register' the debugging information is registered internally and can then be retrieved using the getDebugContent() function

getDebugContent

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

$db->getDebugContent($mode = 'html')
Argument Type Description
$mode * String The mode to retrieve the debug output. 'html' or 'json'
The 'json' mode returns a JSON encoded string with the HTML debug content inside.
Useful especially to get the content with Javascript and Ajax.