php database class suitable for mysql sql service

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; } }

 '',//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));