Queasy DB: Execute queries by accessing class variables

Recommend this page to a friend!
  Info   View files Documentation   View files View files (29)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog (1)    
Last Updated Ratings Unique User Downloads Download Rankings
2021-06-18 (2 months ago) RSS 2.0 feedNot yet rated by the usersTotal: 119 All time: 9,305 This week: 236Up
Version License PHP version Categories
queasy-db 1.0.1Custom (specified...5PHP 5, Databases
Description Author

This package can be used to execute queries by accessing class variables.

It can connect to a given database using the PDO extension.

The package provides classes that have getter functions that when they are accessed they can execute queries to retrieve values from table records.

The classes also have functions to execute SQL INSERT, UPDATE and DELETE queries from given parameter values.

Innovation Award
PHP Programming Innovation award nominee
April 2021
Number 4
Many applications need to perform database queries to store and retrieve the information that they need to execute their tasks.

The traditional solutions to perform database queries require that developers assemble the SQL query strings combining any parameter values that may be necessary to compose the final SQL query strings.

This package provides a much simpler solutions that just requires accessing class variables to assemble and run the SQL queries that need to be executed.

Manuel Lemos
Picture of Vitaly
  Performance   Level  
Name: Vitaly <contact>
Classes: 3 packages by
Country: Ukraine Ukraine
Innovation award
Innovation award
Nominee: 1x

Details

Codacy Badge Build Status codecov Total Downloads License

QuEasy PHP Framework - Database

Package v-dem/queasy-db

Database access classes. Some the most usual queries can be built automatically, more complex queries can be added into database and/or tables config.

Features

Requirements

  • PHP version 5.3 or higher

Installation

composer require v-dem/queasy-db:master-dev

Usage

Notes

  • `queasy\db\Db` class inherits `PDO` class, so any `PDO` methods can be called with it
  • You can use `setLogger()` method which accepts `Psr\Log\LoggerInterface` to log all queries

Initialization

Sample:

$db = new queasy\db\Db(
    [
        'connection' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'name' => 'test',
            'user' => 'test_user',
            'password' => 'test_password'
        ],
        'fetchMode' => PDO::FETCH_ASSOC // Default fetch mode for all queries
    ]
);

Or

$db = new queasy\db\Db(
    [
        'connection' => [
            'dsn' => 'mysql:host=localhost;dbname=test',
            'user' => 'test_user',
            'password' => 'test_password'
        ],
        'fetchMode' => PDO::FETCH_ASSOC // Default fetch mode for all queries
    ]
);

Or PDO-way:

$db = new queasy\db\Db('mysql:host=localhost;dbname=test', 'test_user', 'test_password');

  • By default error mode is set to `PDO::ERRMODE_EXCEPTION`

Get all records from users table

$user = $db->users->all();

Get a single record from users table by id key

$user = $db->users->id[$userId];

It will generate the following query:

SELECT  *
FROM    `users`
WHERE   `id` = :id

It's possible to use select() method to pass PDO options:

$user = $db->users->id->select($userId, $options);

Get multiple records

$users = $db->users->id[[$userId1, $userId2]];

SQL:

SELECT  *
FROM    `users`
WHERE   `id` IN (:id_1, :id_2)

Insert a record into users table using associative array

$db->users[] = [
    'email' => 'john.doe@example.com',
    'password_hash' => sha1('myverystrongpassword')
];

It will generate the following query:

INSERT  INTO `users` (`email`, `password_hash`)
VALUES  (:email, :password_hash)

Insert a record into users table by fields order

$db->users[] = [
    'john.doe@example.com',
    sha1('myverystrongpassword')
];

Insert many records into users table using associative array (it will generate single INSERT statement)

$db->users[] = [
    [
        'email' => 'john.doe@example.com',
        'password_hash' => sha1('myverystrongpassword')
    ], [
        'email' => 'mary.joe@example.com',
        'password_hash' => sha1('herverystrongpassword')
    ]
];

SQL:

INSERT  INTO `users` (`email`, `password_hash`)
VALUES  (:email_1, :password_hash_1),
        (:email_2, :password_hash_2)

Insert many records into users table by order

$db->users[] = [
    [
        'john.doe@example.com',
        sha1('myverystrongpassword')
    ], [
        'mary.joe@example.com',
        sha1('herverystrongpassword')
    ]
];

Inserting many records into users table with field names denoted separately

$db->users[] = [
    [
        'email',
        'password_hash'
    ], [
        [
            'john.doe@example.com',
            sha1('myverystrongpassword')
        ], [
            'mary.joe@example.com',
            sha1('herverystrongpassword')
        ]
    ]
];

It's possible to use insert() method to pass PDO options:

$db->users->insert([
    'email' => 'john.doe@example.com',
    'password_hash' => sha1('myverystrongpassword')
], $options);

Get last insert id (alias of lastInsertId() method)

$newUserId = $db->id();

Update a record in users table by id key

$db->users->id[$userId] = [
    'password_hash' => sha1('mynewverystrongpassword')
]

Update multiple records

$db->users->id[[$userId1, $userId2]] = [
    'is_blocked' => true
]

Delete a record in users table by id key

unset($db->users->id[$userId]);

Delete multiple records

unset($db->users->id[[$userId1, $userId2]]);

Get count of all records in users table

$usersCount = count($db->users);

Using transactions

$db->trans(function(queasy\db\Db $db) use(...) {
    // Run queries inside a transaction
});
  • `queasy\db\Db` instance will be passed as first argument.

Using foreach with a users table

foreach($db->users as $user) {
    // Do something
}

Run custom query (returns PDOStatement)

$result = $db->run('
    SELECT  *
    FROM    `users`
    WHERE   `name` LIKE concat(\'%\', :searchName, \'%\')',
    [
        ':searchName' => $searchName
    ]
);

  • Possible 3rd argument is `$driverOptions` which will be passed to `PDO::prepare()`

Run query predefined in configuration

This feature can help keep code cleaner and place SQL code outside PHP, somewhere in config files.

$db = new queasy\db\Db(
    [
        'connection' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'name' => 'test',
            'user' => 'test_user',
            'password' => 'test_password'
        ],
        'fetchMode' => PDO::FETCH_ASSOC,
        'queries' => [
            'selectUserRoleByName' => [
                'sql' => '
                    SELECT  *
                    FROM    `user_roles`
                    WHERE   `name` = :name',
                'returns' => Db::RETURN_ONE
            ]
        ]
    ]
);

$role = $db->selectUserRoleByName(['name' => 'Manager']);

  • Possible values for `returns` option are `Db::RETURN_STATEMENT` (default), `Db::RETURN_ONE`, `Db::RETURN_ALL`, `Db::RETURN_VALUE`

Also it is possible to group predefined queries by tables:

$db = new queasy\db\Db(
    [
        'connection' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'name' => 'test',
            'user' => 'test_user',
            'password' => 'test_password'
        ],
        'fetchMode' => PDO::FETCH_ASSOC,
        'tables' => [
            `user_roles` => [
                `queries` => [
                    'selectUserRoleByName' => [
                        'sql' => '
                            SELECT  *
                            FROM    `user_roles`
                            WHERE   `name` = :name',
                        'returns' => Db::RETURN_ONE
                    ]
                ]
            ]
        ]
    ]
);

$role = $db->user_roles->selectUserRoleByName(['name' => 'Manager']);

Using v-dem/queasy-db together with v-dem/queasy-config

$config = new queasy\config\Config('config.php'); // Can be also INI, JSON or XML
$db = new queasy\db\Db($config->db);

config.php:

return [
    'db' => [
        'connection' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'name' => 'test',
            'user' => 'test_user',
            'password' => 'test_password'
        ],
        'fetchMode' => PDO::FETCH_ASSOC,
        'tables' => [
            'user_roles' => [
                'queries' => [
                    'selectUserRoleByName' => [
                        'sql' => '
                            SELECT  *
                            FROM    `user_roles`
                            WHERE   `name` = :name',
                        'returns' => Db::RETURN_ONE
                    ]
                ]
            ]
        ]
    ]
];

Using v-dem/queasy-db together with v-dem/queasy-log

$config = new queasy\config\Config('config.php');
$logger = new queasy\log\Logger($config->logger);
$db = new queasy\db\Db($config->db);
$db->setLogger($config->logger);
  • All queries will be logged with `Psr\Log\LogLevel::DEBUG` level. Also it's possible to use any other logger class compatible with PSR-3.
  Files folder image Files  
File Role Description
Files folder imagesrc (5 files, 1 directory)
Files folder imagetests (1 directory)
Accessible without login Plain text file .travis.yml Data Auxiliary data
Accessible without login Plain text file codecov.yml Data Auxiliary data
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file phpunit.xml Data Auxiliary data
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files  /  src  
File Role Description
Files folder imagequery (14 files)
  Accessible without login Plain text file Connection.php Class Class source
  Accessible without login Plain text file Db.php Class Class source
  Accessible without login Plain text file DbException.php Class Class source
  Accessible without login Plain text file Field.php Class Class source
  Accessible without login Plain text file Table.php Class Class source

  Files folder image Files  /  src  /  query  
File Role Description
  Accessible without login Plain text file AbstractQuery.php Class Class source
  Accessible without login Plain text file BatchInsertQuery.php Class Class source
  Accessible without login Plain text file BatchNamedInsertQuery.php Class Class source
  Accessible without login Plain text file BatchSeparatelyNamedInsertQuery.php Class Class source
  Accessible without login Plain text file CountQuery.php Class Class source
  Accessible without login Plain text file CustomQuery.php Class Class source
  Accessible without login Plain text file DeleteQuery.php Class Class source
  Accessible without login Plain text file Query.php Class Class source
  Accessible without login Plain text file QueryInterface.php Class Class source
  Accessible without login Plain text file SelectQuery.php Class Class source
  Accessible without login Plain text file SingleInsertQuery.php Class Class source
  Accessible without login Plain text file SingleNamedInsertQuery.php Class Class source
  Accessible without login Plain text file TableQuery.php Class Class source
  Accessible without login Plain text file UpdateQuery.php Class Class source

  Files folder image Files  /  tests  
File Role Description
Files folder imagesrc (4 files)

  Files folder image Files  /  tests  /  src  
File Role Description
  Accessible without login Plain text file ConnectionTest.php Class Class source
  Accessible without login Plain text file DbTest.php Class Class source
  Accessible without login Plain text file FieldTest.php Class Class source
  Accessible without login Plain text file TableTest.php Class Class source

 Version Control Unique User Downloads Download Rankings  
 100%
Total:119
This week:0
All time:9,305
This week:236Up
For more information send a message to info at phpclasses dot org.