README

Charm\DB is a very thin wrapper around PDO. The only reason it exists, is because I hate to write many lines of code many times over.

It works nicely together with frameworks that use PDO, like Doctrine or Symfony, which is nice if you are writing a library and don't want your library to depend on Doctrine.

I have written this type of wrapper several times, for different projects. So I finally decided to make some documentation and put it on packagist - mainly to make it easier to install for myself. :-D

Examples for lazy readers

• Charm\DB query:

   $rows = $db->query('SELECT * FROM users WHERE id = ?', [ 123 ] );
  

• PDO query:

   $stmt = $db->prepare('SELECT * FROM users WHERE id = ?', [ 123 ] );
   $stmt->execute([123]);
   $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
  

• Charm\DB exec:

   $affectedRows = $db->exec('DELETE FROM trash WHERE age < ?', [ gmdate('Y-m-d H:i:s') ]);
  

• PDO exec:

   $stmt = $db->prepare('DELETE FROM trash WHERE age < ?');
   $stmt->execute([ gmdate('Y-m-d H:i:s') ]);
   $affectedRows = $stmt->rowCount();
  

• PDO for lazy people, prone to human mistakes causing huge security issues:

   $affectedRows = $db->exec('DELETE FROM trash WHERE age < '.$db->quote(gmdate('Y-m-d H:i:s')));
  

• No special method to fetch a single row, just use ->current().

   $row = $db->query('SELECT * FROM users WHERE id = ?', [123])->current();
  

• No special method to fetch a single column, just provide the column number.

   $usernames = $db->query('SELECT username FROM users', [], 0);
  

• Fetching a single field, just use column 0 and ->current() again.

   $value = $db->query('SELECT username FROM users WHERE id = ? LIMIT 1', [123], 0)->current();
  

Sometimes you'll want the extra power that the PDO API provides. Two methods are relevant:

• $db->prepare( string $statement, array $driver_options = array() ) which works identically to the PDO::prepare() method, but with statement caching.

• $db->pdo(): ?PDO which returns the native PDO instance.

Using it

Connect to the database (using PDO DSN strings).

$db = Charm\DB::__construct ( string $dsn, string $username = ?, string $passwd = ?, array $options = ? )

or wrap an existing PDO instance:

$db = Charm\DB::wrap ( PDO $pdoInstance ) : Charm\DB;

Use the built-in factory

In the PHP world, there seems to be a million ways that people want to make it "easy" to configure the framework application. We have to learn how to inject configuration into a configuration that will then be injected into another tool. But it will only be injected, if you configure it to inject it unless you have configured autowiring.

This is all understandable, when you're building what you believe will become the framework that is as loved by Facebook and by Johnny, lead developer of "Johnny Inc." in Georgia. I prefer refactoring my code when (if) I grow out of the architecture I started with.

Us developers keep designing the most complext structures, to make it easy to configure stuff.

But just in case you don't have time to do that, the Charm* components generally allow you to configure them via public static properties and then you can retrieve the instance via their ::instance() static method.

Charm\DB::instance() can be configured via

    // Either put in some configuration:

    Charm\DB::$dsn          = $dsn;
    Charm\DB::$username     = ?;
    Charm\DB::$passwd       = ?;
    Charm\DB::$options      = ?;

or:

    // Or provide an instance directly

    Charm\DB::$instance     = $pdoInstance;

Now, the database can be accessed throughout your project via Charm\DB::instance().

If that doesn't suit you, just go ahead and setup Symfony or design your own method to construct it.

`Charm\DB::query (

More examples, which essentially are the same examples as above under the TLDR headline.

Querying data

Database queries are done through either $db->query() or $db->column().

// Multiple rows returns an iterable
$users = $db->query('SELECTFROM users WHERE last_login < ?', [gmdate('Y-m-d H:i:s')]);

// Single rows can be fetched this way (sending 'LIMIT 1' is kind to your database engine)
$user = $db->query('SELECTFROM users WHERE username = ? LIMIT 1', [ $username ])->current();

// Fetch a single column
$usernames = $db->column('SELECT username FROM users);

// Fetcha single value
$numberOfUsers = $db->column('SELECT COUNT(*) FROM users')->current();

Definition, Manipulation, Control

All modifications to the database are done through the $db->exec() function.

// Insert a row into a table
if ($db->exec('INSERT INTO users (username, password ) VALUES (?, ?)', [ $username, $password ])) {
    echo "Created a new user with id ".$db->lastInsertId()."!\n";
}

// Delete some data
if ($count = $db->exec('DELETE FROM users WHERE expires_date < ?', [ gmdate('Y-m-d H:i:s') ])) {
    echo "Deleted $count users\n";
}

Error Handling

The wrapper defaults to PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, so you'll be receiving standard PDO exceptions.

Invalid usage of this wrapper will trigger Charm\DB\Error exceptions with error codes according to the class constants. If you're not using PDO::ERRMODE_EXCEPTION we'll only throw Charm\DB\Error exceptions.

The query issued when the last error was triggered is available in $db->lastFailedQuery.

Security

This class does NOTHING for security, but it makes it easier for YOU to do more secure database queries.

This class overrides the __debugInfo() magic method to avoid accidentally exposing secrets, but in general you should NEVER display raw error messages in production.