CodeIgniter study notes Item5--AR in CI-PHP开发-php.cn

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

Copy after login

The result set of a table

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

Copy after login

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);

Copy after login

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'));

Copy after login

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'));

Copy after login

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

Copy after login

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(&#39;name&#39;, &#39;mary&#39;)或where(&#39;name =&#39;, &#39;mary&#39;)：表示查询条件是name字段值是mary
where(array(&#39;name&#39;=>&#39;mary&#39;, &#39;id >&#39;=>&#39;1&#39;));：表示查询条件有两个，name字段值是mary并且id字段值是1

Copy after login

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

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

Copy after login

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

Copy after login

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

Copy after login

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)

Copy after login

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

Copy after login

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);

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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%'

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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

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

Copy after login

You can also pass multiple values as an array:

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

Copy after login

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

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

Copy after login

Add the "DISTINCT" keyword to the query:

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

Copy after login

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

Copy after login

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

Copy after login

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

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

Limit the number of results returned by the query:

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

Copy after login

The second parameter sets the result offset.

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

Copy after login

允许你获得某个特定的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

Copy after login

插入数据

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

Copy after login

生成一条基于你所提供的数据的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')

Copy after login

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

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

[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')

Copy after login

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

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

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

Copy after login

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

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

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

Copy after login

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

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

Copy after login

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')

Copy after login

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

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

Copy after login

或者一个对象也可以:

[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');

Copy after login

更新数据

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

Copy after login

根据你提供的数据生成并执行一条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

Copy after login

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

[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

Copy after login

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

你会注意到

$this->db->where()

Copy after login

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

Copy after login

或者是一个数组:

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

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

删除数据

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

Copy after login

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

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

Copy after login

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

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

Copy after login

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

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

Copy after login

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

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

Copy after login

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

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

Copy after login

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

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

Copy after login

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

链式方法

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

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

Copy after login

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