Queries

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();