CodeIgniter study notes Item5--AR in CI

AR (Active Record)

When AR is enabled (CI3.0 is started by default and has no configuration items), you can obtain a record through the get method of

$this->db

The result set of a table

[code]// AR会自动加上表前缀，因此get方法中的表名不用加上表前缀
$res = $this->db->get('user');
foreach ($res->result() as $item)
{
    echo $item->name . "
";
}

You can simply insert a record through the insert method. The parameters are the table name and the associative array

[code]$data = array('name'=>'mary', 'password'=>md5('mary'));
$result = $this->db->insert('user', $data);

Modify the record through the update method. The first parameter is the indication, the second parameter is the modified content, represented by an associative array, and the third parameter is the query condition

[code]$data = array ('email'=>'mary@gmail.com', 'password'=>md5('123456'));
$this->db->update('user', $data, array('name'=>'mary'));

Delete a record through the delete method. The first parameter is the table name, and the second parameter is the query condition

[code]$this->db->delete('user', array('name'=>'mary'));

Consecutive operations, for more complex SQL statements can be queried using the coherent operations provided by AR

[code]$result = $this->db->select('id, name')
            ->from('user')
            ->where('id >=', 1)
            ->limit(3,1)
            ->order_by('id desc ')
            ->get();

Note: The parameter order of limit is opposite to the order in SQL. The first parameter indicates the displayed The number of items, the second parameter represents the number of skipped items

How to write the where statement under different query conditions

[code]where('name', 'mary')或where('name =', 'mary')：表示查询条件是name字段值是mary
where(array('name'=>'mary', 'id >'=>'1'));：表示查询条件有两个，name字段值是mary并且id字段值是1

The last_query() method can be used to obtain coherent operations , the SQL statement assembled by AR

[code]$this->db->last_query();

can only execute relatively simple queries through AR. If it is a complex query, it is recommended to use $this->db->query($sql , $data)
Perform query

Select data

The following functions help you build a SQL SELECT statement.

Note: If you are using PHP5, you can use chain syntax in complex situations. Detailed descriptions are provided at the bottom of this page.

[code]$this->db->get();

Run the select query statement and return the result set. All data of a table can be obtained.

[code]$query = $this->db->get('mytable');
// Produces: SELECT * FROM mytable

The second and third parameters allow you to set the number of records per page of a result set (limit) and the offset of the result set (offset)

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

Note: The second parameter is the number of records per page, and the third parameter is the offset

You will notice that the above function is executed by a variable $query
, this $query
can be used to display the result set.

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

Same as the above function, except that it allows you to add a where clause to the second parameter of the function without using the db->where() function:

[code]$query = $this->db->get_where('mytable', array('id' => $id), $limit, $offset);

Note: get_where() was written as getwhere() in previous versions. This is an obsolete usage, and getwhere() has been removed from the code.

[code]$this->db->select();

Allows you to write the SELECT part in the SQL query:

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

Note: If you want to query all rows in the table, you can There is no need to write this function. When omitted, CodeIgniter will think you want to query all rows (SELECT *).

$this->db->select()

[code]$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE); 
$query = $this->db->get('mytable');

Associative array method:

[code]$array = array('title' => $match, 'page1' => $match, 'page2' => $match);
$this->db->like($array); 
// WHERE title LIKE '%match%' AND page1 LIKE '%match%' AND page2 LIKE '%match%'
$this->db->or_like();

This function is almost identical to the one above. The only difference is that between multiple instances Connected with OR:

[code]$this->db->like('title', 'match');
$this->db->or_like('body', $match); 
// WHERE title LIKE '%match%' OR body LIKE '%match%'

Description: or_like() used to be called orlike(), the latter is obsolete and orlike() has been removed from the code .

[code]$this->db->not_like();

This function is almost identical to like(), the only difference is that it generates NOT LIKE statement:

[code]$this->db->not_like('title', 'match');
// WHERE title NOT LIKE '%match%
$this->db->or_not_like();

This function is similar to not_like () is almost identical, the only difference is that multiple instances are connected with OR:

[code]$this->db->like('title', 'match');
$this->db->or_not_like('body', 'match'); 
// WHERE title LIKE '%match%' OR body NOT LIKE '%match%'
$this->db->group_by();

Allows you to write the GROUP BY part of the query statement:

[code]$this->db->group_by("title"); 
// 生成: GROUP BY title

You can also pass multiple values ​​as an array:

[code]$this->db->group_by(array("title", "date")); 
// 生成: GROUP BY title, date

Note: group_by() used to be called groupby(), The latter is deprecated and groupby() has been removed from the code.

[code]$this->db->distinct();

Add the "DISTINCT" keyword to the query:

[code]$this->db->distinct();
$this->db->get('table');
// 生成: SELECT DISTINCT * FROM table
$this->db->having();

Allows you to write the HAVING part of your query. There are two syntax forms, one or two parameters are acceptable:

[code]$this->db->having('user_id = 45'); 
// 生成: HAVING user_id = 45
$this->db->having('user_id', 45); 
// 生成: HAVING user_id = 45
你也可以把多个值通过数组传递过去:
[code]$this->db->having(array('title =' => 'My Title', 'id <' => $id)); 
// 生成: HAVING title = 'My Title', id < 45

If you are using a database protected by CodeIgniter escape, in order to avoid content escape, You can pass an optional third parameter and set it to FALSE.

[code]$this->db->having('user_id', 45); 
// 生成: HAVING `user_id` = 45 (在诸如MySQL等数据库中) 
$this->db->having('user_id', 45, FALSE); 
// 生成: HAVING user_id = 45
$this->db->or_having();

is almost identical to the having() function, the only difference is that multiple clauses are separated by "OR".

[code]$this->db->order_by();

Helps you set up an ORDER BY clause. The first parameter is the name of the field you want to sort on. The second parameter sets the order of the results. Available options include asc (ascending order) or desc (descending order), or random (random).

[code]$this->db->order_by("title", "desc"); 
// 生成: ORDER BY title DESC

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

[code]$this->db->order_by('title desc, name asc'); 
// 生成: ORDER BY title DESC, name ASC

Or, call this function multiple times. Multiple fields can be sorted.

[code]$this->db->order_by("title", "desc");
$this->db->order_by("name", "asc"); 
// 生成: ORDER BY title DESC, name ASC

Note: order_by() used to be called orderby(), which is obsolete and orderby() has been removed from the code.

Note: Currently, Oracle and MSSQL drivers do not support random sorting and will be set to ‘ASC’ (ascending order) by default.

[code]$this->db->limit();

Limit the number of results returned by the query:

[code]$this->db->limit(10);
// 生成: LIMIT 10

The second parameter sets the result offset.

[code]$this->db->limit(10, 20);
// 生成: LIMIT 20, 10 (仅限MySQL中。其它数据库有稍微不同的语法)
$this->db->count_all_results();

允许你获得某个特定的Active Record查询所返回的结果数量。可以使用Active Record限制函数，例如 where(),or_where()
, like(), or_like() 等等。范例：

[code]echo $this->db->count_all_results('my_table');
// 生成一个整数，例如 25
$this->db->like('title', 'match');
$this->db->from('my_table');
echo $this->db->count_all_results();
// 生成一个整数，例如 17

插入数据

[code]$this->db->insert();

生成一条基于你所提供的数据的SQL插入字符串并执行查询。你可以向函数传递 数组 或一个 对象。下面是一个使用数组的例子：

[code]$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')

第一个参数包含表名，第二个是一个包含数据的关联数组。

下面是一个使用对象的例子：

[code]/*
    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, content, date) VALUES ('My Title', 'My Content', 'My Date')

第一个参数包含表名，第二个是一个对象。（原文有错：The first parameter will contain the table name, the second is an associative array of values.）

注意: 所有的值已经被自动转换为安全查询。

[code]$this->db->set();

本函数使您能够设置inserts(插入)或updates(更新)值。

它可以用来代替那种直接传递数组给插入和更新函数的方式: 

[code]$this->db->set('name', $name); 
$this->db->insert('mytable'); 
// 生成: INSERT INTO mytable (name) VALUES ('{$name}')

如果你多次调用本函数，它们会被合理地组织起来，这取决于你执行的是插入操作还是更新操作:

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

set() 也接受可选的第三个参数($escape)，如果此参数被设置为 FALSE，就可以阻止数据被转义。为了说明这种差异，这里将对 包含转义参数 和 不包含转义参数 这两种情况的 set() 函数做一个说明。

[code]$this->db->set('field', 'field+1', FALSE);
$this->db->insert('mytable'); 
// 得到 INSERT INTO mytable (field) VALUES (field+1)
$this->db->set('field', 'field+1');
$this->db->insert('mytable'); 
// 得到 INSERT INTO mytable (field) VALUES ('field+1')

你也可以将一个关联数组传递给本函数:

[code]$array = array('name' => $name, 'title' => $title, 'status' => $status);
$this->db->set($array);
$this->db->insert('mytable');

或者一个对象也可以:

[code]/*
    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');

更新数据

[code]$this->db->update();

根据你提供的数据生成并执行一条update(更新)语句。你可以将一个数组或者对象传递给本函数。这里是一个使用数组的例子:

[code]$data = array(
               'title' => $title,
               'name' => $name,
               'date' => $date
            );
$this->db->where('id', $id);
$this->db->update('mytable', $data); 
// 生成:
// UPDATE mytable 
// SET title = '{$title}', name = '{$name}', date = '{$date}'
// WHERE id = $id

或者你也可以传递一个对象:

[code]/*
    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); 
// 生成:
// UPDATE mytable 
// SET title = '{$title}', name = '{$name}', date = '{$date}'
// WHERE id = $id

说明: 所有值都会被自动转义，以便生成安全的查询。

你会注意到

$this->db->where()

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

或者是一个数组:

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


在进行更新时，你还可以使用上面所描述的 $this->db->set() 函数。

删除数据

[code]$this->db->delete();

生成并执行一条DELETE(删除)语句。

[code]$this->db->delete('mytable', array('id' => $id)); 
// 生成:
// DELETE FROM mytable 
// WHERE id = $id

第一个参数是表名，第二个参数是where子句。你可以不传递第二个参数，使用 where() 或者 or_where() 函数来替代它:

[code]$this->db->where('id', $id);
$this->db->delete('mytable'); 
// 生成:
// DELETE FROM mytable 
// WHERE id = $id

如果你想要从一个以上的表中删除数据，你可以将一个包含了多个表名的数组传递给delete()函数。

[code]$tables = array('table1', 'table2', 'table3');
$this->db->where('id', '5');
$this->db->delete($tables);

如果你想要删除表中的全部数据，你可以使用 truncate() 函数，或者 empty_table() 函数。

[code]$this->db->empty_table();

生成并执行一条DELETE(删除)语句。

[code] $this->db->empty_table('mytable'); 
// 生成
// DELETE FROM mytable
$this->db->truncate();

生成并执行一条TRUNCATE(截断)语句。

[code]$this->db->from('mytable'); 
$this->db->truncate(); 
// 或 
$this->db->truncate('mytable'); 
// 生成:
// TRUNCATE mytable

说明: 如果 TRUNCATE 命令不可用，truncate() 将会以 “DELETE FROM table” 的方式执行。

链式方法 

链式方法允许你以连接多个函数的方式简化你的语法。考虑一下这个范例:

[code]$this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20);
$query = $this->db->get();

 以上就是CodeIgniter学习笔记 Item5--CI中的AR的内容，更多相关内容请关注PHP中文网（www.php.cn）！