CodeIgniter's active record functions (https://ellislab.com/codeigniter/user-guide/database/active_record.html)
<?php
class Active_record_example extends CI_Model {
const DB_TABLE = 'abstract';
const DB_TABLE_PK = 'abstract';
/**
* create record
*/
private function insert() {
// CI's INSERT - params: table name, object/array to INSERT
$this->db->insert($this::DB_TABLE, $this);
// add INSERT id to model (its existence decides wether to UPDATE or INSERT)
$this->{$this::DB_TABLE_PK} = $this->db->insert_id();
}
/**
* update record
*/
private function update() {
// CI's UPDATE - params: table name, object/array to INSERT, primary key of the table to be updated
$this->db->update($this::DB_TABLE, $this, $this::DB_TABLE_PK);
}
/**
* populate from an array or standard class
*/
public function populate($row) {
foreach ($row as $key => $value) {
$this->$key = $value;
}
}
/**
* load from the DB
*/
public function load($id) {
// CI's GET WHERE - params: table name, WHERE array
$query = $this->db->get_where($this::DB_TABLE, array($this::DB_TABLE_PK=>$id));
// use the result to populate model
$this->populate($query->row());
}
/**
* delete the current record
*/
public function delete() {
$this->db->delete($this::DB_TABLE,
// CI's DELETE - params: table name, WHERE array
array($this::DB_TABLE_PK => $this->{$this::DB_TABLE_PK}));
// delete id from model
unset($this->{$this::DB_TABLE_PK});
}
/**
* save the record
*/
public function save() {
// if id exists already, UPDATE; otherwise INSERT
if (isset($this->{$this::DB_TABLE_PK})) {
$this->update();
} else {
$this->insert();
}
}
/**
* get an array of Models with an optional limit, offset
*/
public function get($limit = 0, $offset = 0) {
if ($limit) {
// CI's GET - params: table name, limit, offset
$query = $this->db->get($this::DB_TABLE, $limit, $offset);
} else {
$query = $this->db->get($this::DB_TABLE);
}
$ret_val = array();
// get class name
$class = get_class($this);
foreach ($query->result() as $row) {
// create new objects
$model = new $class;
// & populate them
$model->populate($row);
// & add them to return array
$ret_val[$row->{$this::DB_TABLE_PK}] = $model;
}
return $ret_val;
}
/* more examples */
/**
* plain sql query
*/
public function getAll() {
$data = array();
$q = $this->db->query('SELECT * FROM data');
if ($q->num_rows() > 0) {
foreach($q->result() as $row) {
$data[] = $row;
}
}
return $data;
}
/**
* get where
*/
public function getWhere($id, $limit, $offset) {
$data = array();
$q = $this->db->get_where('data', array('id' => $id), $limit, $offset);
if ($q->num_rows() > 0) {
foreach($q->result() as $row) {
$data[] = $row;
}
}
return $data;
}
/**
* get specific columns
*/
public function getRow() {
$data = array();
// restrict selection before running get
$this->db->select('title, contents');
$q = $this->db->get('data');
if ($q->num_rows() > 0) {
foreach($q->result() as $row) {
$data[] = $row;
}
}
return $data;
}
/**
* run prepared query
*/
public function getPrepared($id, $author) {
$data = array();
$sql = "SELECT title, contents FROM data WHERE id = ? AND author = ?";
// CI's prepared query - params: sql string, variable/array to substitute ?s for in sql string
$q = $this->db->query($sql, array($id, $author));
if ($q->num_rows() > 0) {
foreach($q->result() as $row) {
$data[] = $row;
}
}
return $data;
}
/**
* sql pieces can be in separate lines
*/
public function getSpecific() {
$data = array();
// restrict selection
$this->db->select('title, contents');
// specify table
$this->db->from('data');
// specify WHERE (key, value pair(s))
$this->db->where('id', 1);
$q = $this->db->get();
if ($q->num_rows() > 0) {
foreach($q->result() as $row) {
$data[] = $row;
}
}
return $data;
}
}