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