adambinnersley/pdo-dbal

A simple database abstraction layer using PHP and PDO

PDO Database Abstraction Layer

A simple database abstraction layer for MySQL PDO

Installation

Installation is available via Composer/Packagist, you can add the following line to your composer.json file:

"adamb/database": "^1.0"

or

composer require adamb/database

Class Features

• Optional cache support with Memcache / Memcached / Redis / XCache
• Optional connection to secondary database incase the no connection to the primary MySQL server is available
• Connects to a MySQL database via PDO
• Simplify queries to SELECT / INSERT / UPDATE and DELETE
• Built in prepared statements
• Compatible with PHP5.6 and later

License

This software is distributed under the MIT license. Please read LICENSE for information on the software availability and distribution.

Usage

Example of usage can be found below with what queries they would result in (For security all queries are run using prepared statements with values added on execute() after the prepare() has been run)

1. Connect

<?php

$hostname = '127.0.0.1';
$username = 'root';
$password = '';
$database = 'my_db';
$backup_server = '127.0.0.2';

// Connect to a single MySQL server
$db = new DBAL\Database($hostname, $username, $password, $database);

// Connect to MySQL server and is primary server is down connect to secondary server
$db = new DBAL\Database($hostname, $username, $password, $database, $backup_server);

2. Select

$db->select('test_table');
// Query Run = "SELECT * FROM `test_table` LIMIT 1;"

$db->select('test_table', array('id' => 3));
// Query Run = "SELECT * FROM `test_table` WHERE `id` = 3 LIMIT 1;"

$db->select('test_table', array('id' => array('>=', 3)));
// Query Run = "SELECT * FROM `test_table` WHERE `id` >= 3 LIMIT 1;"

$db->select('test_table', array('id' => array('>=', 3)), array('name', 'email'));
// Query Run = "SELECT `name`, `email` FROM `test_table` WHERE `id` >= 3 LIMIT 1;"

$db->select('test_table', array('id' => array('>=', 3)), array('name', 'email'), array('id' => 'DESC'));
// Query Run = "SELECT `name`, `email` FROM `test_table` WHERE `id` >= 3 ORDER BY `id` DESC LIMIT 1;"

// Usage of IN or NOT IN operator
$db->select('test_table', array('id' => array('IN' => array(1, 2, 3))));
// Query Run = "SELECT * FROM `test_table` WHERE `id` IN (1,2,3) LIMIT 1;"

$db->select('test_table', array('id' => array('NOT IN' => array(2, 3))));
// Query Run = "SELECT * FROM `test_table` WHERE `id` NOT IN (2,3) LIMIT 1;"

// Usage of BETWEEN or NOT BETWEEN operator
$db->select('test_table', array('id' => array('BETWEEN' => array(1, 3))));
// Query Run = "SELECT * FROM `test_table` WHERE `id` BETWEEN 1 AND 3 LIMIT 1;"

$db->select('test_table', array('id' => array('NOT BETWEEN' => array(2, 10))));
// Query Run = "SELECT * FROM `test_table` WHERE `id` NOT BETWEEN 2 AND 10 LIMIT 1;"

// The same functions can be run using selectAll() rather than select()

$db->selectAll('test_table', array('id' => array('>=', 3)), array('name', 'email'), array('id' => 'DESC'), 150);
// Query Run = "SELECT `name`, `email` FROM `test_table` WHERE `id` >= 3 ORDER BY `id` DESC LIMIT 150;"

// Usage
// select($table, $where = array('field_name' => $value), $selectfield = array('field_1', 'field_2'), $order = array('field_name' => 'ASC' or 'DESC') or RAND());
// selectAll($table, $where = array('field_name' => $value), $selectfield = array('field_1', 'field_2'), $order = array('field_name' => 'ASC' or 'DESC') or RAND(), $limit(default = no limit));

3. Insert

$db->insert('test_table', array('name' => 'Bob', 'email' => '[email protected]'));
// Query Run = "INSERT INTO `test_table` (`name`, `email`) VALUES ('Bob', '[email protected]');"

// Usage
// insert($table, array('field_name' => $value));

4. Update

$db->update('test_table', array('name' => 'Ken', 'email' => '[email protected]'), array('id' => 12345));
// Query Run = "UPDATE `test_table` SET (`name` => 'Ken', `email` => '[email protected]') WHERE `id` = 12345;"

$db->update('test_table', array('name' => 'Ken'), array('name' => 'Bob'), 50);
// Query Run = "UPDATE `test_table` SET (`name` => 'Ken') WHERE `name` = 'Bob' LIMIT 50;"

// Usage
// update($table, $updatevalues = array('field_name' => $value), $where = array('field' => $value));

5. Delete

$db->delete('test_table', array('id' => 1));
// Query Run = "DELETE FROM `test_table` WHERE `id` = 1;"

$db->delete('test_table', array('name' => 'Ted'), 50);
// Query Run = "DELETE FROM `test_table` WHERE `name` = 'Ted' LIMIT 50;"

// Usage
// delete($table, $where = array('field' => $value), $limit);

6. Count

$db->count('test_table');
// Query Run = "SELECT COUNT(*) FROM `test_table`;";

$db->count('test_table', array('name' => 'Bob'));
// Query Run = "SELECT COUNT(*) FROM `test_table` WHERE `name` => 'Bob';";

// Usage
// count($table, $where = array('field' => $value));

7. Query

Any other queries can be run using the query command by passing the SQL query and values

$db->query("SELECT * FROM `test_table` WHERE `name` = ? OR `name` => ?;", array('John', 'Phil'));
// Query Run = "SELECT * FROM `test_table` WHERE `name` = 'John' OR `name` => 'Phil';";

$db->query("UPDATE `test_table` SET `name` = 'Karl' WHERE `name` = ? OR `name` => ?;", array('John', 'Phil'));
// Query Run = "UPDATE `test_table` SET `name` = 'Karl' WHERE `name` = 'John' OR `name` => 'Phil';";

$db->query("SELECT * FROM `test_table` WHERE `field1` = ? AND (`field2` => ? OR `field3` => ?);", array('value1', 'value2', 'value3'));
// Query Run = "SELECT * FROM `test_table` WHERE `field1` = 'value1' AND (`field2` => 'value2' OR `field3` => 'value3');";

# UNION
$db->query("SELECT * FROM `test_table` WHERE `field1` = ? UNION SELECT * FROM `another_table` WHERE `another_field` = ?;", array('value1', 'value2'));
// Query Run = SELECT * FROM `test_table` WHERE `field1` = 'value1' UNION SELECT * FROM `another_table` WHERE `another_field` = 'value2';";

$db->query("SELECT * FROM `test_table` WHERE `field1` = :search UNION SELECT * FROM `another_table` WHERE `another_field` = :search;", array(':search' => 'value1'));
// Query Run = SELECT * FROM `test_table` WHERE `field1` = 'value1' UNION SELECT * FROM `another_table` WHERE `another_field` = 'value1';";

# JOIN
$db->query("SELECT `test_table`.`field1`, `test_table`.`field2`, `another_table`.`field1` FROM `test_table` INNER JOIN `another_table` ON `test_table`.`reference_field` = `another_table`.`some_field`;");
// Query Run = SELECT `test_table`.`field1`, `test_table`.`field2`, `another_table`.`field1` FROM `test_table` INNER JOIN `another_table` ON `test_table`.`reference_field` = `another_table`.`some_field`;";

$db->query("SELECT `test_table`.`field1`, `test_table`.`field2`, `another_table`.`field1` FROM `test_table` INNER JOIN `another_table` ON `test_table`.`reference_field` = `another_table`.`some_field` AND `test_table`.`field1` <= ? LIMIT 100;", array('value'));
// Query Run = SELECT `test_table`.`field1`, `test_table`.`field2`, `another_table`.`field1` FROM `test_table` INNER JOIN `another_table` ON `test_table`.`reference_field` = `another_table`.`some_field` AND `test_table`.`field1` <= 'value' LIMIT 100;";

8. FetchColumn

// This is similar to the select method except return the column value rather than an array of all of the fields 

$column = $db->fetchColumn('test_table', array('id' => 3), array('name', 'email'));
// Query Run = "SELECT `name`, `email` FROM `test_table` WHERE `id` = 3 LIMIT 1;"
echo($column[0]); // will return the name field
echo($column[1]); // will return the email field

$column = $db->fetchColumn('test_table', array('id' => 3), array('name', 'email'), 1);
echo($column); // will return email as the field number of 1 has been set

9. NumRows

$db->numRows();
$db->rowCount();

// Running either of these methods after executing a query will show you how many rows have been affected e.g.
$db->update('test_table', array('name' => 'Ken'), array('name' => 'Bob'));
echo($db->numRows()); // Returns number of results updated e.g. 12

10. LastInsertId

$db->insert('test_table', array('name' => 'Bob', 'email' => '[email protected]'));
echo($db->lastInsertId()); // Returns the key field value number normally the the auto increment field value

11. isConnected

$db->isConnected(); // Returns true of false depending on if the connection is active to the server

12. Caching

Database caching can be added to queries with support for Memcache / Memcached / Redis and XCache

$caching = new DBAL\Caching\Memcached();
$db = new DBAL\Database($hostname, $username, $password, $database, $backup_server, $caching);

// OR

$caching = new DBAL\Caching\Memcached();
$db = new DBAL\Database($hostname, $username, $password, $database);
$db->setCaching($caching);

pdo-dbal's Issues

PHP 7.3/7.4 Notice warning

Hey,

Super cool package - I've enjoyed using it. After upgrading PHP to 7.4 (skipping 7.3) most of my requests now gives the following warning:

Notice: Trying to access array offset on value of type int in /usr/www/users/webinstance/cmp/vendor/adamb/database/src/Database.php on line 560

It does not seem that functionality is affected.

Best,
Albert

Incompatible to php 5.3

Short array syntax has been added in 5.4

Add ability to change type

Method query() does not log queries.

Unlike in other methods, writeQueryToLog() is not called.

Documentation for IN() statements

Can you provide documentation for the following implementation:

SELECT * FROM table_name WHERE field_name IN (values)

I tried using the following but its not working:

$dbInstance->selectAll( 'table_name', [ 'field_name ' => [ 'IN' => [ 'value1', 'value2' ] ] ] );

The returned SQL is:

SELECT * FROM table_nameWHEREfield_name %s ?;

Which is using the format from the IN operator but not really what I'm looking for.

Should I be using a different call or $where array structure?

values is persistent sometimes

I am trying to write a project using pdo-dbal, however when I make more than one query in a call using the MemcacheCache wrapper I am getting persistence in the values class variable.

See below. The first entry is the first query on the page, the second query is the next. The second query still contains the first query's values despite not being passed in. The second image shows the second query's dbal call.

I tried changing the first call's synonymous column of id to lobbyID thinking it was being cached that way, but this did not fix anything.

I can fix this by making values public, and setting it to [] just like what \DBAL\Database\executeQuery() tries to do, but somehow fails in doing so; I can also fix it by clearing values as the first action in \DBAL\Database\select(), see below.

I tried removing the unset() calls, and I tried moving values = [] to right after the bind, but neither of those solved the issue, only clearing values from outside the class.

If I could get xdebug working I could try to narrow it down further, but I guess that isn't happening.

Log location

Log location is not valid and allow ability to change log location

Add a option for fetchColumn

Unable to use custom log location for connection errors.

Since setLogLocation() is available only after initialization, connection error in constructor falls back to the default log location.

Unit testing?

Write documentation

Documentation for Select

Can you provide documentation for the following implementation:

1. SELECT * FROM table_name WHERE field_name1 = value1 AND/OR ( field_name2 = value2 AND/OR field_name3 = value3).
2. JOIN using in SELECT

Thank you in advance!

Add option to change the port number when connecting

Support for more operators

Look to support all of the operators avaliable or streamline current system in the where queries. e.g. BETWEEN, NOT BETWEEN, IN, NOT IN, LIKE, NOT LIKE, etc...

In case of an error, execution goes on.

If an error happens and displayErrors is off - execution goes on.
It must stop independently from displayErrors setting.