EasyDB

A Simple Wrapper for PDO

To get the most out of this library, every table in your database should have a primary key called id.

Getting Started

using composer

composer require llwebsol/easy-db

1. Create a Config

$config = new EasyDb\Core\Config([
    'db_type' => 'mysql',
    'host' => 'localhost',
    'db_name' => 'my_test_db',
    'user' => 'root',
    'password' => ''
]);

Accepted db_type options:

• mysql
• pgsql
• sqlite*
• sqlsrv*

*currently untested

Complete List of Options:

db_type
host
db_name
port
user
password

// mysql specific:
unix_socket
charset

// sqlsrv specific:
app
connection_pooling
encrypt
failover_partner
login_timeout
multiple_active_result_sets
quoted_id
server
trace_file
trace_on
transaction_isolation
trust_server_certificate
wsid

// sqlite specific:
path

2. Get a DB Instance

Use the ConnectionPool to retrieve a database instance for a given configuration.

$db = ConnectionPool::getDbInstance($config);

3. Querying the Database

Once you have an instance of the DB class, there are several helper methods available to you

- Query

accepts any database query you desire, with an optional array of bound parameters

returns a Generator for iterating through your result set

$query = 'SELECT * FROM users WHERE name = :user_name';
$params = [':user_name' => 'Ted'];

$users = $db->query($query,$params);

- QueryOne

helper for getting a single record from the database

Returns the record as an array, (not wrapped in a generator)

$query = 'SELECT * FROM users WHERE id = :user_id';
$params = [':user_id' => 7]

$user = $db->queryOne($query,$params);

- Insert

Insert a record into a given table Returns the last inserted id

$data = [
    'name' => 'Chris',
    'email' => '[email protected]'
];

$inserted_id = $db->insert('users', $data);

- Update

Update a record in a given table returns the number of rows affected

$data = [
    'email' => '[email protected]'
];

$rows_affected = $db->update('users', 76, $data);

- Save

this is just an alias for insert/update if the $data has an 'id' field it will update, otherwise it will insert

- Delete

Delete a record from a given table returns the number of rows affected or false if invalid

// Delete the record with id=76 from 'users'
$rows_deleted = $db->delete('users', 76);

- Delete Where

Delete records from a given table that meet the conditions of the where clause Returns the number of rows deleted

// Delete all clients from Toronto or New York with a name starting with 'T'

$where = 'name LIKE :name_compare AND city_id IN (:toronto_id,:new_york_id)';
$params = [
    ':name_compare' =>  't%',
    ':toronto_id' => 5142,
    ':new_york_id' => 1432
];

$records_deleted = $db->deleteWhere('clients', $where, $params);

- Update Where

Update records from a given table that meet the conditions of the where clause Returns the number of rows updated

Set Status to 'disabled' for all users with hotmail accounts

$update = [ 'status' => 'disabled' ];
$where  = 'email LIKE :email_compare';
$params = [ ':email_compare' => '%@hotmail.com' ]

$rows_updated = $db->updateWhere('users', $update, $where, $params);

- Find In

Returns a Generator with all records in table where $column_name IN ( $in_array )

$records = $db->findIn('clients', 'city_id', [5142,1432,76,222]);

SQL Equivalent:

SELECT *
FROM clients
WHERE city_id IN (5142,1432,76,222);

4. Events

You can add event listeners for any stage of a database interaction

Supported Events:

• ON_ERROR
• BEFORE_QUERY
• AFTER_QUERY
• BEFORE_UPDATE
• AFTER_UPDATE
• BEFORE_INSERT
• AFTER_INSERT
• BEFORE_DELETE
• AFTER_DELETE

Helpers:

• BEFORE_SAVE ( BEFORE_INSERT and BEFORE_UPDATE)
• AFTER_SAVE (AFTER_INSERT and AFTER_UPDATE)

Examples

Echo the sql of every query that is performed

use EasyDb\Events\Listener;

class QueryListener implements Listener
{
    /**
     * @param EventData $data
     * @param array     &$ref_parameters [optional]
     */
    public static function handleEvent(EventData $data, array &$ref_parameters = []){
        echo $data->getSql();
    }
}

// Register the listener
Listeners::register(Event::BEFORE_QUERY, QueryListener::class);

Add a user id to all inserted records

*Assumes all of your tables have a created_user column

use EasyDb\Events\Listener;

class InsertListener implements Listener
{
    /**
     * @param EventData $data
     * @param array     &$ref_parameters [optional]
     */
    public static function handleEvent(EventData $data, array &$ref_parameters = []){
        $ref_parameters['created_user'] = $_SESSION['user'];
    }
}

// Register the listener
Listeners::register(Event::BEFORE_QUERY, InsertListener::class);

*Referenced Parameters are available for BEFORE_INSERT and BEFORE_UPDATE events only

5. Transactions

You can perform multiple actions with a single transaction with the following 3 methods

• beginTransaction
• commitTransaction
• rollbackTransaction

Example:

/**
* @param array $save_records
* @param DB    $db
*
* @return int $records_saved
*/
function save_a_bunch_of_records(array $save_records, DB $db){
    $db->beginTransaction();

    $records_saved = 0;
    foreach($save_records as $table_name => $record){
        try{
            $records_saved += $db->save($table_name, $record);
        }
        catch(QueryException $ex){

            // All or nothing. Undo all previous saves
            $db->rollbackTransaction();

            return false;
        }
    }

    $db->commitTransaction();
    return $records_saved;
}