Nicotine

Open source framework for PHP & MySQL

Downloads Page

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_OBJfetchAll: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