CryCMS-Db

PHP Class for working with MYSQL via PDO.

Write queries or use methods.

Singleton.

Setup

Db::config([
    'host' => 'localhost',
    'user' => 'test',
    'password' => 'test',
    'database' => 'test',
]);

Debug mode

Db::debug(true);

---

Two ways of queries

Native SQL query (also with placeholders)

Db::sql()->query('query', [])->exec();
Db::sql()->query('query', [])->getOne();
Db::sql()->query('query', [])->getAll();

Via builder

$result = Db::table('table')->getOne();
$result = Db::table('table')->getAll();

---

Examples

Create table

Db::table('table')->create([
    'id' => 'INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY',
    'name' => 'VARCHAR(255)',
    'date' => 'DATETIME',
], 'ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT="TEST"');

Add index

Db::table('table')->index(['id', 'date'], 'UNIQUE');

Truncate table

Db::table('table')->truncate();

Drop table

Db::table('table')->drop();

---

Get list of table fields

Db::table('table')->fields();

Queries log (debug=true only)

$log = Db::getLog();

Get query instead of execute

replace methods getOne() or getAll() to getSQL()

---

Insert

Db::table('table')->insert([
    'name' => 'first',
    'date' => date('Y-m-d'),
]);

Get autoincrement after insert

$id = Db::lastInsertId();

Get one row - case 1

$one = Db::table('table')
    ->select(['id', 'name', 'date'])
    ->where(['id = :id'])
    ->values(['id' => $id])
    ->getOne();

Get one row - case 2

$one = Db::table('table')
    ->select(['id', 'name', 'date'])
    ->where(['id' => 1])
    ->getOne();

Get list of rows

$all = Db::table('table', 't')
    ->select(['t.id', 'td.field_1', 'td.field_2'])
    ->calcRows()
    ->leftJoin('testData', 'td', 'td.test_id = t.id')
    ->where(['t.date <= :date'])
    ->values(['date' => date('Y-m-d')])
    ->offset(0)
    ->limit(5)
    ->groupBy(['t.id'])
    ->orderBy(['t.id' => 'DESC'])
    ->getAll();

Get count of rows (with calcRows only)

$count = Db::getFoundRows();

Update - case 1

Db::table('table')->update([
    'name' => 'NAME2'
], [
    'id' => $id
]);

Update - case 2

Db::table('table')->update([
    'name' => 'NAME3',
], [
    'id = :id', // array of SQL with placeholders
], [
    'id' => $id,
]);

Delete - case 1

Db::table('table')->delete([
    'id' => $id
]);

Delete - case 2

Db::table('table')->delete([
    'date <= :date', // array of SQL with placeholders
], [
    'date' => date('Y-m-d')
]);

New singlenton instance with different connection (to other MySQL, for example)

use CryCMS\Db;

class Db2 extends Db
{
    protected static $config;
    protected static $dbh;
    protected static $debug = false;
    protected static $log = [];
}

Db2::config(
    [
        'host' => '',
        'user' => '',
        'password' => '',
        'database' => '',
    ]
);