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)

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

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)

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)

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, $limit = false, $debug = false)

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)

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)

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)

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)

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)

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)

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)

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

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

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

/* will output something like this:

    object
      public 'Field' => string 'actor_id'
      public 'Type' => string 'smallint(5) unsigned'
      public 'Null' => string 'NO'
      public 'Key' => string 'PRI'
      public 'Default' => null
      public 'Extra' => string 'auto_increment'

    object
      public 'Field' => string 'first_name'
      public 'Type' => string 'varchar(45)'
      public 'Null' => string 'NO'
      public 'Key' => string ''
      public 'Default' => null
      public 'Extra' => string ''
*/

getColumnNames

Gets the columns names of the target table

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

$db->getColumnNames($table)

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

$columns_names = $db->getColumns('table');

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

/* will output something like this:

    array
      0 => string 'id'
      1 => string 'field_name'
      // ...
*/

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) {
    // if no table found
    echo 'No table found.';
} else {
    // loop the tables
    foreach($tables as $table) {
        echo $table . '<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)

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)

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)

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

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