From 0f35b6e8fd4640915b1793f2e531867b859f1e3d Mon Sep 17 00:00:00 2001 From: kylefarris Date: Fri, 26 Aug 2011 02:40:56 -0400 Subject: Updated the Active Record documentation to reflect new publicly visible Active Record methods: get_compiled_select(), get_compiled_delete(), get_compiled_insert(), get_compiled_update(), and reset_query(). --- user_guide/database/active_record.html | 113 +++++++++++++++++++++++++++++---- 1 file changed, 100 insertions(+), 13 deletions(-) (limited to 'user_guide/database') diff --git a/user_guide/database/active_record.html b/user_guide/database/active_record.html index 92d9614d5..d53630402 100644 --- a/user_guide/database/active_record.html +++ b/user_guide/database/active_record.html @@ -73,6 +73,7 @@ is generated by each database adapter. It also allows for safer queries, since
  • Deleting Data
  • Method Chaining
  • Active Record Caching
    • +
  • Reset Active Record

    •  Selecting Data

    @@ -107,6 +108,27 @@ foreach ($query->result() as $row)

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

    +

    $this->db->get_compiled_select();

    + +

    Compiles the selection query just like $this->db->get() but does not run the query. This method simply returns the SQL query as a string.

    + +$sql = $this->db->get_compiled_select('mytable');
    +echo $sql;
    +
    +// Produces string: SELECT * FROM mytable     + +

    The second parameter enables you to set whether or not the active record query will be reset (by default it will be—just like $this->db->get()):

    + +echo $this->db->limit(10,20)->get_compiled_select('mytable', FALSE);
    +
    +// Produces 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();
    +
    +// Produces string: SELECT title, content, date FROM mytable     + +

    The key thing to notice in the above example is that the second query did not utlize $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().

    +

    $this->db->get_where();

    @@ -334,13 +356,6 @@ $this->db->or_where('id >', $id); $this->db->like('title', 'match', 'both');
    // Produces: WHERE title LIKE '%match%' -If you do not want to use the wildcard (%) you can pass to the optional third argument the option 'none'. - - - $this->db->like('title', 'match', 'none');
    -// Produces: WHERE title LIKE 'match' -     -
  • Associative array method: @@ -528,11 +543,41 @@ $this->db->insert('mytable', $object);

    Note: All values are escaped automatically producing safer queries.

    +  +

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

    + + +$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 active record query will be reset (by default it will be—just like $this->db->insert()):

    + +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 utlize $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().

    + +

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

    - + $data = array(
       array(
          'title' => 'My title' ,
    @@ -544,7 +589,7 @@ $data = array(
          'name' => 'Another Name' ,
          'date' => 'Another date'
       )
    -);
    +);

    $this->db->update_batch('mytable', $data);

    @@ -670,6 +715,14 @@ You can optionally pass this information directly into the update function as a

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

    + +

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

    + +

    For more information view documentation for get_compiled_insert().

    + +  

    Deleting Data

    @@ -699,11 +752,22 @@ the data to the second parameter of the function:

    $this->db->where('id', '5');
    $this->db->delete($tables);

    If you want to delete all data from a table, you can use the truncate() function, or empty_table().

    + + +

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

    +

    For more information view documentation for get_compiled_insert().

    + + +

    $this->db->empty_table();

    Generates a delete SQL string and runs the query. $this->db->empty_table('mytable');

    // Produces
    // DELETE FROM mytable

    + + +

    $this->db->truncate();

    Generates a truncate SQL string and runs the query.

    $this->db->from('mytable');
    @@ -716,7 +780,9 @@ $this->db->truncate('mytable');

    Note: If the TRUNCATE command isn't available, truncate() will execute as "DELETE FROM table".

    -

     Method Chaining

    + +  +

    Method Chaining

    Method chaining allows you to simplify your syntax by connecting multiple functions. Consider this example:

    @@ -727,9 +793,9 @@ $query = $this->db->get();

    Note: Method chaining only works with PHP 5.

    -

     

    +  -

     Active Record Caching

    +

    Active Record Caching

    While not "true" caching, Active Record enables you to save (or "cache") certain parts of your queries for reuse at a later point in your script's execution. Normally, when an Active Record call is completed, all stored information is reset for the next call. With caching, you can prevent this reset, and reuse information easily.

    @@ -769,7 +835,28 @@ $this->db->get('tablename');
    //Generates: SELECT `field2` FROM (`tablename`)

    Note: The following statements can be cached: select, from, join, where, like, group_by, having, order_by, set

    -

     

    + + +  +

    Reset Active Record

    + +

    Resetting Active Record 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 Active Record Caching.

    +

    This is useful in situations where you are using Active Record to generate SQL (ex. $this->db->get_compiled_select()) but then choose to, for instance, run the query:

    + + +// 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;
    +     + -- cgit v1.2.3-24-g4f1b