Database

Insert

<?php

$id = App::db()
    ->insert('customers', ['email' => 'john@example.com', 'firstname' => 'John', 'lastname' => 'Doe']);
    
// INSERT INTO customers (email,firstname,lastname) VALUES ('john@example.com','John','Doe')

<?php

$id = App::db()
    ->insert(
        'customers',
        ['email' => 'john@example.com', 'attempts' => 1],
        ['attempts = attempts + 1']
    );
    
// INSERT INTO customers (email) VALUES ('john@example.com') ON DUPLICATE KEY UPDATE attempts = attempts + 1

Update

<?php

$count = App::db()
    ->where(['email =' => 'john@example.com'])
    ->update('customers', ['firstname' => 'Jane', 'lastname' => null]);
    
// UPDATE customers SET firstname = 'Jane', lastname = NULL WHERE (email = 'john@example.com')

<?php

$count = App::db()
    ->where(['email =' => 'john@example.com'])
    ->update('customers', ['failures_num = failures_num + 1', 'lastname = upper(lastname)']);
    
// UPDATE customers SET failures_num = failures_num + 1, lastname = upper(lastname) WHERE (email = 'john@example.com')

Delete

<?php

App::db()->where(['email =' => 'john@example.com'])->delete('customers');
    
// DELETE FROM customers WHERE (email = 'john@example.com')

Get All

<?php

$customers = App::db()
    ->getAll('customers');
    
// SELECT * FROM customers

<?php

$customers = App::db()
    ->groupBy('email')
    ->orderBy('firstname', 'ASC')
    ->page(3)
    ->limit(20)
    ->getAll('customers', ['firstname', 'lastname', 'email']);
    
// SELECT firstname,lastname,email FROM customers GROUP BY email ORDER BY firstname ASC LIMIT 40,20

<?php

$customers = App::db()
    ->leftJoins([
        'orders o' => ['o.customer_id = c.id'],
        'invoices i' => ['i.order_id = o.id'],
    ])
    ->getAll('customers c');
    
// SELECT * FROM customers c LEFT JOIN orders o ON (o.customer_id = c.id) LEFT JOIN invoices i ON (i.order_id = o.id)

<?php

$customers = App::db()
    ->where([
        [
            'email =' => 'john@example.com',
            // OR
            'lastname =' => 'Doe'
        ],
        // AND
        ['is_active =' => 1],
        // AND
        ['`group` IN' => ['general', 'gold']]
    ])
    ->getAll('customers');
    
// SELECT * FROM customers WHERE (email = 'john@example.com' OR lastname = 'Doe') AND (is_active = '1') AND (`group` IN ('general','gold'))

Get Row

<?php

$customer = App::db()
    ->where(['email =' => 'john@example.com'])
    ->getRow('customers');
    
// SELECT * FROM customers WHERE (email = 'john@example.com') LIMIT 0,1

Get Value

<?php

$customerId = App::db()
    ->where(['email =' => 'john@example.com'])
    ->getVal('customers', ['id']);
    
// SELECT id FROM customers WHERE (email = 'john@example.com') LIMIT 0,1

Debug & Dump

<?php

App::db()->debug(true); // Do not execute all the next requests

App::db()
    ->where(['email =' => 'john@example.com'])
    ->update('customers', ['firstname' => 'Jane']);
   
var_dump(App::db()->dump()); // Retrieve last query and params

App::db()->debug(false); // Disabled the debug mode