[TOC]
## **1. Query Builder Class (class `CI_DB_query_builder`)**
class `CI_DB_query_builder`
[【官方文档】](https://www.codeigniter.com/user_guide/database/query_builder.html)
Beyond simplicity, a major benefit to using the Query Builder features is that it allows you to create database independent applications, since the query syntax is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system.
### Selecting Data (build SQL SELECT statements.)
#### **`get()`**
~~~php
$query = $this->db->get('mytable', 10, 20);
// Executes: SELECT * FROM mytable LIMIT 20, 10
// (in MySQL. Other databases have slightly different syntax)
foreach ($query->result() as $row)
{
echo $row->title;
}
//output every 'title' value
~~~
#### **`get_compiled_select()`**
~~~php
echo $this->db->limit(10,20)->get_compiled_select('mytable', FALSE);
// Prints string: SELECT * FROM mytable LIMIT 20, 10
// (in MySQL. Other databases have slightly different syntax)
echo $this->db->select('title, content, date')->get_compiled_select();
// Prints string: SELECT title, content, date FROM mytable LIMIT 20, 10
~~~
This method does not run the query, simply returns the SQL query as a string.The key thing to notice in the above example is that the second query did not utilize `$this->db->from()` and did not pass a table name into the first parameter. The reason for this outcome is because the query has not been executed using `$this->db->get()` which resets values or reset directly using `$this->db->reset_query()`.
#### **`get_where()`**
~~~php
$query = $this->db->get_where('mytable', array('id' => $id), $limit, $offset);
~~~
#### **`select()`**
~~~php
$this->db->select('title, content, date');
$query = $this->db->get('mytable');
// Executes: SELECT title, content, date FROM mytable
~~~
`$this->db->select()` accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names. This is useful if you need a compound select statement where automatic escaping of fields may break them.
~~~php
$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4) AS amount_paid', FALSE);
$query = $this->db->get('mytable');
~~~
#### **`select_max()`**
~~~php
$this->db->select_max('age');
$query = $this->db->get('members');
// Produces: SELECT MAX(age) as age FROM members
$this->db->select_max('age', 'member_age');
$query = $this->db->get('members');
// Produces: SELECT MAX(age) as member_age FROM members
~~~
#### **`select_min()`**
~~~php
$this->db->select_min('age');
$query = $this->db->get('members');
// Produces: SELECT MIN(age) as age FROM members
~~~
#### **`select_avg()`**
~~~php
$this->db->select_avg('age');
$query = $this->db->get('members');
// Produces: SELECT AVG(age) as age FROM members
~~~
#### **`select_sum()`**
~~~php
$this->db->select_sum('age');
$query = $this->db->get('members');
// Produces: SELECT SUM(age) as age FROM members
~~~
#### **`from()`**
~~~php
$this->db->select('title, content, date');
$this->db->from('mytable');
$query = $this->db->get();
// Produces: SELECT title, content, date FROM mytable
~~~
#### **`join()`**
~~~php
$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');
$query = $this->db->get();
// Produces:
// SELECT * FROM blogs JOIN comments ON comments.id = blogs.id
~~~
### Looking for Specific Data
#### **`where()`**
1. Simple key/value method:
~~~php
$this->db->where('name', $name);
$this->db->where('title', $title);
$this->db->where('status', $status);
// WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
~~~
2. Custom key/value method:
~~~php
$this->db->where('name !=', $name);
$this->db->where('id <', $id); // Produces: WHERE name != 'Joe' AND id < 45
~~~
3. Associative array method:
~~~php
$array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);
$this->db->where($array);
~~~
4. Custom string:
~~~php
$where = "name='Joe' AND status='boss' OR status='active'";
$this->db->where($where);
~~~
>[info] `$this->db->where()` accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names.
~~~php
$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);
~~~
#### **`or_where()`**
~~~php
$this->db->where('name !=', $name);
$this->db->or_where('id >', $id);
// Produces: WHERE name != 'Joe' OR id > 50
~~~
#### **`where_in()`**
~~~php
$names = array('Frank', 'Todd', 'James');
$this->db->where_in('username', $names);
// Produces: WHERE username IN ('Frank', 'Todd', 'James')
~~~
#### **`or_where_in()`**
~~~php
$names = array('Frank', 'Todd', 'James');
$this->db->or_where_in('username', $names);
// Produces: OR username IN ('Frank', 'Todd', 'James')
~~~
#### **`where_not_in()`**
~~~php
$names = array('Frank', 'Todd', 'James');
$this->db->where_not_in('username', $names);
// Produces: WHERE username NOT IN ('Frank', 'Todd', 'James')
~~~
#### **`or_where_not_in()`**
~~~php
$names = array('Frank', 'Todd', 'James');
$this->db->or_where_not_in('username', $names);
// Produces: OR username NOT IN ('Frank', 'Todd', 'James')
~~~
### Looking for Similar Data
#### **`like()`**
1. Simple key/value method:
~~~php
$this->db->like('title', 'match');
$this->db->like('body', 'match');
// WHERE `title` LIKE '%match%' ESCAPE '!' AND `body` LIKE '%match% ESCAPE '!'
this->db->like('title', 'match', 'before'); // Produces: WHERE `title` LIKE '%match' ESCAPE '!'
$this->db->like('title', 'match', 'after'); // Produces: WHERE `title` LIKE 'match%' ESCAPE '!'
$this->db->like('title', 'match', 'both'); // Produces: WHERE `title` LIKE '%match%' ESCAPE '!'
~~~
2. Associative array method:
~~~php
$array = array('title' => $match, 'page1' => $match, 'page2' => $match);
$this->db->like($array);
// WHERE `title` LIKE '%match%' ESCAPE '!' AND `page1` LIKE '%match%' ESCAPE '!' AND `page2` LIKE '%match%' ESCAPE '!'
~~~
#### **`or_like()`**
~~~php
$this->db->like('title', 'match');
$this->db->or_like('body', $match);
// WHERE `title` LIKE '%match%' ESCAPE '!' OR `body` LIKE '%match%' ESCAPE '!'
~~~
#### **`not_like()`**
~~~php
$this->db->not_like('title', 'match');
// WHERE `title` NOT LIKE '%match% ESCAPE '!'
~~~
#### **`or_not_like()`**
~~~php
$this->db->like('title', 'match');
$this->db->or_not_like('body', 'match');
// WHERE `title` LIKE '%match% OR `body` NOT LIKE '%match%' ESCAPE '!'
~~~
#### **`group_by()`**
~~~php
$this->db->group_by("title");
// Produces: GROUP BY title
~~~
#### **`distinct()`**
~~~php
$this->db->distinct();
$this->db->get('table'); // Produces: SELECT DISTINCT * FROM table
~~~
#### **`having()`**
~~~php
$this->db->having('user_id = 45'); // Produces: HAVING user_id = 45
$this->db->having('user_id', 45); // Produces: HAVING user_id = 45
$this->db->having(array('title =' => 'My Title', 'id <' => $id));
// Produces: HAVING title = 'My Title', id < 45
~~~
~~~php
$this->db->having('user_id', 45); // Produces: HAVING `user_id` = 45 in some databases such as MySQL
$this->db->having('user_id', 45, FALSE); // Produces: HAVING user_id = 45
~~~
#### **`or_having()`**
`$this->db->or_having()` : Identical to having(), only separates multiple clauses with “OR”.
### Ordering results
#### **`order_by()`**
The second parameter lets you set the direction of the result. Options are ASC, DESC AND RANDOM.
~~~php
$this->db->order_by('title', 'DESC');
// Produces: ORDER BY `title` DESC
$this->db->order_by('title DESC, name ASC');
// Produces: ORDER BY `title` DESC, `name` ASC
$this->db->order_by('title', 'DESC');
$this->db->order_by('name', 'ASC');
// Produces: ORDER BY `title` DESC, `name` ASC
$this->db->order_by('title', 'RANDOM');
// Produces: ORDER BY RAND()
$this->db->order_by(42, 'RANDOM');
// Produces: ORDER BY RAND(42)
~~~
>[info] Random ordering is not currently supported in Oracle and will default to ASC instead.
### Limiting Results
#### **`limit()`**
~~~php
$this->db->limit(10); // Produces: LIMIT 10
$this->db->limit(10, 20); // Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)
~~~
### Counting Results
#### **`count_all()`**
Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter. Example:
~~~php
echo $this->db->count_all('my_table'); // Produces an integer, like 25
~~~
Permits you to determine the number of rows in a particular Active Record query. Queries will accept Query Builder restrictors such as `where()`, `or_where()`, `like()`, `or_like()`,etc.
#### **`count_all_results()`**
~~~php
echo $this->db->count_all_results('my_table'); // Produces an integer, like 25
$this->db->like('title', 'match');
$this->db->from('my_table');
echo $this->db->count_all_results(); // Produces an integer, like 17
~~~
However, this method also resets any field values that you may have passed to select(). If you need to keep them, you can pass FALSE as the second parameter:
~~~php
echo $this->db->count_all_results('my_table', FALSE);
~~~
### Query grouping
This will allow you to create queries with complex WHERE clauses. Nested groups are supported. Example:
~~~php
$this->db->select('*')->from('my_table')
->group_start()
->where('a', 'a')
->or_group_start()
->where('b', 'b')
->where('c', 'c')
->group_end()
->group_end()
->where('d', 'd')
->get();
// Generates:
// SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd'
~~~
>[info]groups need to be balanced, make sure every `group_start()` is matched by a `group_end()`.
#### **`group_start()`**
`$this->db->group_start()`,Starts a new group by adding an opening parenthesis to the WHERE clause of the query.
#### **`or_group_start()`**
`$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’.
#### **`not_group_start()`**
`$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’.
#### **`or_not_group_start()`**
`$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’.
#### **`group_end()`**
`$this->db->group_end()`,Ends the current group by adding an closing parenthesis to the WHERE clause of the query.
### Inserting Data
#### **`insert()`**
using an array:
~~~php
$data = array(
'title' => 'My title',
'name' => 'My Name',
'date' => 'My date'
);
$this->db->insert('mytable', $data);
// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')
~~~
using an object:
~~~php
/*
class Myclass {
public $title = 'My Title';
public $content = 'My Content';
public $date = 'My Date';
}
*/
$object = new Myclass;
$this->db->insert('mytable', $object);
// Produces: INSERT INTO mytable (title, content, date) VALUES ('My Title', 'My Content', 'My Date')
~~~
#### **`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.
~~~php
$data = array(
'title' => 'My title',
'name' => 'My Name',
'date' => 'My date'
);
$sql = $this->db->set($data)->get_compiled_insert('mytable');
echo $sql;
// Produces string: INSERT INTO mytable (`title`, `name`, `date`) VALUES ('My title', 'My name', 'My date')
~~~
The second parameter enables you to set whether or not the query builder query will be reset (by default it will be–just like $this->db->insert()):
~~~php
echo $this->db->set('title', 'My Title')->get_compiled_insert('mytable', FALSE);
// Produces string: INSERT INTO mytable (`title`) VALUES ('My Title')
echo $this->db->set('content', 'My Content')->get_compiled_insert();
// Produces string: INSERT INTO mytable (`title`, `content`) VALUES ('My Title', 'My Content')
~~~
The key thing to notice in the above example is that the second query did not utilize $this->db->from() nor did it pass a table name into the first parameter. The reason this worked is because the query has not been executed using $this->db->insert() which resets values or reset directly using $this->db->reset_query().
>[info] This method doesn’t work for batched inserts.
#### **`insert_batch()`**
Generates an insert string based on the data you supply, and runs the query. You can either pass an array or an object to the function.
using an array:
~~~php
$data = array(
array(
'title' => 'My title',
'name' => 'My Name',
'date' => 'My date'
),
array(
'title' => 'Another title',
'name' => 'Another Name',
'date' => 'Another date'
)
);
$this->db->insert_batch('mytable', $data);
// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date')
~~~
>[info] All values are escaped automatically producing safer queries.
### Updating Data
#### **`replace()`**
This method executes a REPLACE statement, which is basically the SQL standard for (optional) DELETE + INSERT, using PRIMARY and UNIQUE keys as the determining factor. In our case, it will save you from the need to implement complex logics with different combinations of `select()`, `update()`, `delete()` and `insert()` calls.
~~~php
$data = array(
'title' => 'My title',
'name' => 'My Name',
'date' => 'My date'
);
$this->db->replace('table', $data);
// Executes: REPLACE INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')
~~~
In the above example, if we assume that the title field is our primary key, then if a row containing ‘My title’ as the title value, that row will be deleted with our new row data replacing it.
#### **`set()`**
This function enables you to set values for inserts or updates.
It can be used instead of passing a data array directly to the insert or update functions:
~~~php
$this->db->set('name', $name);
$this->db->insert('mytable'); // Produces: INSERT INTO mytable (`name`) VALUES ('{$name}')
$this->db->set('name', $name);
$this->db->set('title', $title);
$this->db->set('status', $status);
$this->db->insert('mytable');
~~~
`set()` will also accept an optional third parameter ($escape), that will prevent data from being escaped if set to FALSE. To illustrate the difference, here is set() used both with and without the escape parameter.
~~~php
$this->db->set('field', 'field+1', FALSE);
$this->db->where('id', 2);
$this->db->update('mytable'); // gives UPDATE mytable SET field = field+1 WHERE id = 2
$this->db->set('field', 'field+1');
$this->db->where('id', 2);
$this->db->update('mytable'); // gives UPDATE `mytable` SET `field` = 'field+1' WHERE `id` = 2
~~~
using an array:
~~~php
$array = array(
'name' => $name,
'title' => $title,
'status' => $status
);
$this->db->set($array);
$this->db->insert('mytable');
~~~
using an object:
~~~php
/*
class Myclass {
public $title = 'My Title';
public $content = 'My Content';
public $date = 'My Date';
}
*/
$object = new Myclass;
$this->db->set($object);
$this->db->insert('mytable');
~~~
#### **`update()`**
using an array:
~~~php
$data = array(
'title' => $title,
'name' => $name,
'date' => $date
);
$this->db->where('id', $id);
$this->db->update('mytable', $data);
// Produces:
//
// UPDATE mytable
// SET title = '{$title}', name = '{$name}', date = '{$date}'
// WHERE id = $id
~~~
using an object:
~~~php
/*
class Myclass {
public $title = 'My Title';
public $content = 'My Content';
public $date = 'My Date';
}
*/
$object = new Myclass;
$this->db->where('id', $id);
$this->db->update('mytable', $object);
// Produces:
//
// UPDATE `mytable`
// SET `title` = '{$title}', `name` = '{$name}', `date` = '{$date}'
// WHERE id = `$id`
~~~
You can optionally pass this information directly into the update function as a string:
~~~php
$this->db->update('mytable', $data, "id = 4");
~~~
Or as an array:
~~~php
$this->db->update('mytable', $data, array('id' => $id));
~~~
You may also use the $this->db->set() function described above when performing updates.
#### **`update_batch()`**
Generates an update string based on the data you supply, and runs the query. You can either pass an array or an object to the function.
~~~php
$data = array(
array(
'title' => 'My title' ,
'name' => 'My Name 2' ,
'date' => 'My date 2'
),
array(
'title' => 'Another title' ,
'name' => 'Another Name 2' ,
'date' => 'Another date 2'
)
);
$this->db->update_batch('mytable', $data, 'title');
// Produces:
// UPDATE `mytable` SET `name` = CASE
// WHEN `title` = 'My title' THEN 'My Name 2'
// WHEN `title` = 'Another title' THEN 'Another Name 2'
// ELSE `name` END,
// `date` = CASE
// WHEN `title` = 'My title' THEN 'My date 2'
// WHEN `title` = 'Another title' THEN 'Another date 2'
// ELSE `date` END
// WHERE `title` IN ('My title','Another title')
~~~
The first parameter will contain the table name, the second is an associative array of values, the third parameter is the where key.
>[info] `update_batch()` returns the number of rows affected.
#### **`get_compiled_update()`**
This works exactly the same way as $this->db->get_compiled_insert() except that it produces an UPDATE SQL string instead of an INSERT SQL string.
>[info] This method doesn’t work for batched updates.
### Deleting Data
#### **`delete()`**
Generates a delete SQL string and runs the query.
~~~php
$this->db->delete('mytable', array('id' => $id));
// Produces: // DELETE FROM mytable // WHERE id = $id
~~~
The first parameter is the table name, the second is the where clause.
You can also use the where() or or_where() functions instead of passing the data to the second parameter of the function:
~~~php
$this->db->where('id', $id);
$this->db->delete('mytable');
// Produces:
// DELETE FROM mytable
// WHERE id = $id
~~~
An array of table names can be passed into delete() if you would like to delete data from more than 1 table.
~~~php
$tables = array('table1', 'table2', 'table3');
$this->db->where('id', '5');
$this->db->delete($tables);
~~~
#### **`empty_table()`**
If you want to delete all data from a table, you can use the truncate() function, or empty_table().
~~~php
$this->db->empty_table('mytable'); // Produces: DELETE FROM mytable
~~~
#### **`truncate()`**
If the TRUNCATE command isn’t available, truncate() will execute as “DELETE FROM table”.
~~~php
$this->db->from('mytable');
$this->db->truncate();
// or
$this->db->truncate('mytable');
// Produce:
// TRUNCATE mytable
~~~
#### **`get_compiled_delete()`**
This works exactly the same way as `$this->db->get_compiled_insert()` except that it produces a DELETE SQL string instead of an INSERT SQL string.
### Method Chaining
Method chaining allows you to simplify your syntax by connecting multiple functions.
~~~php
$query = $this->db->select('title')
->where('id', $id)
->limit(10, 20)
->get('mytable');
~~~
### Query Builder Caching
#### **`$this->db->start_cache()`**
This function must be called to begin caching. All Query Builder queries of the correct type (see below for supported queries) are stored for later use.
#### **`$this->db->stop_cache()`**
This function can be called to stop caching.
#### **`$this->db->flush_cache()`**
This function deletes all items from the Query Builder cache.
~~~php
$this->db->start_cache();
$this->db->select('field1');
$this->db->stop_cache();
$this->db->get('tablename');
//Generates: SELECT `field1` FROM (`tablename`)
$this->db->select('field2');
$this->db->get('tablename');
//Generates: SELECT `field1`, `field2` FROM (`tablename`)
$this->db->flush_cache();
$this->db->select('field2');
$this->db->get('tablename');
//Generates: SELECT `field2` FROM (`tablename`)
~~~
>[info] The following statements can be cached: `select`, `from`, `join`, `where`, `like`, `group_by`, `having`, `order_by`
### Resetting Query Builder
#### **`reset_query()`**
Resetting Query Builder allows you to start fresh with your query without executing it first using a method like $this->db->get() or $this->db->insert(). Just like the methods that execute a query, this will not reset items you’ve cached using Query Builder Caching.
This is useful in situations where you are using Query Builder to generate SQL (ex. $this->db->get_compiled_select()) but then choose to, for instance, run the query:
~~~php
// Note that the second parameter of the get_compiled_select method is FALSE
$sql = $this->db->select(array('field1','field2'))
->where('field3',5)
->get_compiled_select('mytable', FALSE);
// ...
// Do something crazy with the SQL code... like add it to a cron script for
// later execution or something...
// ...
$data = $this->db->get()->result_array();
// Would execute and return an array of results of the following query:
// SELECT field1, field1 from mytable where field3 = 5;
~~~
>[info] Double calls to `get_compiled_select()` while you’re using the Query Builder Caching functionality and NOT resetting your queries will results in the cache being merged twice. That in turn will i.e. if you’re caching a `select()` - select the same field twice.
## **2. Generating Query Results(class `CI_DB_result`)**
class `CI_DB_result`
[【官方文档】](https://www.codeigniter.com/user_guide/database/results.html)
### Result Arrays
#### **`result()`**
This method returns the query result as an array of objects, or an empty array on failure.
~~~php
$query = $this->db->query("YOUR QUERY");
foreach ($query->result() as $row)
{
echo $row->title;
echo $row->name;
echo $row->body;
}
~~~
You can also pass a string to result() which represents a class to instantiate for each result object (note: this class must be loaded)
~~~php
$query = $this->db->query("SELECT * FROM users;");
foreach ($query->result('User') as $user)
{
echo $user->name; // access attributes
echo $user->reverse_name(); // or methods defined on the 'User' class
}
~~~
#### **`result_array()`**
This method returns the query result as a pure array, or an empty array when no result is produced.
~~~php
$query = $this->db->query("YOUR QUERY");
foreach ($query->result_array() as $row)
{
echo $row['title'];
echo $row['name'];
echo $row['body'];
}
~~~
### Result Rows
#### **`row()`**
This method returns a single result row. If your query has more than one row, it returns only the first row.
~~~php
$query = $this->db->query("YOUR QUERY");
$row = $query->row();
if (isset($row))
{
echo $row->title;
echo $row->name;
echo $row->body;
}
~~~
If you want a specific row returned you can submit the row number as a digit in the first parameter:
~~~php
$row = $query->row(5);
~~~
You can also add a second String parameter, which is the name of a class to instantiate the row with:
~~~php
$query = $this->db->query("SELECT * FROM users LIMIT 1;");
$row = $query->row(0, 'User');
echo $row->name; // access attributes
echo $row->reverse_name(); // or methods defined on the 'User' class
~~~
#### **`row_array()`**
It returns an array.
~~~php
$query = $this->db->query("YOUR QUERY");
$row = $query->row_array();
if (isset($row))
{
echo $row['title'];
echo $row['name'];
echo $row['body'];
}
~~~
If you want a specific row returned you can submit the row number as a digit in the first parameter:
~~~php
$row = $query->row_array(5);
~~~
you can walk forward/backwards/first/last through your results using these variations:
~~~php
$row = $query->first_row();
$row = $query->last_row();
$row = $query->next_row();
$row = $query->previous_row();
~~~
~~~php
By default they return an object unless you put the word “array” in the parameter:
$row = $query->first_row(‘array’);
$row = $query->last_row(‘array’);
$row = $query->next_row(‘array’);
$row = $query->previous_row(‘array’);
~~~
>[info] All the methods above will load the whole result into memory (prefetching). Use unbuffered_row() for processing large result sets.
#### **`unbuffered_row()`**
This method returns a single result row without prefetching the whole result in memory as row() does. If your query has more than one row, it returns the current row and moves the internal data pointer ahead.
~~~php
$query = $this->db->query("YOUR QUERY");
while ($row = $query->unbuffered_row())
{
echo $row->title;
echo $row->name;
echo $row->body;
}
~~~
You can optionally pass ‘object’ (default) or ‘array’ in order to specify the returned value’s type:
~~~php
$query->unbuffered_row(); // object
$query->unbuffered_row('object'); // object
$query->unbuffered_row('array'); // associative array
~~~
### Custom Result Objects
You can have the results returned as an instance of a custom class instead of a stdClass or array, as the result() and result_array() methods allow. This requires that the class is already loaded into memory. The object will have all values returned from the database set as properties. If these have been declared and are non-public then you should provide a __set() method to allow them to be set.
#### **``**
~~~php
~~~
### Result Helper Methods
#### **`num_rows()`**
The number of rows returned by the query.
~~~php
$query = $this->db->query('SELECT * FROM my_table');
echo $query->num_rows();
~~~
>[info] Not all database drivers have a native way of getting the total number of rows for a result set. When this is the case, all of the data is prefetched and count() is manually called on the resulting array in order to achieve the same result.
#### **`num_fields()`**
The number of FIELDS (columns) returned by the query. Make sure to call the method using your query result object:
~~~php
$query = $this->db->query('SELECT * FROM my_table');
echo $query->num_fields();
~~~
#### **`free_result()`**
It frees the memory associated with the result and deletes the result resource ID. Normally PHP frees its memory automatically at the end of script execution. However, if you are running a lot of queries in a particular script you might want to free the result after each query result has been generated in order to cut down on memory consumption.
~~~php
$query = $this->db->query('SELECT title FROM my_table');
foreach ($query->result() as $row)
{
echo $row->title;
}
$query->free_result(); // The $query result object will no longer be available
$query2 = $this->db->query('SELECT name FROM some_table');
$row = $query2->row();
echo $row->name;
$query2->free_result(); // The $query2 result object will no longer be available
~~~
#### **`data_seek()`**
This method sets the internal pointer for the next result row to be fetched. It is only useful in combination with `unbuffered_row()`.
It accepts a positive integer value, which defaults to 0 and returns TRUE on success or FALSE on failure.
~~~php
$query = $this->db->query('SELECT `field_name` FROM `table_name`');
$query->data_seek(5); // Skip the first 5 rows
$row = $query->unbuffered_row();
~~~
>[info] Not all database drivers support this feature and will return FALSE. Most notably - you won’t be able to use it with PDO.
## **3. Query Helper Methods**
[【官方文档】](https://www.codeigniter.com/user_guide/database/helpers.html)
### Information From Executing a Query
#### **`insert_id()`**
~~~php
$this->db->insert_id();
~~~
The insert ID number when performing database inserts.
>[info] If using the PDO driver with PostgreSQL, or using the Interbase driver, this function requires a $name parameter, which specifies the appropriate sequence to check for the insert id.
#### **`affected_rows()`**
~~~php
$this->db->affected_rows();
~~~
Displays the number of affected rows, when doing “write” type queries (insert, update, etc.).
>[info] In MySQL “DELETE FROM TABLE” returns 0 affected rows. The database class has a small hack that allows it to return the correct number of affected rows. By default this hack is enabled but it can be turned off in the database driver file.
#### **`last_query()`**
Returns the last query that was run (the query string, not the result).
~~~php
$str = $this->db->last_query();
// Produces: SELECT * FROM sometable....
~~~
>[info] Disabling the save_queries setting in your database configuration will render this function useless..
### Information About Your Database
#### **`count_all()`**
Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter.
~~~php
echo $this->db->count_all('my_table');
// Produces an integer, like 25
~~~
#### **`platform()`**
Outputs the database platform you are running (MySQL, MS SQL, Postgres, etc…):
~~~php
echo $this->db->platform();
~~~
#### **`version()`**
Outputs the database version you are running:
~~~php
echo $this->db->version();
~~~
### Making Your Queries Easier
#### **`insert_string()`**
This function simplifies the process of writing database inserts. It returns a correctly formatted SQL insert string.
~~~php
$data = array('name' => $name, 'email' => $email, 'url' => $url);
$str = $this->db->insert_string('table_name', $data);
~~~
The first parameter is the table name, the second is an associative array with the data to be inserted. The above example produces:
~~~
INSERT INTO table_name (name, email, url) VALUES ('Rick', 'rick@example.com', 'example.com')
~~~
>[info] Values are automatically escaped, producing safer queries.
#### **`update_string()`**
This function simplifies the process of writing database updates. It returns a correctly formatted SQL update string.
~~~php
$data = array('name' => $name, 'email' => $email, 'url' => $url);
$where = "author_id = 1 AND status = 'active'";
$str = $this->db->update_string('table_name', $data, $where);
~~~
The first parameter is the table name, the second is an associative array with the data to be updated, and the third parameter is the “where” clause. The above example produces:
~~~
UPDATE table_name SET name = 'Rick', email = 'rick@example.com', url = 'example.com' WHERE author_id = 1 AND status = 'active'
~~~
>[info] Values are automatically escaped, producing safer queries.
## **4. DB Driver Reference (class `CI_DB_driver`)**
[【官方文档】](https://www.codeigniter.com/user_guide/database/db_driver_reference.html)
#### **list_fields($table)**
Parameters: $table (string) – The table name
Returns: Array of field names or FALSE on failure
Return type: array
Gets a list of the field names in a table.
- WebAPP
- Linux Command
- 入门
- 处理文件
- 查找文件单词
- 环境
- 联网
- Linux
- Linux目录配置标准：FHS
- Linux文件与目录管理
- Linux账号管理与ACL权限设置
- Linux系统资源查看
- 软件包管理
- Bash
- Daemon/Systemd
- ftp
- Apache
- MySQL
- Command
- Replication
- mysqld
- remote access
- remark
- 限制
- PHP
- String
- Array
- Function
- Class
- File
- JAVA
- Protocals
- http
- mqtt
- IDE
- phpDesigner
- eclipse
- vscode
- Notepad++
- WebAPI
- Javasript
- DOM
- BOM
- Event
- Class
- Module
- Ajax
- Fetch
- Promise
- async/await
- Statements and declarations
- Function
- Framwork
- jQurey
- Types
- Promise
- BootStrap
- v4
- ThinkPHP5
- install
- 定时任务
- CodeIgniter
- React.js
- node.js
- npm
- npm-commands
- npm-folder
- package.json
- Docker and private modules
- module
- webpack.js
- install
- configuration
- package.json
- entry
- modules
- plugins
- Code Splitting
- loaders
- libs
- API
- webpack-cli
- Vue.js
- install
- Compile
- VueAPI
- vuex
- vue-router
- vue-devtools
- vue-cli
- vue-loader
- VDOM
- vue-instance
- components
- template
- Single-File Components
- props
- data
- methods
- computed
- watch
- Event-handling
- Render Func
- remark
- 案例学习
- bootstrap-vue
- modal
- fontAwesome
- Hosting Font Awesome Yourself
- using with jquery
- using with Vue.js
- HTML
- CSS
- plugins
- Chart.js
- D3.js
- phpSpreadSheet
- Guzzle
- Cmder
- Git
- git命令
- git流程
- Postman
- Markdown
- Regular Expressions
- PowerDesigner
- 附录1-学习资源