PHP code example of finesse / mini-db

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

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

    

finesse / mini-db example snippets

$database = Database::create([
    'driver'   => 'mysql',
    'dsn'      => 'mysql:host=localhost;dbname=my_database',
    'username' => 'root',
    'password' => 'qwerty',
    'prefix'   => 'test_'
]);

$database->statement('
    CREATE TABLE '.$database->addTablePrefix('users').' (
        id INT(11) NOT NULL AUTO_INCREMENT,
        email VARCHAR(50) NOT NULL, 
        account INT(11) NOT NULL DEFAULT 0
    )
');

$database->table('users')->insert([
    ['name' => 'Jack', 'account' => 1200],
    ['name' => 'Bob', 'account' => 500],
    ['name' => 'Richard', 'account' => 800]
]);

$database->table('users')->where('account', '>', 600)->get(); // Jack and Richard

use Finesse\MiniDB\Database;

$database = Database::create([
    'driver'   => 'mysql',                     // DBMS type: 'mysql', 'sqlite' or anything else for other (optional) 
    'dsn'      => 'mysql:host=host;dbname=db', // PDO data source name (DSN)
    'username' => 'root',                      // Database username (optional)
    'password' => 'qwerty',                    // Database password (optional)
    'options'  => [],                          // PDO options (optional)
    'prefix'   => ''                           // Tables prefix (optional)
]);

use Finesse\MicroDB\Connection;
use Finesse\MiniDB\Database;
use Finesse\QueryScribe\Grammars\MySQLGrammar;
use Finesse\QueryScribe\PostProcessors\TablePrefixer;

$connection = Connection::create('mysql:host=host;dbname=db', 'username', 'password');
$grammar = new MySQLGrammar();
$tablePrefixer = new TablePrefixer('demo_');

$database = new Database($connection, $grammar, $tablePrefixer);

$database->insertGetId('INSERT INTO users (name, email) VALUES (?, ?), (?, ?)', ['Ann', '[email protected]', 'Bob', '[email protected]']); // 19 (the last inserted row id)

$database->select('SELECT * FROM users WHERE name = ? OR email = ?', ['Jack', '[email protected]']);
/*
    [
        ['id' => 4, 'name' => 'Jack', 'email' => '[email protected]', 'account' => 1230],
        ['id' => 17, 'name' => 'Bill', 'email' => '[email protected]', 'account' => -100]
    ]
 */
 
$database->import('path/to/file.sql');

$database->select('SELECT * FROM '.$database->addTablePrefix('users').' ORDER BY id');

$database
    ->table('users')
    ->where('status', 'active')
    ->orderBy('name')
    ->offset(40)
    ->limit(10)
    ->get();
    
/*
    [
        ['id' => 17, 'name' => 'Bill', 'email' => '[email protected]', 'status' => 'active'],
        ['id' => 4, 'name' => 'Jack', 'email' => '[email protected]', 'status' => 'active']
    ]
 */

$database
    ->table('users')
    ->where('status', 'active')
    ->orderBy('name')
    ->first();
    
/*
    ['id' => 17, 'name' => 'Bill', 'email' => '[email protected]', 'status' => 'active'] or null
 */

$query = $database
    ->table('posts')
    ->where('category', 'archive')
    ->orderBy('date', 'desc');
    // Don't call ->get() here

use Finesse\MiniDB\ThirdParty\PagerfantaAdapter;
use Pagerfanta\Pagerfanta;

$paginator = new Pagerfanta(new PagerfantaAdapter($query));
$paginator->setMaxPerPage(10); // The number of rows on a page
$paginator->setCurrentPage(3); // The current page number

$currentPageRows = $paginator->getCurrentPageResults(); // The rows for the current page
$pagesCount = $paginator->getNbPages();                 // Total pages count
$haveToPaginate = $paginator->haveToPaginate();         // Whether the number of results is higher than the max per page

$database
    ->table('users')
    ->orderBy('id')
    ->chunk(100, function ($users) {
        foreach ($users as $user) {
            // Process a row here
        }
    });

$database
    ->table('products')
    ->where('price', '>', 1000)
    ->count(); // 31

$database->table('debts')->insert([
    ['name' => 'Sparrow', 'amount' => 13000, 'message' => 'Sneaky guy'],
    ['name' => 'Barbos', 'amount' => 4999, 'message' => null],
    ['name' => 'Pillower', 'message' => 'Call tomorrow']
]); // 3 (number of inserted rows)

$database->table('debts')->insertGetId([
    'name' => 'Bigbigger',
    'amount' => -3500,
    'message' => 'I owe him'
]); // 4 (id of the inserted row)

$database->table('debts')->insertFromSelect(['name', 'amount', 'message'], function ($query) {
    $query
        ->from('users')
        ->addSelect(['name', $query->raw('- account'), 'description'])
        ->where('status', 'debtor');
}); // 6 (number of inserted rows)

$database
    ->table('posts')
    ->where('date', '<', '2017-01-01')
    ->update([
        'status' => 'obsolete',
        'category' => null
    ]); // 5 (number of updated rows)

$database
    ->table('messages')
    ->where('sender_id', 1456)
    ->orWhere('status', 'stink')
    ->delete(); // 5 (number of deleted rows)

$searchString = '%iamhacker%';

$query->where('name', 'like', $query->escapeLikeWildcards($searchString).'_'); // "name" LIKE \%iamhacker\%_

$query->whereRaw('MIN('.$query->quoteIdentifier('data"base').'.'.$query->quoteIdentifier('ta"ble').') > 10');
// or
$query->whereRaw('MIN('.$query->quoteCompositeIdentifier('data"base.ta"ble').') > 10'); // MIN("data""base"."ta""ble") > 10

$database
    ->table('users', 'u')
    ->addSelect('name')
    ->where('status', 'verified')
    ->orWhere('u.type', 'admin')
    ->addTablesToColumnNames();

// SELECT "name" FROM "users" AS "u" WHERE "status" = ? OR "u"."type" = ?