Codeigniter Query Builder Class – This database reference provides you access a query builder class. This pattern allows you to retrieve the information, insert, and update in your database with minimum scripting. In some case, only one or two lines of code are required to perform a database action. Codeigniter does not require that each database table should have its own class file. It alternative provide a more simple interface. Here in this tutorial, we are going to explain how to use Query Builder Class.

Codeigniter Query Builder Class | Example.

Let us understand how Query Builder Class works in codeigniter with examples.

Functions:-

There are following functions available in query helper methods . Now we will explain one by one.

• 1. Selecting Data.
• 2. Looking for Specific Data.
• 3. Ordering results.
• 4. Limiting or Counting Results.
• 5. Query grouping.
• 6. Inserting Data.
• 7. Updating Data.
• 8. Deleting Data.
• 9. Method Chaining.
• 10. Query Builder Caching.
• 11. Resetting Query Builder.

1. Selecting Data.

Here is simple demo of selecting data.

Example:-

$this->db->get()

The insert id number when performing database insert.

$this->db->get_compiled_select()

Display the number of affected row.

$this->db->get_where()

Display the last query that was run.

$this->db->select()

Display the last query that was run.

$this->db->select_max()

Display the last query that was run.

$this->db->select_min()

Display the last query that was run.

$this->db->select_avg()

Display the last query that was run.

$this->db->select_sum()

Display the last query that was run.

$this->db->from()

Display the last query that was run.

$this->db->join()

Display the last query that was run.

db->get('blog');
foreach ($query->result() as $row)
{
echo $row->title;
}
echo "
";
echo "
";
$this->db->select_min('title');
$query = $this->db->get('blog');
foreach ($query->result() as $row)
{
echo $row->title;
}
echo "
";
$this->db->select_min('id');
$query = $this->db->get_compiled_select('blog');
echo $this->db->last_query();
echo "
";
$this->db->select('id, title, name');
$query = $this->db->get('blog');
foreach ($query->result() as $row)
{
echo $row->id;
echo $row->title;
echo $row->name;
echo "
";
}		
}
}
?>

Output will be look like this:-

2. Looking for Specific Data.

Here is simple demo of looking for Specific Data.

Example:-

$this->db->like()

This method enable you to generate like clauses. It is used for searching.

$this->db->or_like()

this is also used for searching multiple instance.

$this->db->not_like()

This method generate not like statement.

$this->db->or_not_like()

This method also generate not like statement with multiple instance.

$this->db->group_by()

This method permits you to write the group by portion of your query.

$this->db->distinct()

This method add the distinct keyword to a query.

$this->db->having()

This function permits you to write the having portion to the query and by this function you can pass multiple values.

$this->db->or_having()

Identical to having(), only separates multiple clauses with “OR”.

db->like('title', 'Title 1');
$this->db->or_like('name', 'Doe');
$this->db->or_not_like('title');		
$query = $this->db->get('blog');		
foreach ($query->result() as $row)
{				
echo $row->title;				
echo $row->name;
echo "
";				
}
echo $this->db->last_query();
echo "
";
$this->db->distinct();		
$query = $this->db->get('blog');
echo $this->db->last_query();
echo "
";
$this->db->having('id = 4');
$query = $this->db->get('blog');
echo $this->db->last_query();
}
}
?>

Output will be look like this:-

3. Ordering results.

Here is simple demo of ordering results.

Example:-

$this->db->order_by()

This method is used to set order by clause.

db->order_by('title', 'DESC');
$query = $this->db->get('blog');
foreach ($query->result() as $row)
{				
echo $row->title;
echo "
";				
}
echo "
";
echo "ASC ORDER";
$this->db->order_by('name', 'ASC');
$query = $this->db->get('blog');
foreach ($query->result() as $row)
{								
echo $row->name;
echo "
";				
}
echo "
";
echo "RANDOM ORDER";
$this->db->order_by('name', 'RANDOM');
$query = $this->db->get('blog');
foreach ($query->result() as $row)
{								
echo $row->name;
echo "
";				
}
}
}
?>

Output will be look like this:-

4. Limiting or Counting Results.

Here is simple demo of limiting or Counting Results.

Example:-

$this->db->limit()

This method is used to set limit of row.

$this->db->count_all_results()

This method permits you to determine the number of rows in a particular active record query.

db->count_all_results('blog');
echo "
";		
$this->db->like('name', 'doe');
$this->db->from('blog');
echo $this->db->count_all_results();
}
}
?>

Output will be look like this:-

5. Query grouping.

Here is simple demo of Query grouping.

Example:-

$this->db->group_start()

Starts a new group by adding an opening parenthesis to the WHERE clause of the query.

$this->db->or_group_start()

Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with ‘OR’.

$this->db->not_group_start()

Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with ‘NOT’.

$this->db->or_not_group_start()

Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with ‘OR NOT’.

$this->db->group_end()

Ends the current group by adding an closing parenthesis to the WHERE clause of the query.

db->select('*')->from('blog')
->group_start()
->where('id', 'id')
->or_group_start()
->where('title', 'title')
->where('name', 'name')
->group_end()
->group_end()
->get();
echo $this->db->last_query();	
}
}
?>

Output will be look like this:-

6. Inserting data.

Here is simple demo of inserting data.

Example:-

$this->db->insert()

Generates an insert string based on the data you supply, and runs the query.

$this->db->get_compiled_insert()

Compiles the insertion query just like $this->db->insert() but does not run the query. This method simply returns the SQL query as a string.

$this->db->insert_batch()

Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with ‘NOT’.

 '7',
'title' => 'Job',
'name' => 'Ram'
);
$this->db->insert('blog', $data);
echo "
";
$insert = array(
'id' => '8',
'title'  => 'Game',
'name'  => 'Shyam'
);
$sql = $this->db->set($insert)->get_compiled_insert('blog');
echo $sql;		
echo "
";
$query = array(array(
'id' => '9',
'title' => 'Study',
'name' => 'Books'),
array(
'id' => '10',
'title' => 'Game',
'name' => 'Cricket'));
$this->db->insert_batch('blog', $query);
}
}
?>

Output will be look like this:-

7. Updating Data.

Here is simple demo of updating data.

Example:-

$this->db->replace()

This method execute a replace statement.

$this->db->set()

This function enable tou to set value for insert and update.

$this->db->update()

Generates an update string and runs the query based on the data you supply.

$this->db->update_batch()

Generates an update string based on the data you supply, and runs the query. You can pass an array or an object to the function.

$this->db->get_compiled_update()

This function produce update sql string.

 '5',
'title'  => 'updateTitle',
'name'  => 'UpdateName');
$this->db->replace('blog', $data);
echo "
";

// Set Function
$this->db->set('name', 'Ram');
$this->db->insert('blog');
echo "
";

//Update Function
$data = array(
'title' => 'Software Engineer',
'name' => 'Sonu');
$this->db->where('id', '5');
$this->db->update('blog', $data);		
echo "
";

//Update_batch Function
$data = array(array(
'id' => '1' ,
'title' => 'Company' ,
'name' => 'SolidCoupon'),
array(
'id' => '2' ,
'title' => 'Website' ,
'name' => 'tutorialsplane.com'));
$this->db->update_batch('blog', $data, 'id');
}
}
?>

Output will be look like this:-

8. Deleting Data.

Here is simple demo of deleting data.

Example:-

$this->db->delete()

$this->db->empty_table()

$this->db->truncate()

Generate a delete sql string and run the query.

$this->db->get_compiled_delete()

It produce a delete sql string.

db->delete('abc', array('id' => '1'));
echo "
";
		
$tables = array('abc', 'abcd');
$this->db->where('id', '2');
$this->db->delete($tables);
echo "
";
		
$this->db->empty_table('abc');
echo "
";
		
$this->db->truncate('abc');
}
}
?>

9. Method chaining.

Chaining method allows you to simplify your syntax by connecting multiple functions.

Example:-

db->select('title')
->where('id', '2')
->limit(1, 5)
->get('abc');				
}
}
?>

10. Query Builder Caching.

Query Builder enables you to save certain parts of your queries for reuse at a later point in your script’s execution.

Example:-

$this->db->start_cache()

This function must be called to begin caching.

$this->db->stop_cache()

This function can be called to stop caching.

$this->db->flush_cache()

This function delete all items from the query builder cache.

db->start_cache();
$this->db->select('id');
$this->db->stop_cache();
$this->db->get('abc');
$this->db->select('title');
$this->db->get('abc');
$this->db->flush_cache();
$this->db->select('description');
$this->db->get('abc');
echo $this->db->last_query(); 
}
}
?>

11. Resetting Query Builder.

Here is the simple demo of resetting query builder.

Example:-

$this->db->reset_query()

This query allows you to start fresh with your query without executing it first using a method like get and insert.

db->select(array('id','title'))
->where('name', 'Ram')
->get_compiled_select('blog', FALSE);
echo $sql;
}
}
?>

Class reference:-

There are following references available in query builder class. Now we will explain.

1. Reset query.

This reference is used to reset the current query builder state.

reset_query()

• Parameters :
• Returns : CI_DB_query_builder instance.
• Returns type : CI_DB_query_builder.

2. Start cache.

This reference is used to start the query builder cache.

start_cache()

• Parameters :
• Returns : CI_DB_query_builder instance.
• Returns type : CI_DB_query_builder.

3. Stop cache.

This reference is used to stop the query builder cache.

stop_cache()

• Parameters :
• Returns : CI_DB_query_builder instance.
• Returns type : CI_DB_query_builder.

4. Flush cache.

This reference is used to empties the query builder cache.

flush_cache()

• Parameters :
• Returns : CI_DB_query_builder instance.
• Returns type : CI_DB_query_builder.

5. Set dbprefix.

This reference is used to set the database prefix, without having to reconnect.

set_dbprefix([$prefix = ''])

• Parameters :
• $prefix (string) : The new prefix to use.
• Returns : The DB prefix in use.
• Returns type : String.

6. Dbprefix.

This reference is used to prepand a database prefix, if one exits in configuration.

dbprefix([$table = ''])

• Parameters :
• $table (string) : The table name to prefix.
• Returns : The prefixed table name.
• Returns type : String.

7. Count all result.

This reference generates a platform specific query string that counts all records and returned by an Query Builder query.

count_all_results([$table = ''[$reset = TRUE]])

• Parameters :
• $table (string) : Table name.
• $reset (bool) : Whether to reset values for SELECTs.
• Returns : Number of rows in the query result.
• Returns type : Int.

8. Get.

This reference is used to compile and run select statement based on the already called query builder method.

get([$table = ''[$limit = NULL[$offset = NULL]]])

• Parameters :
• $table (string) : The table to query.
• $limit : The LIMIT clause.
• $offset (int) : The OFFSET clause.
• Returns : CI_DB_result instance.
• Returns type : CI_DB_result.

9. Get where.

This reference is same like get() method but also allow where method.

get_where([$table = ''[$where = NULL[$limit = NULL[$offset = NULL]]]])

• Parameters :
• $table (string) : The table(s) to fetch data from; string or array.
• $limit : The LIMIT clause.
• $where (string) : The WHERE clause.
• $offset (int) : The OFFSET clause.
• Returns : CI_DB_result instance.
• Returns type : CI_DB_result.

10. Select.

This reference is used to add a SELECT clause to a query.

select([$select = '*'[$escape = NULL]])

• Parameters :
• $select (string) : The SELECT portion of a query.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : CI_DB_result instance.
• Returns type : CI_DB_result.

11. Select avg.

This reference is used to add a SELECT AVG(field) clause to a query.

select_avg([$select = ''[$alias = '']])

• Parameters :
• $select (string) : Field to compute the average of.
• $alias (string) : Alias for the resulting value name.
• Returns : CI_DB_result instance.
• Returns type : CI_DB_result.

12. Select max.

This reference is used to add a SELECT MAX(field) clause to a query.

select_max([$select = ''[$alias = '']])

• Parameters :
• $select (string) : Field to compute the maximum of.
• $alias (string) : Alias for the resulting value name.
• Returns : CI_DB_result instance.
• Returns type : CI_DB_result.

13. Select min.

This reference is used to add a SELECT MIN(field) clause to a query.

select_min([$select = ''[$alias = '']])

• Parameters :
• $select (string) : Field to compute the minimum of.
• $alias (string) : Alias for the resulting value name.
• Returns : CI_DB_result instance.
• Returns type : CI_DB_result.

14. Select sum.

This reference is used to add a SELECT SUM(field) clause to a query.

select_sum([$select = ''[$alias = '']])

• Parameters :
• $select (string) : Field to compute the sum of.
• $alias (string) : Alias for the resulting value name.
• Returns : CI_DB_result instance.
• Returns type : CI_DB_result.

15. Distinct.

This reference is used to set a flag which tells the query builder to add a DISTINCT clause to the SELECT portion of the query.

distinct([$val = TRUE])

• Parameters :
• $val (bool) : Desired value of the “distinct” flag.
• Returns : CI_DB_result instance.
• Returns type : CI_DB_result.

16. From.

This reference is used to specify the FROM clause of query.

from($from)

• Parameters :
• $from (mixed) : Table name(s); string or array.
• Returns : CI_DB_result instance.
• Returns type : CI_DB_result.

17. Join.

This reference is used to add a JOIN clause to a query.

join($table, $cond[$type = ''[$escape = NULL]])

• Parameters :
• $table (string) : Table name to join.
• $cond (string) : The JOIN ON condition.
• $type (string) : The JOIN type.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : CI_DB_result instance.
• Returns type : CI_DB_result.

18. Where.

This reference is used to generate the WHERE portion of the query and seperated multiple call with AND.

where($key[$value = NULL[$escape = NULL]])

• Parameters :
• $key (mixed) : Name of field to compare, or associative array.
• $value (mixed) : If a single key, compared to this value.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : DB_query_builder instance.
• Returns type : Object.

19. Or where.

This reference is used to generate the WHERE portion of the query and seperated multiple call with OR.

or_where($key[$value = NULL[$escape = NULL]])

• Parameters :
• $key (mixed) : Name of field to compare, or associative array.
• $value (mixed) : If a single key, compared to this value.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : DB_query_builder instance.
• Returns type : Object.

20. Or where in.

This reference is used to generate the WHERE field IN SQL query, joined with OR.

or_where_in([$key = NULL[$values = NULL[$escape = NULL]]])

• Parameters :
• $key (mixed) : The field to search.
• $value (mixed) : The values searched on.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : DB_query_builder instance.
• Returns type : Object.

21. Or where not in.

This reference is used to generate the WHERE field NOT IN SQL query, joined with OR.

or_where_not_in([$key = NULL[$values = NULL[$escape = NULL]]])

• Parameters :
• $key (mixed) : The field to search.
• $value (mixed) : The values searched on.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : DB_query_builder instance.
• Returns type : Object.

22. Where in.

This reference is used to generate the WHERE field IN SQL query, joined with AND if appropriate.

where_in([$key = NULL[$values = NULL[$escape = NULL]]])

• Parameters :
• $key (mixed) : Name of field to examine.
• $value (mixed) : Array of target values.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : DB_query_builder instance.
• Returns type : Object.

23. Where not in.

This reference is used to generate the WHERE field NOT IN SQL query, joined with AND if appropriate.

where_not_in([$key = NULL[$values = NULL[$escape = NULL]]])

• Parameters :
• $key (mixed) : Name of field to examine.
• $value (mixed) : Array of target values.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : DB_query_builder instance.
• Returns type : Object.

24. Group start.

Start a group expression, using AND for the condition inside it.

group_start()

• Parameters :
• Returns : DB_query_builder instance.
• Returns type : Object.

25. Or group start.

Start a group expression, using OR for the condition inside it.

or_group_start()

• Parameters :
• Returns : DB_query_builder instance.
• Returns type : Object.

26. Not group start.

Start a group expression, using AND NOTs for the condition inside it.

not_group_start()

• Parameters :
• Returns : DB_query_builder instance.
• Returns type : Object.

27. or not group start.

Start a group expression, using OR NOTs for the condition inside it.

or_not_group_start()

• Parameters :
• Returns : DB_query_builder instance.
• Returns type : Object.

28. Group end.

It is used to end a group expression.

group_end()

• Parameters :
• Returns : DB_query_builder instance.
• Returns type : Object.

29. Like.

It is used to adds a LIKE clause to a query, separating multiple calls with AND.

like($field[$match = ''[$side = 'both'[$escape = NULL]]])

• Parameters :
• $field (string) : Field name
• $match (string) : Text portion to match
• $side (string) : Which side of the expression to put the ‘%’ wildcard on
• $escape (bool) : Whether to escape values and identifiers
• Returns : DB_query_builder instance.
• Returns type : CI_DB_query_builder.

30. Or like.

It is used to adds a LIKE clause to a query, separating multiple class with OR.

or_like($field[$match = ''[$side = 'both'[$escape = NULL]]])

• Parameters :
• $field (string) : Field name
• $match (string) : Text portion to match
• $side (string) : Which side of the expression to put the ‘%’ wildcard on
• $escape (bool) : Whether to escape values and identifiers
• Returns : DB_query_builder instance.
• Returns type : CI_DB_query_builder.

31. Or not like.

It is used to adds a NOT LIKE clause to a query, separating multiple calls with OR.

or_not_like($field[$match = ''[$side = 'both'[$escape = NULL]]])

• Parameters :
• $field (string) : Field name
• $match (string) : Text portion to match
• $side (string) : Which side of the expression to put the ‘%’ wildcard on
• $escape (bool) : Whether to escape values and identifiers
• Returns : DB_query_builder instance.
• Returns type : CI_DB_query_builder.

32. Having.

It is used to adds a HAVING clause to a query, separating multiple calls with AND.

having($key[$value = NULL[$escape = NULL]])

• Parameters :
• $key (mixed) : Identifier (string) or associative array of field/value pairs.
• $value (string) : Value sought if $key is an identifier.
• $escape (bool) : Whether to escape values and identifiers
• Returns : DB_query_builder instance.
• Returns type : CI_DB_query_builder.

33. Or having.

It is used to adds a HAVING clause to a query, separating multiple calls with OR.

or_having($key[$value = NULL[$escape = NULL]])

• Parameters :
• $key (mixed) : Identifier (string) or associative array of field/value pairs.
• $value (string) : Value sought if $key is an identifier.
• $escape (bool) : Whether to escape values and identifiers
• Returns : DB_query_builder instance.
• Returns type : CI_DB_query_builder.

34. Group by.

It is used to adds a GROUP BY clause to a query.

group_by($by[$escape = NULL])

• Parameters :
• $by (mixed) : Field(s) to group by; string or array.
• Returns : DB_query_builder instance.
• Returns type : CI_DB_query_builder.

35. Order by.

It is used to adds an ORDER BY clause to a query.

order_by($orderby[$direction = ''[$escape = NULL]])

• Parameters :
• $orderby (string) : Field to order by.
• $direction (string) : The order requested – ASC, DESC or random.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : DB_query_builder instance.
• Returns type : CI_DB_query_builder.

36. Limit.

It is used to adds LIMIT and OFFSET clauses to a query.

limit($value[$offset = 0])

• Parameters :
• $value (int) : Number of rows to limit the results to.
• $offset (int) : Number of rows to skip.
• Returns : DB_query_builder instance.
• Returns type : CI_DB_query_builder.

37. Offset.

It is used to adds an OFFSET clause to a query.

offset($offset)

• Parameters :
• $offset (int) : Number of rows to skip.
• Returns : DB_query_builder instance.
• Returns type : CI_DB_query_builder.

38. Set.

It is used to adds field/value pairs to be passed later to insert(), update() and replace().

set($key[$value = ''[$escape = NULL]])

• Parameters :
• $key (mixed) : Field name, or an array of field/value pairs.
• $value (string) : Field value, if $key is a single field.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : DB_query_builder instance.
• Returns type : CI_DB_query_builder.

39. Insert.

It is used to Compiles and executes an INSERT statement.

insert([$table = ''[$set = NULL[$escape = NULL]]])

• Parameters :
• $table (string) : Table name.
• $set (array) : An associative array of field/value pairs.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : TRUE on success, FALSE on failure.
• Returns type : Bool.

40. Insert batch.

It is used to Compiles and executes an INSERT statement.

insert_batch($table[$set = NULL[$escape = NULL[$batch_size = 100]]])

• Parameters :
• $table (string) : Table name.
• $set (array) : Data to insert.
• $escape (bool) : Whether to escape values and identifiers.
• $batch_size (int) : Count of rows to insert at once.
• Returns : Number of rows inserted or FALSE on failure.
• Returns type : Mixed.

41. Set insert batch.

It is used to adds field/value pairs to be inserted in a table later via insert_batch().

set_insert_batch($key[$value = ''[$escape = NULL]])

• Parameters :
• $key (mixed) : Field name or an array of field/value pairs.
• $value (string) : Field value, if $key is a single field.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : CI_DB_query_builder instance.
• Returns type : CI_DB_query_builder.

42. Update.

It is used to Compiles and executes an UPDATE statement.

update([$table = ''[$set = NULL[$where = NULL[$limit = NULL]]]])

• Parameters :
• $table (string) : Table name.
• $set (array) : Field name, or an associative array of field/value pairs.
• $where (string) : The WHERE clause.
• $limit (int) : The LIMIT clause.
• Returns : TRUE on success, FALSE on failure.
• Returns type : Bool.

43. Update batch.

It is used to Compiles and executes batch UPDATE statements.

update_batch($table[$set = NULL[$value = NULL[$batch_size = 100]]])

• Parameters :
• $table (string) : Table name.
• $set (array) : Field name, or an associative array of field/value pairs.
• $value (string) : Field value, if $set is a single field.
• $batch_size (int) : Count of conditions to group in a single query.
• Returns : Number of rows updated or FALSE on failure.
• Returns type : mixed.

44. Set update batch.

It is used to adds field/value pairs to be updated in a table later via update_batch().

set_update_batch($key[$value = ''[$escape = NULL]])

• Parameters :
• $key (mixed) : Field name or an array of field/value pairs.
• $value (string) : Field value, if $key is a single field.
• $escape (bool) : Whether to escape values and identifiers.
• Returns : CI_DB_query_builder instance.
• Returns type : CI_DB_query_builder.

45. Replace.

It is used to compiles and executes a REPLACE statement.

replace([$table = ''[$set = NULL]])

• Parameters :
• $table (string) : Table name.
• $set (array) : An associative array of field/value pairs.
• Returns : TRUE on success, FALSE on failure.
• Returns type : Bool.

46. Delete.

It is used to compiles and executes a DELETE query.

delete([$table = ''[$where = ''[$limit = NULL[$reset_data = TRUE]]]])

• Parameters :
• $table (string) : The table(s) to delete from; string or array.
• $where (string) : The WHERE clause.
• $limit (int) : The LIMIT clause.
• $reset_data (bool) : TRUE to reset the query “write” clause.
• Returns : CI_DB_query_builder instance.
• Returns type : mixed.

47. Truncate.

It is used to executes a TRUNCATE statement on a table.

truncate([$table = ''])

• Parameters :
• $table (string) : Table name.
• Returns : TRUE on success, FALSE on failure.
• Returns type : Bool.

48. Empty table.

It is used to deletes all records from a table via a DELETE statement.

empty_table([$table = ''])

• Parameters :
• $table (string) : Table name.
• Returns : TRUE on success, FALSE on failure.
• Returns type : Bool.

49. Get compile select.

It is used to Compiles a SELECT statement and returns it as a string.

get_compiled_select([$table = ''[$reset = TRUE]])

• Parameters :
• $table (string) : Table name.
• $reset (bool) : Whether to reset the current QB values or not.
• Returns : The compiled SQL statement as a string.
• Returns type : string.

50. Get compile insert.

It is used to compiles an INSERT statement and returns it as a string.

get_compiled_insert([$table = ''[$reset = TRUE]])

• Parameters :
• $table (string) : Table name.
• $reset (bool) : Whether to reset the current QB values or not.
• Returns : The compiled SQL statement as a string.
• Returns type : string.

51. Get compile update.

It is used to compiles an UPDATE statement and returns it as a string.

get_compiled_update([$table = ''[$reset = TRUE]])

• Parameters :
• $table (string) : Table name.
• $reset (bool) : Whether to reset the current QB values or not.
• Returns : The compiled SQL statement as a string.
• Returns type : string.

52. Get compile delete.

It is used to compiles a DELETE statement and returns it as a string.

get_compiled_delete([$table = ''[$reset = TRUE]])

• Parameters :
• $table (string) : Table name.
• $reset (bool) : Whether to reset the current QB values or not.
• Returns : The compiled SQL statement as a string.
• Returns type : string.

Codeigniter DB Driver Reference – This database reference has many class references. This is the platform-independent based DB implement class. This class will not be called directly. Or the adapter class for the specific database will extend and instantiate it. Here in this tutorial, we are going to explain how to use DB Driver Reference.

Codeigniter DB Driver Reference | Example

Let us understand how DB Driver Reference works in codeigniter with examples.

Class reference:-

There are following references available in DB Driver Reference. Now we will explain.

1. Initialize.

This reference is used to initialized database setting, established a connection to the database.

initialize()

• Parameters :
• Returns : TRUE on success, FALSE on failure
• Returns type : Bool

2. DB connect.

This reference is used to establish a connection with the database.

db_connect($persistent = TRUE)

• Parameters :
• $persistent (bool) : Whether to establish a persistent connection or a regular one
• Returns : Database connection resource/object or FALSE on failure
• Returns type : Mixed

3. DB pconnect.

This reference is used to establish a persistent connection with the database.

db_pconnect()

• Parameters :
• Returns : Database connection resource/object or FALSE on failure
• Returns type : Mixed

4. Reconnect.

This reference is used to keep/reestablish the database connection.

reconnect()

• Parameters :
• Returns : TRUE on success, FALSE on failure
• Returns type : Bool

5. DB select.

This reference is used to select/switch the current database.

db_select([$database = ''])

• Parameters :
• $database (string) : Database name
• Returns : TRUE on success, FALSE on failure
• Returns type : Bool

6. DB set charset.

This reference is used to set client character set.

db_set_charset($charset)

• Parameters :
• $charset (string) : Character set name
• Returns : TRUE on success, FALSE on failure
• Returns type : Bool

7. Platform.

This reference is used to display the name of platform.

platform()

• Parameters :
• Returns : Platform name.
• Returns type : String

8. Version.

This reference is used to display database number version.

version()

• Parameters :
• Returns : The version of the database being used.
• Returns type : string

9. Query.

This reference is used to execute an SQL query.

query($sql[$binds = FALSE[$return_object = NULL]])

• Parameters :
• $sql (string) : The SQL statement to execute
• $binds (array) : An array of binding data
• $return_object (bool) : Whether to return a result object or not
• Returns : TRUE for successful “write-type” queries, CI_DB_result instance on “query” success, FALSE on failure.
• Returns type : mixed

10. Simple query.

This reference is used to simplified version of the array method.

simple_query($sql)

• Parameters :
• $sql (string) : The SQL statement to execute
• Returns : Whatever the underlying driver’s “query” function returns.
• Returns type : mixed

11. Affected rows.

This reference is used to return the number of row changed by the last executed query.

affected_rows()

• Parameters :
• Returns : Number of rows affected.
• Returns type : int

12. Trans strict.

This reference is used to enable/disable transaction “strict” mode.

trans_strict([$mode = TRUE])

• Parameters :
• $mode (bool) : Strict mode flag.
• Returns type : void

13. Trans off.

This reference is used to disables transactions at run-time.

trans_off()

• Parameters :
• Returns type : void

14. Trans start.

This reference is used to disables transactions at run-time.

trans_start([$test_mode = FALSE])

• Parameters :
• $test_mode (bool) : Test mode flag
• Returns : TRUE on success, FALSE on failure
• Returns type : Bool

15. Trans complete.

This reference is used to complete the transaction.

trans_complete()

• Parameters :
• Returns : TRUE on success, FALSE on failure
• Returns type : Bool

16. Trans status.

This reference is used to retrieve the transaction status flag.

trans_status()

• Parameters :
• Returns : TRUE if the transaction succeeded, FALSE if it failed.
• Returns type : Bool

17. compile binds.

This reference is used to compiles an SQL query with the bind values passed for it.

compile_binds($sql, $binds)

• Parameters :
• $sql (string) : The SQL statement
• $binds (array) : An array of binding data
• Returns : The updated SQL statement.
• Returns type : String.

18. Is write type.

This reference is used to determines if a query is of a “write” type (INSERT, UPDATE, DELETE) or “read” type (SELECT).

is_write_type($sql)

• Parameters :
• $sql (string) : The SQL statement
• Returns : TRUE if the SQL statement is of “write type”, FALSE if not.
• Returns type : Bool.

19. Elapsed time.

This reference is used to calculate the aggregate query elapsed time.

elapsed_time([$decimals = 6])

• Parameters :
• $decimals (int) : The number of decimal places.
• Returns : The aggregate query elapsed time, in microseconds.
• Returns type : String.

20. Total queries.

This reference returns the total number of queries that have been executed.

total_queries()

• Parameters :
• Returns : The total number of queries executed.
• Returns type : Int.

21. Last query.

This reference returns the last query that was executed.

last_query()

• Parameters :
• Returns : The last query executed.
• Returns type : string.

22. Escape.

This reference is used to escapes input data based on type, including boolean and NULL.

escape($str)

• Parameters :
• $str (mixed) : The value to escape, or an array of multiple ones
• Returns : The escaped value.
• Returns type : mixed.

23. Escape str.

This reference is used to escapes string values.

escape_str($str[$like = FALSE])

• Parameters :
• $str (mixed) : A string value or array of multiple ones.
• $like (bool) : Whether or not the string will be used in a LIKE condition.
• Returns : The escaped string.
• Returns type : mixed.

24. Escape like str.

This reference is used to escapes like string.

escape_like_str($str)

• Parameters :
• $str (mixed) : A string value or array of multiple ones.
• Returns : The escaped string.
• Returns type : mixed.

25. Primary.

This reference is used to retrieves the primary key of a table.

primary($table)

• Parameters :
• $table (string) : Table name.
• Returns : The primary key name, FALSE if none.
• Returns type : String.

26. Count all.

This reference returns the total number of rows in a table.

count_all([$table = ''])

• Parameters :
• $table (string) : Table name.
• Returns : Row count for the specified table.
• Returns type : Int.

27. List tables.

This reference is used to get a list of the tables in the current database.

list_tables([$constrain_by_prefix = FALSE])

• Parameters :
• $constrain_by_prefix (bool) : TRUE to match table names by the configured dbprefix.
• Returns : Array of table names or FALSE on failure.
• Returns type : Array.

28. Table exists.

This reference is used to determine if a particular table exists.

table_exists($table_name)

• Parameters :
• $table_name (string) : The table name.
• Returns : TRUE if that table exists, FALSE if not.
• Returns type : Bool.

29. List fields.

This reference is used to get a list of the field names in a table.

list_fields($table)

• Parameters :
• $table (string) : The table name.
• Returns : Array of field names or FALSE on failure.
• Returns type : Array.

30. Field exists.

This reference is used to determine if a particular field exists.

field_exists($field_name, $table_name)

• Parameters :
• $table_name (string) : The table name.
• $field_name (string) : The field name.
• Returns : TRUE if that field exists in that table, FALSE if not.
• Returns type : Bool.

31. Field data.

This reference is used to get a list containing field data about a table.

field_data($table)

• Parameters :
• $table_name (string) : The table name.
• Returns : Array of field data items or FALSE on failure.
• Returns type : Bool.

32. Escape identifiers.

This reference is used to escape SQL identifiers, such as column, table and names.

escape_identifiers($item)

• Parameters :
• $item (mixed) : The item or array of items to escape.
• Returns : The input item(s), escaped.
• Returns type : Mixed.

33. Insert string.

This reference is used to generate an INSERT statement string.

insert_string($table, $data)

• Parameters :
• $table (string) : The target table.
• $data (array) : An associative array of key/value pairs.
• Returns : The SQL INSERT statement, as a string.
• Returns type : String.

34. Update string.

This reference is used to generate an UPDATE statement string.

update_string($table, $data, $where)

• Parameters :
• $table (string) : The target table.
• $data (array) : An associative array of key/value pairs.
• $where (mixed) : The WHERE statement conditions.
• Returns : The SQL UPDATE statement, as a string.
• Returns type : String.

35. Call function.

This reference is used to Runs a native PHP function , using a platform agnostic wrapper.

call_function($function)

• Parameters :
• $function (string) : Function name.
• Returns : The function result.
• Returns type : String.

36. Cache set path.

This reference is used to sets the directory path to use for caching storage.

cache_set_path([$path = ''])

• Parameters :
• $path (string) : Path to the cache directory.
• Returns type : String.

37. Cache set path.

This reference is used to sets the directory path to use for caching storage.

cache_set_path([$path = ''])

• Parameters :
• $path (string) : Path to the cache directory.
• Returns type : String.

38. Cache on.

This reference is used to disable database results caching.

cache_on()

• Parameters :
• Returns : TRUE if caching is on, FALSE if not.
• Returns type : String.

39. Cache delete.

This reference is used to delete the cache files associated with a particular URI.

cache_delete([$segment_one = ''[$segment_two = '']])

• Parameters :
• $segment_one (string) : First URI segment
• $segment_two (string) : Second URI segment
• Returns : TRUE on success, FALSE on failure.
• Returns type : Bool.

40. Cache delete all.

This reference is used to delete all cache files.

cache_delete_all()

• Parameters :
• Returns : TRUE on success, FALSE on failure.
• Returns type : Bool.

41. Close.

This reference is used to close the DB Connection.

close()

• Parameters :
• Returns type : void.

42. Display error.

This reference is used to display an error message and stop script execution.

display_error([$error = ''[$swap = ''[native = FALSE]]])

• Parameters :
• $error (string) : The error message
• $swap (string) : Any “swap” values
• $native (bool) : Whether to localize the message
• Returns type : void.

43. Protect identifiers.

This reference is used to takes a column or table name and applies the configured dbprefix to it.

protect_identifiers($item[$prefix_single = FALSE[$protect_identifiers = NULL[$field_exists = TRUE]]])

• Parameters :
• $item (string) : The item to work with
• $prefix_single (bool) : Whether to apply the dbprefix even if the input item is a single identifier
• $protect_identifiers (bool) : Whether to quote identifiers
• $field_exists (bool) : Whether the supplied item contains a field name or not
• Returns : The modified item
• Returns type : string.

Codeigniter Database Caching Class – This database reference provides many functions that are used to caching the database results, this class permits you to cache your queries as text files for reducing the database load. This class is automatically loaded by the database driver when caching is enable. Do not load this class manually. Here in this tutorial, we are going to explain how to use Database Caching Class.

Codeigniter Database Caching Class | Example.

Let us understand how Database Caching Class works in codeigniter with examples.

Functions:-

There are following functions available in Database Caching Class. Now we will explain one by one.

1. Enabling caching.
2. How does caching work.
3. How are cache files stored?.
4. Managing your cache files.
5. Function reference.

1. Enabling caching.

Caching is enabled in three steps:-

• Create a writable on your server where the cache file can be stored.
• Set the path to your cache folder in your application/config/database.php file.
• Enable the caching feature, or globally by setting the preference in your application/config/database.php file.

Once enabled, caching will happened automatically whenever a page is loaded than contain database queries.

2. How Does Caching Work?.

Codeigniter query caching system happen dynamically when your page is viewed. When caching is enabled, the first time a web page is loaded, the query result will be serialized and stored in a text file on your server. Next time page is loaded the cache file will be used instead of accessing your database.

Only read type(SELECT) queries can be cached since these are the only type of queries that produce a result. Write type(INSERT, UPDATE, etc..) queries, since they don’t generate the result, will not be generated by the system.

Cache file DO NOT expire until you delete them.

3. How are Cache Files Stored?.

Codeigniter place the result of EACH query into in own cache file. Set of cache file are further organized into sub-folders corresponding to your controller function. To be precise, the sub-folder are named identically to the first two segment of your URI.

For example, let’s say you have a controller called blog with the function called comments that contain three queries. The caching system will create a cache folder called blog+comments, into which it will write three cache file.

4. Managing your Cache Files.

Cache file does not expire, you will need to build deletion routines into your application.

Not all database function working with caching.

The following functions are not available when using a cached result object.

• num_fields()
• fields_name()
• field_data()
• free_result()

The two database resource(result_id and conn_id) are not available when caching, since result resource only belong to run-time operations.

5. Function Reference.

Here is the simple demo of function reference

$this->db->cache_on()/$this->db->cache_on()

It is used to enables/disables caching.

$this->db->cache_on();
$query = $this->db->query("SELECT * FROM blog");

// Turn caching off for this one query
$this->db->cache_off();
$query = $this->db->query("SELECT * FROM blog WHERE member_id = '$current_user'");

// Turn caching back on
$this->db->cache_on();
$query = $this->db->query("SELECT * FROM admin");

$this->db->cache_delete()

It is used to deletes the cache files associated with a particular page.

$this->db->cache_delete('blog', 'comments');

$this->db->cache_delete_all()

It is used to clears all existing cache files.

$this->db->cache_delete_all();

Codeigniter Custom Function Calls – This database reference provides many functions that are used to enable you to call PHP database function that is not natively included in CodeIgniter. For example, let’s say you want to call the mysql_get_client_info() function, which is not natively supported by CodeIgniter. Here in this tutorial, we are going to explain how to use Custom Function Calls.

Codeigniter Custom Function Calls | Example.

Let us understand how Custom Function Calls works in codeigniter with examples.

Custom Function Calls.

Here is simple demo of Custom Function Calls.

$this->db->call_function();

You could do so like this.

Example:-

$this->db->call_function('get_client_info');

You must supply the name of the function, without the mysql_prefix, in the first parameter. The prefix is added automatically based on which database driver is currently being used. The permits you to run the same function of different database platform. Obviously not all function call are identical between platforms, so there are limit to how useful this function can be in terms of portability.

$this->db->call_function('any_function', $parameter1, $parameter2, etc.);

Many times, you will either need to supply a database connection ID or a database result ID. The connection ID can be accessed using:

$this->db->conn_id;

This result ID can be accessed from within your result object, like this:-

$query = $this->db->query("SOME QUERY");
$query->result_id;

Codeigniter Database Metadata Reference – This database reference provides many functions that are used to fetching table information from the database. Here in this tutorial, we are going to explain how to use Database Metadata Reference.

Codeigniter Database Metadata Reference | Example.

Let us understand how Database Metadata Reference works in codeigniter with examples.

Functions:-

There are following functions available in Database Metadata reference. Now we will explain one by one.

• 1. Table MetaData.
• 2. Field MetaData.
• 3. Retrieve Field Metadata.

1. Table MetaData.

Here is simple demo of table metaData.

Example:-

db->list_tables();
foreach ($tables as $table)
{
echo $table;
echo "
";
}
}
}
?>

Output will be like this:-

2. Field MetaData.

Here is simple demo of field metaData.

Example:-

db->list_fields('blog');
foreach ($fields as $field)
{
echo $field;
}		
}
}
?>

Output will be like this:-

3. Retrieve Field Metadata.

Here is simple demo of retrieve field metadata.

Example:-

db->field_data('blog');
foreach ($fields as $field)
{
echo $field->name;
echo $field->type;
echo $field->max_length;
echo $field->primary_key;
}		
}
}
?>

The following data is available from this function if supported by your database.

• Name : Column name
• max_length : maximum length of the column
• primary_key : 1 if the column is a primary key
• type : the type of the column

Output will be like this:-

Codeigniter Transactions Reference – This database reference provides many functions that allows you to use transactions with databases that support transaction-safe table type. In MySQL, you will need to be running InnoDB or BDB table type rather than the more common MY|SAM. Most other database platform support transactions natively. Here in this tutorial, we are going to explain how to use transactions reference.

Codeigniter Transactions Reference | Example.

Let us understand how transactions reference works in codeigniter with examples.

Functions:-

There are following functions available in transactions reference. Now we will explain one by one.

• 1. Running Transactions.
• 2. Strict Mode.
• 3. Managing Errors.
• 4. Disabling Transactions.
• 5. Test Mode.
• 6. Running Transactions Manually.

1. Running Transactions.

Here is simple demo of running transactions.

Example:-

db->trans_start();
$this->db->query('select * from blog');
if($this->db->query('select * from blog'))
{
	echo "success!";
}
$this->db->trans_complete();
}
}
?>

Output will be like this:-

2. Strict Mode.

Here is simple demo of strict mode.

Example:-

$this->db->trans_strict(FALSE);

3. Managing Errors.

Here is simple demo of managing errors.

Example:-

db->trans_start();
$this->db->query('select * from blog');
$this->db->trans_complete();
if ($this->db->trans_status() === FALSE)
{
echo "error";
}
else	
{
echo "Successfully run !";
}			
}
}
?>

Output will be like this:-

4. Disabling Transactions.

Here is simple demo of disabling transactions.

Example:-

db->trans_off();
$this->db->trans_start();
$this->db->query('select * from blog');
$this->db->trans_complete();
echo $this->db->last_query();
}
}
?>

Output will be like this:-

5. Test Mode.

Here is simple demo of test mode.

Example:-

db->trans_start(TRUE); 
$this->db->query("UPDATE blog SET name='Doe' WHERE id=2");
$this->db->trans_complete();
echo $this->db->last_query();
}
}
?>

Output will be like this:-

6. Running Transactions Manually.

Here is simple demo of running transactions manually.

Example:-

db->trans_begin();
$this->db->query('select * from blog');
$this->db->query("UPDATE blog SET name='Doe' WHERE id=2");
if ($this->db->trans_status() === FALSE)
{
echo $this->db->trans_rollback();
}
else
{
echo $this->db->trans_commit();
}
}
}
?>

Codeigniter Query Helper Methods – This database reference provide many functions that are used to check insert, update, count all, version of data. It is also used to making your queries easier. Here in this tutorial, we are going to explain how to use Query Helper Methods.

Codeigniter Query Helper Methods | Example.

Let us understand how generating query helper methods works in codeigniter with examples.

Functions:-

There are following functions available in query helper methods . Now we will explain one by one.

• 1. Information From Executing a Query.
• 2. Information About Your Database.
• 3. Making Your Queries Easier.

1. Information From Executing a Query.

Here is simple demo of information from executing a query.

Example:-

$this->db->insert_id()

The insert id number when performing database insert.

$this->db->affected_rows()

Display the number of affected row.

$this->db->last_query()

Display the last query that was run.

db->insert_id('blog');
echo "
";
echo $this->db->affected_rows();
echo "
";
echo $this->db->count_all('blog');
echo "
";
echo $this->db->last_query();
}
}
?>

2. Information About Your Database.

Here is simple demo of information about your database.

Example:-

$this->db->count_all()

this query gives permit you to determine the number of rows in a particular table.

$this->db->platform()

Display the output the database platform you are running like(MYSQL, MS SQL, Postgres ) etc.

$this->db->version()

Display the database version you are running.

db->count_all('blog');
echo "
";
echo $this->db->platform();
echo "
";
echo $this->db->version();
}
}
?>

3. Making Your Queries Easier.

Here is simple demo of making your queries easier.

Example:-

$this->db->insert_string()

This function reduce the process of writing database insert and return correctly formatted SQL insert string.

the first parameter is the table name and second one is an associative array with the data to be inserted.

$this->db->update_string()

This function reduce the process of writing database update and return correctly formatted SQL update string.

db->count_all('blog');
echo "
";
echo $this->db->platform();
echo "
";
echo $this->db->version();
}
}
?>

Codeigniter Generating Query Results – This database reference is used for generate the query results. Here in this tutorial, we are going to explain how to use generating query results.

Codeigniter Generating Query Results | Example.

Let us understand how generating query result works in codeigniter with examples.

Functions:-

There are following way to generate query results. Now we will explain one by one.

• 1. Result Arrays.
• 2. Result Rows.
• 3. Custom Result objects.
• 4. Result Helper Methods.

1. Result Arrays.

Here is simple demo of result arrays.

Example:-

db->query("select * from blog");
foreach ($query->result() as $row)
{
echo $row->title;
echo $row->name;
echo "
";
}
}
}
?>

Output will be like this:-

2. Result Rows.

Here is simple demo of result rows.

Example:-

db->query("select * from blog");
$row = $query->row();
if (isset($row))
{
echo $row->title;
echo $row->name;
echo "
";				
}
}
}
?>

Output will be like this:-

3. Custom Result objects.

Here is simple demo of custom result objects.

Example:-

Output will be like this:-

4. Result Helper Methods.

Here is simple demo of result helper methods.

Example:-

db->query('SELECT * FROM blog');
echo $query->num_rows();
echo "
";
//num field
echo $query->num_fields();
echo "
";
//free result
foreach ($query->result() as $row)
{
echo $row->title;
}
$query2 = $this->db->query('SELECT name FROM blog');
$query->free_result();
echo "
";
$row = $query2->row();
echo $row->name;
$query2->free_result();
}
}
?>

Output will be like this:-

Class reference:-

There are following references available in generating query results. Now we will explain.

1. Result.

This reference is used to wrapping for the result_array() method.

result([$type = 'object'])

• Parameters :
• $type (string) : Type of requested results – array, object, or class name.
• Returns : Array containing the fetched rows.
• Returns type : Array.

2. Result array.

This reference is used to return the query result as an array of row.

result_array()

• Parameters :
• Returns : Array containing the fetched rows.
• Returns type : Array.

3. Result Object.

This reference is used to return the query result as an array of row. Where each row is an object of type.

result_object()

• Parameters :
• Returns : Array containing the fetched rows.
• Returns type : Array.

4. Custom result object.

Here each row is instance of class.

custom_result_object($class_name)

• Parameters :
• $class_name (string) : Class name for the resulting rows.
• Returns : Array containing the fetched rows.
• Returns type : Array.

5. Row.

A wrapper of row_array() method.

row([$n = 0[$type = 'object']])

• Parameters :
• $n (int) : Index of the query results row to be returned.
• $type (string) : Type of the requested result – array, object, or class name.
• Returns : The requested row or NULL if it doesn’t exist.
• Returns type : mixed.

6. Unbuffered row.

This refernce is used to fetch the next row and return in the requested form.

unbuffered_row([$type = 'object'])

• Parameters :
• $type (string) : Type of the requested result – array, object, or class name.
• Returns : Next row from the result set or NULL if it doesn’t exist.
• Returns type : mixed.

7. Row array.

This refernce is used to returns the requested result row as an associative array.

row_array([$n = 0])

• Parameters :
• $n (int) : Index of the query results row to be returned.
• Returns : The requested row or NULL if it doesn’t exist.
• Returns type : Array.

8. Row object.

This refernce is used to returns the requested result row as an object of type stdclass.

row_object([$n = 0])

• Parameters :
• $n (int) : Index of the query results row to be returned.
• Returns : The requested row or NULL if it doesn’t exist.
• Returns type : stdClass.

9. Custom row object.

This refernce is used to returns instance of the requested class.

custom_row_object($n, $type)

• Parameters :
• $n (int) : Index of the results row to return.
• $class_name (string) : Class name for the resulting row.
• Returns : The requested row or NULL if it doesn’t exist.
• Returns type : $type.

10. Data seek.

This refernce is used to move to internal result row pointer to the desired offset.

data_seek([$n = 0])

• Parameters :
• $n (int) : Index of the results row to be returned next.
• Returns : TRUE on success, FALSE on failure.
• Returns type : Bool.

11. Set row.

This refernce is used to assisgn a value to a particular column.

set_row($key[$value = NULL])

• Parameters :
• $key (mixed) : Column name or array of key/value pairs.
• $value (mixed) : Value to assign to the column, $key is a single field name.
• Returns type : void.

12. Next row.

This refernce is used to assisgn a value to a particular column.

next_row([$type = 'object'])

• Parameters :
• $type (string) : Type of the requested result – array, object, or class name.
• Returns : Next row of result set, or NULL if it doesn’t exist.
• Returns type : Mixed.

13. Previous row.

This refernce return the previous row from the result set.

previous_row([$type = 'object'])

• Parameters :
• $type (string) : Type of the requested result – array, object, or class name.
• Returns : Previous row of result set, or NULL if it doesn’t exist.
• Returns type : Mixed.

14. First row.

This refernce return the first row from the result set.

previous_row([$type = 'object'])

• Parameters :
• $type (string) : Type of the requested result – array, object, or class name.
• Returns : First row of result set, or NULL if it doesn’t exist.
• Returns type : Mixed.

15. Last row.

This refernce return the last row from the result set.

last_row([$type = 'object'])

• Parameters :
• $type (string) : Type of the requested result – array, object, or class name.
• Returns : Last row of result set, or NULL if it doesn’t exist.
• Returns type : Mixed.

16. Num rows.

This refernce return the number of row from the result set.

num_rows()

• Parameters :
• Returns : Number of row in the result set.
• Returns type : Mixed.

17. Num field.

This refernce return the number of field from the result set.

num_fields()

• Parameters :
• Returns : Number of fields in the result set.
• Returns type : Mixed.

18. Field data.

This refernce is used to generate an array of stdclass object containing field meta-data.

field_data()

• Parameters :
• Returns : Array containing field meta-data.
• Returns type : Array .

19. Free result.

This refernce provide free a result set.

free_result()

• Parameters :
• Returns type : void.

20. List fields.

This refernce return an array containing the field name in the result set.

list_fields()

• Parameters :
• Returns : Array of column names.
• Returns type : void.

Codeigniter Running Queries reference – This database reference is used for submit a query and use the query function. We can also use this reference to identify regular, simplified and escaping query. Here in this tutorial, we are going to explain how to use running queries reference.

Codeigniter Running Queries Reference | Example.

Let us understand how running queries reference works in codeigniter with examples.

Functions:-

There are following functions available in running queries reference. Now we will explain one by one.

• 1. Regular Queries.
• 2. Simplified Queries.
• 3. Working with Database prefixes manually.
• 4. Protecting identifiers.
• 5. Escaping Queries.
• 6. Query Bindings.
• 7. Handling Errors.

1. Regular Queries.

Here is simple demo of regular queries.

db->query('select * from blog');
}
}
?>

2. Simplified Queries.

Here is simple demo of simplified queries.

db->simple_query('select * from blog'))
{
echo "Success!";
}
else
{
echo "Query failed!";
}
}
}
?>

The simple_query method is a simplified version of the $this->db->query() method.

Output will be like this:-

3. Working with Database prefixes manually.

Here is simple demo of working with database prefixes manually.

db->set_dbprefix('Database table name is ');
echo $this->db->dbprefix('blog'); 
}
}
?>

Output will be like this:-

4. Protecting identifiers.

Here is simple demo of protecting identifiers.

db->protect_identifiers('blog');
}
}
?>

This function is used to protect table and field name in database.

Output will be like this:-

5. Escaping Queries.

Here is simple demo of escaping queries.

db->escape_str($title)."')";
}
}
?>

6. Query Bindings.

Here is simple demo of query bindings.

db->query($sql, array(3, 'Job', 'Ram'));	
print_r($result);
}
}
?>

The question marks in the query are automatically replaced with the value in the array in second paerameter in the query function.

7. Handling Errors.

Here is simple demo of handling errors.

db->simple_query('SELECT `title` FROM `blog`'))
{
$error = $this->db->error();
echo $error;				
}
else
{
echo "success";
}
}
}
?>

Output will be like this:-