1. Go to this page and download the library: Download phpvv/db library. Choose the download type require.
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once('vendor/autoload.php');
/* Start to develop here. Best regards https://php-download.com/ */
phpvv / db example snippets
use App\Db\MainDb;
use VV\Db\Param;
use VV\Db\Sql;
$db = MainDb::instance();
$categoryId = 102;
$stateParam = Param::int(value: 1, name: 'state');
$query = $db->tbl->product
->select('title', 'b.title brand', 'price', 'c.title as color') // SELECT p.title, ... FROM tbl_product as p
->join($db->tbl->brand) // INNER JOIN tbl_brand as b ON b.brand_id = p.brand_id
->left($db->tbl->color, 'p') // LEFT JOIN tbl_color as c ON c.color_id = p.color_id
// ->left($db->tbl->color, on: 'c.color_id = p.color_id', alias: 'c') // same as above
->where( // WHERE ...
Sql::condition()
->exists( // WHERE EXISTS (
$db->tbl->productsCategories->select('1') // SELECT 1 FROM tbl_products_categories pc
->where('`pc`.`product_id`=`p`.`product_id`') // WHERE ("pc"."product_id"="p"."product_id")
->where([
'category_id' => $categoryId, // AND pc.category_id = :p1
'state' => $stateParam // AND pc.state = :state
])
) // )
->and('state')->eq($stateParam) // AND p.state = :state
// ->and('title')->like('Comp%')
->and('title')->startsWith('Comp') // AND p.title LIKE :p2 -- 'Comp%'
->and('price')
->gte(1000) // AND p.price >= :p3
->and
->lte(2000) // AND p.price <= :p4
->and('weight')->between(7000, 15000) // AND p.weight BETWEEN :p5 AND :p6
->and( // AND (
Sql::condition('width') // nested condition
->lt(500) // p.width > :p7
->or // OR
->isNull() // p.width IS NULL
) // )
->and('c.color_id')->in(1, 5, null) // AND (c.color_id IN (:p8, :p9) OR c.color_id IS NULL)
->and('brand_id')->not->in(3, 4) // AND (p.brand_id NOT IN (:p10, :p11) OR p.brand_id IS NULL)
->and('height')->isNotNull() // AND p.height IS NOT NULL
)
// ->groupBy(...)
// ->having(...)
->orderBy( // ORDER BY
Sql::case('p.color_id')
->when(5)->then(1) // color_id = 5 - first
->when(1)->then(2) // color_id = 1 - second
->else(3), // other colors at the end - first
// Sql::case()
// ->when(Sql::condition('p.color_id')->eq(5))->then(1) // same CASE as above
// ->when(['p.color_id' => 1])->then(2)
// ->else(3),
'-price', // price DESC,
'title' // title ASC
)
->limit(10); // LIMIT 10
echo "SQL:\n", $query->toString(), "\n\n";
// $rows = $query->rows;
// $rows = $query->rows(\VV\Db::FETCH_NUM);
// $rows = $query->rows(null, keyColumn: 'product_id', decorator: function (&$row, &$key) => { /*...*/ });
// $row = $query->row;
// $row = $query->row(\VV\Db::FETCH_NUM | \VV\Db::FETCH_LOB_NOT_LOAD);
// $title = $query->column;
// $brand = $query->column(1);
/*
$statement = $query->prepare();
print_r($statement->result()->rows);
$stateParam->setValue(0);
print_r($statement->result()->rows);
$statement->close();
*/
echo "rows:\n";
foreach ($query->result as $row) {
print_r($row);
}
use App\Db\MainDb;
use VV\Db\Param;
$db = MainDb::instance();
$userId = 1;
$cart = [
// productId => quantity
10 => rand(1, 2),
20 => rand(1, 3),
40 => rand(1, 5),
];
$productIterator = $db->tbl->product->select('product_id', 'price')
->whereIdIn(...array_keys($cart))
->result(\VV\Db::FETCH_NUM);
$transaction = $db->startTransaction();
try {
$orderId = $db->tbl->order->insert()
->set([
'user_id' => $userId,
'date_created' => new \DateTime(),
])
->insertedId($transaction);
// variants:
switch (3) {
case 1:
// build and execute queries for each item
foreach ($productIterator as [$productId, $price]) {
$db->tbl->orderItem->insert()
->set([
'order_id' => $orderId,
'product_id' => $productId,
'price' => $price,
'quantity' => $cart[$productId],
])
->exec($transaction);
}
break;
case 2:
// build and execute one query for all items
$insertItemQuery = $db->tbl->orderItem->insert()
->columns('order_id', 'product_id', 'price', 'quantity');
foreach ($productIterator as [$productId, $price]) {
$insertItemQuery->values($orderId, $productId, $price, $cart[$productId]);
}
$insertItemQuery->exec($transaction);
break;
case 3:
// prepare query and execute it for each item
$prepared = $db->tbl->orderItem->insert()
->set([
'order_id' => Param::int($orderId),
'product_id' => $productIdParam = Param::str(size: 16),
'price' => $priceParam = Param::str(size: 16),
'quantity' => $quantityParam = Param::str(size: 16),
]);
foreach ($productIterator as [$productId, $price]) {
$productIdParam->setValue($productId);
$priceParam->setValue($price);
$quantityParam->setValue($cart[$productId]);
$prepared->exec($transaction);
}
break;
}
$db->tbl->order->update()
->set(
'amount',
$db->tbl->orderItem->select('SUM(price * quantity)')->where('order_id=o.order_id')
)
->whereId($orderId)
// ->exec() // throws an exception that you are trying to execute statement
// outside of transaction started for current connection
->exec($transaction);
// you can execute important statement in transaction free connection
$db->tbl->log->insert()
->set(['title' => "new order #$orderId"])
->exec($db->getFreeConnection());
// throw new \RuntimeException('Test transactionFreeConnection()');
$transaction->commit();
} catch (\Throwable $e) {
$transaction->rollback();
/** @noinspection PhpUnhandledExceptionInspection */
throw $e;
}
use APP\DB\MAIN as CONF;
use VV\Db\Connection;
use VV\Db\Pdo\Driver;
// $driver = new \VV\Db\Oci\Driver;
// $driver = new \VV\Db\Mysqli\Driver;
$driver = new Driver(Driver::DBMS_POSTGRES);
$connection = new Connection($driver, CONF\HOST, CONF\USER, CONF\PASSWD, CONF\DBNAME);
// $connection->connect(); // auto connect on first query is enabled by default
// all variants do same job:
$queryString = 'SELECT product_id, title FROM tbl_product WHERE price > :price';
$result = $connection->query($queryString, ['price' => 100]);
// or
$result = $connection->prepare($queryString)->bind(['price' => 100])->result();
// or
$result = $connection->select('product_id', 'title')->from('tbl_product')->where('price > ', 100)->result();
print_r($result->rows);
namespace App\Db;
use APP\DB\MAIN as CONF;
use VV\Db\Connection;
use VV\Db\Pdo\Driver;
/**
* @method MainDb\TableList tables()
* @method MainDb\ViewList views()
* @property-read MainDb\TableList $tbl
* @property-read MainDb\TableList $vw
*/
class MainDb extends \VV\Db {
public function createConnection(): Connection {
$driver = new Driver(Driver::DBMS_POSTGRES);
return new Connection($driver, CONF\HOST, CONF\USER, CONF\PASSWD, CONF\DBNAME);
}
}
use App\Db\MainDb;
use VV\Db\Model\Generator\ModelGenerator;
(new ModelGenerator(MainDb::instance()))->build();
$query = $db->tbl->product->select(/*...*/);
// or
$query = $db->select(/*...*/)->from($db->tbl->product); // same as above
// or
$query = $db->select(/*...*/)->from('tbl_product'); // not same as above regarding JOIN clause
$query = $db->tbl->orderItem->select(/*...*/)
->join($db->tbl->order)
->join($db->tbl->product, 'oi'); // join to tbl_order_item (not tbl_order) by product_id field
$query = $db->tbl->orderItem->select('item_id', 'price', 'quantity')
->joinNestedColumns(
$db->tbl->order->select('order_id', 'amount', 'comment'), // sub query
'oi.order_id', // ON condition (see above)
['my', 'nested', 'order'] // nesting path
)
->joinNestedColumns(
$db->tbl->product->select('product_id', 'title') // sub query
->joinNestedColumns(
$db->tbl->brand->select('brand_id', 'title'), // sub sub query
'p.brand_id'
)
->joinNestedColumns($db->tbl->color, 'p', 'color'), // just join table - select all its columns
'oi.product_id',
'product'
);
print_r($query->row);
$query = $db->tbl->product->select(/*...*/)
->where( // WHERE
Sql::condition()
->and('color_id')->eq(5) // ("color_id" = ?
->and('price')->lte(2000) // AND "price" <= ?
->and('title')->isNotNull() // AND "title" IS NOT NULL
->and('brand_id')->in(2, 3) // AND "brand_id" IN (?, ?)
->and('width')->between(250, 350) // AND "width" BETWEEN ? AND ?
->and('state=1')->custom() // AND state=1) -- w/o quotes: custom query not changed
);
$query = $db->tbl->product->select('b.title brand', 'p.title product', 'price')
->left($db->tbl->brand)
->orderBy( // ORDER BY
'b.title', // "b"."title" NULLS FIRST,
'-price' // "price" DESC NULLS LAST
);
$query = $db->tbl->product->select('p.title product', 'color_id')
->orderBy( // ORDER BY
Sql::case('color_id') // CASE "color_id"
->when(5)->then(1) // WHEN 5 THEN 1
->when(1)->then(2) // WHEN 1 THEN 2
->else(100) // ELSE 100 END
);
use App\Db\MainDb;
$db = MainDb::instance();
$connection = $db->getConnection(); // or $db->getFreeConnection();
// $connection = new \VV\Db\Connection(...);
// from Connection directly:
$insertQuery = $connection->insert()->into('tbl_order');
// from Db:
$insertQuery = $db->insert()->into('tbl_order');
// from Table (recommended):
$insertQuery = $db->tbl->order->insert();
// $insertQuery = $connection->insert()->into($db->tbl->order);
$db->tbl->foo->insert([
'int_column' => true, // inserts `1` (or `0` for `false`)
'bool_column' => 1, // inserts `true` for `1` and `false` for `0` (exception for other numbers)
'date_column' => new \DateTimeImmutable(), // inserts date only: `Y-m-d`
'time_column' => new \DateTimeImmutable(), // inserts time only: `H:i:s`
'timestamp_column' => new \DateTimeImmutable(), // inserts date and time: `Y-m-d H:i:s`
'timestamp_tz_column' => new \DateTimeImmutable(), // inserts date and time with time zone: `Y-m-d H:i:s P`
]);
$query = $db->tbl->orderItem->insert()
->columns('order_id', 'product_id', 'price', 'quantity')
->execPer(1000);
foreach ($valuesList as $values) {
$query->values(...$values);
}
$query->execPerFinish(); // exec last
use App\Db\MainDb;
$db = MainDb::instance();
$connection = $db->getConnection(); // or $db->getFreeConnection();
// $connection = new \VV\Db\Connection(...);
// from Connection directly:
$updateQuery = $connection->update()->table('tbl_order');
// from Db:
$updateQuery = $db->update()->table('tbl_order');
// from Table (recommended):
$updateQuery = $db->tbl->order->update();
// $updateQuery = $connection->update()->table($db->tbl->order);
$db->tbl->foo->update([
'int_column' => true, // sets `1` (or `0` for `false`)
'bool_column' => 1, // sets `true` for `1` and `false` for `0` (exception for other numbers)
'date_column' => new \DateTimeImmutable(), // sets date only: `Y-m-d`
'time_column' => new \DateTimeImmutable(), // sets time only: `H:i:s`
'timestamp_column' => new \DateTimeImmutable(), // sets date and time: `Y-m-d H:i:s`
'timestamp_tz_column' => new \DateTimeImmutable(), // sets date and time with time zone: `Y-m-d H:i:s P`
]);
$transaction = $db->startTransaction();
try {
$newOrderId = $db->tbl->order->insert()
->set([
'user_id' => $userId,
'date_created' => new \DateTime(),
])
->insertedId($transaction);
echo "new Order ID: $newOrderId\n";
$affectedRows = $db->tbl->orderItem->insert()
->columns('order_id', 'product_id', 'price', 'quantity')
->values(
$db->tbl->orderItem
->select((string)$newOrderId, 'product_id', 'price', 'quantity')
->where('order_id', $orderId)
)
->affectedRows($transaction);
echo "copied Order items: $affectedRows\n";
$db->tbl->order->update()
->set(
'amount',
$db->tbl->orderItem->select('SUM(price * quantity)')->where('order_id=o.order_id')
)
->whereId($newOrderId)
->exec($transaction);
// you can execute important statement in transaction free connection
$db->tbl->log->insert()
->set(['title' => "new order copy #$newOrderId"])
->exec($db->getFreeConnection());
// throw new \RuntimeException('Test transactionFreeConnection()');
$transaction->commit();
} catch (\Throwable $e) {
$transaction->rollback();
/** @noinspection PhpUnhandledExceptionInspection */
throw $e;
}
use VV\Db\Sql;
use VV\Db\Sql\Condition;
$condition = new Condition();
// or
$condition = Sql::condition();
$condition = Sql::condition()
->and('foo')->gte(100)
->and/*('foo')*/->lte(1000) // if target expression of next predicate is same as previous one
// you may omit argument for "connector" method
->and(
(new Condition())
->or('bar')->eq(1)
->or('bar')->isNull()
// Sql::condition('bar')->eq(1)->or->isNull()
)