MageWork

Back to home

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

Conditions

Conditions are given to where() and to the joins. Values are bound as strings (shown inline below).

Structure

ConditionSQL
['email =' => 'john@example.com'](email = 'john@example.com')
['email' => 'john@example.com'](email = 'john@example.com'), = is the default operator
['email =' => 'john@example.com', 'lastname =' => 'Doe'](email = 'john@example.com' OR lastname = 'Doe')
[['email =' => 'john@example.com'], ['is_active =' => 1]](email = 'john@example.com') AND (is_active = '1')
['email IS NULL'](email IS NULL), raw SQL condition
['']Ignored, an empty raw SQL condition has no column to apply to
['email =' => ['john@example.com', 'jane@example.com']](email = 'john@example.com' OR email = 'jane@example.com')
['id IN' => [1, 2]](id IN ('1','2'))
['0 = STRCMP' => ['John', 'Jane']](0 = STRCMP ('John','Jane')), also GREATEST and LEAST

Allowed operators: =, !=, <>, <, <=, >, >=, <=>, LIKE, NOT LIKE, IN, NOT IN.

Values

ConditionSQL
['status =' => 0](status = '0')
['status =' => '0'](status = '0')
['is_active =' => false](is_active = '0')
['is_active =' => true](is_active = '1')
['status =' => ''](status = '')
['status =' => null]Ignored
[['id =' => 1], ['email =' => null]](id = '1')
['id IN' => [1, null, '', false]](id IN ('1','','0')), null is removed from the list
['id IN' => []](0 = 1), an empty list matches nothing
['id IN' => [null]](0 = 1), the list is empty once cleaned
['id NOT IN' => []](1 = 1), an empty list excludes nothing (same for !=, <> and NOT LIKE)
['0 = STRCMP' => ['John', null]](0 = STRCMP ('John',NULL)), function arguments are kept as is

A null value means "no filter". Check the values coming from the request before using them in a condition:
with ['token =' => $_GET['token'] ?? null], a missing token removes the condition and the query returns any row.

When all the conditions are ignored, the query has no WHERE clause. To prevent a whole table change, update() and
delete() throw a LogicException in this case:

<?php

App::db()->where(['email =' => null])->delete('customers');

// LogicException: All conditions are empty, the statement would affect the whole table

Calling update() or delete() without where() (or with an empty array) affects the whole table.

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

Multiple connections

App::db() uses the database model. To use a second database, create a model that
extends Core_Model_Database:

packages/Acme/Model/Reporting.php

<?php

declare(strict_types=1);

class Acme_Model_Reporting extends Core_Model_Database
{
}

Configure its credentials under the model identifier:

<?php
// etc/local.php

$config = [
    /* ... */
    'default' => [
        Core_Model::TYPE => [
            'reporting' => [
                'db_host' => '',
                'db_username' => '',
                'db_password' => '',
                'db_database' => '',
            ],
        ],
    ],
    /* ... */
];

Then pass the model identifier to App::db():

<?php

$rows = App::db('reporting')->getAll('events');