Managing Data

Introduction

CRUD stands for Create, Read, Update, and Delete, which are the four basic operations for managing persistent data. Hazaar DBI includes an ORM-style layer that focuses on creating, reading, updating, and deleting records in a consistent way across PDO drivers. It is responsible for:

• Building SQL queries from structured criteria.
• Executing CRUD operations and returning results in predictable formats.
• Providing a simple adapter-level API for common operations.
• Exposing a richer Table API for advanced queries with joins, grouping, and query composition.

You can use simple CRUD methods directly on the adapter for straightforward operations, or use the Table class for more advanced querying and control.

For more details, see the API docs for Hazaar\DBI\Adapter and Hazaar\DBI\Table.

Querying the Database (Adapter)

The adapter provides database-agnostic CRUD helpers for common queries.

$db = Hazaar\DBI\Adapter::create();
$result = $db->find('my_table');

The find() method executes a SELECT query and returns a Hazaar\DBI\Result object you can iterate over. See the Hazaar\DBI\Result class documentation for more information.

$result = $db->find('my_table', ['id' => 1]);

The fetch() method returns the next row as an associative array.

$result = $db->find('my_table');
while ($row = $result->fetch()) {
    // Do something with the row
}

Limiting Columns

You can limit the selected columns by passing an array of column names as the third argument to find() or findOne().

$result = $db->find('my_table', ['status' => 'active'], ['id', 'email', 'created_at']);

$row = $db->findOne('my_table', ['id' => 1], ['id', 'email']);

Finding a Single Row

findOne() returns the first matching row as an associative array (or false if none match).

$row = $db->findOne('my_table', ['id' => 1]);

Inserting Rows (Adapter)

Use insert() to add a new row. It returns the inserted ID or a result depending on the driver and returning behavior.

$id = $db->insert('my_table', [
    'email' => '[email protected]',
    'status' => 'active',
]);

Updating Rows (Adapter)

Use update() to modify rows. It returns the number of affected rows.

$updated = $db->update('my_table',
    ['status' => 'disabled'],
    ['id' => 1]
);

Deleting Rows (Adapter)

Use delete() to remove rows. It returns the number of affected rows.

$deleted = $db->delete('my_table', ['id' => 1]);

Using the Table Class for Advanced Queries

For more complex queries, use the Table class. This gives you a fluent API for joins, grouping, ordering, and composing queries. Start by calling table() on the adapter.

$table = $db->table('users');

Joins and Advanced Selection

The Table API allows you to join other tables and build advanced queries while keeping the query database-agnostic.

$result = $db->table('users')
    ->select(['users.id', 'users.email', 'profiles.display_name'])
    ->leftJoin('profiles', ['users.id' => 'profiles.user_id'])
    ->where(['users.status' => 'active'])
    ->order(['users.created_at' => SORT_DESC])
    ->limit(25)
    ->find();

You can also add grouping and aggregates:

$result = $db->table('orders')
    ->select(['customer_id', 'COUNT(*) AS order_count'])
    ->group('customer_id')
    ->having(['order_count' => ['>' => 5]])
    ->find();

Inserting with Table

Use insert() on a Table instance to insert rows and optionally return columns:

$insertedId = $db->table('users')->insert([
    'email' => '[email protected]',
    'status' => 'active',
]);

Updating with Table

Use update() on a Table instance with criteria and optional returning columns:

$updated = $db->table('users')->update(
    ['status' => 'disabled'],
    ['id' => 1]
);

Deleting with Table

Use delete() on a Table instance with criteria:

$deleted = $db->table('users')->delete(['id' => 1]);