April 6, 2017

Codeigniter Query Builder Class

❮❮ Previous
Next ❯❯

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.

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:-

Codeigniter Query Builder Class

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:-

Codeigniter Query Builder Class

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:-

Codeigniter Query Builder Class

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:-

Codeigniter Query Builder Class

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:-

Codeigniter Query Builder Class

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:-

Codeigniter Query Builder Class

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:-

Codeigniter Query Builder Class

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;
}
}
?>
Codeigniter Query Builder Class

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.

Advertisements

Add Comment

📖 Read More


❮❮ Previous
Next ❯❯