From fb28bb8d3bb01993e83126be028a1dda43422a39 Mon Sep 17 00:00:00 2001 From: admin Date: Sun, 24 Sep 2006 17:59:33 +0000 Subject: Adding database folder --- user_guide/database/active_record.html | 610 +++++++++++++++++++++++++++++++++ 1 file changed, 610 insertions(+) create mode 100644 user_guide/database/active_record.html (limited to 'user_guide/database/active_record.html') diff --git a/user_guide/database/active_record.html b/user_guide/database/active_record.html new file mode 100644 index 000000000..5492ddb4c --- /dev/null +++ b/user_guide/database/active_record.html @@ -0,0 +1,610 @@ + + + + +Code Igniter User Guide + + + + + + + + + + + + + + + + + + + + + + +
+ + + + + +

Code Igniter User Guide Version 1.4.1

+
+ + + + + + + + + +
+ + + +
+ + + +
+ + +

Active Record Class

+ + +

Code Igniter 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. +Code Igniter 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.

+ +

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.

+ +
+ + + + + + +  +

Selecting Data

+ +

The following functions allow you to build SQL SELECT statements.

+ +

Note: If you are using PHP 5 you can use method chaining for more compact syntax. This is described at the end of the page.

+ + +

$this->db->get();

+ +

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');
+
+// Produces: SELECT * FROM mytable + +

The second and third parameters enable you do set a limit and offset clause:

+ +$query = $this->db->get('mytable', 10, 20);
+
+// Produces: SELECT * FROM mytable LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax) + + + +

You'll notice that the above function is assigned to a variable named $query, which can be used to show the results:

+ +$query = $this->db->get('mytable');
+
+foreach ($query->result() as $row)
+{
+    echo $row->title;
+} + +

Please visit the result functions page for a full discussion regarding result generation.

+ + +

$this->db->getwhere();

+ +

Identical to the above function except that it permits you to add a "where" clause in the second parameter, +instead of using the db->where() function:

+ +$query = $this->db->getwhere('mytable', array(id => $id), $limit, $offset); + +

Please read the about the where function below for more information.

+ + +

$this->db->select();

+ +

Permits you to write the SELECT portion of your query:

+ + +$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, Code Igniter assumes you wish to SELECT *

+ + +

$this->db->from();

+ +

Permits you to write the FROM portion of your query:

+ + +$this->db->select('title, content, date');
+$this->db->from('mytable');
+
+$query = $this->db->get();
+
+// Produces: SELECT title, content, date FROM mytable + +

Note: As shown earlier, the FROM portion of your query can be specified in the $this->db->get() function, so use whichever method +you prefer.

+ +

$this->db->join();

+ +

Permits you to write the JOIN portion of your query:

+ + +$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
+ + +

Multiple function calls can be made if you need several joins in one query.

+ +

If you need something other than a natural JOIN you can specify it via the third parameter of the function. +Options are: left, right, outer, inner, left outer, and right outer.

+ + +$this->db->join('comments', 'comments.id = blogs.id', 'left');
+
+// Produces: LEFT JOIN comments ON comments.id = blogs.id + + + + + +

$this->db->where();

+

This function enables you to set WHERE clauses using one of four methods:

+ +

Note: All values passed to this function are escaped automatically, producing safer queries.

+ +
    +
  1. Simple key/value method: + + $this->db->where('name', $name); +

    // Produces: WHERE name = 'Joe' +     + +

    Notice that the equal sign is added for you.

    + +

    If you use multiple function calls they will be chained together with AND between them:

    + + $this->db->where('name', $name);
    + $this->db->where('title', $title);
    + $this->db->where('status', $status); +

    // WHERE = 'Joe' AND title = 'boss' AND status = 'active' +     + + +
    2. + +
  2. Custom key/value method: + +

    You can include an operator in the first parameter in order to to control the comparison:

    + + $this->db->where('name !=', $name);
    + $this->db->where('id <', $id); +

    // Produces: WHERE name != 'Joe' AND id < 45 +     + + + +
    3. +
  3. Associative array method: + + + + $array = array('name' => $name, 'title' => $title, 'status' => $status);

    + + $this->db->where($array); +

    // Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active' +     + +

    You can include your own operators using this method as well:

    + + + $array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);

    + + $this->db->where($array);     + +
    4. +
  4. Custom string: + +

    You can write your own clauses manually:

    + + + $where = "name='Joe' AND status='boss' OR status='active'";

    + $this->db->where($where);     + +
    5. +
+ + +

$this->db->orwhere();

+

This function is identical to the one above, except that multiple instances are joined by OR:

+ + +$this->db->where('name !=', $name);
+$this->db->orwhere('id >', $id); +

// Produces: WHERE name != 'Joe' OR id > 50 + + + + + +

$this->db->like();

+

This function enables you to generate LIKE clauses, useful for doing searches.

+ +

Note: All values passed to this function are escaped automatically.

+ + +
    +
  1. Simple key/value method: + + $this->db->like('title', $match); +

    // Produces: WHERE title LIKE '%match%' +     + +

    If you use multiple function calls they will be chained together with AND between them:

    + + $this->db->like('title', $match);
    + $this->db->like('body', $match); +

    // WHERE title LIKE '%match%' AND body LIKE '%match%' +     + +
    2. + +
  2. Associative array method: + + + $array = array('title' => $match, 'page1' => $match, 'page2' => $match);

    + + $this->db->like($array); +

    // WHERE title LIKE '%match%' AND page1 LIKE '%match%' AND page2 LIKE '%match%' +     + +
    3. +
+ + +

$this->db->orlike();

+

This function is identical to the one above, except that multiple instances are joined by OR:

+ + +$this->db->like('title', $match);
+$this->db->orlike('body', $match); +

// WHERE title LIKE '%match%' OR body LIKE '%match%' + + + + + +

$this->db->groupby();

+ +

Permits you to write the GROUP BY portion of your query:

+ +$this->db->groupby("title"); +

// Produces: GROUP BY title + + +

You can also pass an array of multiple values as well:

+ +$this->db->groupby(array("title", "date"); +

// Produces: GROUP BY title, date + + + +

$this->db->having();

+ +

Permits you to write the HAVING portion of your query:

+ +$this->db->having('user_id = 45'); +

// Produces: HAVING 'user_id = 45' + + +

You can also pass an array of multiple values as well:

+ + +$this->db->having(array('title =' => 'My Title', 'id <' => $id)); +

// Produces: HAVING title = 'My Title', 'id < 45' + + + + +

$this->db->orderby();

+

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 RAND()

+ +$this->db->orderby("title", "desc"); +

// Produces: ORDER BY title DESC + + +

You can also pass your own string in the first parameter:

+ +$this->db->orderby('title desc, name asc'); +

// Produces: ORDER BY title DESC, name ASC + + + + +

$this->db->limit();

+

Lets you limit the number of rows you would like returned by the query: + + +$this->db->limit(10);
+
+// Produces: LIMIT 10 + + +

The second parameter lets you set a result offset.

+ + +$this->db->limit(10, 20);
+
+// Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax) + + +

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

+ +echo $this->db->count_all('my_table');
+
+// Produces an integer, like 25 + + + + +  +

Inserting Data

+ +

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

+ + +$data = array(
+               'title' => $title,
+               'name' => $name,
+               'date' => $date
+            );
+
+$this->db->insert('mytable', $data); +

+// Produces: INSERT INTO mytable (title, name, date) VALUES ('{$title}', '{$name}', '{$date}') + +

The first parameter will contain the table name, the second is an associative array of values.

+ +

Here is an example using an object:

+ + +/*
+    class Myclass {
+        var = $title = 'My Title';
+        var = $content = 'My Content';
+        var = $date = 'My Date';
+    }
+*/
+
+$object = new Myclass;
+
+$this->db->insert('mytable', $object); +

+// Produces: INSERT INTO mytable (title, name, date) VALUES ('{$title}', '{$name}', '{$date}') + +

The first parameter will contain the table name, the second is an associative array of values.

+ +

Note: All values are escaped automatically producing safer queries.

+ + + + +

$this->db->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:

+ +$this->db->set('name', $name); +
+$this->db->insert('mytable'); +

+// Produces: INSERT INTO mytable (name) VALUES ('{$name}') + +

If you use multiple function called they will be assembled properly based on whether you are doing an insert or an update:

+ +$this->db->set('name', $name);
+$this->db->set('title', $title);
+$this->db->set('status', $status);
+$this->db->insert('mytable'); + + +

You can also pass an associative array to this function:

+ + +$array = array('name' => $name, 'title' => $title, 'status' => $status);

+ +$this->db->set($array);
+$this->db->insert('mytable'); + + +

Or an object:

+ + + +/*
+    class Myclass {
+        var = $title = 'My Title';
+        var = $content = 'My Content';
+        var = $date = 'My Date';
+    }
+*/
+
+$object = new Myclass;
+
+$this->db->set($object);
+$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 +array or an object to the function. Here is an example using +an array:

+ + +$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 + +

Or you can supply an object:

+ + +/*
+    class Myclass {
+        var = $title = 'My Title';
+        var = $content = 'My Content';
+        var = $date = 'My Date';
+    }
+*/
+
+$object = new Myclass;
+
+$this->db->where('id', $id);
+$this->db->update('mytable', $object, $where); +

+// Produces:
+// UPDATE mytable
+// SET title = '{$title}', name = '{$name}', date = '{$date}'
+// WHERE id = $id + + + +

Note: All values are escaped automatically producing safer queries.

+ +

You'll notice the use of the $this->db->where() function, enabling you to set the WHERE clause. +You can optionally pass this information directly into the update function as a string:

+ +$this->db->update('mytable', $data, "id = 4"); + +

Or as an array:

+ +$this->db->update('mytable', $data, array('id' => $id)); + +

You may also use the $this->db->set() function described above when performing updates.

+ + +  +

Deleting Data

+ + + +

$this->db->delete();

+

Generates a delete SQL string and runs the query.

+ + +$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 orwhere() functions instead of passing +the data to the second parameter of the function: + + +$this->db->where('id', $id);
+$this->db->delete('mytable'); +

+// Produces:
+// DELETE FROM mytable
+// WHERE id = $id + +

Note: All values are escaped automatically producing safer queries.

+ + +  +

Method Chaining

+ +

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);
+
+$query = $this->db->get(); + +

Note: Method chaining only works with PHP 5.

+ + + +
+ + + + + + + \ No newline at end of file -- cgit v1.2.3-24-g4f1b