Read and Write Database

Reader Command

Reader is a command object that help us read records from database, this is a simple example to use Reader.

$reader = $db->getReader();

$items = $reader->setQuery($sql)->loadObjectList();

// OR

$items = $db->getReader($sql)->loadObjectList();

Query Statement

Every reader is a different query statement.

$stat1 = $db->getReader($sql1);
$stat2 = $db->getReader($sql2);

// Thses 2 object will be diferent connection cursors so they can load items parallelly.
foreach ($stat1 as $item1) {
    $stat2->loadObjectList();
}

loadObjectList()

Return an array, every element is a record and wrap with an object. This method is same as $db->loadAll():

$sql = 'SELECT * FROM foo_table WHERE state = 1';

$reader = $db->getReader($sql);

$items = $reader->loadObjectList();

The return value is an array contains all records we found, every record will be an stdClass object.

We can set object class to store records:

$items = $reader->loadObjectList(null, 'Windwalker\\Data\\Data');

// bool(true)
var_dump($items[0] instanceof \Windwalker\Data\Data);

Use column values as array index. For example, we can use id as array indexes:

$items = $reader->loadObjectList('id');

// True
($items[11]->id == 11);

loadObject()

Return only one record and wrap with an object. This method is same as $db->loadOne():

$sql = 'SELECT * FROM foo_table WHERE id = 1';

$reader = $db->getReader($sql);

$item = $reader->loadObject();

The $item will be a stdClass object or false (If no any records found). we can set object class:

$item = $reader->loadObject('MyObject');

Then the object will be an instance of MyObject.

loadArrayList()

Same as $db->loadAll('array'), returns an array and every element is an array indexed by column number as returned in your result set, starting at column 0, we can set a column as index:

$reader = $db->getReader($sql);

$items = $reader->loadArrayList();
$items = $reader->loadArrayList('id'); // Use id as index

loadArray()

Returns an array indexed by column number as returned in your result set, starting at column 0:

$reader = $db->getReader($sql);

$item = $reader->loadArray();

loadAssocList()

Returns an array and every element is an associative array indexed by column name.

$reader = $db->getReader($sql);

$items = $reader->loadAssocList();
$items = $reader->loadAssocList('id'); // Use id as index

loadAssoc()

Returns an associative array indexed by column name.

$reader = $db->getReader($sql);

$item = $reader->loadAssoc();

loadColumn()

Fetch values of a column field, please select only one column in this query:

$titles = $db->getReader('SELECT title FROM article_table')->loadColumn();

loadResult()

Fetch only one cell as a value:

// Get article id = 3 title
$title = $db->getReader('SELECT title FROM article_table WHERE id = 3')->loadResult();

// Get total hits
$sum = $db->getReader('SELECT SUM(hits) FROM article_table')->loadResult();

// Get a value
$id = $db->getReader('SELECT LAST_INSERT_ID()')->loadResult();

Run as Iterator

$reader = $db->getReader($sql);

foreach ($reader as $item) {
    $item->title;
}

About PDO Fetch

See: PHP.net / PDOStatement::fetch

Quick Fetch From Driver

DatabaseDriver::loadAll()

Using a query we set into DB object to fetch records.

$sql = 'SELECT * FROM foo_table WHERE state = 1';

$db->setQuery($sql);

$items = $db->loadAll();

// Same as $reader->loadObjectList('id')
$items = $db->loadAll('id');

// Same as $reader->loadObjectList(null, 'MyObject')
$items = $db->loadAll(null, 'MyObject');

// Same as $reader->loadArrayList()
$items = $db->loadAll(null, 'array');

// Same as $reader->loadAssocList()
$items = $db->loadAll(null, 'assoc');

The return value is an array contains all records we found.

DatabaseDriver::loadOne()

We can only get first record and ignore others:

$db->setQuery($sql);

$item = $db->loadOne();

// Same as $reader->loadObject(null, 'MyObject')
$item = $db->loadOne('MyObject');

// Same as $reader->loadArray()
$item = $db->loadOne('array');

// Same as $reader->loadAssoc()
$item = $db->loadOne('assoc');

The $item will be a record or false (If no any records found).

DatabaseDriver::loadColumn()

Same as $db->getReader($sql)->loadColumn().

DatabaseDriver::loadResult()

Same as $db->getReader($sql)->loadResult().

count()

Count the total found rows of last query.

countAffected()

Count the affected rows of last query.

insertId()

Get the last inserted id.

Writer Command

Writer object provide us an interface to write data into database.

insertOne()

Using an object or array to store data into a table, argument 3 is the name of primary key that will be added to data object if insert success:

$data = array(
    'title' => 'Sakura',
    'created' => '2014-03-02'
);

$db->getWriter()->insertOne('#__articles', $data, 'id');

// $data['id'] will be the last inserted id
echo $data['id'];

// OR using object

$data = new stdClass();

$data->title = 'Sakura';
$data->created = '2014-03-02';

$db->getWriter()->insertOne('#__articles', $data, 'id');

// $data->id will be the last inserted id
echo $data->id;

insertMultiple()

Insert many records:

$dataSet = array(
    array(
        'title' => 'Sakura',
        'created' => '2014-03-02'
    ),
    array(
        'title' => 'Sunflower',
        'created' => '2014-05-02'
    );
);

$db->getWriter()->insertMultiple('#__articles', $dataSet, 'id');

// $dataSet[0]['id'] will be the last inserted id
echo $dataSet[0]['id'];

updateOne()

Using an object or array to update a record into a table, argument 3 is the where key value that we added to query:

$data = new stdClass();

$data->id = 1;
$data->title = 'Sakura2';

$db->getWriter()->updateOne('#__articles', $data, 'id');

// Same as `UPDATE #__articles SET title = "Sakura2" WHERE id = 1;`

Also we can use array instead of object as data.

updateMultiple()

Same as update() but update every record in an array.

$dataSet = array(
    $data1,
    $data2
);

$db->getWriter()->updateMultiple('#__articles', $dataSet, 'id');

updateBatch()

Using where conditions to update some values to every records which matched this condition.

$data = new stdClass();

$data->state = 0;

// Update all author=13 records to state 0, same as `UPDATE #__articles SET state = 0 WHERE author = 15;`
$db->getWriter()->updateBatch('#__articles', $data, array('author' => 15));

Using other conditions:

$conditions = array(
    'author' => 15,
    'updated < "2014-03-02"',
    'catid' => array(1, 2, 3)
);

$db->getWriter()->updateBatch('#__articles', $data, $conditions);

// Same as `UPDATE #__articles SET state = 0 WHERE author = 15 AND updated < "2014-03-02" AND catid IN(1, 2, 3);`

See Conditions

Save & SaveMultiple

save() and saveMultiple() will auto check the primary exists or not. If primary key exists, it will use update, if not exists, it will use insert to store data.

Delete

Use conditions to delete data.

$db->getWriter()->delete('table', array('id' => 5));

See Conditions

insertId()

Get the last inserted id.

countAffected()

Count the affected rows of last query.

Transaction Command

Start Transaction

$tran = $this->db->getTransaction()->start();

try
{
    $this->db->setQuery($sql1)->execute();

    $this->db->setQuery($sql2)->execute();

    $this->db->setQuery($sql3)->execute();
}
catch (\Exception)
{
    $tran->rollback();
}

$tran->commit();

Database Command

How to get Database Command:

$database = $db->getDatabase('flower');

create()

Create a new database.

$database = $db->getDatabase('flower');

$database->create();

drop()

Drop a database.

$database = $db->getDatabase('flower');

$database->drop();

rename()

Rename a database.

$database = $db->getDatabase('flower');

// The return value is a new command object
$newDatabaseCommand = $database->rename('flower2');

$newDatabaseCommand->getName(); // flower2

getTables()

Get table name list.

$database = $db->getDatabase('flower');

$tables = $database->getTables();

getTableDetail() & getTableDetails()

Get tables information detail.

If you found a typo or error, please help us improve this document.