README

A complete database toolkit written in PHP to handle PDO statements

Installation

Use the package manager composer to install the library

composer require riculum/php-pdo

Initial setup

Credentials

The basic database settings can be set through environment variables. Add a .env file in the root of your project. Make sure the .env file is added to your .gitignore so it is not checked-in the code. By default, the library looks for the following variables:

DB_HOST
DB_NAME
DB_USERNAME
DB_PASSWORD

More information how to use environment variables here

Configuration

Import vendor/autoload.php and load the .env settings

require_once 'vendor/autoload.php';

use Database\Core\Database as DB;

$dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
$dotenv->load();

Statements

The select statement is used to return an array containing all the result set rows

try {
    $user = DB::select('SELECT * FROM my_table WHERE firstname = ?', ['John']);
} catch (PDOException $e) {
    echo $e->getMessage();
}

The single statement is used to fetch the next row from a result set

try {
    $user = DB::single('SELECT * FROM my_table WHERE firstname = ?', ['John']);
} catch (PDOException $e) {
    echo $e->getMessage();
}

The insert statement is used to insert new records in a table

try {
    $id = DB::insert('INSERT INTO my_table (firstname, lastname) VALUES (?,?)', ['John', 'Doe']);
} catch (PDOException $e) {
    echo $e->getMessage();
}

The insertAssoc statement is used to insert new records in a table using an associative array

$data = [
    'firstname' => 'John',
    'lastname' => 'Doe'
];

try {
    $id = DB::insertAssoc('my_table', $data);
} catch (PDOException $e) {
    echo $e->getMessage();
}

The update statement is used to modify the existing records in a table

try {
    DB::update('UPDATE my_table SET firstname = ? WHERE lastname = ?', ['John', 'Doe']);
} catch (PDOException $e) {
    echo $e->getMessage();
}

The updateAssoc statement is used to update records in a table using an associative array and a where clause

$data = [
    'firstname' => 'John',
    'lastname' => 'Doe'
];

$condition = [
    'key' => 'id',
    'operator' => '=',
    'value' => 1
];

try {
    $id = DB::updateAssoc('my_table', $data, $condition);
} catch (PDOException $e) {
    echo $e->getMessage();
}

The delete statement is used to delete existing records in a table

try {
    DB::delete('DELETE FROM my_table WHERE firstname = ?', ['John']);
} catch (PDOException $e) {
    echo $e->getMessage();
}

Use statement for other operations not mentioned

try {
    DB::statement('DROP TABLE my_table');
} catch (PDOException $e) {
    echo $e->getMessage();
}

Transactions

Transaction are used to run a series of operations within an entity. If an exception is thrown between beginTransaction and commit, the transaction will automatically be rolled back.

$data = [
    'firstname' => 'John',
    'lastname' => 'Doe'
];

try {
    DB::beginTransaction();
    $id = DB::insertAssoc('my_table', $data);
    DB::update('UPDATE my_table SET firstname = ?, lastname = ? WHERE id = ?', ['Jane', 'Doe', $id]);
    DB::commit();
} catch (PDOException $e) {
    echo $e->getMessage();
}

Bugreport & Contribution

If you find a bug, please either create a ticket in github, or initiate a pull request

Versioning

We adhere to semantic (major.minor.patch) versioning (https://semver.org/). This means that:

Patch (x.x.patch) versions fix bugs
Minor (x.minor.x) versions introduce new, backwards compatible features or improve existing code.
Major (major.x.x) versions introduce radical changes which are not backwards compatible.

In your automation or procedure you can always safely update patch & minor versions without the risk of your application failing.

riculum / php-pdo

Maintainers

Details