CodeIgniter Result Functions

In CodeIgniter, when you run active record query it returns the codeigniter resultset instance. In order, to fetch the values from the resultset instance you must have to use one of the result functions.

You can fetch the result-set in following ways –

result()

The result() function returns the result as an array of objects, or an empty array on failure.

Example:-

result_array()

The result_array() function returns the result as an array, or an empty array when no record found.

Example:-

row()

The row() function returns a single result row. If your query returns more than one records, then the row() function returns only the first row. The result is returned as an object.

Example:-

If your query returns more than one records, you can access specific record by passing the row number.

Example:-

row_array()

The row_array() function is similar to the row() function, except that it returns result row as an array.

Example:-

You can traverse through the results forward/backwards/first/last using following variations:

$row = $query->first_row()
$row = $query->last_row()
$row = $query->next_row()
$row = $query->previous_row()

By default they return an object unless you put the word “array” in the parameter:

$row = $query->first_row(‘array’)
$row = $query->last_row(‘array’)
$row = $query->next_row(‘array’)
$row = $query->previous_row(‘array’)

$query->num_rows()

The num_rows() function is used to determine the number of rows in query result-set.

Example:-

$query->num_fields()

The num_rows() function is used to determine the number of FIELDS (columns) in query result-set instance.

Example:-

 

$query->free_result()

The free_result() function frees the memory associated with the query result-set and deletes the result resource ID.

Example:-

 

What Is Database Caching In CodeIgniter?

CodeIgniter framework comes with built-in dynamic database caching mechanism. In CodeIgniter, when database caching is enabled, a web page is loaded first time , corresponding query result stored as a cache file. Next time when the same page is requested it is served using cache file instead of accessing your database again. This way it reduces the database load and result in performance gain. Database caching is only applicable with SELECT queries.

Enabling Caching In CodeIgniter

Before you start using cache, you must enable it using following steps –

Step1:- Create a directory with write permission on your server where the cache files can be stored.

Step2:- Set the path to your cache folder in your application/config/database.php file.

Step3:- Enable caching, either globally by setting the preference in your application/config/database.php file, or manually using following functions.

$this->db->cache_on()

This function is used to enable the caching manually.

$this->db->cache_off()

This function is used to disable the caching manually.

$this->db->cache_delete()

This function is used to delete the cache files associated with a particular page.

Syntax:-

$this->db->cache_delete_all()

This function is used to delete all cache files.

Example 1:-

Example 2:-

If you are viewing a page at example.com/index.php/employee/profile, the caching system will put all cache files associated with it in a folder called employee+profile. Cache files for this page can be deleted as following –

Example 3:-

This will clear cache files for the current URI.

 

What Is Active Record Caching In CodeIgniter?

Active Record caching helps you to save some parts of your database queries so that you can reuse them at a later point in your script’s execution. Usually, when a query is executed all of the query related information is reset for the next query. Active record caching gives you way to prevent this reset, and reuse query information easily. Cached calls works in cumulative fashion, making 2 cached select() calls, and then 2 uncached select() calls, will result in 4 select() calls.

In CodeIgniter, we have following three active record caching functions –

$this->db->start_cache()

This function is used to start query caching.

$this->db->stop_cache()

This function is used to stop query caching.

$this->db->flush_cache()

This function is used to delete all items from the Active Record cache.

Example:-

Note:- The following statements can be cached: select, from, join, where, like, group_by, having, order_by, set

What Method Chaining In CodeIgniter?

In CodeIgniter, method chaining enables us to connect multiple method calls in single statement. Method chaining simplifies the syntax required to call multiple methods sequential over single object. Each of the method returns an object, without requiring intermediate variables to hold the results. Method chaining reduces the amount of code and improves the readability of code.

Example:- Let’s say you have a MySQL table named ’employee_master’ with the following fields –
emp_ID, emp_name, emp_email, emp_phone, emp_address, emp_code and emp_dept

 

 

In CodeIgniter, delete() method is used to delete an existing record from database table. In order to generate delete statement, delete() method is used in following ways –

Syntax:-

Here,

$table(mixed):- The table(s) to delete from. String or array

$where(mixed):- The where clause

$limit(mixed):- The limit clause

$reset_data(bool):- TRUE to reset the query “write” clause

Returns(mixed)

Example:- Let’s say you have a MySQL table named ’employee_master’ with the following fields –
emp_ID, emp_name, emp_email, emp_phone, emp_address, emp_code and emp_dept

Delete Record using $this->db->delete()

This is how you can delete a record from ’employee_master’ table using $this->db->delete().

An array of table names can be passed into delete() if you would like to delete record(s) from multiple tables.

Delete All Records using $this->db->empty_table()

This is how you can delete all records from ’employee_master’ table using $this->db->empty_table().

Delete All Records using $this->db->truncate()

This is how you can delete all records from ’employee_master’ table using $this->db->truncate().

Delete With Join

 

 

 

In CodeIgniter, update() method is used to update existing record in database table. In order to generate update statement, update() method is used along with set() and where() methods in following ways –

Syntax:-

Here,

$table(String):- Table Name

$set(array):- An associative array of column/value for update.

$where(mixed):- It contains where clause

$limit(int):- It contains limit clause

Returns(bool):- It returns TRUE on success and FALSE on failure.

Example:- Let’s say you have a MySQL table named ’employee_master’ with the following fields –
emp_ID, emp_name, emp_email, emp_phone, emp_address, emp_code and emp_dept

Update using $this->db->update()

This is how you can update an existing record into ’employee_master’ table using $this->db->update().

Update with $this->db->where()

This is how you can update an existing record into ’employee_master’ table using $this->db->update() along with $this->db->where().

Update with $this->db->set()

This is how you can update an existing record into ’employee_master’ table using $this->db->update() along with $this->db->set().

Update using $this->db->update_batch()

This is how you can update an existing records into ’employee_master’ table using $this->db->update_batch().

The first parameter will contain the table name, the second is an associative array of values, the third parameter is the where key.

 

In CodeIgniter, insert() method is used to insert record in database table. In order to generate insert statement, insert() method can be used in following ways –

Syntax:-

Here,

$table(String):- Table Name

$set(array):- An associative array of column/value.

$escape(bool):- Whether to escape values and identifiers (TRUE/FALSE)

Returns(bool):- It returns TRUE on success and FALSE on failure.

Example:- Let’s say you have a MySQL table named ’employee_master’ with the following fields –
emp_name, emp_email, emp_phone, emp_address, emp_code and emp_dept

Inserting Data using $this->db->insert()

This is how you can insert a record into ’employee_master’ table using $this->db->insert().

Inserting Data using $this->db->query()

This is how you can insert a record into ’employee_master’ table using $this->db->query().

Inserting Data with Query Bindings

This is how you can insert a record into ’employee_master’ table with Query Binding.

Inserting Data using $this->db->set()

This is how you can insert a record into ’employee_master’ table using $this->db->set().

Inserting Data using $this->db->insert_batch()

The $this->db->insert_batch() method is used to insert batch of records in single query, this is how you can insert batch of record into ’employee_master’ table using $this->db->insert_batch().

Escaping Insert Queries

In CodeIgniter, values can be escaped using $this->db->escape_str() function to produce safer queries.

Retrieve Last Inserted ID

The insert_id() method is used to retrieve the last Inserted ID when performing database inserts.

Retrieve Affected Rows

The affected_rows() method is used to displays the number of affected rows, when doing “write” type queries (insert, update, etc.).

 

In CodeIgniter, get() method is used to run select statement and return data from table. It can be used stand alone to retrieve all records from a table.

Syntax:-

Here,

$table(String):- Table Name

$limit(string):- It contains limit clause

$offset(string):- It contains offset clause

Returns(CI_DB_result):- It returns CI_DB_result.

In order to generate SQL SELECT statement, get() method is used along with various other functions.

Example:- Let’s say you have a MySQL table named ’employee_master’ with the following fields –
emp_ID, emp_name, emp_email, emp_phone, emp_address, emp_code, emp_salary and emp_dept

CodeIgniter Select Query with $this->db->get()

This is how you can use $this->db->get() function to retrieve records from ’employee_master’ table.

Example:-

get() method has optional second and third parameter which allows you to set limit and offset.

Example:-

Since $this->db->get() returns CI_DB_result instance which you can not directly use to access the result, thus you need to assign it to a variable and then use one of the built in result functions to fetch the result.You will usually use a for or foreach loop to iterate over results like –

Example:-

CodeIgniter Select Query with $this->db->get_where()

The get_where() method is similar to get() method except that it allows you to add “WHERE” clause in second parameter. This is how you can select records from ’employee_master’ table using $this->db->get_where().

Example:-

CodeIgniter Select Query with $this->db->select()

The select() method allows you to write the “SELECT” portion of your query. This is how you can select records from ’employee_master’ table using $this->db->select() method.

Example:-

By default it selects all (*) from given table.The select() method accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.

Example:-

CodeIgniter Select Query with $this->db->from()

The from() method allows you to write the “FROM” clause of your query. This is how you can select records from ’employee_master’ table using $this->db->from() method along with the $this->db->get() method.

Example:-

CodeIgniter Select Query with $this->db->join()

The join() method allows you to write the “JOIN” clause for your select query. The “JOIN” clause makes easy to fetch records from multiple tables.

Example:-

The $this->db->join(); method can be called more than once to have more than one JOIN in the query. The third parameter of the join() method is used to specify the type of JOIN (left, right, outer, inner, left outer, right outer).

Example:-

CodeIgniter Select Query with $this->db->where()

The where() method allows you to set “WHERE” clauses for your select query. You are free to use where() method multiple times to prepare your select query, they all will be chained together with AND between them. Using where() function you can set “WHERE” clause in following four ways –

Simple key/value method :-

Example:-

Custom key/value method :- You are free to use an operator in the first parameter in order to control the comparison.

Example:-

Associative array method :-

Example:-

Operators can also be included in this method.

Example:-

Custom string :- You can pass complete WHERE Clause string as following –

Example:-

CodeIgniter Select Query with $this->db->or_where()

The or_where() function is similar as where() function, except that it joins multiple where() function calls with OR operator.

Example:-

CodeIgniter Select Query with $this->db->where_in()

The where_in() function is used to generate WHERE field IN (‘item’, ‘item’) SQL query string joined with AND if appropriate.

Example:-

CodeIgniter Select Query with $this->db->or_where_in()

The or_where_in() function is used to generate WHERE field IN (‘item’, ‘item’) SQL query string joined with OR if appropriate.

Example:-

CodeIgniter Select Query with $this->db->where_not_in()

The where_not_in() function is used to generate WHERE field NOT IN (‘item’, ‘item’) SQL query string joined with AND if appropriate.

Example:-

CodeIgniter Select Query with $this->db->or_where_not_in()

The or_where_not_in() function is used to generate WHERE field NOT IN (‘item’, ‘item’) SQL query string joined with OR if appropriate.

Example:-

CodeIgniter Select Query with $this->db->like()

The like() function allows you to generate “LIKE” clauses for your query. You are free to use like() function multiple times to prepare your select query, they all will be chained together with AND between them. Using like() function you can set “LIKE” clause in following ways –

Simple key/value method:-

Example:-

An optional third argument helps you to set where the wildcard (%) character will be placed. Options are ‘before’, ‘after’, ‘both’ (default) and ‘none’ (no wildcard).

Example:-

Associative array method:-

Example:-

CodeIgniter Select Query with $this->db->or_like()

The or_like() function is similar as like() function, except that it joins multiple like() function calls with OR operator.

Example:-

CodeIgniter Select Query with $this->db->not_like()

The not_like() function allows you to generate “NOT LIKE” clauses for your query.

Example:-

CodeIgniter Select Query with $this->db->or_not_like()

The or_not_like() function is similar as not_like() function, except that it joins multiple not_like() function calls with OR operator.

CodeIgniter Select Query with $this->db->group_by()

The group_by() method allows you to set the “GROUP BY” clause for your query.You can group by multiple field values using an array.

Example:-

CodeIgniter Select Query with $this->db->distinct()

The distinct() function is used to add “DISTINCT” keyword to your query.

Example:-

CodeIgniter Select Query with $this->db->having()

The having() function allows you to generate “HAVING” clause for your query. Using having() function you can set “HAVING” clause in following ways –

Example:-

CodeIgniter Select Query with $this->db->or_having()

The or_having() function is similar as having() function, except that it joins multiple having() function calls with OR operator.

CodeIgniter Select Query with $this->db->order_by()

The order_by() function allows you to set “ORDER BY” clause for your query. The first parameter is used to pass column name you would like to order by. The second parameter allows you to set the direction of the order. Options are asc or desc, or random.The order_by() function can be used in following ways –

Example:-

CodeIgniter Select Query with $this->db->limit()

The limit() function allows you to set “LIMIT” clause for your query. The first parameter is used to set number of rows you would like to retrieve. The second parameter allows you to set the result offset. The limit() function can be used in following ways –

Example:-

CodeIgniter Select Query with $this->db->count_all_results()

The count_all_results() function allows you to you to determine the number of rows in a particular Active Record query resultset.

Example:-

CodeIgniter Select Query with $this->db->count_all()

The count_all() function allows you to determine the number of rows in particular table.

Example:-

 

 

 

Before, you start interacting with application database and perform some database operation; you must have configured your application to use the database. CodeIgniter framework comes with an advanced implementation of the PHP Active Record Pattern, which makes it very easy to interact with application database and performing CRUD (Create, Read, Update, Delete) operations.

CodeIgniter Database Configuration

In order to establish database connection, we have to make some configuration changes in our database config file which is stored in application/config directory at application/config/database.php. Let’s open database.php file, here you will find following lines of code –

Database configuration settings are stored in an array, change the default values for hostname, username, password, and database with your database details.

Usually, you keep database connection settings in default database group. But, if you want to connect multiple database then you will need to add another database group to $db config array. This database group will keep the connection detail for this specific database connection.

Connecting Database In CodeIgniter

Before, you start interacting with application database you must have database connection available. In CodeIgniter, we can connect database in following two ways –

Automatically Connecting:- In this method, we set database library class to be autoloaded. It makes database instance to be available automatically throughout the app. In order to enable auto connect open the autoload configuration file located at application/config/autoload.php, find the $autoload[‘libraries’] array, and modify it to auto-load the database library, as following –

Manually Connecting:- This method is useful when only some of your pages require database connectivity. To obtain the database connectivity add the following line of code in specific function, or in your class constructor to make database connection available globally in that class.

Connecting Multiple Databases In CodeIgniter

Sometimes you may require to use two or multiple database in your application. If you need to connect to more than one database simultaneously. To do this you will need to add another database group to $db config array in database config file which is stored in application/config directory at application/config/database.php.

Example:-

Once you defined the database group, you can obtain the database instance as following –

Syntax:-

Replace <db-group-name> with the your database group name, In above function first parameter is database group name which you have define in database.php file and setting the second parameter to TRUE return the database object.

Example:-

Let’s Retrieve data from default group database

Let’s Retrieve data from db2 group database

Closing Database Connection In CodeIgniter

CodeIgniter takes care of closing your database connections automatically, but if in case you want to close the database connection explicitly then it can done using close() method as following –

 

What Is CodeIgniter Controller?

In MVC framework, the letter “C” stands for Controller. Controller works as an intermediary between the Model, View and any other resources required to process the HTTP request, it receives the incoming HTTP requests and process it communicating with models and views, then return the results back to the web browser. In CodeIgniter, controllers is completely responsible for handling the application logic.

In CodeIgniter, all of the controllers stored in application/controllers directory.

What Is CodeIgniter View?

In MVC framework, the letter “V” stands for View. View is one of the key component in any Model-View-Controller(MVC) framework.Views are meant to hold the presentation logic in any codeigniter application. Views separates presentation logic from the application logic. A view file usually contains the information being presented to a user. View can be a complete web page or parts of page like header and footer.

In CodeIgniter, views are stored in application/views directory.

What Is CodeIgniter Model?

In MVC framework, the letter “M” stands for Model. Model are means to handle the business logic in any MVC framework based application. Model is a class that represents the logical structure and gives you the way to retrieve, insert, and update information into your data table. In CodeIgniter, a model usually contain functions that help you retrieve, insert, and update information in your database.

All of the CodeIgniter Models are stored in the application/models directory.