Download the PHP package v-dem/queasy-db without Composer

QuEasy PHP Framework - Database

Package v-dem/queasy-db

QuEasy DB is a set of database access classes for CRUD operations. Some of the most usual queries can be built automatically (like SELECT by unique field value/values, UPDATE, INSERT and DELETE). More complex queries can be defined in database and/or tables config. The main goal is to separate SQL queries out of PHP code and provide an easy way for CRUD operations.

Features

• QuEasy DB extends PDO class, so any project which uses PDO can be seamlessly moved to use QuEasy DB.
• Simple CRUD database operations in just one PHP code row.
• Separating SQL queries from PHP code.

Requirements

• PHP version 5.3 or higher

Installation

composer require v-dem/queasy-db

It will also install v-dem/queasy-helper.

Usage

Notes

• You can use setLogger() method which accepts Psr\Log\LoggerInterface implementation to log all queries, by default Psr\Log\NullLogger is used.
• By default error mode is set to PDO::ERRMODE_EXCEPTION (as in PHP8) if another mode is not set in $options.

IMPORTANT!

• For MySQL Server need to set option PDO::MYSQL_ATTR_INIT_COMMAND to SET GLOBAL SQL_MODE=ANSI_QUOTES or run same query before calling DB-specific methods.
• For MSSQL Server need to run SET QUOTED_IDENTIFIER ON or SET ANSI_DEFAULTS ON query before calling DB-specific methods.

Initialization

Sample:

Or:

Or PDO-way:

• Fourth argument ($options) is optional, will be passed to PDO::prepare()

Get all records from users table

Using foreach with users table

Get single record from users table by id key

It's possible to use select() method to pass PDO options; select() returns PDOStatement instance:

Get multiple records

Insert a record into users table using associative array

Insert a record into users table by fields order

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

Insert many records into users table by order

Inserting many records into users table with field names denoted separately

Also it's possible to use insert() method (in the same way as above) when need to pass PDO options; returns last insert id for single insert and number of inserted rows for multiple inserts:

• Second argument ($options) is optional, will be passed to PDO::prepare()

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

Update a record in users table by id key

• Third argument ($options) is optional, will be passed to PDO::prepare()

Update multiple records

Delete a record in users table by id key

Delete multiple records

• Second argument ($options) is optional, will be passed to PDO::prepare()

Get count of all records in users table

Using transactions

• On exception transaction is rolled back and exception re-thrown to outer code.

Run custom query (returns PDOStatement)

• Third argument ($options) is optional, 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.

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

Also it is possible to group predefined queries by tables:

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

config.php:

Initializing:

• 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.