README

A wrapper for PHP and PDO.

Purpose

PdoFish2 is an Active Record-inspired query interface built on top of PDO. This is not a PHP implementation of Active Record, but rather, a class that mimics its best parts while keeping the amount of code needed to use PDO to a minimum.

This project is not for everyone; however, if you're already familiar with Active Record or wish to use an Active Record style DB interface in PHP, this may suit. The aim of this project was simple: readable code that is as thin a layer on top of PDO as possible.

Installation

Via Composer:

composer require sethadam1/PdoFish2

Manually:

Upload the files to your web server.
Include PdoFish2.php in your code.
Instantiate the class as seen below.

require_once '/path/to/PdoFish2/src/PdoFish2.php';

// Pass credentials directly, or set DB_HOST, DB_NAME, DB_USER, DB_PASSWORD
// environment variables and call with no arguments (see Instantiating below).
$pf2 = new PdoFish2(['host' => 'localhost', 'database' => 'mydb', 'username' => 'dbuser', 'password' => 'secret']);

Using environment variables

These two calls are functionally equivalent:

$pf2 = new PdoFish2();

$pf2 = new PdoFish2([
    'host'     => $_ENV['DB_HOST'],
    'database' => $_ENV['DB_NAME'],
    'username' => $_ENV['DB_USER'],
    'password' => $_ENV['DB_PASSWORD'],
]);

Currently Supported Methods

$object->set_table(tbl_name) - a chainable function to specify a table
$object->t(tbl_name) - a shortcut for set_table
$object->raw($sql) - execute raw SQL
$object->find($id) - find by a column called "id"
$object->all($args) - return all rows matching a query
$object->first($args) - returns the first row matching a query
$object->conditions($conditions) - shorthand for all() with a conditions array
$object->find_by_sql($sql) - returns a single row matching a query
$object->find_all_by_sql($sql) - returns all rows matching a query
$object->find_by_column($col, $val) - find a single row by a specific column value
$object->find_by_slug($val) - shorthand for find_by_column('slug', $val)
$object->find_by_ref($val) - shorthand for find_by_column('ref', $val)
$object->count($args) - return matching row count
$object->insert($data) - insert a record; returns the last insert ID
$object->update($data, $where) - update field(s)
$object->delete($where) - delete rows matching criteria
$object->delete_by_id($id) - delete by a column called "id"
$object->delete_by_column($col, $val) - delete by any column
$object->delete_many($column, $vals) - delete multiple rows matching a list of values (uses the IN keyword)
$object->quote_identifier($name) - backtick-quote a table or column name for safe use in raw SQL
$object->begin() - start a transaction
$object->commit() - commit the current transaction
$object->rollback() - roll back the current transaction
$object->transaction($callback) - run a callback inside a transaction, committing on return and rolling back on exception

Dynamic function names

$object->find_by_[field]($val) - find a single row by a specific column value
$object->find_all_by_[field]($val) - find multiple rows where one column matches the given value

Calling any other undefined method throws BadMethodCallException.

Basic CRUD

For the purposes of these examples, we'll assume you'll assign PdoFish2 to the variable $pf2.

Instantiating

PdoFish2 accepts credentials either as a constructor argument or from environment variables. If no argument is passed, it automatically reads DB_HOST, DB_NAME, DB_USER, and DB_PASSWORD from your environment (e.g. via .env + a loader, server config, or shell exports). Each is read from $_ENV first and falls back to getenv(), so this works whether or not your php.ini's variables_order populates $_ENV (it often doesn't in production configs).

// Option 1: pass credentials directly
$pf2 = new PdoFish2([
    'host'     => 'localhost',
    'database' => 'mydb',
    'username' => 'dbuser',
    'password' => 'secret',
]);

// Option 2: rely on environment variables — no argument needed
// Requires DB_HOST, DB_NAME, DB_USER, and DB_PASSWORD to be set in $_ENV
$pf2 = new PdoFish2();

// Optional: pass PDO driver options (TLS, timeouts, persistent connections, etc.)
$pf2 = new PdoFish2([
    'host'     => 'db.example.com',
    'database' => 'mydb',
    'username' => 'dbuser',
    'password' => 'secret',
    'options'  => [
        PDO::MYSQL_ATTR_SSL_CA           => '/path/to/ca.pem',
        PDO::MYSQL_ATTR_MULTI_STATEMENTS => false,
    ],
]);

Create

$data = [
    'col1' => '2020-08-27 09:58:01',
    'col2' => 'a string',
    'col3' => 12345
];
$id = $pf2->t('products')->insert($data);

echo $id;
// example response "3"

Read

// print an object
$x = $pf2->t('users')->first([ 'conditions' => ['some_field=?', 'some_value'] ]);
print_r($x);

// print an associative array
$x = $pf2->first(['from' => 'table_name', 'conditions' => ['some_field=?', 'some_value']], PDO::FETCH_ASSOC);
print_r($x);

// print a single row matching a SQL query
$x = $pf2->find_by_sql('select * from random_table where random_field=12');
print_r($x);

// print a row where id = 5
$x = $pf2->t('table')->find(5);
print_r($x);

// print 5 rows from a complex query
$x = $pf2->all([
    'select'     => 'field1, field2, field3',
    'from'       => 'table t',
    'joins'      => 'LEFT JOIN table2 t2 ON t.field1=t2.other_field',
    'conditions' => ['some_field=?', $some_value],
    'order'      => 'field3 ASC',
    'limit'      => 5
]);
print_r($x);

Update

// updates column "firstname" to "Boris" where id = 5
$pf2->t('table_name')->update(['firstname' => 'Boris'], ['id' => 5]);

// updates multiple columns where id = 5
$pf2->t('table_name')->update(['firstname' => 'June', 'lastname' => 'Basoon'], ['id' => 5]);

Delete

// delete rows where column "firstname" is equal to "Boris"
$pf2->t('table_name')->delete(['firstname' => 'Boris']);

// delete row where column "id" is equal to 5
$pf2->t('table_name')->delete_by_id(5);

// delete rows where column "user_id" is equal to 1, 2, or 3
$pf2->t('table_name')->delete_many('user_id', [1, 2, 3]);

Transactions

begin(), commit(), and rollback() are thin passthroughs to the underlying PDO connection. transaction() wraps them around a callback: it commits if the callback returns normally, and rolls back and rethrows if it throws.

// Manual
$pf2->begin();
try {
    $pf2->t('accounts')->update(['balance' => 90], ['id' => 1]);
    $pf2->t('accounts')->update(['balance' => 110], ['id' => 2]);
    $pf2->commit();
} catch (\Throwable $e) {
    $pf2->rollback();
    throw $e;
}

// Equivalent, using the callback form
$pf2->transaction(function (PdoFish2 $pf2) {
    $pf2->t('accounts')->update(['balance' => 90], ['id' => 1]);
    $pf2->t('accounts')->update(['balance' => 110], ['id' => 2]);
});

Nesting is not supported; calling begin() while already inside a transaction behaves exactly as calling PDO::beginTransaction() twice does on your driver (typically a PDOException).

Arguments supported

The following arguments are supported in the PdoFish queries:
select - columns to select
from - table, or table and an alias e.g. "prices p"
joins - a string of joins in SQL syntax, e.g. LEFT JOIN table2 on prices.field=table2.field
conditions - an array of SQL with ? placeholders and bound arguments e.g. ['year=? AND mood=?', 2021, 'happy']
group - group by field name
having - having clause e.g. 'count(x)>3'
order - order by e.g. 'id DESC'
offset - row offset (integer)
limit - a positive integer greater than 0

Security notes

Values are always bound. Every value you pass to find(), insert(), update(), delete(), delete_many(), find_by_*(), and the conditions / in arguments is sent to the database as a bound parameter, never interpolated into the SQL string.

Table and column names are quoted. The array keys in insert(), update(), and delete(), and the column arguments to find_by_column(), delete_by_column(), delete_many(), and dynamic find_by_[field]() calls are backtick-quoted and escaped, so a name that arrives from user input (for example insert($_POST)) cannot inject SQL. The table set with t() / set_table() is quoted the same way in those methods and in find() / delete_by_id(). Reserved words like order and qualified names like db.table work without manual quoting. The exception is all(), first(), and count(), which use the table name as a raw SQL fragment so that aliases like t('prices p') keep working; do not build that name from user input.

Raw SQL arguments are raw. The select, from, joins, group, having, and in[0] arguments, the SQL string in conditions[0], and everything passed to run(), raw(), find_by_sql(), and find_all_by_sql() are SQL fragments by design. Never build them from user input. If you need a dynamic table or column name in one of them, pass it through quote_identifier() first:

$col = $pf2->quote_identifier($_GET['group_by']);
$rows = $pf2->t('events')->all(['select' => "$col, COUNT(*) AS n", 'group' => $col]);

The order argument accepts only column names, commas, dots, backticks, and ASC / DESC, plus the literal RAND() (case-insensitive, surrounding whitespace ignored) so that ['order' => 'RAND()'] works without dropping down to run(). Anything else -- including any other function call -- is silently dropped. Parentheses are not otherwise permitted, so the RAND() carve-out matches one fixed spelling and doesn't loosen the guard for anything else.

delete() and update() refuse an empty $where. Both throw InvalidArgumentException rather than touching every row. Use run() if you really mean an unconditional statement.

Failed queries are logged without their bound values. run() and the query builder write the error message and SQL to error_log(), but replace the parameter values with a count so passwords, tokens, and personal data stay out of your logs. Set $pf2->log_params = true to include the values while debugging.

Credentials stay out of stack traces. The constructor argument is marked #[\SensitiveParameter], so a failed connection that surfaces through an error reporter shows SensitiveParameterValue instead of your password (PHP 8.2+; the attribute is ignored on 8.1).

Other fixes

count() is a real SELECT COUNT(*). It previously ran the same query as all() and returned rowCount(), which silently capped the result at the default 4000-row limit (or any explicit limit you passed). It now counts every matching row regardless of limit/offset/order, which are ignored since they don't change how many rows match. An ungrouped count is issued directly as SELECT COUNT(*) FROM ...; only a group/having or DISTINCT query is wrapped in a derived table, since that is the only case where the rows have to be materialized before they can be counted. This matters for counts over a join: a derived table may not repeat a column name, and joined tables almost always share one (an id, typically), so wrapping unconditionally would fail with "Duplicate column name" on any join left at the default select of *.

Calling an undefined method throws. find_by_[field](), find_all_by_[field](), and view_[name]() still work as before; anything else now throws BadMethodCallException instead of silently returning null, so a typo like $pf2->finde(5) fails loudly.

SET NAMES only runs for MySQL. It previously ran unconditionally, which broke the type option for any non-MySQL driver (pgsql, sqlite, sqlsrv, dblib). It's now skipped unless type is mysql (the default).

Testing

The suite runs against an in-memory SQLite database by default and needs nothing else installed:

composer install
composer test

To also run the same suite against a real MySQL/MariaDB database (a disposable one — its users table is dropped and recreated by every test), point these environment variables at it before running composer test:

PDOFISH2_TEST_MYSQL_HOST=127.0.0.1
PDOFISH2_TEST_MYSQL_PORT=3306        # optional, defaults to 3306
PDOFISH2_TEST_MYSQL_DATABASE=pf2test # optional, defaults to pf2test
PDOFISH2_TEST_MYSQL_USER=root        # optional, defaults to root
PDOFISH2_TEST_MYSQL_PASSWORD=        # optional, defaults to empty

Without PDOFISH2_TEST_MYSQL_HOST set, those tests are skipped rather than failed.

Credits

This project draws inspiration from David Carr's PDOWrapper.

sethadam1 / pdofish2

Package info

Security

Statistics