Description
"Active record class" is one of the most popular and useful features of Codeigniter. An updated version of the CodeIgniter Active Records Database Pattern is that allows the information to be retrieved, inserted, and updated in database with less scripting.
While using this pattern, SQL queries are created and executed on
model section which reduces efforts for developers. Here, active records reduces the burden of writing query syntax, by calling functions with proper parameters sequentially, which when executed gives a full length SQL query and returns respective results of database. This helps in not doing mistakes while writing long SQL queries.
Another advantage of using CodeIgniter Active Records is it can create database independent applications, since the query syntax is generated by each adapter of database. As the system automatically escapes the values, safe queries are inputted. Description
Selecting Data is mostly used CRUD operations. The below functions builds SQL SELECT statements which looks familiar to the original SQL Statements.
$this->db->get_where();
In the above code, WHERE clause helps in extracting only the records that satisfy the given condition. For Example,
[php]
$query = $this->db->get_where('thetable', array('id' => $id));
// or //
$query = $this->db->get_where('thetable', array('id' => $id), $limit, $offset);
[/php]
The function where() enables to set WHERE clauses using one of 4 methods which helps to run the query multiple times and then $this->db->get(); can be used to send the query.
For example,
[php]$this->db->where('name', $name); [/php]
Output will be as follows:
[php]WHERE name = ‘Rick’[/php]
When multiple function calls are occurs, the result will be grouped together with AND between them.[php]
$this->db->where('name', $name);
$this->db->where('title', $title);
$this->db->where('status', $status);
[/php]
Output will be as follows:
[php]WHERE name = ‘Rick’ AND title = ‘Rock Star’ AND status = ‘Legend’[/php]
Description
Suppose there is a database table called
website with the fields of website_name, website_url and website_description. Below is the code to insert the values into database table using active record $this->db->insert();.
[php]
$data = array(
'website_name' => 'SPLessons' ,
'website_url' => 'http://www.splessons.com' ,
'website_description' => 'Stop Thinking, Start Coding'
);
$this->db->insert('website', $data);
[/php]
Output will be as follows:
[php]
INSERT INTO website (website_name, website_url, website_description) VALUES ('SPLessons', 'http://www.splessons.com', 'Stop Thinking, Start Coding')
[/php]
In the above example, a set of values that are related to each other in an array are taken, and inserted that data into one row of database table that has 3 fields. But multiple groups of data can also be inserted at a time using function $this->db->insert_batch();.
[php]
$data = array(
'website_name' => 'SPLessons' ,
'website_url' => 'http://www.splessons.com' ,
'website_description' => 'Stop Thinking, Start Coding'
),
array(
'website_name' => 'Google' ,
'website_url' => 'http://www.google.com' ,
'website_description' => 'The Google Search Engine Homepage'
)
);
$this->db->insert_batch('website', $data);
[/php]
The output produced will be as follows:
[php]
INSERT INTO website (website_name, website_url, website_description) VALUES ('SPLessons', 'http://www.splessons.com', 'Stop Thinking, Start Coding'), ('Google', 'http://www.google.com', 'The Google Search Engine Homepage')
[/php] Description
Records in a table are deleted by the DELETE Statement. This function generates a delete SQL string and runs the query.
[php]
$this->db->delete('mytable', array('id' => $id));[/php]
The output is as follows:
[php]DELETE FROM mytable WHERE id = $id[/php]
Table names can be passed as an array into the function
delete() when required to delete data from multiple tables.
[php]
$tables = array('table1', 'table2', 'table3');
$this->db->where('id', '5');
$this->db->delete($tables);
[/php] Description
Records in a table are updated using UPDATE statement. An array or an object can be passed to the function. Below is the syntax of array.
Array syntax:
[php]
$data = array(
'title' => $title,
'name' => $name,
'date' => $date
);
$this->db->where('id', $id);
$this->db->update('mytable', $data);
[/php]
The output will be as follows.
[php]UPDATE mytable SET title = ‘{$title}’, name = ‘{$name}’, date = ‘{$date}’ WHERE id = $id[/php]
Object syntax:
[php]
class Myclass {
var $title = 'My Title';
var $content = 'My Content';
var $date = 'My Date';
}
$object = new Myclass;
$this->db->where('id', $id);
$this->db->update('mytable', $object);
[/php]
Output will be as follows:
[php]
UPDATE mytable SET title = ‘{$title}’, name = ‘{$name}’, date = ‘{$date}’ WHERE id = $id[/php]
Description
To check if any record exists in the database or not, Active record can be used. The function
exists() will be used for this functionality.
[php]
function view($name)
{
$data['page'] = $this->Page->find_by_name($name);
if ($data['page']->exists())
{
$this->load->view('single_page', $data);
}
else
{
show_404();
}
}[/php] Key Points
- Active Record can create database independent applications.
- CodeIgniter Active Records calls various functions with proper parameters sequentially.
- CRUD operations of database can be done with still more ease.
- exists() functions checks if the object is present or not in the database.