php框架codeigniter数据库操作整理

1.

$query = $this->db->query('SELECT name, title, email FROM my_table');
foreach ($query->result() as $row)
{
    echo $row->title;
    echo $row->name;
    echo $row->email;
}
2.
foreach ($query->result_array() as $row)
{
    echo $row['title'];
    echo $row['name'];
    echo $row['email'];
}
3.
if ($query->num_rows() > 0)

?

4.
$query = $this->db->query('SELECT name FROM my_table LIMIT 1');
$row = $query->row();//$row = $query->row_array();
echo $row->name;

?

//你可以传递参数以便获得某一行的数据。比如我们要获得第 5 行的数据：
$row = $query->row_array(5);
//除此以外, 我们还可以使用下面的方法通过游标的方式获取记录：
$row = $query->first_row()
$row = $query->last_row()
$row = $query->next_row()
$row = $query->previous_row()

5.
$sql = "INSERT INTO mytable (title, name)
        VALUES (".$this->db->escape($title).", ".$this->db->escape($name).")";
$this->db->query($sql);//$query = $this->db->get('table_name');
echo $this->db->affected_rows();
6.
$data = array(
               'title' => $title,
               'name' => $name,
               'date' => $date
            );
$this->db->insert('mytable', $data);
// Produces: INSERT INTO mytable (title, name, date) VALUES ('{$title}', '{$name}', '{$date}')
7.
$this->db->escape()
8.
$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick')); //自动转义
9.
//该函数返回当前请求的字段数（列数）：
$query = $this->db->query('SELECT * FROM my_table');
echo $query->num_fields();
10.
$query = $this->db->query('SELECT title FROM my_table');
foreach ($query->result() as $row)
{
   echo $row->title;
}
$query->free_result(); // $query 将不再可用
$query2 = $this->db->query('SELECT name FROM some_table');
$row = $query2->row();
echo $row->name;
$query2->free_result(); // $query2 将不再可用
11.
$this->db->insert_id()
$this->db->affected_rows()
$this->db->count_all();
$this->db->platform()
$this->db->version()
$this->db->last_query();
$this->db->insert_string();
$this->db->update_string();
12.AR
$this->db->get();
$query = $this->db->get_where('mytable', array('id' => $id), $limit, $offset);
$this->db->select('title, content, date');
$query = $this->db->get('mytable');
$this->db->select_max('age');//min,avg,sum
$query = $this->db->get('members');
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->like('title', 'match', 'before');
// Produces: WHERE title LIKE '&#xma;tch'
$this->db->like('title', 'match', 'after');
// Produces: WHERE title LIKE 'match%'
$this->db->like('title', 'match', 'both');
// Produces: WHERE title LIKE '&#xma;tch%'
$this->db->insert();
$this->db->update();
$this->db->delete();
$this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20);
$query = $this->db->get();
$this->db->start_cache();
$this->db->select('field1');
$this->db->stop_cache();
$this->db->get('tablename');
// Results in:
// SELECT `field1` FROM (`tablename`)
$this->db->select('field2');
$this->db->get('tablename');
// Results in:
// SELECT `field1`, `field2` FROM (`tablename`)
$this->db->flush_cache();
$this->db->select('field2');
$this->db->get('tablename');
// Results in:
// SELECT `field2` FROM (`tablename`)
13.事务
$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');
$this->db-&