CI学习笔记！

ci数据库操作

$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'); //可以是这种链式写法


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

echo parse_url($url, PHP_URL_PATH);
//就是在第二个参数中，设定如下的参数：
PHP_URL_PATH
PHP_URL_SCHEME, 
PHP_URL_HOST, 
PHP_URL_PORT, 
PHP_URL_USER,
 PHP_URL_PASS, 
PHP_URL_PATH, 
PHP_URL_QUERY,  
PHP_URL_FRAGMENT 

CI配置多数据库连接方法
1、打开根目录中的config.php文件将下面的代码复制进去

# 数据库
define('DB_TYPE2'   , 'mysqli');
define('DB_HOST2'   , '127.0.0.1');
define('DB_USER2'   , 'root');
define('DB_PASS2'   , '');
define('DB_NAME2'   , 'zch_mallgeelycom');
define('DB_PREFIX2' , 'boc_');

2、打开site/config/database.php 将下面的代码复制进去 注意：前台和后台都有一个database.php 根据需求添加

$db['read']['hostname'] = DB_HOST2;
$db['read']['username'] = DB_USER2;
$db['read']['password'] = DB_PASS2;
$db['read']['database'] = DB_NAME2;
$db['read']['dbdriver'] = DB_TYPE2;
$db['read']['dbprefix'] = DB_PREFIX2;
$db['read']['pconnect'] = TRUE;
$db['read']['db_debug'] = TRUE;
$db['read']['cache_on'] = FALSE;
$db['read']['cachedir'] = '';
$db['read']['char_set'] = 'utf8';
$db['read']['dbcollat'] = 'utf8_general_ci';
$db['read']['swap_pre'] = '';
$db['read']['autoinit'] = TRUE;
$db['read']['stricton'] = FALSE;

3、打开libs/core/MY_Model.php 将下面的代码替换进去
第10行替换成

public function __construct(){
        parent::__construct();

        if (!$this->table) {
            $this->table = strtolower( $this->router->class);
        }
        // TODO: 处理数据表字段 ，判定在create , edit 的数据字段为子集
        $this->db2=$this->load->database('read', TRUE);
    }

4、此时你想要用哪种获取数据方式只要复制一份更改下方法名 和 db名即可
例如：

public function get_list($limit=5,$start=0,$order=false,$where=false,$fields="*",$table=FALSE){
        if (!$table) {
            $table = $this->table;
        }

        $this->db2
            ->select($fields)
            ->from($table)
            ->limit($limit,$start);
        if ($order) {
            if (is_array($order)) {
                foreach ($order as $k => $v){
                    $this->db2->order_by($k,$v);
                }
            }else if(is_string($order)){
                $this->db2->order_by($order);
            }
        }else{
            if ($this->db2->field_exists('sort_id',$table)) {
                $this->db2->order_by('sort_id','desc');
            }else{
                $this->db2->order_by('id','desc');
            }
        }
        if ($where) {
            if (is_string($where)) {
                $where = ' '.$where.' ';
            }elseif (is_array($where)) {
                $this->db2->where($where);
            }
        }
        $query = $this->db2->get();
        return $query->result_array();
    }