Working with database. About $this->db available both in the controller and model
This property holds an instance of Database class.
Database error reporting is set to none on the production mode, warning on staging mode, and exception (fatal) on development mode.
For credentials, character set and collation, please edit /config.php
Get a single value.
<?php
$totalUsers = $this->db->getValue("SELECT COUNT(*) FROM `staff`");
d($totalUsers);
// 124
Get a single row.
<?php
$user = $this->db->getRow("SELECT * FROM `staff` WHERE `email` = :email LIMIT 1", [':email' => 'admin@example.com']);
d($user);
/*
array (
id => 136
first_name => "John"
email => "john@example.com"
)
*/
Get a column as a flat array.
<?php
$names = $this->db->getColumn("SELECT `first_name` FROM `staff` WHERE `id` > :id", [':id' => 7]);
d($names);
/*
array (
0 => "Foo"
1 => "Bar"
)
*/
Fetch unique. In this example array keys (first column selected) will be the ids.
<?php
$data = $this->db->getUnique("SELECT `id`, `staff`.* FROM `staff` WHERE `id` >= :id", [':id' => 16]);
d($data);
/*
array (
16 => array (
id => 16
email => "admin@example.com"
first_name => "Foo"
)
)
*/
Fetch key pair. In this example array keys will be the ids and array values will be the first name.
<?php
$data = $this->db->getPair("SELECT `id`, `first_name` FROM `staff` WHERE `id` >= :id", [':id' => 16]);
d($data);
/*
array (
16 => "Foo"
17 => "Bar"
)
*/
Fetch group by first column selected.
<?php
$data = $this->db->getGroup("SELECT `email`, `newsletter`.* FROM `newsletter` WHERE `id` >= :id", [':id' => 16]);
d($data);
/*
array (
foo@example.com => array (
0 => array (
id => 16
email => "foo@example.com"
)
0 => array (
id => 17
email => "foo@example.com"
)
)
bar@example.com => array (
0 => array (
id => 18
email => "bar@example.com"
)
)
)
*/
Fetch all. The result will be an array of arrays.
<?php
$data = $this->db->getAll("SELECT * FROM `users` WHERE `id` >= :id", [':id' => 16]);
d($data);
/*
array (
0 => array (
id => 16
email => "foo@example.com"
first_name => "Foo"
)
1 => array (
id => 17
email => "bar@example.com"
first_name => "Bar"
)
)
*/
Custom fetch.
<?php
$data = $this->db->getCustom("SELECT * FROM `staff` WHERE `id` >= :id", [':id' => 16], \PDO::FETCH_OBJ, fetchAll:false);
d($data);
/*
Object stdClass {
id => 16
email => "foo@example.com"
first_name => "Foo"
}
*/
Insert, update, delete. Mostly this method returns the affected rows.
<?php
$active = 1;
$this->db->set("INSERT INTO `staff` SET `email` = :email, `active` = ".intval($active), [':email' => 'foo@example.com']);
Last insert Id.
<?php
$this->db->set("INSERT INTO `staff` SET `email` = :email", [':email' => 'bar@example.com']);
d($this->db->getLastInsertId());
// 19