$query1 = $this->Multi_database_model_db_1->select_1();
$this->load->model('Multi_database_model_db_2');
$query2 = $this->Multi_database_model_db_2->select_2();
foreach ($query1->result() as $row1) {
echo $row1->t1_first_name . ' ' . $row1-
>t1_last_name;
echo '<br />';
}
foreach ($query2->result() as $row2) {
echo $row2->t2_first_name . ' ' . $row2-
>t2_last_name;
echo '<br />';
}
}
}
3. Create the model /path/to/codeigniter/application/models/multi_
database_model_db_1php. This model will communicate with 'database1'.
Add the following code to the model:
<?php if ( ! defined('BASEPATH')) exit('No direct script
access allowed');
class Multi_database_model_db_1 extends CI_Model {
function __construct() {
parent::__construct();
}
function select_1() {
$DBconn1 = $this->load->database('database1', TRUE);
$query1 = $DBconn1->query("SELECT * FROM `table1`");
return $query1;
}
}
138
Chapter 6
4. Create the model /path/to/codeigniter/application/models/multi_
database_model_db_2.php. This model will communicate with 'database2'.
Add the following code to the model:
<?php if ( ! defined('BASEPATH')) exit('No direct script
access allowed');
class Multi_database_model_db_2 extends CI_Model {
function __construct() {
parent::__construct();
}
function select_2() {
$DBconn2 = $this->load->database('database2', TRUE);
$query2 = $DBconn2->query("SELECT * FROM `table2`");
return $query2;
}
}
How it works...
Firstly, let's pay attention to the settings we've defined for each one of our databases in the file /path/to/codeigniter/application/config/database.php. These database settings are specific to each database we want to connect to. We have also set the configuration variable 'pconnect' to FALSE for each of our databases (see the preceding bold text).
When we run the controller Multi_database in the browser, the controller will load our two database models named—for ease of explanation—'Multi_database_model_db_1' and
'Multi_database_model_db_2'. The Multi_database controller will then call one function from each model, again named select_1 and select_2 for ease of explanation.
The following code shows the same:
$this->load->model('Multi_database_model_db_1');
$query1 = $this->Multi_database_model_db_1->select_1();
$this->load->model('Multi_database_model_db_2');
$query2 = $this->Multi_database_model_db_2->select_2();
Okay! So far so good. There's nothing new here—just calling some database models; however, it's inside those models that things get interesting. Let's take a look at the code for the model Multi_database_model_db_1:
function select_1() {
$DBconn1 = $this->load->database('database1', TRUE);
$query1 = $DBconn1->query("SELECT * FROM `table1`");
return $query1;
}
139
Working with Databases
We're loading the database 'database1'—meaning, we're connecting to a database called 'database1' using the settings defined for 'database1' in the database.php configuration file—and storing that in the object which we're calling, that is, $DBconn1: $DBconn1 = $this->load->database('database1', TRUE);
Next, we're using the database object $DBconn1 to run a query and store the database result object in the variable $query1:
$query1 = $DBconn1->query("SELECT * FROM `table1`");
We then return $query to the calling controller. The controller Multi_database then loops through the $query1 result object, echoing as we go:
foreach ($query1->result() as $row1) {
echo $row1->t1_first_name . ' ' . $row1->t1_last_name;
echo '<br />';
}
Active Record – create (insert)
There are several ways to insert data into a database using CodeIgniter Active Record; for example, $this->db->insert() and $this->db->insert_batch(). The first will insert only one record at a time, and the second will insert an array of data as individual rows into the database; this can be quite useful if you know you need to insert more than one record at a time, thereby saving you the trouble of calling insert() more than once.
Getting ready
This is the SQL code required to support this recipe; you'll need to adapt it to your circumstances. Copy the following SQL code into your database:
CREATE TABLE IF NOT EXISTS `ch6_users` (
ìdìnt(11) NOT NULL AUTO_INCREMENT,
`firstnamè varchar(50) NOT NULL,
`lastnamè varchar(50) NOT NULL,
ùsernamè varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`created_dateìnt(11) NOT NULL,
ìs_activè varchar(3) NOT NULL,
PRIMARY KEY (ìd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
140
Chapter 6
How to do it...
We're going to create the following two files (or amend those files if you have already created them):
f
/path/to/codeigniter/application/controllers/database.php
f
/path/to/codeigniter/application/models/database_model.php
The following steps will demonstrate how to insert data into a database using CodeIgniter Active Record:
1. Add the following code into the controller database.php:
<?php if (!defined('BASEPATH')) exit('No direct script
access allowed');
class Database extends CI_Controller {
function __construct() {
parent::__construct();
}
public function index() {
redirect('database/create');
}
public function create() {
$data = array(
'firstname' => 'Lucy',
'lastname' => 'Welsh',
'username' => 'lucywelsh',
'password' => 'password',
'created_date' => time(),
'is_active' => 'yes'
);
$this->load->model('Database_model');
if ($this->Database_model->insert_data($data) ) {
echo 'Success';
}
else
{
echo 'Cannot insert to database';
}
}
public function create_batch() {
141
Working with Databases
$data = array(
array(
'firstname' => 'Lucy',
'lastname' => 'Welsh',
'username' => 'lwelsh',
'password' => 'password',
'created_date' => time(),
'is_active' => 'yes'),
array(
'firstname' => 'claire',
'lastname' => 'Strickland',
'username' => 'cstrickland',
'password' => 'password',
'created_date' => time(),
'is_active' => 'yes'),
array(
'firstname' => 'Douglas',
'lastname' => 'Morrisson',
'username' => 'dmorrisson',
'password' => 'password',
'created_date' => time(),
'is_active' => 'yes')
);
$this->load->model('Database_model');
if ($this->Database_model->insert_batch_data($data)) {
echo 'Success';
}
else
{
echo 'Cannot insert to database';
}
}
}
2. Add the following code into the model database_model.php:
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Database_model extends CI_Model {
function __construct() {
parent::__construct();
}
function insert_data($data) {
$this->db->insert('ch6_users', $data);
}
function insert_batch_data($data) {
142
Chapter 6
$this->db->insert_batch('ch6_users',$data);
}
}
How it works...
There are two methods used here: create() and create_batch(). Let's take each function in turn and go through how they work.
The public function create()
The create() function should be fairly familiar; we're creating an array (named $data) and populating it with the data for one user or equivalent to one row's insert. The create() method then passes the $data array to the model function insert_data() with the following code:
$this->load->model('Database_model');
$this->Database_model->insert_data($data);
The model will then insert one row into the table ch6_users:
function insert_data($data) {
$this->db->insert('ch6_users', $data);
}
The public function create_batch()
The create_batch() public function similar to the preceding functionality of create(), but rather than passing an array with one set of items, we create a multidimensional array with multiple rows of data as follows:
$data = array(
array(
'firstname' => 'Lucy',
'lastname' => 'Welsh',
'username' => 'lwelsh',
'password' => 'password',
'created_date' => time(),
'is_active' => 'yes'),
array(
'firstname' => 'claire',
'lastname' => 'Strickland',
'username' => 'cstrickland',
'password' => 'password',
'created_date' => time(),
'is_active' => 'yes'),
array(
'firstname' => 'Douglas',
'lastname' => 'Morrisson',
143
Working with Databases
'username' => 'dmorrisson',
'password' => 'password',
'created_date' => time(),
'is_active' => 'yes')
);
We then send that array to a new model function create_batch():
function insert_batch_data($data) {
$this->db->insert_batch('ch6_users',$data);
}
The function create_batch() function uses the CodeIgniter function insert_
batch() to INSERT each row into the database.
Active Record – read (select)
The R of CRUD represents the process to select data from a database. CodeIgniter uses the $this->db->get() database function to fetch rows from the database. Its usage is explained in the following sections.
Getting ready
The following is the SQL code required to support this recipe; you'll need to adapt it to your circumstances.
CREATE TABLE IF NOT EXISTS `ch6_users` (
ìdìnt(11) NOT NULL AUTO_INCREMENT,
`firstnamè varchar(50) NOT NULL,
`lastnamè varchar(50) NOT NULL,
ùsernamè varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`created_dateìnt(11) NOT NULL,
ìs_activè varchar(3) NOT NULL,
PRIMARY KEY (ìd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTÒch6_users` (`firstnamè, `lastnamè, ùsernamè,
`password`, `created_datè, ìs_activè) VALUES
('claire', 'Strickland', 'cstrickland', 'password', 1366114115,
'yes'),
('Douglas', 'Morrisson', 'dmorrisson', 'password', 1366114115,
'yes'),
('Jessica', 'Welsh', 'jesswelsh', 'password', 1366114115, 'yes');
144
Chapter 6
How to do it...
We're going to create the following two files (or amend those files if you have already created them):
f
/path/to/codeigniter/application/controllers/database.php
f
/path/to/codeigniter/application/models/database_model.php\
The following steps will demonstrate how to read data into a database using CodeIgniter Active Record:
1. Add the following code into the file /path/to/codeigniter/application/
controllers/database.php:
public function select_row() {
$id = 1;
$this->load->model('Database_model');
$result = $this->Database_model->select_row($id);
echo '<pre>';
var_dump($result->result());
}
2. Add the following code into the file /path/to/codeigniter/application/
models/database_model.php:
function select_row($id) {
$this->db->where('id', $id);
$query = $this->db->get('ch6_users');
return $query;
}
You know it has worked if you see the following output:
array(1) {
[0]=>
object(stdClass)#20 (7) {
["id"]=>
string(1) "1"
["firstname"]=>
string(4) "Lucy"
["lastname"]=>
string(5) "Welsh"
["username"]=>
string(6) "lwelsh"
["password"]=>
string(8) "password"
["created_date"]=>
string(10) "1366114115"
145
Working with Databases
["is_active"]=>
string(3) "yes"
}
}
How it works...
In the preceding controller, public function select_row() assigns $id with the value 1—however, this can also be done from post, get, session, or another source—and loads the database model, passing the variable $id to it as follows:
$this->load->model('Database_model');
$this->Database_model->insert_batch_data($data);
The model function select_row()pulls the matching record from the table 'ch6_users'
and returns it to the calling controller.
Active Record – update
The U of CRUD represents the process to update data record(s) from a database in a database. CodeIgniter uses the database function $this->db->update() to update database records; this recipe will explain how it is done.
Getting ready
The following is the SQL code required to support this recipe; you'll need to adapt it to your circumstances.
CREATE TABLE IF NOT EXISTS `ch6_users` (
ìdìnt(11) NOT NULL AUTO_INCREMENT,
`firstnamè varchar(50) NOT NULL,
`lastnamè varchar(50) NOT NULL,
ùsernamè varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`created_dateìnt(11) NOT NULL,
ìs_activè varchar(3) NOT NULL,
PRIMARY KEY (ìd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTÒch6_users` (`firstnamè, `lastnamè, ùsernamè,
`password`, `created_datè, ìs_activè) VALUES ('Jessica',
'Welsh', 'jesswelsh', 'password', 1366114115, 'yes');
146
Chapter 6
How to do it...
We're going to create the following two files (or amend those files if you have already created them):
f
/path/to/codeigniter/application/controllers/database.php
f
/path/to/codeigniter/application/models/database_model.php
The following step will demonstrate how to update data into a database using CodeIgniter Active Record:
1. Add the following code into the file: /path/to/codeigniter/application/
controllers/database.php:
public function update_row() {
$id = 1;
$data = array(
'firstname' => 'Jessica',
'lastname' => 'Welsh',
'username' => 'jesswelsh',
'password' => 'password',
'created_date' => time(),
'is_active' => 'yes'
);
$this->load->model('Database_model');
$result = $this->Database_model->update_row($id, $data);
redirect('database/select_row');
}
Add the following code into the file: /path/to/codeigniter/
application/models/database_model.php:::
function update_row($id, $data) {
$this->db->where('id', $id);
$this->db->update('ch6_users', $data);
}
array(1) {
[0]=>
object(stdClass)#20 (7) {
["id"]=>
string(1) "1"
["firstname"]=>
string(7) "Jessica"
["lastname"]=>
147
Working with Databases
string(5) "Welsh"
["username"]=>
string(9) "jesswelsh"
["password"]=>
string(8) "password"
["created_date"]=>
string(10) "1366117753"
["is_active"]=>
string(3) "yes"
}
}
How it works...
In the controller we just saw, public function update_row() assigns $id with the value 1—however this can be from post, get, session or another source—and loads the database model, passing the variable $id to it as follows:
$this->load->model('Database_model');
$result = $this->Database_model->update_row($id, $data);
The model function update_row() updates the matching record from the table as follows: function update_row($id, $data) {
$this->db->where('id', $id);
$this->db->update('ch6_users', $data);
}
ActiveRecord – delete
The D of CRUD is used for deleting rows of data in a database table. CodeIgniter uses the $this->db->delete()database function to remove rows from a database; it is used in the following section.
Getting ready
The following is the SQL code required to support this recipe; you'll need to adapt it to your circumstances:
CREATE TABLE IF NOT EXISTS `ch6_users` (
ìdìnt(11) NOT NULL AUTO_INCREMENT,
`firstnamè varchar(50) NOT NULL,
`lastnamè varchar(50) NOT NULL,
ùsernamè varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`created_dateìnt(11) NOT NULL,
ìs_activè varchar(3) NOT NULL,
148
Chapter 6
PRIMARY KEY (ìd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTÒch6_users` (`firstnamè, `lastnamè, ùsernamè,
`password`, `created_datè, ìs_activè) VALUES ('Jessica',
'Welsh', 'jesswelsh', 'password', 1366114115, 'yes');
How to do it...
We're going to create the following two files (or amend those files if you have already created them):
f
/path/to/codeigniter/application/controllers/database.php
f
/path/to/codeigniter/application/models/database_model.php
The following steps will demonstrate how to delete data from a database using CodeIgniter Active Record:
1. Add the following code into the file /path/to/codeigniter/application/
controllers/database.php:
public function delete_row() {
$id = 1;
$this->load->model('Database_model');
$result = $this->Database_model->delete_row($id);
redirect('database/select_row');
}
2. Add the following code into the file /path/to/codeigniter/application/
models/database_model.php:
function delete_row($id) {
$this->db->where('id', $id);
$this->db->delete('ch6_users');
}
How it works...
In the preceding controller, public function delete_row() assigns $id with the value 1—however, this can be from post, get, session or another source—and loads the database model, passing the variable $id to it as follows:
$this->load->model('Database_model');
$result = $this->Database_model->delete_row($id);
149
Working with Databases
The model function delete_row()deletes the matching record from the table: function delete_row($id) {
$this->db->where('id', $id);
$this->db->delete('ch6_users');
}
Looping through the database results
In any application with database connectivity, you'll probably need to display records from a database; looping through rows of data returned from a query is one of the most common tasks you'll perform in programming. CodeIgniter handles looping through database results using PHP for each statement. In this recipe, we will loop through each record at a time, echoing out the relevant information.
Getting ready
To support this recipe, we are going to create a database table and write some data to it.
If you already have the data, you can skip this recipe; if not, copy the following code into your database:
CREATE TABLE IF NOT EXISTS `loop_tablè (
ìdìnt(11) NOT NULL AUTO_INCREMENT,
`first_namè varchar(255) NOT NULL,
`last_namè varchar(255) NOT NULL,
PRIMARY KEY (ìd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTÒloop_tablè (ìd`, `first_namè, `last_namè) VALUES
(1, 'Lucy', 'Welsh'),
(2, 'Rob', 'Foster');
How to do it...
1. Add or adapt the following code into your controller:
public function loop_through_data() {
$this->load->model('Some_model');
$data['query'] = $this->Some_model->select_data();
$this->load->view('some_view', $data);
}
2. Add or adapt the following code into your model:
function select_data() {
$query = $this->db->get('loop_table');
return $query;
}
150
Chapter 6
3. Add or adapt the following code into your view:
foreach ($query->result() as $row) {
echo $row->first_name . ' ' . $row->last_name;
echo '<br />';
}
How it works...
Firstly, let's look at the SQL code; if you used the preceding SQL code, all we would have done is create a very simple table and populate it with two rows of data.
Next, we call the controller function loop_through_data(), which loads a model; in this case, rename Some_model to the model relevant to your application. We call the model function select_data(), storing its returned result in the $data array, or more specifically, in a section of the $data array that we're calling 'query':
$data['query'] = $this->Some_model->select_data();
The model function select_data() fetches all the rows from the database table loop_table and returns it to the calling controller function.
Returning to our controller, now that we have the database result in our $data array, we can call the view file some_view.php—you obviously need to rename it to something else in your application—and pass the $data array to it:
$this->load->view('some_view', $data);
The view file then uses a simple foreach()loop to cycle through each result in $query.
Let's look at this foreach() loop more closely. Look at the following line of code: foreach ($query->result() as $row) {
Remember how we stored the database result in $data['query']? Well, we're going to use the 'query' part of the $data array, which has stored the database results, and we're going to use the CodeIgniter function result() on it. I hear you ask, "What does result() do?"
The result() function will take an object or array and allow you to iterate through each row, allowing you to act on the individual data items within that row.
So, we're using result() to split apart $query into each row, passing that row to $row (because it's obvious) and allowing us to do something like:
echo $row->first_name . ' ' . $row->last_name;
This is displaying the first and last name of each person in $row.
151