Download the PHP package phpvv/db without Composer

VV DB

VV database abstraction layer with query builder and DB structure models.

Installation

This is the basic package. To use it with concrete DBMS install one of these drivers using Composer:

• db-mysqli - MySQL driver over MySQLi extension;
• db-pdo - PostgreSQL (and other) driver over PDO extension;
• db-oci - Oracle driver over oci8 extension.

Big Select Example

big-select.php in DB Playground:

Output:

Big Transaction Example

big-transaction.php in DB Playground:

Basics

Using only Connection without schema model representation

Example (connection.php):

Using DB Model(s)

Configuration

At start, it is needed to create somewhere class <MyNameOf>Db extends \VV\Db and implement one abstract method createConnection(). Example (App/Db/MainDb.php):

Model Generation

Just run this code (gen-db-model.php):

DB schema representation classes will be created in the App\Db\MainDb folder.

Usage

Example (db-model.php):

SELECT

Create SelectQuery

There are several variants to create SelectQuery (create-select-query.php):

Fetch Query Result

From Result

Fetch single row or cell (execute-select-query.php):

Fetch all rows or column (execute-select-query.php):

You can set fetch mode flags to fetch(), row(), rows(), column():

Fetch result directly from query (execute-select-query.php):

SELECT Clause

Method select(...) (see above) returns select.php):

All these methods accepts string or select.php):

FROM Clause

To set table or view to query you can call from() method or create query directly from from.php):

By default, alias of table (or view) consists of first letters of each word of table (or view) name without prefix (tbl_,t_, vw_, v_). For example: tbl_order -> o, tbl_order_item -> oi.

To change table alias, call mainTableAs() method of query (from.php):

JOIN Clause

To set JOIN clause use these methods: join(), left(), right(), full().

Example (join.php):

By default, table joins to previous table by primary key column. Default alias of table is first letters of each word of table name. You can change ON condition (second parameter) and alias (third parameter) (join.php):

ON Condition Shortcuts

Specify alias of table to which join is needed (join.php):

Specify column of table to which join is needed (join.php):

Specify alias and column of table to which join is needed (join.php):

joinParent() (join.php):

joinBack() (join.php):

Nested Columns

Nest resulting columns manually (nested-columns.php):

Result:

Nest resulting columns with join (nested-columns.php):

Result:

WHERE Clause

To set query condition use where() method. Each where() adds AND condition. Method accepts:

• Condition as first argument (where.php):

• Expression|string as first argument and (binding) value (or Expression) to compare as second argument (where.php):

• string as custom SQL as first argument and (binding) array of values as second argument (where.php):

• array as first argument ($expression => $parameter) (where.php):

WHERE Shortcuts

Query has some shortcuts methods:

• ->whereId(1) (for $db->tbl->product->select() - product_id = ?);
• ->where[Not]In('brand_id', 1, 2, 3);
• ->whereId[Not]In(1, 2, 3);
• ->where[Not]Between('width', 250, 350);
• ->where[Not]Like('title', 'computer%', caseInsensitive: true).

GROUP BY and HAVING Clauses

To set GROUP BY clause use groupBy() method that behaves like columns() (see Columns Clause).

To set condition for aggregate use having() method that behaves like where() (see Where Clause).

Example (group-by-having.php):

ORDER BY Clause

Simple order by columns (order-by.php):

Order by expression (CASE for example) (order-by.php):

LIMIT Clause

Use ->limit($count, $offset) (limit.php):

UNION, INTERSECT, EXCEPT Clauses

Use ->union[All](...$queries), ->intersect[All](...$queries), ->except[All](...$queries) (union-intersect-except):

INSERT

Create InsertQuery

There are several variants to create InsertQuery (create-insert-query.php):

Last variant is preferable due to adjusting type of inserted value to column type:

Execute InsertQuery

Just execute:

Get inserted ID (autoincrement) or affected rows (execute-insert-query.php):

Execute query and return inserted ID or affected rows (execute-insert-query.php):

Insert Single Row

Regular insert query (insert-single-row.php):

Insert assignment list (insert-single-row.php):

Shortcut (executes query) (insert-single-row.php):

Insert Multiple Rows

Insert values list (insert-multiple-rows.php):

Insert from Select (insert-multiple-rows.php):

Insert values list executing statement per N rows (insert-multiple-rows.php):

UPDATE

Create UpdateQuery

There are several variants to create UpdateQuery (create-update-query.php):

Last variant is preferable due to adjusting type of updated value to column type:

Execute UpdateQuery

Just execute:

Get affected rows (execute-update-query.php):

Execute query and return affected rows (execute-update-query.php):

SET and WHERE Clauses

Method set() accepts column name as first argument and value (or Expression) as second argument or array column => value. WHERE clause is required for UpdateQuery. To set condition use where() method or its shortcuts (see select query where clause). To update all rows just set something like this: ->where('1=1').

Example (update.php):

Shortcut (executes query) (update.php):

Update with update.php):

DELETE

Create DeleteQuery

There are several variants to create DeleteQuery (create-delete-query.php):

Execute DeleteQuery

See execute UpdateQuery section:

WHERE Clause

WHERE clause is required for DeleteQuery. To set condition use where() method or its shortcuts (see WHERE clause section). To delete all rows just set something like this: ->where('1=1').

Example (delete.php):

Transaction

To start transaction for connection use startTransaction():

To commit or rollback changes use Transactions commit() or rollback() methods:

To execute query inside transaction pass Transaction object to method exec($transaction) (or affectedRows($transaction), or insertedId($transaction)). Query execution for Connection with started transaction without passing Transaction to exec() throws exception. To overcome this use transaction free connection ($db->getFreeConnection()).

Example (copy-order.php):

Condition

Create condition:

To add new predicate to condition at first you need set target expression via "connector" methods and($expression) or or($expression). Then call "comparison" method like eq($value), like($value), in($value1, $value2) and other:

Comparison Methods

eq($param) - = ?
ne($param) - != ?
lt($param) - < ?
lte($param) - <= ?
gt($param) - > ?
gte($param) - >= ?
compare($param, $operator) - $operator ?
between($from, $till) - BETWEEN ? AND ?
in(...$params) - IN (?, ?, ?, ...)
isNull() - IS NULL
isNotNull() - IS NOT NULL
like($pattern, $caseInsensitive = false) - LIKE ?
startsWith($prefix, $caseInsensitive = false) - LIKE ?%
endsWith($suffix, $caseInsensitive = false) - LIKE %?
contains($string, $caseInsensitive = false) - LIKE %?%
exists($db->select(...)->where(...) - EXISTS (SELECT ... FROM ... WHERE ...)
and('MY_FUNC(foo, ?, ?)')->custom($param1, $param2) - MY_FUNC(foo, ?, ?)

Case Expression