CI框架中AR操作

2019-11-14 14:44:24

字体：大中小

来源：转载

供稿：网友

Model 层中的部分代码

 1     /** 2      * CI 中的 AR 操作 3      * @author    zhaoyingnan 4      **/ 5     public function mAR() 6     { 7         /*************** 查询 *************/ 8         //select * from mp4ba limit 21,10; 9         //$objResult    =    $this->db->get('mp4ba', 10, 21);10         //echo $this->db->last_query();die;11 12 13         //select * from mp4ba where id =32 limit 21,10;14         //select * from mp4ba where id =32 and name = '刺客聂隐娘'limit 21,10;15         //$objResult    =    $this->db->get_where('mp4ba', array('id'=>32), 10, 21);16         //echo $this->db->last_query();die;17         //$objResult    =    $this->db->get_where('mp4ba', array('id'=>32,'name'=>'刺客聂隐娘'), 10, 21);18         //echo $this->db->last_query();die;19 20 21         //select id,name,url from mp4ba where id =32;22         //$objResult    =    $this->db->select('id,name,url')->get_where('mp4ba', array('id'=>32));23         //echo $this->db->last_query();die;24 25         //select id,name,url from mp4ba where id =32 or id=39;26         //$objResult    =    $this->db->select('id,name,url')->where(array('id'=>32))->or_where(array('id'=>39))->get('mp4ba');27         //echo $this->db->last_query();die;28         29 30         //select id,name,url from mp4ba where id in(33,44,55);31         //select id,name,url from mp4ba where id in(33,44,55) or sort_id in (3,4);32         //select id,name,url from mp4ba where id not in(33,44,55);33         //$objResult    =    $this->db->select('id,name,url')->where_in('id', array(33,44,55))->get('mp4ba');34         //$objResult    =    $this->db->select('id,name,url')->where_in('id', array(33,44,55))->or_where_in('sort_id', array(3,4))->get('mp4ba');35         //$objResult    =    $this->db->select('id,name,url')->where_not_in('id', array(33,44,55))->get('mp4ba');36         //echo $this->db->last_query();die;37 38         //select id,name,url from mp4ba join user on (mp4ba.uid=user.id) order by mp4ba.dateline desc;39         //$objResult    =    $this->db->select('id,name,url')->from('mp4ba')->join('user', 'mp4ba.uid = user.id')->order_by('mp4ba.dateline', 'desc')->get();40         //echo $this->last_query();die;41 42 43         //select * from mp4ba where name like '%刺客%';44         //select * from mp4ba where name not like '%刺客%';45         //select * from mp4ba where name like '%刺客%' or url like 'eqfdf%';46         //$objResult    =    $this->db->like('name', '刺客')->get('mp4ba');47         //$objResult    =    $this->db->not_like('name', '刺客')->get('mp4ba');48         //$objResult    =    $this->db->like('name', '刺客')->or_like('url', 'eqfdf', 'after')->get('mp4ba');49         //echo $this->db->last_query();die;50 51 52 53         //select max(id) from mp4ba where name = '刺客聂隐娘';54         //select min(id) from mp4ba where name = '刺客聂隐娘';55         //$objResult    =    $this->db->select_max('id')->get_where('mp4ba', array('name'=>'刺客聂隐娘'));56         //echo $this->db->last_query();die;57         //$objResult    =    $this->db->select_min('id')->get_where('mp4ba', array('name'=>'刺客聂隐娘'));58         //echo $this->db->last_query();die;59 60         //SELECT id,sort_id,menu,name FROM mp4ba WHERE id > 3 ORDER BY `dateline` desc LIMIT 10,10061         //$objResult    =    $this->db->select('id,sort_id,menu,name')->from('mp4ba')->where('id >', 3)->order_by('dateline desc')->limit(100,10)->get();62         //echo $this->db->last_query();63         //return $objResult->result();64 65 66         /*************** 插入 *************/67         //生成一条基于你所提供的数据的SQL插入字符串并执行查询。你可以向函数传递 数组 或一个 对象。下面是一个使用数组的例子：68         $arInsert    =    array(69             'name'        =>    '小黄人',70             'url'        =>    'www.test.com',71             'sort_id'    =>    1,72             'menu'        =>    '动画片'73         );74         //$this->db->insert('mp4ba', $arInsert);75         //echo $this->db->insert_id();die;76 77 78         /*************** 修改 *************/79         $arData    =    array(80             'name'        =>    '小黄人,好玩嘛',81             'url'        =>    'www.test_xiaohuangren.com',82             'sort_id'    =>    1,83             'menu'        =>    '动画片'84         );85         //$this->db->update('mp4ba', $arData, array('id'=>3498));86         //echo $this->db->affected_rows();    #受影响的行数87         //echo '<br/>';88         //$objResult    =    $this->db->where(array('id'=>3498))->get('mp4ba');89         //formatOut($objResult->result());die;90         91         /*************** 删除 *************/92         $this->db->delete('mp4ba', array('id'=>3498));93         echo $this->db->affected_rows();    #受影响的行数94     }

CI 中 DB_active_rec.php 该类中的部分方法的标注（会持续补充）

  1 <?php  2 class CI_DB_active_record  3 {  4     /**  5      * get  6      * @author    zhaoyingnan 2015-10-14 12:50  7      * @param    string        $table    操作的表  8      * @param    int            $limit    limit 值  9      * @param    int            $offset    offset 值 10      * @return    object 11      **/ 12     public function get($table = '', $limit = null, $offset = null) 13     {} 14  15     /** 16      * get_where 17      * @author    zhaoyingnan    2015-10-14 12:58 18      * @param    string        $table    操作的表 19      * @param    array        $where    where 子句 20      * @param    int            $limit    limit 值 21      * @param    int            $offset    offset 值 22      * @return    object 23      **/ 24     public function get_where($table = '', $where = null, $limit = null, $offset = null) 25     {} 26  27     /** 28      * select 29      * @author    zhaoyingnan    2015-10-14 13:13 30      * @param    string        $select    查询的字段，用逗号隔开 31      * @param    boolean        $escape    如果你把它设为FALSE， CodeIgniter 将不会使用反引号保护你的字段或者表名 。这在进行复合查询时很有用。 32      * @return    object 33      **/ 34     public function select($select = '*', $escape = NULL) 35     {} 36  37     /** 38      * SELECT MAX(field) portion of a query* @description     39      * @author    zhaoyingnan    2015-10-14 13:20 40      * @param    string        $select    max(field)作用列 41      * @param    string        $alias    别名 42      * @return    object 43      **/ 44     public function select_max($select = '', $alias = '') 45     {} 46  47     /** 48      * SELECT MIN(field) portion of a query 49      * @author    zhaoyingnan    2015-10-14 13:20 50      * @param    string        $select    min(field)作用列 51      * @param    string        $alias    别名 52      * @return    object 53      **/ 54     public function select_min($select = '', $alias = '') 55     {} 56  57     /** 58      * SELECT AVG(field) portion of a query 59      * @author    zhaoyingnan    2015-10-14 13:20 60      * @param    string        $select    AVG(field)作用列 61      * @param    string        $alias    别名 62      * @return    object 63      **/ 64     public function select_avg($select = '', $alias = '') 65     {} 66  67     /** 68      * SELECT SUM(field) portion of a query 69      * @author    zhaoyingnan    2015-10-14 13:20 70      * @param    string        $select    SUM(field)作用列 71      * @param    string        $alias    别名 72      * @return    object 73      **/ 74     public function select_sum($select = '', $alias = '') 75     {} 76  77  78     /** 79      * @description     80      * @author    zhaoyingnan    2015-10-14 13:26 81      * @param    string        $from    表名 82      * @return    object 83      **/ 84     public function from($from) 85     {} 86  87     /** 88      * where 89      * @author    zhaoyingnan    2015-10-14 13:31 90      * @param    mix        $key    传递数组的 key 值 91      * @param    mix        $value    传递数组的 key 对应的 value 值 92      * @return    object 93      **/ 94     public function where($key, $value = NULL, $escape = TRUE) 95     { 96         //1,简单的 key/value 方法: 97         //$this->db->where('name', $name);  98         //生成: WHERE name = 'Joe' 99 100         //2.自定义 key/value 方法:101         //$this->db->where('name !=', $name);102         //$this->db->where('id <', $id);103         //生成: WHERE name != 'Joe' AND id < 45104 105         //3.关联数组方法:106         //$array = array('name' => $name, 'title' => $title, 'status' => $status);107         //$this->db->where($array); 108         //生成: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'109         //使用这个方法时你也可以包含运算符:110         //$array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);111 112         //4.自定义字符串:113         //$where = "name='Joe' AND status='boss' OR status='active'";114         //$this->db->where($where);115         return $this->_where($key, $value, 'AND ', $escape);116     }117 118     /**119      * where120      * @author    zhaoyingnan    2015-10-14 13:31121      * @param    mix        $key    传递数组的 key 值122      * @param    mix        $value    传递数组的 key 对应的 value 值123      * @return    object124      **/125     public function or_where($key, $value = NULL, $escape = TRUE)126     {127         //参考where128         return $this->_where($key, $value, 'OR ', $escape);129     }130 131 132     /**133      * where_in134      * @author    zhaoyingnan    2015-10-14 13:58135      * @param    string        $key    要查询的列136      * @param    string        $values    列的值得范围137      * @return    object138      **/139     public function where_in($key = NULL, $values = NULL)140     {141         return $this->_where_in($key, $values);142     }143 144     /**145      * or_where_in146      * @author    zhaoyingnan    2015-10-14 13:58147      * @param    string        $key    要查询的列148      * @param    string        $values    列的值得范围149      * @return    object150      **/151     public function or_where_in($key = NULL, $values = NULL)152     {153         return $this->_where_in($key, $values, FALSE, 'OR ');154     }155 156     /**157      * where_not_in158      * @author    zhaoyingnan    2015-10-14 13:58159      * @param    string        $key    要查询的列160      * @param    string        $values    列的值得范围161      * @return    object162      **/163     public function where_not_in($key = NULL, $values = NULL)164     {165         return $this->_where_in($key, $values, TRUE);166     }167 168     /**169      * or_where_not_in170      * @author    zhaoyingnan    2015-10-14 13:58171      * @param    string        $key    要查询的列172      * @param    string        $values    列的值得范围173      * @return    object174      **/175     public function or_where_not_in($key = NULL, $values = NULL)176     {177         return $this->_where_in($key, $values, TRUE, 'OR ');178     }179 180     /**181      * order by182      * @author    zhaoyingnan    2015-10-14 13:35183      * @param    string        $orderby    被排序的列184      * @param    string        $direction    asc 或者 desc185      * @return    object186      **/187     public function order_by($orderby, $direction = '')188     {}189 190     /**191      * join192      * @author    zhaoyingnan    2015-10-14 14:07193      * @param    string        $table    表名194      * @param    string        $cond    条件195      * @param    string        $type    指定 JOIN 的类型可选项包括：left, right, outer, inner, left outer, 以及 right outer196      * @return    197      **/198     public function join($table, $cond, $type = '')199     {}200 201     /**202      * like203      * @author    zhaoyingnan    2015-10-14 14:28204      * @param    stringi        $field    错作的列205      * @param    mix            $match    规则206      * @param    mix            $side    通配符（%）位置可用的选项是 'before', 'after' 以及 'both' (这是默认值)207      * @return    object208      **/209     public function like($field, $match = '', $side = 'both')210     {211         return $this->_like($field, $match, 'AND ', $side);212     }213 214     /**215      * not_like216      * @author    zhaoyingnan    2015-10-14 14:28217      * @param    stringi        $field    错作的列218      * @param    mix            $match    规则219      * @param    mix            $side    通配符（%）位置可用的选项是 'before', 'after' 以及 'both' (这是默认值)220      * @return    object221      **/222     public function not_like($field, $match = '', $side = 'both')223     {224         return $this->_like($field, $match, 'AND ', $side, 'NOT');225     }226 227     /**228      * or_like229      * @author    zhaoyingnan    2015-10-14 14:28230      * @param    stringi        $field    错作的列231      * @param    mix            $match    规则232      * @param    mix            $side    通配符（%）位置可用的选项是 'before', 'after' 以及 'both' (这是默认值)233      * @return    object234      **/235     public function or_like($field, $match = '', $side = 'both')236     {237         return $this->_like($field, $match, 'OR ', $side);238     }239 240     /**241      * or_not_like242      * @author    zhaoyingnan    2015-10-14 14:28243      * @param    stringi        $field    错作的列244      * @param    mix            $match    规则245      * @param    mix            $side    通配符（%）位置可用的选项是 'before', 'after' 以及 'both' (这是默认值)246      * @return    object247      **/248     public function or_not_like($field, $match = '', $side = 'both')249     {250         return $this->_like($field, $match, 'OR ', $side, 'NOT');251     }252 253     /**254      * insert 单条插入255      * @author    zhaoyingnan    2015-10-14 14:52256      * @param    string        $table    表名257      * @param    array        $set    关联数组258      * @return    object259      **/260     function insert($table = '', $set = NULL)261     {}262 263     /**264      * insert 批量出入265      * @author    zhaoyingnan    2015-10-14 14:52266      * @param    string        $table    表名267      * @param    array        $set    关联数组268      * @return    object269      **/270     public function insert_batch($table = '', $set = NULL)271     {}272 273     /**274      * update275      * @author    zhaoyingnan    2015-10-14 15:02276      * @param    string        $table    表名277      * @param    array        $set    修改内容的关联数组278      * @param    mixed        $where    where条件279      * @return    object280      **/281     public function update($table = '', $set = NULL, $where = NULL, $limit = NULL)282     {}283 284     /**285      * delete286      * @author    zhaoyingnan    2015-10-14 15:12287      * @param    mix            $table    表名288      * @param    mixed        $where    where条件289      * @return    object290      **/291     public function delete($table = '', $where = '', $limit = NULL, $reset_data = TRUE)292     {293         //第一个参数是表名，第二个参数是where子句。你可以不传递第二个参数，使用 where() 或者 or_where() 函数来替代它:294         //$this->db->where('id', $id);295         //$this->db->delete('mytable'); 296 297 298         //如果你想要从一个以上的表中删除数据，你可以将一个包含了多个表名的数组传递给delete()函数。299         //$tables = array('table1', 'table2', 'table3');300         //$this->db->where('id', '5');301         //$this->db->delete($tables);302 303 304         //如果你想要删除表中的全部数据，你可以使用 truncate() 函数，或者 empty_table() 函数。305     }306 307     /**308      * limit309      * @author    zhaoyingnan    2015-10-14 14:34310      * @param    int            $value311      * @param    int            $offset312      * @return    object313      **/314     public function limit($value, $offset = '')315     {}316 317 318 319     /**320      * Where321      *322      * Called by where() or or_where()323      *324      * @param    mixed325      * @param    mixed326      * @param    string327      * @return    object328      **/329     PRotected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL)330     {}331 332     /**333      * Like334      *335      * Called by like() or orlike()336      *337      * @param    mixed338      * @param    mixed339      * @param    string340      * @return    object341      **/342     protected function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '')343     {344         if ( ! is_array($field))345         {346             $field = array($field => $match);347         }348 349         foreach ($field as $k => $v)350         {351             $k = $this->_protect_identifiers($k);352 353             $prefix = (count($this->ar_like) == 0) ? '' : $type;354 355             $v = $this->escape_like_str($v);356 357             if ($side == 'none')358             {359                 $like_statement = $prefix." $k $not LIKE '{$v}'";360             }361             elseif ($side == 'before')362             {363                 $like_statement = $prefix." $k $not LIKE '%{$v}'";364             }365             elseif ($side == 'after')366             {367                 $like_statement = $prefix." $k $not LIKE '{$v}%'";368             }369             else370             {371                 $like_statement = $prefix." $k $not LIKE '%{$v}%'";372             }373 374             // some platforms require an escape sequence definition for LIKE wildcards375             if ($this->_like_escape_str != '')376             {377                 $like_statement = $like_statement.sprintf($this->_like_escape_str, $this->_like_escape_chr);378             }379 380             $this->ar_like[] = $like_statement;381             if ($this->ar_caching === TRUE)382             {383                 $this->ar_cache_like[] = $like_statement;384                 $this->ar_cache_exists[] = 'like';385             }386 387         }388         return $this;389     }390 391 }