Below is a list of available database methods to run queries.
query
Run database query. You may use :prefix: keyword in the query which will be replaced with the table prefix. You may also bind values to the query via ? character.
// Simply query
$this->db->query("SELECT * FROM `news` WHERE `id`=5 LIMIT 1")->row();
// Include table prefix
$this->db->query("SELECT * FROM `:prefix:news` WHERE `id`=5 LIMIT 1")->row();
// Bind value
$this->db->query("SELECT * FROM `:prefix:news` WHERE `id`=? LIMIT 1", array(5))->row();
When binding value, system automatically escapes them which makes it very convenient.
escape
Escape string before submitting it into your database.
$this->db->escape('exa"mple'); // 'exa\"mple'
$id = input::post("id") // 5
$id = $this->db->escape($id); // 5
$this->db->query("SELECT * FROM `:prefix:table` WHERE `id`=" . $id . " LIMIT 1");
escapeLike
This method should be used when strings are to be used in LIKE conditions so that LIKE wildcards ('%', '_') in the string are also properly escaped.
$this->db->escape('5'); // 5
$this->db->escapeLike('exa%mple'); // '%exa\%mple%'
$this->db->escapeLike('exa%mple', false); // 'exa\%mple'
$this->db->escapeLike('exa%mple', 'left'); // '%exa\%mple'
$this->db->escapeLike('exa%mple', 'right'); // 'exa\%mple%'
insert
Insert table row.
$data = array(
'title' => "Hello",
'body' => "Example entry"
);
// $id will contain primary ID value of the new row (if available)
$id = $this->db->insert('news', $data);
update
Update table row.
$data = array(
'body' => "Replaced text"
);
// The last argument is optional and limits the query to 1 row
$this->db->update('news', $data, array('id' => 5), 1);
// You may use several conditional arguments if necessary
$this->db->update('news', $data, array('id' => 5, 'user_id' => 1), 1);
delete
Delete table row.
// Delete record with ID of 5 and limit it to 1 row
$this->db->delete('news', array('id' => 5), 1);
affectedRows
Get number of affected rows.
$total = $this->db->affectedRows();
insertID
Get insert ID.
$id = $this->db->insertID();
startTransaction
Start transaction.
$this->db->startTransaction();
endTransaction
End transaction.
$this->db->endTransaction();
rollbackTransaction
Rollback transaction.
$this->db->rollbackTransaction();
getQueries
Get query log, only if debug is enabled.
$queries = $this->db->getQueries();
getQueryTimes
Get query times array, only if debug is enabled.
$times = $this->db->getQueryTimes();
getQueryTime
Get total time it took to run queries, only if debug is enabled.
$time = $this->db->getQueryTime();
getTotalQueries
Get total query ran, only if debug is enabled.
$total = $this->db->getTotalQueries();