llwebsol/easy-db

A Wrapper for PDO

EasyDB

A Simple Wrapper for PDO, _(*1)

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

Getting Started

using composer, _(*3)

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, _(*4)

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., _(*5)

$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, _(*6)

- Query

accepts any database query you desire, with an optional array of bound parameters, _(*7)

returns a Generator for iterating through your result set, _(*8)

$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, _(*9)

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

$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, _(*11)

$data = [
    'name' => 'Chris',
    'email' => 'chris@landlordwebsolutions.com'
];

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

- Update

Update a record in a given table returns the number of rows affected, _(*12)

$data = [
    'email' => 'new.email@email.com'
];

$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, _(*13)

- Delete

Delete a record from a given table returns the number of rows affected or false if invalid, _(*14)

// 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, _(*15)

// 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, _(*16)

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 ), _(*17)

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

SQL Equivalent:, _(*18)

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, _(*19)

Supported Events: - ON_ERROR - BEFORE_QUERY - AFTER_QUERY - BEFORE_UPDATE - AFTER_UPDATE - BEFORE_INSERT - AFTER_INSERT - BEFORE_DELETE - AFTER_DELETE, _(*20)

Helpers: - BEFORE_SAVE ( BEFORE_INSERT and BEFORE_UPDATE) - AFTER_SAVE (AFTER_INSERT and AFTER_UPDATE), _(*21)

Examples

Echo the sql of every query that is performed, _(*22)

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, _(*23)

*Assumes all of your tables have a created_user column, _(*24)

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();, _(*25)

$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;

} ```, _(*26)