Tutorialsplane

Codeigniter Query Builder Class

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.

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.

Syntax of selecting data.

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class query_builder_controller extends CI_Controller 
{
public function select_data()
{
$query = $this-?>db->get('blog');
foreach ($query->result() as $row)
{
echo $row->title;
}
echo "<br/>";
echo "<br/>";
$this->db->select_min('title');
$query = $this->db->get('blog');
foreach ($query->result() as $row)
{
echo $row->title;
}
echo "<br/>";
$this->db->select_min('id');
$query = $this->db->get_compiled_select('blog');
echo $this->db->last_query();
echo "<br/>";
$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 "<br/>";
}		
}
}
?>

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”.

Syntax of specific data.

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class query_builder_controller extends CI_Controller 
{
public function similar_data()
{
$this-?>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 "<br/>";				
}
echo $this->db->last_query();
echo "<br/>";
$this->db->distinct();		
$query = $this->db->get('blog');
echo $this->db->last_query();
echo "<br/>";
$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.

Syntax of ordering results.

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class query_builder_controller extends CI_Controller 
{
public function OrderBy()
{
echo "DESC ORDER";		
$this-?>db->order_by('title', 'DESC');
$query = $this->db->get('blog');
foreach ($query->result() as $row)
{				
echo $row->title;
echo "<br/>";				
}
echo "<br/>";
echo "ASC ORDER";
$this->db->order_by('name', 'ASC');
$query = $this->db->get('blog');
foreach ($query->result() as $row)
{								
echo $row->name;
echo "<br/>";				
}
echo "<br/>";
echo "RANDOM ORDER";
$this->db->order_by('name', 'RANDOM');
$query = $this->db->get('blog');
foreach ($query->result() as $row)
{								
echo $row->name;
echo "<br/>";				
}
}
}
?>

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.

Syntax of limiting or Counting Results.

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class query_builder_controller extends CI_Controller 
{
public function limitcounting()
{
echo $this-?>db->count_all_results('blog');
echo "<br/>";		
$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.

Syntax of Query grouping.

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class query_builder_controller extends CI_Controller 
{
public function querygrouping()
{
$this-?>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’.

Syntax of inserting data.

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class query_builder_controller extends CI_Controller 
{
public function insert()
{
$data = array
(
'id' =?> '7',
'title' => 'Job',
'name' => 'Ram'
);
$this->db->insert('blog', $data);
echo "<br/>";
$insert = array(
'id' => '8',
'title'  => 'Game',
'name'  => 'Shyam'
);
$sql = $this->db->set($insert)->get_compiled_insert('blog');
echo $sql;		
echo "<br/>";
$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.

Syntax of updating data.

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class query_builder_controller extends CI_Controller 
{
public function update()
{
//Replace Function
$data = array(
'id' =?> '5',
'title'  => 'updateTitle',
'name'  => 'UpdateName');
$this->db->replace('blog', $data);
echo "<br/>";

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

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

//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.

Syntax of deleting data.

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class query_builder_controller extends CI_Controller 
{
public function deleteTable()
{
$this-?>db->delete('abc', array('id' => '1'));
echo "<br/>";
		
$tables = array('abc', 'abcd');
$this->db->where('id', '2');
$this->db->delete($tables);
echo "<br/>";
		
$this->db->empty_table('abc');
echo "<br/>";
		
$this->db->truncate('abc');
}
}
?>

9. Method chaining.

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

Example:-

Syntax of method chaining.

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class query_builder_controller extends CI_Controller 
{
public function chaining()
{
$query = $this-?>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.

Syntax of Query Builder Caching.

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class query_builder_controller extends CI_Controller 
{
public function querycaching()
{
$this-?>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.

Syntax of resetting query builder.

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class query_builder_controller extends CI_Controller 
{
public function querybuilder()
{
$sql = $this-?>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()

2. Start cache.

This reference is used to start the query builder cache.

start_cache()

3. Stop cache.

This reference is used to stop the query builder cache.

stop_cache()

4. Flush cache.

This reference is used to empties the query builder cache.

flush_cache()

5. Set dbprefix.

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

set_dbprefix([$prefix = ''])

6. Dbprefix.

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

dbprefix([$table = ''])

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]])

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]]])

9. Get where.

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

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

10. Select.

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

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

11. Select avg.

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

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

12. Select max.

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

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

13. Select min.

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

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

14. Select sum.

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

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

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])

16. From.

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

from($from)

17. Join.

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

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

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]])

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]])

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]]])

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]]])

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]]])

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]]])

24. Group start.

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

group_start()

25. Or group start.

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

or_group_start()

26. Not group start.

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

not_group_start()

27. or not group start.

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

or_not_group_start()

28. Group end.

It is used to end a group expression.

group_end()

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]]])

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]]])

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]]])

32. Having.

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

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

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]])

34. Group by.

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

group_by($by[$escape = NULL])

35. Order by.

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

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

36. Limit.

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

limit($value[$offset = 0])

37. Offset.

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

offset($offset)

38. Set.

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

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

39. Insert.

It is used to Compiles and executes an INSERT statement.

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

40. Insert batch.

It is used to Compiles and executes an INSERT statement.

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

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]])

42. Update.

It is used to Compiles and executes an UPDATE statement.

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

43. Update batch.

It is used to Compiles and executes batch UPDATE statements.

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

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]])

45. Replace.

It is used to compiles and executes a REPLACE statement.

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

46. Delete.

It is used to compiles and executes a DELETE query.

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

47. Truncate.

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

truncate([$table = ''])

48. Empty table.

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

empty_table([$table = ''])

49. Get compile select.

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

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

50. Get compile insert.

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

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

51. Get compile update.

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

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

52. Get compile delete.

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

get_compiled_delete([$table = ''[$reset = TRUE]])
Codeigniter Views Subfolders
Codeigniter Pass Data To View
Codeigniter Return View as Data String

Models

Codeigniter Models Intro
Codeigniter Load Model
Codeigniter controllers subfolders
Codeigniter Autoload Models

Connect Database

Codeigniter Connect Database

Helpers

Codeigniter Helpers Example
Codeigniter Load Helper

Libraries

Codeigniter libraries

Helper Reference

Array Helper
CAPTCHA Helper
Cookie Helper
Date Helper
Directory Helper
Codeigniter Download Helper
Codeigniter Email Helper
Codeigniter File Helper
Codeigniter Form Helper
Codeigniter Html Helper
Codeigniter Inflector Helper
Codeigniter Number Helper
Codeigniter Language Helper
Codeigniter Path Helper
Codeigniter Security Helper
Codeigniter Smiley Helper
Codeigniter String Helper
Codeigniter Text Helper
Codeigniter Typography Helper
Codeigniter XML Helper
Codeigniter URL Helper

Library Reference

Codeigniter Benchmarking Class Library
Codeigniter Caching Driver Library
Codeigniter Calendaring Class Library
Codeigniter Shopping Cart Class
Codeigniter Config Class Library
Codeigniter Email Class Library
Codeigniter Encrypt Class Library
Codeigniter File Uploading Class Library
Codeigniter Form Validation Library
Codeigniter FTP Class Library
Codeigniter Image Manipulation Class Library
Codeigniter Input Class Library
Codeigniter Language Class Library
Codeigniter Loader Class Library
Codeigniter Migrations Class Library
Codeigniter Output Class Library
Codeigniter Pagination Class Library
Codeigniter Template Parser Class Library
Codeigniter Security Class Library
Codeigniter Session Class Library
Codeigniter HTML Table Class Library
Codeigniter Trackback Class Library
Codeigniter Typography Class Library
Codeigniter URI Class Library
Codeigniter User Agent Class Library
Codeigniter Zip Encoding Class Library

Database Reference

Codeigniter Database Quick Start: Example Code
Codeigniter Database Configuration
Codeigniter Connect Your Database
Codeigniter Running Queries Reference
Codeigniter Generating Query Results
Codeigniter Query Helper Methods
Codeigniter Transactions Reference
Codeigniter Database Metadata Reference
Codeigniter Custom Function Calls
Codeigniter Database Caching Class
Codeigniter DB Driver Reference
Codeigniter Query Builder Class