CI 框架中 AR 操作，ciar
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,100
61 //$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 < 45
104
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 * where
120 * @author zhaoyingnan 2015-10-14 13:31
121 * @param mix $key 傳遞數組的 key 值
122 * @param mix $value 傳遞數組的 key 對應的 value 值
123 * @return object
124 **/
125 public function or_where($key, $value = NULL, $escape = TRUE)
126 {
127 //參考where
128 return $this->_where($key, $value, 'OR ', $escape);
129 }
130
131
132 /**
133 * where_in
134 * @author zhaoyingnan 2015-10-14 13:58
135 * @param string $key 要查詢的列
136 * @param string $values 列的值得范圍
137 * @return object
138 **/
139 public function where_in($key = NULL, $values = NULL)
140 {
141 return $this->_where_in($key, $values);
142 }
143
144 /**
145 * or_where_in
146 * @author zhaoyingnan 2015-10-14 13:58
147 * @param string $key 要查詢的列
148 * @param string $values 列的值得范圍
149 * @return object
150 **/
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_in
158 * @author zhaoyingnan 2015-10-14 13:58
159 * @param string $key 要查詢的列
160 * @param string $values 列的值得范圍
161 * @return object
162 **/
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_in
170 * @author zhaoyingnan 2015-10-14 13:58
171 * @param string $key 要查詢的列
172 * @param string $values 列的值得范圍
173 * @return object
174 **/
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 by
182 * @author zhaoyingnan 2015-10-14 13:35
183 * @param string $orderby 被排序的列
184 * @param string $direction asc 或者 desc
185 * @return object
186 **/
187 public function order_by($orderby, $direction = '')
188 {}
189
190 /**
191 * join
192 * @author zhaoyingnan 2015-10-14 14:07
193 * @param string $table 表名
194 * @param string $cond 條件
195 * @param string $type 指定 JOIN 的類型可選項包括：left, right, outer, inner, left outer, 以及 right outer
196 * @return
197 **/
198 public function join($table, $cond, $type = '')
199 {}
200
201 /**
202 * like
203 * @author zhaoyingnan 2015-10-14 14:28
204 * @param stringi $field 錯作的列
205 * @param mix $match 規則
206 * @param mix $side 通配符（%）位置可用的選項是 'before', 'after' 以及 'both' (這是默認值)
207 * @return object
208 **/
209 public function like($field, $match = '', $side = 'both')
210 {
211 return $this->_like($field, $match, 'AND ', $side);
212 }
213
214 /**
215 * not_like
216 * @author zhaoyingnan 2015-10-14 14:28
217 * @param stringi $field 錯作的列
218 * @param mix $match 規則
219 * @param mix $side 通配符（%）位置可用的選項是 'before', 'after' 以及 'both' (這是默認值)
220 * @return object
221 **/
222 public function not_like($field, $match = '', $side = 'both')
223 {
224 return $this->_like($field, $match, 'AND ', $side, 'NOT');
225 }
226
227 /**
228 * or_like
229 * @author zhaoyingnan 2015-10-14 14:28
230 * @param stringi $field 錯作的列
231 * @param mix $match 規則
232 * @param mix $side 通配符（%）位置可用的選項是 'before', 'after' 以及 'both' (這是默認值)
233 * @return object
234 **/
235 public function or_like($field, $match = '', $side = 'both')
236 {
237 return $this->_like($field, $match, 'OR ', $side);
238 }
239
240 /**
241 * or_not_like
242 * @author zhaoyingnan 2015-10-14 14:28
243 * @param stringi $field 錯作的列
244 * @param mix $match 規則
245 * @param mix $side 通配符（%）位置可用的選項是 'before', 'after' 以及 'both' (這是默認值)
246 * @return object
247 **/
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:52
256 * @param string $table 表名
257 * @param array $set 關聯數組
258 * @return object
259 **/
260 function insert($table = '', $set = NULL)
261 {}
262
263 /**
264 * insert 批量出入
265 * @author zhaoyingnan 2015-10-14 14:52
266 * @param string $table 表名
267 * @param array $set 關聯數組
268 * @return object
269 **/
270 public function insert_batch($table = '', $set = NULL)
271 {}
272
273 /**
274 * update
275 * @author zhaoyingnan 2015-10-14 15:02
276 * @param string $table 表名
277 * @param array $set 修改內容的關聯數組
278 * @param mixed $where where條件
279 * @return object
280 **/
281 public function update($table = '', $set = NULL, $where = NULL, $limit = NULL)
282 {}
283
284 /**
285 * delete
286 * @author zhaoyingnan 2015-10-14 15:12
287 * @param mix $table 表名
288 * @param mixed $where where條件
289 * @return object
290 **/
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 * limit
309 * @author zhaoyingnan 2015-10-14 14:34
310 * @param int $value
311 * @param int $offset
312 * @return object
313 **/
314 public function limit($value, $offset = '')
315 {}
316
317
318
319 /**
320 * Where
321 *
322 * Called by where() or or_where()
323 *
324 * @param mixed
325 * @param mixed
326 * @param string
327 * @return object
328 **/
329 protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL)
330 {}
331
332 /**
333 * Like
334 *
335 * Called by like() or orlike()
336 *
337 * @param mixed
338 * @param mixed
339 * @param string
340 * @return object
341 **/
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 else
370 {
371 $like_statement = $prefix." $k $not LIKE '%{$v}%'";
372 }
373
374 // some platforms require an escape sequence definition for LIKE wildcards
375 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 }
View Code
