From 4b9c62980599228f070b401c7673dce8085b0c61 Mon Sep 17 00:00:00 2001
From: Derek Jones CodeIgniter uses a modified version of the Active Record Database Pattern.
This pattern allows information to be retrieved, inserted, and updated in your database with minimal scripting.
In some cases only one or two lines of code are necessary to perform a database action.
-CodeIgniter does not require that each database table be its own class file. It instead provides a more simplified interface.
Beyond simplicity, a major benefit to using the Active Record 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.
+is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system. -Note: If you intend to write your own queries you can disable this class in your database config file, allowing the core database library and adapter to utilize fewer resources.
Note: If you intend to write your own queries you can disable this class in your database config file, allowing the core database library and adapter to utilize fewer resources.
Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table:
+Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table:
$query = $this->db->get('mytable');
@@ -126,7 +126,7 @@ $this->db->select('title, content, date');
$query = $this->db->get('mytable');
// Produces: SELECT title, content, date FROM mytable
-Note: If you are selecting all (*) from a table you do not need to use this function. When omitted, CodeIgniter assumes you wish to SELECT *
+Note: If you are selecting all (*) from a table you do not need to use this function. When omitted, CodeIgniter assumes you wish to SELECT *
$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 with backticks. This is useful if you need a compound select statement.
Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE);
@@ -351,7 +351,7 @@ $this->db->or_where('id >', $id);
$this->db->like('title', 'match');
@@ -278,7 +278,7 @@ $this->db->or_where('id >', $id);
$this->db->where_in();
-
$names = array('Frank', 'Todd', 'James');
If you want to control where the wildcard (%) is placed, you can use an optional third argument. Your options are 'before', 'after' and 'both' (which is the default).
$this->db->where_in('username', $names);
@@ -322,7 +322,7 @@ $this->db->or_where('id >', $id);
$this->db->like('title', 'match');
+ // WHERE title LIKE '%match%' AND body LIKE '%match%
$this->db->like('body', 'match');
- // WHERE title LIKE '%match%' AND body LIKE '%match%$this->db->like('title', 'match', 'before');
+
@@ -340,7 +340,7 @@ $this->db->or_where('id >', $id);
$array = array('title' => $match, 'page1' => $match, 'page2' => $match);
$this->db->like($array);
-
// WHERE title LIKE '%match%' AND page1 LIKE '%match%' AND page2 LIKE '%match%'
// WHERE title LIKE '%match%' AND page1 LIKE '%match%' AND page2 LIKE '%match%'
$this->db->or_like('body', $match);
-
// WHERE title LIKE '%match%' OR body LIKE '%match%'
+
// WHERE title LIKE '%match%' OR body LIKE '%match%'
@@ -367,7 +367,7 @@ $this->db->or_like('body', $match);
$this->db->like('title', 'match');
+// WHERE title LIKE '%match% OR body NOT LIKE '%match%'
$this->db->or_not_like('body', 'match');
-// WHERE title LIKE '%match% OR body NOT LIKE '%match%'
Permits you to write the GROUP BY portion of your query:
@@ -385,7 +385,7 @@ $this->db->or_not_like('body', 'match');Adds the "DISTINCT" keyword to a query
+Adds the "DISTINCT" keyword to a query
$this->db->distinct();
@@ -409,16 +409,16 @@ $this->db->having('user_id', 45);
$this->db->get('table');
@@ -397,7 +397,7 @@ $this->db->or_not_like('body', 'match');
// Produces: HAVING user_id = 45
-$this->db->having('user_id', 45);
+$this->db->having('user_id', 45);
// Produces: HAVING user_id = 45
// Produces: HAVING title = 'My Title', id < 45
If you are using a database that CodeIgniter escapes queries for, you can prevent escaping content by passing an optional third argument, and setting it to FALSE.
-Identical to having(), only separates multiple clauses with "OR". Lets you set an ORDER BY clause. The first parameter contains the name of the column you would like to order by.
-The second parameter lets you set the direction of the result. Options are asc or desc, or random. $this->db->having('user_id', 45);
+$this->db->having('user_id', 45);
// Produces: HAVING `user_id` = 45 in some databases such as MySQL
- $this->db->having('user_id', 45, FALSE);
+ $this->db->having('user_id', 45, FALSE);
// Produces: HAVING user_id = 45$this->db->or_having();
$this->db->order_by();
$this->db->order_by("title", "desc");
@@ -455,12 +455,12 @@ $this->db->limit(10);
$this->db->limit(10, 20);
-// Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)
+// Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)
Permits you to determine the number of rows in a particular Active Record query. Queries will accept Active Record restrictors such as where(), or_where(), like(), or_like(), etc. Example:
+Permits you to determine the number of rows in a particular Active Record query. Queries will accept Active Record restrictors such as where(), or_where(), like(), or_like(), etc. Example:
echo $this->db->count_all_results('my_table');
// Produces an integer, like 25
@@ -472,7 +472,7 @@ echo $this->db->count_all_results();
$this->db->count_all();
-Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter. Example:
+Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter. Example:
echo $this->db->count_all('my_table');
@@ -485,7 +485,7 @@ echo $this->db->count_all_results();
$this->db->insert();
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. Here is an example using an array:
+array or an object to the function. Here is an example using an array:
$data = array(
@@ -505,9 +505,9 @@ $this->db->insert('mytable', $data);
/*
class Myclass {
- var $title = 'My Title';
- var $content = 'My Content';
- var $date = 'My Date';
+ var $title = 'My Title';
+ var $content = 'My Content';
+ var $date = 'My Date';
}
*/
@@ -523,7 +523,7 @@ $this->db->insert('mytable', $object);
$this->db->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. Here is an example using an array:
+array or an object to the function. Here is an example using an array:
$data = array(
@@ -541,7 +541,7 @@ $data = array(
$this->db->update_batch('mytable', $data);
-// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date')
+// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date')
The first parameter will contain the table name, the second is an associative array of values.
@@ -588,9 +588,9 @@ $this->db->insert('mytable');
/*
class Myclass {
- var $title = 'My Title';
- var $content = 'My Content';
- var $date = 'My Date';
+ var $title = 'My Title';
+ var $content = 'My Content';
+ var $date = 'My Date';
}
*/
@@ -606,7 +606,7 @@ $this->db->insert('mytable');
Updating Data
$this->db->update();
-Generates an update string and runs the query based on the data you supply. You can pass an
+
Generates an update string and runs the query based on the data you supply. You can pass an
array or an object to the function. Here is an example using
an array:
@@ -630,9 +630,9 @@ $this->db->update('mytable', $data);
/*
class Myclass {
- var $title = 'My Title';
- var $content = 'My Content';
- var $date = 'My Date';
+ var $title = 'My Title';
+ var $content = 'My Content';
+ var $date = 'My Date';
}
*/
@@ -711,7 +711,7 @@ $this->db->truncate('mytable');
Method Chaining
-Method chaining allows you to simplify your syntax by connecting multiple functions. Consider this example:
+Method chaining allows you to simplify your syntax by connecting multiple functions. Consider this example:
$this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20);
@@ -752,14 +752,14 @@ $this->db->get('tablename');
$this->db->select('field2');
$this->db->get('tablename');
-//Generates: SELECT `field1`, `field2` FROM (`tablename`)
+//Generates: SELECT `field1`, `field2` FROM (`tablename`)
$this->db->flush_cache();
$this->db->select('field2');
$this->db->get('tablename');
-//Generates: SELECT `field2` FROM (`tablename`)
+//Generates: SELECT `field2` FROM (`tablename`)
Note: The following statements can be cached: select, from, join, where, like, group_by, having, order_by, set
--
cgit v1.2.3-24-g4f1b