PHP code example of tribal2 / db-handler

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

tribal2 / db-handler example snippets

use Tribal2\DbHandler\Core\PDOWrapper;
use Tribal2\DbHandler\Db;
use Tribal2\DbHandler\DbConfig;

$config = DbConfig::create('my_database')
  ->withUser('username')
  ->withPassword('password')
  ->withHost('localhost')   // Optional. Default: 'localhost'
  ->withPort(3306)          // Optional. Default: 3306
  ->withCharset('utf8mb4'); // Optional. Default: 'utf8mb4'

$pdoWrapper = new PDOWrapper(
  $config,
  // Optional Psr\Log\LoggerInterface instance.
  // $logger, // Default: Psr\Log\NullLogger
);

$db = new Db(
  $pdoWrapper,
  // Optional Psr\SimpleCache\CacheInterface instance.
  // $cache,  // Default: NULL
);

$where = Where::equals('status', 'active');
// Output: `status` = :status___1

$where = Where::notEquals('category', 'archived');
// Output: `category` <> :category___1

$where = Where::greaterThan('price', 100);
// Output: `price` > :price___1

$where = Where::greaterThanOrEquals('price', 100);
// Output: `price` >= :price___1

$where = Where::lessThan('price', 50);
// Output: `price` < :price___1

$where = Where::lessThanOrEquals('price', 50);
// Output: `price` <= :price___1

$where = Where::isNull('description');
// Output: `description` IS NULL

$whereNotNull = Where::isNotNull('description');
// Output: Output: `description` IS NOT NULL

$where = Where::like('name', '%Apple%');
// Output: `name` LIKE :name___1

$where = Where::notLike('name', '%Apple%');
// Output: `name` NOT LIKE :name___1

$where = Where::between('date', '2021-01-01', '2021-12-31');
// Output: `date` BETWEEN :date___1 AND :date___2

$where = Where::notBetween('date', '2021-01-01', '2021-12-31');
// Output: `date` NOT BETWEEN :date___1 AND :date___2

$where = Where::in('status', ['active', 'pending', 'on-hold']);
// Output: `status` IN (:status___1, :status___2, :status___3)

$where = Where::notIn('status', ['active', 'pending', 'on-hold']);
// Output: `status` NOT IN (:status___1, :status___2, :status___3)

$where1 = Where::equals('status', 'active');
$where2 = Where::greaterThan('price', 100);
$orWhere = Where::or($where1, $where2);
// Output: (`status` = :status___1 OR `price` > :price___1)

$andWhere = Where::and($where1, $where2);
// Output: (`status` = :status___1 AND `price` > :price___1)

$where3 = Where::equals('category', 'archived');
$combinedWhere = Where::and($where3, $orWhere);
// Output: (`category` = :category___1 AND (`status` = :status___1 OR `price` > :price___1))

$results = $db
  ->select()
  ->columns(['column1', 'column2'])
  ->from('table_name')
  ->where(Where::equals('column2', 1))
  ->fethAll();

$select = $db->select()
  ->columns(['column1', 'column2'])
  ->column('column3')
  ->from('table_name')
  ->where(Where::equals('column2', 1))  // See "Where Clauses" section above
  ->groupBy('column1')
  ->having(Where::equals('sum(column2)', 5))
  ->orderBy('column3', 'ASC')
  ->limit(10)
  ->offset(5);

$sql = $select->getSql();
// $sql:
// SELECT
//     `column1`,
//     `column2`,
//     `column3`
// FROM
//     `table_name`
// WHERE
//     `column2` = :column2___1
// GROUP BY
//     `column1`
// HAVING
//     `sum(column2)` = :sum_column2____1
// ORDER BY
//     `column3` ASC
// LIMIT
//     10
// OFFSET
//     5;

$allResults = $select->fetchAll();
$firstResult = $select->fetchFirst();
$column1Values = $select->fetchColumn('column1');
$column3DistinctValues = $select->fetchDistincts('column3');

// Output: object(FetchResult) {
//     data => array(n) {
//         [0]...
//         [1]...
//         [n-1]...
//     },
//     count => int(n)
// }

$countResults = $select->fetchCount();
// Output: 5

  $select = $db->select()
    ->from('table_name')
    // ...
    ->paginate(itemsPerPage: 10);
  

  $select = $db->select()
    ->from('table_name')
    // ...
    ->limit(10)
    ->offset(0);
  

// Example output structure of FetchPaginatedResult
object(FetchPaginatedResult) {
    data => array(n) {
        [0]...
        [1]...
        [n-1]...
    },
    count => int(n),
    page => int(10),
    perPage => int(10),
    totalPages => int(23)
}

$select = $db->select()->setCache($simpleCacheInstance);

$select->withCache(defaultValue, ttl);

$allResults = $select->fetchAll();
$firstResult = $select->fetchFirst();
$column1Values = $select->fetchColumn('column1');
$column3DistinctValues = $select->fetchDistincts('column3');

$insert = $db->insert()
  ->into('table_name')
  ->value('column1', 'value1')
  ->values(['column2' => 'value2', 'column3' => 'value3']);

$rows = [
  ['column1' => 'value1', 'column2' => 'value2'],
  ['column1' => 'value3', 'column2' => 'value4'],
  // ...
];
$insert->rows($rows);

$success = $insert->execute();

$update = $db->update()
  ->table('table_name')
  ->set('column1', 'newValue1')
  ->set('column2', 'newValue2');
    

$update->where(Where::equals('column3', 'conditionValue'));

$success = $update->execute();

$delete = $db->delete()
  ->from('table_name')
  ->where(Where::equals('column', 'value'));

$success = $delete->execute();

$procedure = $db->storedProcedure()
  ->call('procedure_name')
  ->with('paramName', $value)
  // ...
  ->with('paramName2', $value);

$results = $procedure->execute();

$db->transaction->begin();
$db->transaction->commit();
$db->transaction->rollback();

// Begin a transaction
$db->transaction->begin();

// Disable automatic commits
$db->transaction->setCommitsModeOff();

// Execute other methods that use transactions
// $db->transaction->begin();
// ...
// $db->transaction->commit();

// Re-enable automatic commits
$db->transaction->setCommitsModeOn();

// Commit the transaction
$db->transaction->commit();