CI 数据库操作总结

时间：2023-03-08 16:46:53
CI 数据库操作总结

最简单示例

$query = $this->db->query("YOUR QUERY");

foreach ($query->result() as $row)

{

    echo $row->title;

    echo $row->name;

    echo $row->body;

}

foreach ($query->result_array() as $row)

{

    echo $row['title'];

    echo $row['name'];

    echo $row['body'];

}

CURD

$sql = 'select * from tab_city where fatherID=0';  $list = $this->db->query($sql); //原生sql

$query = $this->db->get('mytable'); //select * from mytable

$query = $this->db->get('mytable', 10, 20);//limit10,20

$query = $this->db->get_where('mytable', array('id' => $id), $limit, $offset); //where id=$id limit $limit,$offset

$this->db->select('title, content, date'); //SELECT title, content, date

$this->db->from('mytable');//from mytable

$this->db->join('comments', 'comments.id = blogs.id');//JOIN

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

$this->db->where('name', $name); //WHERE name = 'Joe'

$this->db->where('name !=', $name);//WHERE name != 'Joe'

$this->db->where('id <', $id);//AND id < 45

$array = array('name' => $name, 'title' => $title, 'status' => $status); $this->db->where($array); //WHERE name = 'Joe' AND title = 'boss' AND status = 'active'

$where = "name='Joe' AND status='boss' OR status='active'"; $this->db->where($where);//WHERE name='Joe' AND status='boss' OR status='active'

$this->db->or_where('id >', $id); //WHERE name='Joe' or id>10

$names = array('Frank', 'Todd', 'James');$this->db->where_in('username', $names); //WHERE username IN ('Frank', 'Todd', 'James')

$names = array('Frank', 'Todd', 'James');$this->db->or_where_in('username', $names);//OR username IN ('Frank', 'Todd', 'James')

$names = array('Frank', 'Todd', 'James');$this->db->where_not_in('username', $names);//WHERE username NOT IN ('Frank', 'Todd', 'James')

$names = array('Frank', 'Todd', 'James');$this->db->or_where_not_in('username', $names);//OR username NOT IN ('Frank', 'Todd', 'James')

$this->db->like('title', 'match');//WHERE `title` LIKE '%match%'

$this->db->like('title', 'match', 'before');    //  WHERE `title` LIKE '%match'

$this->db->like('title', 'match', 'after'); // WHERE `title` LIKE 'match%'

$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('body', $match);//OR  `body` LIKE '%match%'

$this->db->not_like('title', 'match');  // WHERE `title` NOT LIKE '%match%'

$this->db->or_not_like('body', 'match');

$this->db->group_by("title");

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

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

$this->db->having('user_id',  45);  //HAVING user_id = 45

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

$this->db->or_having();

$this->db->order_by('title', 'DESC');

$this->db->order_by('title DESC, name ASC');

$this->db->order_by(42, 'RANDOM');//随机排序

$this->db->limit(10);

$this->db->limit(10, 20);

$this->db->count_all_results('my_table');//返回结果的数量 前面可以带$this->db->where() 等等

$this->db->count_all('my_table');//获得当前表的总行数

$this->db->group_start();其实就是  and a=1 and (`b` = 'b' AND `c` = 'c' )) 括号里面的

$this->db->group_end();//建议去看手册

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

$data = array(

    array('title' => 'My title', 'name' => 'My Name', 'date' => 'My date'),

    array('title' => 'Another title', 'name' => 'Another Name',  'date' => 'Another date')

);

$this->db->insert_batch('mytable', $data);//插入多条

$data = array( 'title' => 'My title', 'name'  => 'My Name', 'date'  => 'My date');

$this->db->replace('table', $data);//DELETE + INSERT   REPLACE INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')

//我们假设 title 字段是我们的主键，那么如果我们数据库里有一行 的 title 列的值为 'My title'，这一行将会被删除并被我们的新数据所取代

$this->db->set('name', $name); $this->db->insert('mytable'); //插入

$this->db->set('name', $name); $this->db->update('mytable'); //更新

$data = array('title' => $title,'name' => $name,'date' => $date); //既可以是数组也可以是对象

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

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

$this->db->delete('mytable', array('id' => $id));

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

$query = $this->db->select('title')->where('id', $id)->limit(10, 20)->get('mytable'); //可以是这种链式写法
查询结果
$query = $this->db->query("YOUR QUERY");

foreach ($query->result() as $row)

{

    echo $row->title;

    echo $row->name;

    echo $row->body;

} //结果转换对象

foreach ($query->result_array() as $row)

{

    echo $row['title'];

    echo $row['name'];

    echo $row['body'];

}//结果转换成数组

$row = $query->row();//获取第一行数据

$row = $query->row(5); // 获得指定行数据

$row = $query->row_array(); //获得第一行数据的，一维数组

$row = $query->row_array(5);//特定行的一维数组

$row = $query->first_row();$row = $query->last_row();$row = $query->next_row();$row = $query->previous_row();//第一个，最后一个，前一个，后一个 。结果obj

$row = $query->first_row('array'); //结果array

/*这个方法和 row() 方法一样返回单独一行结果，但是它不会预读取所有的结果数据到内存中。 如果你的查询结果不止一行，它将返回当前一行，并通过内部实现的指针来移动到下一行。*/

$query = $this->db->query("YOUR QUERY");

while ($row = $query->unbuffered_row())

{

    echo $row->title;

    echo $row->name;

    echo $row->body;

}

$query->unbuffered_row();       // object

$query->unbuffered_row('object');   // object

$query->unbuffered_row('array');    // associative array

//自定义结果对象,参考手册

$query = $this->db->query('SELECT * FROM my_table');echo $query->num_rows(); //返回查询结果总行数

echo $query->num_fields();//该方法返回查询结果的字段数（列数）

$query->free_result();//主动释放内存
查询辅助函数
$this->db->insert_id();// INSERT 时，返回新插入行的ID

$this->db->affected_rows();//INSERT、UPDATE 时，返回受影响的行数

$this->db->last_query();//该方法返回上一次执行的查询语句

echo $this->db->count_all('my_table');//获取数据表的总行数
事务
$this->db->trans_start();

//sql_1

//sql_2

$this->db->trans_complete();

$this->db->trans_start();

//sql_1

//sql_2

if ($this->db->trans_status() === FALSE)

{

    $this->db->trans_rollback();

}

else

{

    $this->db->trans_commit();

}
相关文章