README

Factory

The factory creates PDO objects using DealNews\GetConfig to read database settings (from [app home]/etc/config.ini) and create a PDO database connection.

Supported Settings

Example config.ini file

This file should be stored in a directory named etc in the application's home directory.

db.mydb.type   = mysql
db.mydb.server = 127.0.0.1
db.mydb.user   = example
db.mydb.pass   = example

Usage

Example:

$mydb = \DealNews\DB\Factory::init("mydb");

CRUD

The CRUD class is a helper that wraps up common PDO logic for CRUD operations.

Basic Usage

$crud = \DealNews\DB\CRUD::factory('mydb');

// Create
$result = $crud->create(
    // table name
    "test",
    // data to add
    [
        "name"        => $name,
        "description" => $description,
    ]
);

// Read
$rows = $crud->read(
    // table name
    "test",
    // where clause data
    ["id" => $id]
);

// Update
$result = $crud->update(
    // table name
    "test",
    // data to update
    ["name" => "Test"],
    // where clause data
    ["id" => $id]
);

// Delete
$result = $crud->delete(
    // table name
    "test",
    // where clause data
    ["id" => $row["id"]]
);

Advanced Usage

The class also exposes a run method which is used internally by the other methods. Complex queries can be run using this method by providing an SQL query and a parameter array which will be mapped to the prepared query. It returns a PDOStatement object.

// Run a select with no parameters
$stmt = $crud->run("select * from table limit 10");

// Run a select query with paramters
$stmt = $crud->run(
    "select * from table where foo = :foo"
    [
        ":foo" => $foo
    ]
);

Data Mapper Pattern

This library includes an abstract mapper class for creating data mapper classes. The data mapper pattern separates the object from how it is stored. A value object is created and a data mapper that is responsible for CRUD operations to and from a datastore.

// value object
class Book {
    public string $title = '';
    public string $author = '';
    public string $isbn = '';
}

// mapper
class BookMapper extends \DealNews\DB\AbstractMapper {
    
    public const DATABASE_NAME = 'example';
    
    public const TABLE = 'books';
    
    public const PRIMARY_KEY = 'isbn';
    
    public const MAPPED_CLASS = Book::class;
    
    public const MAPPING = [
        'title'  => [],
        'author' => [],
        'isbn'   => []    
    ];
}

To load, save, delete, etc. the mapper is used like so.

$book = new Book();
$book->title = 'Professional PHP Programming';
$book->author = 'Jesus Castagnetto';
$book->isbn = '1-861002-96-3';

$mapper = new BookMapper();
$book = $mapper->save($book); // the entity is returned from save, reloaded from the database

// load a book
$book = $mapper->load('1-861002-96-3');

// delete a book
$mapper->delete('1-861002-96-3');

// find books based on author
$books = $mapper->find(['author' => 'Rasmus Lerdorf'], limit: 10, start: 0, order: 'title');

Generating Value Objects and Mappers

This library includes a script for generating value objects and mappers. It has been tested and works with MySQL and PostgreSQL. Once installed in your project, the script can be run from the vendor/bin directory.

$ ./bin/create_objects.php -h

This script builds data objects and mappers.
USAGE:
  create_objects.php  -h | --db DBNAME | --namespace NAMESPACE | --table TABLE [--base-class CLASS] [--dir DIR] [--ini-file FILE] [-q] [--schema SCHEMA] [-v]

OPTIONS:
  --base-class  CLASS      Optional base class for value objects. See README
                           for recommendations.
  --db          DBNAME     Name of the databse configuration in config.ini
  --dir         DIR        Directory to write objects to. Defaults to `src`.
   -h                      Shows this help
  --ini-file    FILE       Alternate ini file to use. Defaults to
                           etc/config.ini.
  --namespace   NAMESPACE  Base namespace for objects.
   -q                      Be quiet. Will override -v
  --schema      SCHEMA     Name of the databse schema if different from the
                           database configuration name.
  --table       TABLE      Name of the databse table to create objects for.
   -v                      Be verbose. Additional v will increase verbosity.
                           e.g. -vvv

Copyright DealNews.com, Inc.  1997-2025

Base Class for Value Objects

This library will work with plan PHP classes that do not extend any other class. However, using a base class such as Moonspot\ValueObjects can make working with the value objects easier.

Nested Mappers, Relational Data, and More

Documentation coming

Query Builder

The Query class provides a fluent interface for building complex SELECT queries with JOINs, subqueries, GROUP BY, HAVING, and more. It complements CRUD for queries that go beyond simple single-table operations.

Basic Usage

$crud = \DealNews\DB\CRUD::factory('mydb');
$query = new \DealNews\DB\Util\Query($crud);

$query->select(['id', 'name', 'email'])
    ->from('users')
    ->where('status', '=', 'active')
    ->orderBy('created_at', 'DESC')
    ->limit(10);

$rows = $crud->runFetch($query->getSql(), $query->getParams());

Complex Queries with JOINs

$query = new \DealNews\DB\Util\Query($crud);

$query->select([
        'u.id',
        'u.name',
        \DealNews\DB\Util\Query::raw('COUNT(p.id) AS post_count'),
        \DealNews\DB\Util\Query::raw('AVG(p.views) AS avg_views'),
    ])
    ->from('users', 'u')
    ->leftJoin('posts', 'p', 'p.user_id', '=', 'u.id')
    ->leftJoin('profiles', 'pr', 'pr.user_id', '=', 'u.id')
    ->where('u.status', '=', 'active')
    ->where('pr.verified', '=', true)
    ->groupBy(['u.id', 'u.name'])
    ->having('post_count', '>', 5)
    ->orderBy('post_count', 'DESC')
    ->limit(20);

$rows = $crud->runFetch($query->getSql(), $query->getParams());

Nested WHERE Conditions

$query->select(['id'])
    ->from('users')
    ->where('status', '=', 'active')
    ->where(function ($q) {
        $q->where('role', '=', 'admin')
          ->orWhere('role', '=', 'moderator');
    });

// Generates: WHERE status = :p0 AND (role = :p1 OR role = :p2)

Available Methods

Testing

By default, only unit tests are run. To run the functional tests the host machine will need to be a docker host. Also, the pdo_pgsql, pdo_mysql, and pdo_sqlite extensions must be installed on the host machine. PHPUnit will start and stop docker containers to test the MySQL and Postgres connections. Use --group functional when running PHPUnit to run these tests.