php database class suitable for mysql sql service

BaseModel basic Model class and other database table class files are based on this class. The current link is the database mysql of sql service. You only need to modify the query and execute.

<?php
/**
 * 数据库基础类
 * @author ***
 */
abstract class BaseModel
{
    static $model;
    private $sql;
    private $PrimaryKeyField; //主键字段名
    private $field_values;//存放数据库字段数组
    protected $db;
    protected $pk; //主键
    protected $table;
    protected $field = &#39;*&#39;;
    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(), &#39;,&#39;) !== FALSE){
                list($page, $count) = explode(&#39;,&#39;, $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][&#39;total&#39;] : 0;
    }


    /**
     * 根据主键更新
     */
    function updateByPk($fieldList)
    {
        $sql = "UPDATE {$this->getTable()} SET ";
        foreach ($this->getFieldValues() as $key => $one){
            if ($one != NULL){
                $sql .= "$key=&#39;$one&#39;,";
            }
        }
        $sql = rtrim($sql, &#39;,&#39;);
        $sql .= " WHERE {$this->getPrimaryKeyField()}=&#39;{$this->getPk()}&#39;";
        return $this->execute($sql);
    }
    
    /**
     * 根据WHERE更新
     */
    function updateByWhere($fieldList)
    {
        $sql = "UPDATE {$this->getTable()} SET ";
        foreach ($this->getFieldValues() as $key => $one){
            if ($one != NULL){
                $sql .= "$key=&#39;$one&#39;,";
            }
        }
        $sql = rtrim($sql, &#39;,&#39;);
        $sql .= " {$this->getWhere()}";
        return $this->execute($sql);
    }
    
    /**
     * 根据WHERE更新
     */
    function insert($fieldList)
    {
        $sql_values = &#39;&#39;;
        $sql = "INSERT INTO {$this->getTable()} (";
        foreach ($this->getFieldValues() as $key => $one){
            if ($one != NULL){
                $sql .= "$key,";
                $sql_values .= "&#39;$one&#39;,";
            }
        }
        $sql = rtrim($sql, &#39;,&#39;).") VALUES (".rtrim($sql_values, &#39;,&#39;).")";
        return $this->execute($sql);
    }
    
    /**
     * odbc query操作
     */
    private function query($sql)
    {
        $this->setSQL($sql);
        $data = array();
        $sql = iconv(&#39;UTF-8&#39;, &#39;GBK&#39;, $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(&#39;GBK&#39;, &#39;UTF-8&#39;, $one);
                }
                $data[] = $res;
            }
        }
        return $data;
    }
    
    /**
     * odbc execute
     */
    private function execute($sql, $iconv = TRUE)
    {
        $this->setSQL($sql);
        if ($iconv){
            $sql = iconv(&#39;UTF-8&#39;, &#39;GBK&#39;, $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;
    }
}

The Model class of the database table inherits the BaseModel class

<?php
/**
 * 游戏截图类
 * @author ***
 */
class GameImagesModel extends BaseModel
{
    private $field_values = array(
        &#39;game_id&#39; => &#39;&#39;,//key为数据库字段名
        &#39;img_url&#39; => &#39;&#39;,
        &#39;atime&#39; => &#39;&#39;,
        &#39;add_user&#39; => &#39;&#39;,
    );
    
    function __construct() {
        parent::__construct();
        $this->setTable(&#39;game_images&#39;);
        $this->setPrimaryKeyField(&#39;id&#39;);
    }
    
    /**
     * 字段处理函数
     * @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);
    }
}

Instance:

// 对于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));