আগামী ১৫ ডিসেম্বর -২০১৭ তারিখ থেকে শুরু হচ্ছে পাঁচ মাস ব্যাপী Professional Web Design and Development with HTML, CSS PHP,MySQL,JavaScript, AJAX, JQUERY, Bootstrap and Live Project কোর্সের ৮৭ তম ব্যাচ এবং ২৬ ডিসেম্বর-২০১৭ তারিখ থেকে শুরু হচ্ছে চার মাস ব্যাপী Zend PHP-7 Certified PHP Engineering (Advance PHP) কোর্সের ৩৫ তম ব্যাচ। প্রত্যেকটি কোর্স এর ফী নির্ধারণ করা হয়েছে ৩০,০০০/= আগ্রহীদেরকে অতিসত্বর মাসুদ আলম স্যার এর সাথে যোগাযোগ করতে অনুরোধ করা যাচ্ছে। স্যার এর মোবাইল: 01722 81 75 91

An Introduction to CodeIgniter & Database

CodeIgniter supports many types of databases such as MySQL, PostGre SQL, Oracle, and others.

CodeIgniter database support of the provision of some form of database driver who will also have the function security, caching and active record.


Connect to Database

CodeIgniter has a config file that allows you to save the configuration to connect to datase (username, password, database name, etc.).

The configuration file is in application / config / database.php configuration settings are stored in the form of multi-dimensional arrays prototype as follows:

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = '';
$db['default']['password'] = '';
$db['default']['database'] = '';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';

We can have multiple configuration database. The reason we use a multi-dimensional array is that allows you to optionally store multiple sets of values the connection if, for example, you run the application in some environmental conditions / Environment (development, production, testing, etc.) in under one installation, then you can set the configuration for each Environment. As For example, you are running the application in the test environment then you just add configuration following and change its default configuration (setting the $ active_group = “test”) to test

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = '';
$db['default']['password'] = '';
$db['default']['database'] = '';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$active_group = 'default';
$active_record = TRUE;

To connect to the database there are several ways provided by CodeIgniter include:

1. Adding Database Library For Autoload Library

To connect to the database as the database you can add autoload library in the file application / config / autoload.php method is very simple, just add the word “database” into autoload library so that it becomes:

 $autoload ['libraries'] = array ("database");
 

when added autoload sure that the database is configured correctly to avoid error.

2. Enabling Manual Of Library Database

If only there were some pages that require database connectivity, to connect to the database optimization done manually, simply by add the following line of code for each function that requires a connection kedatabase place or in your class constructor to make the database available globally in the classroom.

 $this-> load-> database ();
 

If the above function does not contain any information on the first parameter will connect to the configuration of the active group. To select a specific group of files configuration, you can do as an example. This is useful when using an application that has two databases.

 $ this-> load-> database ('group_name');
 

group_name is the name of the configuration group from your configuration file. To manually connect to the database you want, you can pass an array of values:

 $active_group = 'default';
 $active_record = TRUE;

 $db ['default'] ['hostname'] = 'localhost';
 $db ['default'] ['username'] ='';
 $db ['default'] ['password'] ='';
 $db ['default'] ['database'] ='';
 $db ['default'] ['dbdriver'] = 'mysql';
 $db ['default'] ['dbprefix'] ='';
 $db ['default'] ['pconnect'] = TRUE;
 $db ['default'] ['db_debug'] = TRUE;
 $db ['default'] ['cache_on'] = FALSE;
 $db ['default'] ['cachedir'] ='';
 $db ['default'] ['char_set'] = 'utf8';
 $db ['default'] ['dbcollat'] = 'utf8_general_ci';
 
 $this-> load-> database ($ config);
 

Or you can send your database values as Data sorce Name. DSN must have protoyipe like this:

 $dsn = "dbdriver :/ / username: password @ hostname / database ';
 $this-> load-> database ($ dsn);
 

3. Enables manual of the model

In addition to both of the above you can also activate the database at the time of loading model.

The trick is to set the third parameter to TRUE to load model. Example:

$this->load->model(‘Model_name’,”, TRUE);

You also can use a specific configuration for a model. Example:

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = '';
$db['default']['password'] = '';
$db['default']['database'] = '';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$this->load->model('Model_name','',$manual_config);

CodeIgniter Model

Model on CaodeIgniter is a php class that serves to handle the data. Recall data not only from the database but also from the File Text, Web Service or other data services. Model example:

Class Blogmodel extends Model{
public $title='';
public $content='';
public $date='';
function Blogmodel(){
parent::Model();
}

function get_data(){
$query=$this->db->get('entries',10);
return $query->result();
}
}

A model does not have to extend the Model class. Extend the model class when you want to use the database features in CodeIgniter only. all Models should be placed in the folder application / models. In order to use the model we have to load the model. The commands that can be used to load a model is

 $this->load->model('Model_name');
 $this->model_name->get_data ();

When it managed to load a model, then the model will be a property. Through the property that you will use all the functions available in in the model (note the line to 2 in the example above is a function get_data classroom Model_name)

Doing Quary on Database

To obtain data on the database you have to perform a query. Once done then the query can get the data query results, either in the form of object or array.

 $query = $this->db->query('YOUR SQL QUERY');

It should be emphasized that the above query functions do not produce any data. The output is just a Object (true) or False. When the output is False then the query is done is to fail. But if true or returns an object then the query is done meaningful work. Of the object ($query variable that refers to the example above)

You can retrieve the desired data Example:

$query=$this->db->query('SELECT name, title, email FROM my_table');
foreach($query->result() as $row){
echo $row->title;
echo $row->name;
echo $row->email;
}
echo 'Total Results: '.$query->num_rows();

The example above is one example of using the query (but remembers make sure you have the correct database configuration and have load library database. Note the $query->result (). It is an example of the syntax to get the query results in the form of an object, you can also use $query->result_array () to get the query results in the form of associative array

Query Return Value

as mentioned above, the following query do we need to call a certain function to get the results of the query. In general there two types of data that can be generated are arrays and objects. In terms of the amount we can only take one record (a record) or overall record.

*Returns the query results as a collection of Array.

Before taking the results of the query, it is better to check first if the query has a result or not. Consider the function $ result->result_array (),

it is a method used to retrieve query results. While the function $result->num_rows () is used to determine how many records were obtained

$result=$this->db->query('SELECT * FROM users');
if($result->num_rows()>0){
foreach($result->result_array() as $row){
echo $row['username'];
echo $row['email'];
}
}

* Returns the query results as a collection of Object.

Function used is almost the same as the return value of the query as an array. Simply by calling the method $result->result ().

$result=$this->db-Query('SELECT * FROM users');
if($result->num_rows()>0){
foreach($result->result() as $row){
echo $row->username;
echo $row->email;
}
}

* Returns Result Row Query As Array

So we just took a record from a query Not all query results

$result=$this->db->query('SELECT * FROM users');
if($query->num_rows()>0){
$row=$query->row();
echo $row['username'];
echo $row['email'];
}

* Return As Row Object Query Results

So we just took a record from a query as an object. Not all query results

$result=$this->db->query('SELECT * FROM users');
if($query->num_rows()>0){
$row=$query->row();
echo $row->username;
echo $row->email;
}

Using Active Record

Active Record (AR) is a pattern / approach to reading data from a table or view by wrapping it in a class.

So, no SQL is needed if we use active record. The advantage gained is its simplicity and flexibility in coding program when a change of scheme or any kind of database (independent of the syntax of SQL syntax that is specific to a particular database).
CodeIgniter does not fully mimic Active Record pattern, but he did a few modifications to the pattern. By using Active Record CI then the process of data retrieval, insert, update and delete are simpler.

 $this->db->get('users')

The query above will result in the query “SELECT * FROM users” and is equivalent to

 $this->db->query('SELECT * FROM users');

When we do not use any function then the function will get produce all records are in the table, but if given the function select (), where (), and so the results will be different.

Selecting data

To retrieve data from the database with some fields we can use the select (). Example:

$this-> db-> select ('username, password, email');
$this-> db-> get ('users');

The query above is equivalent to

$this->db->query('SELECT username, password, email FROM users');

In addition to select the method, there are also those methods such as from (), where (), where_in (), group_by, order_by, like, distinct () and others. all method if called upon to form a query, and the query will be executed when the method get () is called. For convenience, the active record already features the chaining method, so we can assemble all the queries had to be more concise.

Examples of complex query

$data_all=$this->db
->select("sum(budget) as sum_budget, site_ad_mapping.*)
->where('site_ad_mapping.site_id',$site_id)
->where('ad_status','approved')
->where('request_status','active')
->whree('ad_operational_status','active')
->join('ads','ads.id_ad=site_ad_mapping.ad_id')
->join('advertisers','id_advertiser=advertiser_id')
->join('site_counter','site_counter.site_id=site_id')
->join('sites','site_counter.site_id=sites.id_site')
->joint('publishers','sites.publisher_id=id_publisher')
->from('site_ad_mapping')
->order_by('ad_priority_level','desc')
->order_by('ad_id')->limit(0,10)
->get()->result();

Insert data

In addition to data retrieval, Active record also provides the functionality to insert data. To insert a data takes only two parameters the name of the table and the data to be entered. Data to be entered must be formatted as an array of associative or object. The advantage using active records to insert the data in addition to simple to use, this is the method with build-in functions for handling escaping, so the query we have said, is safe from error, for example:

$data=array('username'=>'Galib','email'=>'[email protected]');
$this->db->insert('users',$data);

In addition to the above, we can also enter the desired data one by one.

For example:

$data=array('username'=>'Galib','email'=>'[email protected]');
$this->db->set($data);
$this->db->insert('users');

Or

$this->db->set('username','Galib');
$this->db->set('email','[email protected]'));
$this->db->insert('users');

The second example above produces the same query.

Update Data

In addition to insert and select data, CodeIgniter also provides functions to update. The use is similar as the use of inserts, for example:

$this->db->set('username','Hanif');
$this->db->set('password','123456');
$this->db->update('users');

The query above will result in the query “UPDATE users SET username = ‘Hanif’, password = ‘123456 ‘.

A simpler by using associative arrays

$data=array('username'=>'Hanif','password'=>'123456');
$this->db->where('id',5);
$this->db->update('users',$data);

Delete data

to perform delete on CodeIgniter, we can use the following command:

$this->db->where('id',5);
$this->db->delete('table_name');

GoodGood VeryGood…:)

Hi, My name is Masud Alam, love to work with Open Source Technologies, living in Dhaka, Bangladesh. I’m a Certified Engineer on ZEND PHP 5.3, I served my first five years a number of leadership positions at Winux Soft Ltd, SSL Wireless Ltd, Canadian International Development Agency (CIDA), World Vision, Care Bangladesh, Helen Keller, US AID and MAX Group where I worked on ERP software and web development., but now i’m a founder and CEO of TechBeeo Software Company Ltd. I’m also a Course Instructor of ZCPE PHP 7 Certification and professional web development course at w3programmers Training Institute – a leading Training Institute in the country.

Leave a Reply

Your email address will not be published. Required fields are marked *