/#/lib/class.model.php

  1<?php
  2
  3class BaseModel{
  4
  5    protected $table;
  6
  7    protected $db = null;
  8
  9    /**
 10     * 实例化数据库连接
 11     * */
 12    public function __construct() {
 13        $this->db = PdoBackend::get_instance();
 14        $this->table = $this->get_table($this->table);
 15    }
 16
 17    /**
 18     * 插入
 19     * @param array $data
 20     * @return int last-insert-id
 21     * */
 22    public function insert($data) {
 23        $sql = "INSERT INTO {$this->table}(%s)VALUES(%s)";
 24
 25        $keys = array_keys($data);
 26        $values_bind = array();
 27        foreach($keys as $v) {
 28            $values_bind[$v] = ":".$v;
 29        }
 30        $sql = sprintf($sql, implode(',', $keys), implode(',', $values_bind));
 31        $binds = array();
 32
 33        foreach($values_bind as $k=>$v) {
 34            $binds[$v] = $data[$k];
 35        }
 36
 37        $stmt = $this->db->prepare($sql);
 38        $rs = $stmt->execute($binds);
 39
 40        return $this->db->lastInsertId();
 41    }
 42
 43    /**
 44     * 编辑
 45     * @param int $id
 46     * @param array $data
 47     * @return boolean
 48     * */
 49    public function edit($id, $data) {
 50        $sql = "UPDATE {$this->table} SET %s WHERE id=%d";
 51
 52        foreach($data as $k=>$v) {
 53            $sql_placeholder[] = sprintf("%s=:%s", $k, $k);
 54            $values_bind[":".$k] = $v;
 55        }
 56        $sql = sprintf($sql, implode(",", $sql_placeholder), $id);
 57        $stmt = $this->db->prepare($sql);
 58        $rs = $stmt->execute($values_bind);
 59
 60        return $this->db->lastInsertId();
 61    }
 62
 63    /**
 64     * 多条件更新
 65     * */
 66    public function update($condition, $data) {
 67        $condition = $this->get_condition($condition);
 68        foreach($data as $k=>$v) {
 69            if(!$k) {
 70                continue;
 71            }
 72            $update[] = sprintf('%s="%s"', $k, $v);
 73        }
 74
 75        if($update) {
 76            $sql = sprintf("UPDATE {$this->table} SET %s {$condition}", implode(",", $update));
 77            $this->db->query($sql);
 78            return $this->db->lastInsertId();
 79        }
 80
 81        return true;
 82    }
 83
 84    /**
 85     * 根据单一条件删除
 86     * */
 87    public function delete($value, $field="id") {
 88        $sql = "DELETE FROM {$this->table} WHERE {$field}=:value";
 89        $stmt = $this->db->prepare($sql);
 90        return $stmt->execute(array(":value"=> $value));
 91    }
 92
 93    /**
 94     * 执行插入/编辑
 95     * */
 96    public function replace($data, $id = 'null', $primary_key = "id") {
 97        if(!$data[$primary_key] and false !== $id) {
 98            $data[$primary_key] = $id;
 99        }
100        $sql = "REPLACE INTO {$this->table}(%s)VALUES(%s)";
101        $keys = array_keys($data);
102        $values_bind = array();
103        foreach($keys as $v) {
104            $values_bind[$v] = ":".$v;
105        }
106        $sql = sprintf($sql, implode(',', $keys), implode(',', $values_bind));
107        $binds = array();
108
109        foreach($values_bind as $k=>$v) {
110            $binds[$v] = $data[$k];
111        }
112
113        $stmt = $this->db->prepare($sql);
114        $rs = $stmt->execute($binds);
115
116        return $this->db->lastInsertId();
117    }
118
119    /**
120     * 根据某个单一字段条件获取一条或者多条数据
121     * @param string $field
122     * @param mixed $value
123     * @param boolean $getone
124     * @param boolean | integer $cache 是否缓存/缓存存活时间
125     * @return array
126     * */
127    public function get_by($value, $field="id", $getone = true) {
128        $sql = "SELECT * FROM `{$this->table}` WHERE %s = '%s'";
129        $sql = sprintf($sql, $field, $value);
130        
131        $method = $getone ? 'fetch' : 'fetchAll';
132        $result = $this->db->query($sql);
133        if($result) {
134            $result = $result->$method(PDO::FETCH_ASSOC);
135        }
136        return $result;
137    }
138
139    /**
140     * 获取列表
141     * @param array $condition
142     * @param string $order
143     * */
144    public function get_list($condition=array(), $order="id DESC", $table = '') {
145
146        $table = $table ? $this->get_table($table) : $this->table;
147        
148        $condition_str = $this->get_condition($condition);
149
150        $this->sql = sprintf("SELECT * FROM {$table} %s", $condition_str);
151        if($order) {
152            $sql.= " ORDER BY ".$order;
153        }
154        
155        if($rs = $this->db->query($this->sql)) {
156            return $rs->fetchAll(PDO::FETCH_ASSOC);
157        }
158        
159        return array();
160    }
161    
162    /**
163     * 获取分页
164     * @param array $condition
165     * @param string $order
166     * @param integer $size 每页显示的数量
167     */
168    public function get_page_list($condition=array(), $oder="id DESC", $size=20) {
169        $condition = $this->get_condition($condition);
170        $sql = sprintf("SELECT * FROM {$this->table} %s", $condition);
171        if($order) {
172            $sql.= " ORDER BY ".$order;
173        }
174        
175        $total = $this->count($condition);
176        
177        import("lib/class.paginator");
178        $p = new Paginator($this->db, $_GET["page"], $size);
179        return $p->work($sql, $total);
180    }
181
182    /**
183     * 返回表名
184     * */
185    public function get_table($table = null) {
186        if(!$table) {
187            return $this->table;
188        }
189        global $C;
190        return $C["database"]["pre"].$table;
191    }
192
193    /**
194     * 根据数组获取SQL WHERE
195     * */
196    public function get_condition($condition = array()) {
197        $operation = array(
198            "!" => "!=",
199            ">" => ">",
200            "<" => "<",
201            "#" => ">=",
202            "$" => "<=",
203            "^" => " IN "
204        );
205
206        $condition_str = " WHERE TRUE";
207
208        if($condition) {
209            foreach($condition as $k=>$v) {
210                if(!$k) {
211                    continue;
212                }
213                $split = "=";
214                if(is_array($v)) {
215                    foreach($v as $_k=>$_v) {
216                        if(!$_k) {
217                            continue;
218                        }
219                        $_split = "=";
220                        if(key_exists($_v{0}, $operation)) {
221                            $_split = $operation[$_v{0}];
222                            $_v = substr($_v, 1, strlen($_v));
223                        }
224                        $sub_conditions[] = sprintf("%s%s'%s'", $k, $_split, $_v);
225                    }
226                } else {
227                    if(key_exists($v{0}, $operation)) {
228                        $split = $operation[$v{0}];
229                        $v = substr($v, 1, strlen($v));
230                    }
231                    $conditions[] = sprintf("%s%s'%s'", $k, $split, $v);
232                }
233            }
234
235            $condition_str = '';
236            if($sub_conditions) {
237                $conditions[] = '('.implode(' OR ', $sub_conditions).')';
238            }
239
240            if($conditions) {
241                $condition_str = ' WHERE '.implode(" AND ", $conditions);
242            }
243        }
244
245        $condition_str = str_replace(
246            array("'(", ")'"),
247            array("(", ")"),
248            $condition_str
249        );
250
251        return $condition_str;
252    }
253
254    public function count($condition = "", $field = "id", $table = "") {
255        $condition = is_array($condition) ? $this->get_condition($condition) : $condition;
256        
257        if($table) {
258            global $C;
259            if(false === strpos($table, $C["database"]["pre"])) {
260                $table = $this->get_table($table);
261            } else {
262                $table = $table;
263            }
264        } else {
265            $table = $this->table;
266        }
267        
268        $sql = sprintf("SELECT COUNT(%s) AS count FROM {$table} %s", $field, $condition);
269        $this->sql = $sql;
270        $rs = $this->db->query($sql);
271        if(!$rs) {
272            return 0;
273        }
274
275        $count = $rs->fetch(PDO::FETCH_ASSOC);
276        return $count['count'];
277    }
278
279}