Hopper - Database Abstraction Layer (DBAL) for PHP

Version: v1.6.2

This library provides a simple and powerful way to SET and GET records from a PDO database. It has many powerful database interaction methods that have been developed over the past 10 years.

Install

To install with composer:

composer require truecastdesign/hopper

Requires PHP 5.5 or newer.

Usage

Here's a basic usage example:

# composer autoloader
require '/path/to/vendor/autoload.php';

# create a new instance of the Hopper class
$DB = new \Truecast\Hopper(['type'=>'mysql', 'username'=>'', 'password'=>'', 'database'=>'']);

# insert a record
$DB->set('table_name', ['first_name'=>'John', 'last_name'=>'Doe', 'phone'=>'541-555-5555', 'status'=>'live']);  # id:1
$DB->set('table_name', ['first_name'=>'Tim', 'last_name'=>'Baldwin', 'phone'=>'541-555-5551', 'status'=>'live']); # id:2

# update a record
$DB->set('table_name', ['id'=>1, 'phone'=>'541-555-5556']);

# execute your own update or insert query and check if it was successful
if ($DB->execute('update tablename set field1=?, field2=? where field3=?', ['val1', 'val2', 'val3'])) {
	# updated row
} else {
	# didn't update row
}

# execute your own update or insert query and check if it was successful
if ($DB->execute('insert into tablename set field1=?, field2=? where field3=?', ['val1', 'val2', 'val3'])) {
	# updated row
} else {
	# didn't update row
}

# get single record
$recordObj = $DB->get('select * from table_name where id=?', [1], 'object');
$recordArray = $DB->get('select * from table_name where id=?', [1], 'array');

# output:
stdClass Object ('id'=>1, first_name'=>'John', 'last_name'=>'Doe', 'phone'=>'541-555-5555', 'status'=>'live')
Array ('id'=>1, first_name'=>'John', 'last_name'=>'Doe', 'phone'=>'541-555-5555', 'status'=>'live')

# get a single value
$value = $DB->get('select first_name from table_name where id=?', [1], 'value');

# output
string 'John'

# get several records
$recordList = $DB->get('select id,first_name,last_name from table_name where status=?', ["live"], '2dim');

# output
Array (	[0]=>Array ('id'=>1, first_name'=>'John', 'last_name'=>'Doe')
		[1]=>Array ('id'=>2, 'first_name'=>'Tim', 'last_name'=>'Baldwin'))

Create config file in the format and the truecastdesign/config class to turn it into an object to pass to the construct. This allows you to store configuration in an ini file.

; MySQL database config

config_title = 'mysql'
type = 'mysql'
hostname = 'localhost'
username = 'root'
password  = 'password'
database  = 'dbname'
port = 3306
persistent = true
emulate_prepares = false
compress = true
charset = 'utf8'
buffer = true
sslCertAuthority = '' // The file path to the SSL certificate authority.
sslCaCertificates = '' // The file path to the directory that contains the trusted SSL CA certificates, which are stored in PEM format.
sslCert = '' // The file path to the SSL certificate.
sslCipher = '' // A list of one or more permissible ciphers to use for SSL encryption, in a format understood by OpenSSL. For example: DHE-RSA-AES256-SHA:AES128-SHA
sslKey = '' // The file path to the SSL key.
sslVerifyCert = '' // Provides a way to disable verification of the server SSL certificate.
multiStatements = '' // Disables multi query execution in both PDO::prepare() and PDO::query() when set to false.

Instantiate using a config file.

$DB = new \Truecast\Hopper($Config->mysql);

Delete a record

$DB->delete('table_name', 1); # deletes record with id=1

$DB->delete('table_name', 'value', 'field_name'); # deletes records with field_name='value'

There is a method for setting a record into a Scalable Key-Value table.

Scalable Key-Value table is a table that have field names like id, record_id, key, value. Each key-value pair in a records is stored in its own table row. This way you can dynamically add and remove fields you want to store.

$settings = ['record_id_field'=>'record_id', 'record_id'=>1, 'key_field'=>'field_name', 'value_field'=>'value'];

$DB->setScalableKeyValue('table_name', ['first_name'=>'John', 'last_name'=>'Doe', 'phone'=>'541-555-5555', 'status'=>'live'], $settings)

More method documentation coming as soon as I have time to write it up.