basemodel 基础model类 其他的数据库表类文件都基础此类 当前链接的是sql service的数据库 mysql的只需要修改query和execute就行
php/** * 数据库基础类 * @author *** */abstract class basemodel{ static $model; private $sql; private $primarykeyfield; //主键字段名 private $field_values;//存放数据库字段数组 protected $db; protected $pk; //主键 protected $table; protected $field = '*'; protected $where; protected $orderby; protected $limit; protected $groupby; /** * 初始化 * * @global array $tmacconfig */ public function __construct() { $this->db = $this->getdb(); } private function getdb() { if(empty(self::$model)){ $mssql = new mssql(db_default_host, db_default_name, db_default_passwd, db_default_database); self::$model = $mssql->getinstance(); } return self::$model; } /** * 设置sql语句 */ private function setsql($sql) { $this->sql = $sql; } /** * 获取sql语句 */ function getsql() { return $this->sql; } /** * 设置field_values */ function setfieldvalues(array $field_values) { $this->field_values = $field_values; } /** * 获取field_values */ private function getfieldvalues() { return $this->field_values; } /** * 设置主键字段名 */ protected function setprimarykeyfield($primarykeyfield) { $this->primarykeyfield = $primarykeyfield; } /** * 获取主键字段名 */ protected function getprimarykeyfield() { return $this->primarykeyfield; } /** * 设置表名 */ protected function settable($table) { $this->table = $table; } /** * 获取表名 */ protected function gettable() { return $this->table; } /** * 设置主键 */ function setpk($pk) { $this->pk = $pk; } /** * 获取主键 */ function getpk() { return $this->pk; } /** * 设置fields */ function setfields($fields) { $this->field = $fields; } /** * 获取fields */ function getfields() { return $this->field; } /** * 设置where条件 */ function setwhere($where) { $this->where = $where; } /** * 获取where条件 */ function getwhere() { return $this->where; } /** * 设置group */ function setgroupby($groupby) { $this->groupby = $groupby; } /** * 获取group */ function getgroupby() { return $this->groupby; } /** * 设置order */ function setorderby($orderby) { $this->orderby = $orderby; } /** * 设置order */ function getorderby() { return $this->orderby; } /** * 设置条数 */ function setlimit( $limit ) { $this->limit = $limit; } /** * 获取条数 */ function getlimit() { return $this->limit; } /** * 根据主键获取 */ function getinfobypk() { $sql = select {$this->getfields()} .from {$this->gettable()} .where {$this->getprimarykeyfield()}={$this->getpk()}; $result = $this->query($sql); if ($result != null){ $result = $result[0]; } return $result; } /** * 根据where条件获取一条信息 */ function getonebywhere() { $sql = select {$this->getfields()} . from {$this->gettable()} . where {$this->getwhere()}; $res = $this->query( $sql ); return $res[0]; } /** * 根据where条件获取数组列表 */ function getlistbywhere() { $sql = select ; if ( $this->getlimit() != null ) { $line_str = $this->getwhere() != null ? and : where ; if (strpos($this->getlimit(), ',') !== false){ list($page, $count) = explode(',', $this->getlimit()); $page_str = $count*($page-1); $sql .= top $count ; } else { $sql .= top {$this->getlimit()} ; } } $sql .= {$this->getfields()} . from {$this->gettable()} ; if ( $this->getwhere() != null ) { $sql .= where {$this->getwhere()} ; } if (isset($page_str) && $page_str != null){ $line_str = $this->getwhere() != null ? and : where ; $sql .= {$line_str} {$this->getprimarykeyfield()} not in (select top $page_str {$this->getprimarykeyfield()} from {$this->gettable()}) ; } if ( $this->getgroupby() != null ) { $sql .= group by {$this->getgroupby()} ; } if ( $this->getorderby() != null ) { $sql .= order by {$this->getorderby()} ; } $res = $this->query( $sql ); return $res; } /** * 根据where获取count */ function getcountbywhere() { $sql_count = select count(*) as total from {$this->gettable()} ; if ( $this->getwhere() != null ) { $sql_count .= where . $this->getwhere(); } $count = $this->query( $sql_count ); return $count != null ? $count[0]['total'] : 0; } /** * 根据主键更新 */ function updatebypk($fieldlist) { $sql = update {$this->gettable()} set ; foreach ($this->getfieldvalues() as $key => $one){ if ($one != null){ $sql .= $key='$one',; } } $sql = rtrim($sql, ','); $sql .= where {$this->getprimarykeyfield()}='{$this->getpk()}'; return $this->execute($sql); } /** * 根据where更新 */ function updatebywhere($fieldlist) { $sql = update {$this->gettable()} set ; foreach ($this->getfieldvalues() as $key => $one){ if ($one != null){ $sql .= $key='$one',; } } $sql = rtrim($sql, ','); $sql .= {$this->getwhere()}; return $this->execute($sql); } /** * 根据where更新 */ function insert($fieldlist) { $sql_values = ''; $sql = insert into {$this->gettable()} (; foreach ($this->getfieldvalues() as $key => $one){ if ($one != null){ $sql .= $key,; $sql_values .= '$one',; } } $sql = rtrim($sql, ',').) values (.rtrim($sql_values, ',').); return $this->execute($sql); } /** * odbc query操作 */ private function query($sql) { $this->setsql($sql); $data = array(); $sql = iconv('utf-8', 'gbk', $sql); if (self::$model == null){ throw new exception(数据库连接失败); } $result = odbc_do(self::$model, $sql); if ($result != null){ while($res = odbc_fetch_array($result)){ foreach ($res as $key => $one){ $res[$key] = iconv('gbk', 'utf-8', $one); } $data[] = $res; } } return $data; } /** * odbc execute */ private function execute($sql, $iconv = true) { $this->setsql($sql); if ($iconv){ $sql = iconv('utf-8', 'gbk', $sql); } if (self::$model == null){ throw new exception(数据库连接失败); } return odbc_exec(self::$model, $sql); } //析构函数，自动关闭数据库,垃圾回收机制 function __destruct() { odbc_close(self::$model); }}// ++++++++++++++++++++++++++++++++++++++++++++++++ // 自定义类库 // mssql 链接类// ++++++++++++++++++++++++++++++++++++++++++++++++//一个普遍通用的php连接mysql数据库类class mssql { static $conn_line; private $db_host; //数据库主机 private $db_user; //数据库用户名 private $db_pwd; //数据库用户名密码 private $db_database; //数据库名 function __construct($db_host, $db_user, $db_pwd, $db_database) { $this->db_host = $db_host; $this->db_user = $db_user; $this->db_pwd = $db_pwd; $this->db_database = $db_database; } /** * 单例模式处理数据库连接 */ function getinstance() { if (empty(self::$conn_line)){ $connstr = driver={sql server};server=.$this->db_host.;database=.$this->db_database; self::$conn_line = odbc_connect($connstr, $this->db_user, $this->db_pwd); } return self::$conn_line; }}
数据库表的model类 继承 basemodel类
php/** * 游戏截图类 * @author *** */class gameimagesmodel extends basemodel{ private $field_values = array( 'game_id' => '',//key为数据库字段名 'img_url' => '', 'atime' => '', 'add_user' => '', ); function __construct() { parent::__construct(); $this->settable('game_images'); $this->setprimarykeyfield('id'); } /** * 字段处理函数 * @param array $field_values */ function setfieldvalues(array $field_values) { foreach ($field_values as $key => $one){ if (!array_key_exists($key, $this->field_values)){ throw new exception($key.不存在);//判断前端传来的数据是否合理 } } parent::setfieldvalues($field_values); }}
实例：
// 对于gamedetail表的操作$detail_model = new gamedetailmodel();// 查询$detail_model->setfields('did');$detail_model->setwhere(1=1);$detail_model->setorderby(did desc);$detail_model->setlimit(1);$insert_id = $detail_model->getlistbywhere();// 更新 data的key需要和model里面设置的对照 也就是数据库的字段$data = array( 'gamename' => $name, 'subject' => $subject, 'grade' => $grade, 'teach' => $teach, 'point' => $point, 'isfree' => $free, 'detail' => $detail, 'addtime' => $_time);$detail_model->setpk($id);$detail_model->updatebypk($detail_model->setfieldvalues($data));// 对于gameimages表的操作// 插入$image_fields = array( 'game_id' => $id, 'img_url' => $image_path, 'atime' => $_time, 'add_user' => $user_id,);$images_model = new gameimagesmodel();$images_model->insert($images_model->setfieldvalues($image_fields));