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