2 Database usage examples

VasyOk edited this page 2016-02-28 04:26:16 +03:00

Table of Contents

• Insert
• Update
• Delete
• Select
• Increment
• Decrement

$di = \TorrentPier\Di::getInstance();

/** @var \TorrentPier\Db\Adapter $db */
$db = $di->db;

Insert

Return id inserted row.

$insert = ['fieldName' => 'value', ...];
$db->insert(string $tableName, array $insert) : int

Sample:

$id = $query->insert('table_name', [
    'field' => 'value'
]);

Update

Will return the number of rows affected.

$update = ['fieldName' => 'new_value', ...];
$where = ['fieldName' => 'value', ...];
// or
$where = function(\Zend\Db\Sql\Where $where) { /* code */ };

$db->update(string $tableName, array $update, $where) : int

Sample:

$countRows = $query->update('table_name', [
    'field' => 'new value'
], [
    'field' => 'current value'
]);
// or
$countRows = $query->update('table_name', [
    'field' => 'new value'
], function(\Zend\Db\Sql\Where $where) use ($insertRowId) {
    $where->equalTo('field', 'current value');
});

Delete

Will return the number of rows affected.

$where = ['fieldName' => 'value', ...];
// or
$where = function(\Zend\Db\Sql\Where $where) { /* code */ };

$db->delete(string $tableName, $where) : int

Sample:

$countRows = $query->delete('table_name', [
    'field' => 'value'
]);
// or
$countRows = $query->delete('table_name', function(\Zend\Db\Sql\Where $where) use ($insertRowId) {
    $where->equalTo('field', 'value');
});

Select

Returns an object of the result

/** @var \TorrentPier\Db\PrepareStatement $result */
$result = $db->select(BB_USERS, function(\Zend\Db\Sql\Select $select) {
    $select->columns(['id' => 'user_id', 'name' => 'username']);
    $select->where(function(\Zend\Db\Sql\Where $where) {
        $where->greaterThanOrEqualTo('field', 'value');
    });
    $select->join(['t' => BB_TOPICS], 't.topic_poster = u.user_id', ['title' => 'topic_title']);
});

/** @var \ArrayObject $oneResult **/
$oneResult = $result->one();
/** @var \Zend\Db\ResultSet\ResultSet $moreResult **/
$moreResult = $result->all();

Increment

$countRows = $db->increment($table, $field, $where = null, $num = 1)

Decrement

$countRows = $db->decrement($table, $field, $where = null, $num = 1)