PHP code example of inclitoleo / dbmysql

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

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

    

inclitoleo / dbmysql example snippets

use Inclitoleo\Mysql\Connection\ConnectionConfig;
use Inclitoleo\Mysql\Connection\ConnectionManager;
use Inclitoleo\Mysql\Query\QueryBuilder;
use Inclitoleo\Mysql\Security\SchemaRegistry;

$manager = new ConnectionManager(new ConnectionConfig(
    host: '127.0.0.1',
    port: 3306,
    database: 'app',
    username: 'app',
    password: 'secret',
    debug: false,
));

$schema = new SchemaRegistry();
$schema->register('account', ['id', 'name', 'email']);
$schema->register('orders', ['id', 'account_id', 'total']);
$schema->register('logs', ['id', 'message', 'created_at']);

$builder = new QueryBuilder($schema, $manager);

// config/dbmysql.php
'tables' => [
    'account' => ['id', 'name', 'email'],
    'orders' => ['id', 'account_id', 'total'],
],
'debug' => false,

use Inclitoleo\Mysql\Query\QueryBuilder;

public function index(QueryBuilder $db)
{
    return $db->raw('SELECT * FROM account WHERE id = ?', [1]);
}

$config = ConnectionConfig::fromArray(config('database.connections.mysql'));

use Cake\Core\Configure;
use Inclitoleo\Mysql\Connection\ConnectionConfig;
use Inclitoleo\Mysql\Connection\ConnectionManager;
use Inclitoleo\Mysql\Query\QueryBuilder;
use Inclitoleo\Mysql\Security\SchemaRegistry;

$config = ConnectionConfig::fromArray(Configure::read('Datasources.default'));
$schema = new SchemaRegistry();
$schema->register('account', ['id', 'name', 'email']);
$builder = new QueryBuilder($schema, new ConnectionManager($config));

$accounts = $builder->raw('SELECT * FROM account');

public function index(QueryBuilder $db): JsonResponse
{
    return $this->json($db->raw('SELECT * FROM account'));
}

$accounts = $builder->raw('SELECT * FROM account');

$one = $builder->raw('SELECT * FROM account WHERE id = ?', [1]);

$builder->raw('UPDATE account SET name = ? WHERE id = ?', ['Ada', 1]);

$rows = $builder->raw(
    'SELECT a.id, a.name, o.total
     FROM account a
     INNER JOIN orders o ON o.account_id = a.id
     WHERE a.id = ?',
    [1],
);

$left = $builder->raw(
    'SELECT a.name, o.total
     FROM account a
     LEFT JOIN orders o ON o.account_id = a.id',
);

$rows = $builder->raw(
    'WITH recent AS (
         SELECT account_id, SUM(total) AS spent
         FROM orders
         GROUP BY account_id
     )
     SELECT a.name, r.spent
     FROM account a
     INNER JOIN recent r ON r.account_id = a.id',
);

$recent = (new QueryBuilder($schema))
    ->from('orders')
    ->select(['account_id', 'total']);

$rows = $builder
    ->with('recent', $recent)
    ->from('recent')
    ->select(['account_id', 'total'])
    ->get();

// string subquery also works
$rows = $builder
    ->with('recent', 'SELECT account_id, SUM(total) AS spent FROM orders GROUP BY account_id')
    ->from('recent')
    ->select(['account_id', 'spent'])
    ->get();

$rows = $builder
    ->from('account')
    ->select(['id', 'name'])
    ->rowNumber('rank', partitionBy: 'id', orderBy: 'id')
    ->get();
// SELECT id, name, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rank FROM account

use Inclitoleo\Mysql\Query\SortDirection;

$rows = $builder
    ->from('account')
    ->select(['id', 'name', 'email'])
    ->where('id', '=', 1)
    ->orderBy('name', SortDirection::ASC)
    ->limit(10)
    ->get();

$one = $builder->from('account')->where('email', '=', '[email protected]')->first();
$id = $builder->insert('account', ['name' => 'Ada', 'email' => '[email protected]']);

$builder->from('account')->select(['id', 'name']); // ok
$builder->raw('SELECT * FROM account');           // ok
$builder->from('account; DROP TABLE users');      // InvalidIdentifierException
$builder->raw('SELECT * FROM account; DROP TABLE account'); // InvalidIdentifierException

$manager->transaction(function (ConnectionManager $m) use ($builder): void {
    $builder->insert('account', ['name' => 'Ada', 'email' => '[email protected]']);
    $builder->insertBatch('account', $rows, chunkSize: 500);
    $builder->upsert('account', ['email' => '[email protected]', 'name' => 'Ada L.'], ['name']);
});

$page = $builder->cursorPaginate('account', 'id', after: 100, limit: 50)->get();

foreach ($builder->from('logs')->cursor() as $row) {
    // unbuffered generator
}

$manager->registerEndpoint('primary', $primaryConfig);
$manager->registerEndpoint('replica', $replicaConfig);

$write = $manager->connectionFor('write'); // always primary
$read  = $manager->connectionFor('read');  // replica if registered, otherwise primary

$manager = new ConnectionManager($config, logger: $psrLogger);

use Inclitoleo\Mysql\database\MySqlClient;

$db = new MySqlClient();
$id = $db->insert('account', $obj);

const INCLITOSTRICT = true;

// before
$db = new MySqlClient();
$row = $db->select('account', 'id', 1);
$all = $db->select_all('SELECT * FROM account WHERE id = 1', 'A');

// after — same SQL you already had
$all = $builder->raw('SELECT * FROM account WHERE id = ?', [1]);

// or the builder, if you prefer
$row = $builder->from('account')->where('id', '=', 1)->first();

const INCLITOHOST = 'localhost:3306';
const INCLITOPORT = 3306; // optional; default 3306 if undefined
const INCLITODBNAME = '';
const INCLITOUSER = '';
const INCLITOPWD = '';
const INCLITODRIVER = 'mysql';
const INCLITOTYPECONN = false;
const INCLITOBOOLCERT = false;
const INCLITOMYCA = '';
const INCLITOMYCERT = '';
const INCLITOMYKEY = '';
const INCLITOSTRICT = false;

use Inclitoleo\Mysql\Exception\ErrorCode;
use Inclitoleo\Mysql\Exception\MysqlException;

$manager = new ConnectionManager(new ConnectionConfig(
    host: '127.0.0.1',
    database: 'app',
    username: 'app',
    password: 'secret',
    debug: false, // set true only on a local machine
));

try {
    $builder->insert('account', ['name' => 'Leo', 'email' => '[email protected]']);
    echo ErrorCode::ok(); // {"code":200}
} catch (MysqlException $e) {
    echo $e->getMessage(); // debug=false → {"code":500}  |  debug=true → SQLSTATE cru
    echo $e->toJson();     // always {"code":N}
    // $e->getDetail(), getSql(), getBindings() — for your logger, never for the client
}

shell
php artisan vendor:publish --tag=dbmysql-config