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')
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')
Get All
<?php
$customers = App::db()
->getAll('customers');
// SELECT * FROM customers
<?php
$customers = App::db()
->groupBy('email')
->orderBy('firstname', 'ASC')
->page(1)
->limit(20)
->getAll('customers', ['firstname', 'lastname', 'email']);
// SELECT firstname,lastname,email FROM customers GROUP BY email ORDER BY firstname ASC LIMIT 1,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