php 数据库类 适用于 mysql sql service

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