Mysql.class.php
<?php
class Mysql{
protected $conn = false; //数据库连接资源
protected $sql; //sql语句
/**
* 构造函数,负责连接服务器、选择数据库、设置字符集等
* @param $config string 配置数组
*/
public function __construct($config = array()){
$host = isset($config['host'])? $config['host'] : 'localhost';
$user = isset($config['user'])? $config['user'] : 'root';
$password = isset($config['password'])? $config['password'] : '';
$dbname = isset($config['dbname'])? $config['dbname'] : '';
$port = isset($config['port'])? $config['port'] : '3306';
$charset = isset($config['charset'])? $config['charset'] : 'utf8';
$this->conn = mysql_connect("$host:$port",$user,$password) or die('数据库连接错误');
mysql_select_db($dbname) or die('数据库选择错误');
$this->setChar($charset);
}
/**
* 设置字符集
* @access private
* @param $charset string 字符集
*/
private function setChar($charest){
$sql = 'set names '.$charest;
$this->query($sql);
}
/**
* 执行sql语句
* @access public
* @param $sql string 查询sql语句
* @return $result,成功返回资源,失败则输出错误信息,并退出
*/
public function query($sql){
$this->sql = $sql;
$result = mysql_query($this->sql,$this->conn);
if (! $result) {
die($this->errno().':'.$this->error().'<br />出错语句为'.$this->sql.'<br />');
}
return $result;
}
/**
* 获取第一条记录的第一个字段
* @access public
* @param $sql string 查询的sql语句
* @return 返回一个该字段的值
*/
public function getOne($sql){
$result = $this->query($sql);
$row = mysql_fetch_row($result);
if ($row) {
return $row[0];
} else {
return false;
}
}
/**
* 获取一条记录
* @access public
* @param $sql 查询的sql语句
* @return array 关联数组
*/
public function getRow($sql){
if ($result = $this->query($sql)) {
$row = mysql_fetch_assoc($result);
return $row;
} else {
return false;
}
}
/**
* 获取所有的记录
* @access public
* @param $sql 执行的sql语句
* @return $list 有所有记录组成的二维数组
*/
public function getAll($sql){
$result = $this->query($sql);
$list = array();
while ($row = mysql_fetch_assoc($result)){
$list[] = $row;
}
return $list;
}
/**
* 获取某一列的值
* @access public
* @param $sql string 执行的sql语句
* @return $list array 返回由该列的值构成的一维数组
*/
public function getCol($sql){
$result = $this->query($sql);
$list = array();
while ($row = mysql_fetch_row($result)) {
$list[] = $row[0];
}
return $list;
}
重点方法:
getOne:获取第一条记录的第一字段的值
/**
* 获取上一步insert操作产生的id
*/
public function getInsertId(){
return mysql_insert_id($this->conn);
}
/**
* 获取错误号
* @access private
* @return 错误号
*/
public function errno(){
return mysql_errno($this->conn);
}
/**
* 获取错误信息
* @access private
* @return 错误private信息
*/
public function error(){
return mysql_error($this->conn);
}
}
?>
Model.class.php
<?php
//模型类基类
class Model{
protected $db; //数据库连接对象
protected $table; //表名
protected $fields = array(); //字段列表
public function __construct($table){
$dbconfig['host'] = $GLOBALS['config']['host'];
$dbconfig['user'] = $GLOBALS['config']['user'];
$dbconfig['password'] = $GLOBALS['config']['password'];
$dbconfig['dbname'] = $GLOBALS['config']['dbname'];
$dbconfig['port'] = $GLOBALS['config']['port'];
$dbconfig['charset'] = $GLOBALS['config']['charset'];
$this->db = new Mysql($dbconfig);
$this->table = $GLOBALS['config']['prefix'] . $table;
//调用getFields字段
$this->getFields();
}
/**
* 获取表字段列表
*
*/
private function getFields(){
$sql = "DESC ". $this->table;
$result = $this->db->getAll($sql);
foreach ($result as $v) {
$this->fields[] = $v['Field'];
if ($v['Key'] == 'PRI') {
//如果存在主键的话,则将其保存到变量$pk中
$pk = $v['Field'];
}
}
//如果存在主键,则将其加入到字段列表fields中
if (isset($pk)) {
$this->fields['pk'] = $pk;
}
}
/**
* 自动插入记录
* @access public
* @param $list array 关联数组
* @return mixed 成功返回插入的id,失败则返回false
*/
public function insert($list){
$field_list = ''; //字段列表字符串
$value_list = ''; //值列表字符串
foreach ($list as $k => $v) {
if (in_array($k, $this->fields)) {
$field_list .= "`".$k."`" . ',';
$value_list .= "'".$v."'" . ',';
}
}
//去除右边的逗号
$field_list = rtrim($field_list,',');
$value_list = rtrim($value_list,',');
//构造sql语句
$sql = "INSERT INTO `{$this->table}` ({$field_list}) VALUES ($value_list)";
if ($this->db->query($sql)) {
# 插入成功,返回最后插入的记录id
return $this->db->getInsertId();
//return true;
} else {
# 插入失败,返回false
return false;
}
}
/**
* 自动更新记录
* @access public
* @param $list array 需要更新的关联数组
* @return mixed 成功返回受影响的记录行数,失败返回false
*/
public function update($list){
$uplist = ''; //更新列表字符串
$where = 0; //更新条件,默认为0
foreach ($list as $k => $v) {
if (in_array($k, $this->fields)) {
if ($k == $this->fields['pk']) {
# 是主键列,构造条件
$where = "`$k`=$v";
} else {
# 非主键列,构造更新列表
$uplist .= "`$k`='$v'".",";
}
}
}
//去除uplist右边的
$uplist = rtrim($uplist,',');
//构造sql语句
$sql = "UPDATE `{$this->table}` SET {$uplist} WHERE {$where}";
if ($this->db->query($sql)) {
# 成功,并判断受影响的记录数
if ($rows = mysql_affected_rows()) {
# 有受影响的记录数
return $rows;
} else {
# 没有受影响的记录数,没有更新操作
return false;
}
} else {
# 失败,返回false
return false;
}
}
/**
* 自动删除
* @access public
* @param $pk mixed 可以为一个整型,也可以为数组
* @return mixed 成功返回删除的记录数,失败则返回false
*/
public function delete($pk){
$where = 0; //条件字符串
//判断$pk是数组还是单值,然后构造相应的条件
if (is_array($pk)) {
# 数组
$where = "`{$this->fields['pk']}` in (".implode(',', $pk).")";
} else {
# 单值
$where = "`{$this->fields['pk']}`=$pk";
}
//构造sql语句
$sql = "DELETE FROM `{$this->table}` WHERE $where";
if ($this->db->query($sql)) {
# 成功,并判断受影响的记录数
if ($rows = mysql_affected_rows()) {
# 有受影响的记录
return $rows;
} else {
# 没有受影响的记录
return false;
}
} else {
# 失败返回false
return false;
}
}
/**
* 通过主键获取信息
* @param $pk int 主键值
* @return array 单条记录
*/
public function selectByPk($pk){
$sql = "select * from `{$this->table}` where `{$this->fields['pk']}`=$pk";
return $this->db->getRow($sql);
}
/**
* 获取总的记录数
* @param string $where 查询条件,如"id=1"
* @return number 返回查询的记录数
*/
public function total($where){
if(empty($where)){
$sql = "select count(*) from {$this->table}";
}else{
$sql = "select count(*) from {$this->table} where $where";
}
return $this->db->getOne($sql);
}
/**
* 分页获取信息
* @param $offset int 偏移量
* @param $limit int 每次取记录的条数
* @param $where string where条件,默认为空
*/
public function pageRows($offset, $limit,$where = ''){
if (empty($where)){
$sql = "select * from {$this->table} limit $offset, $limit";
} else {
$sql = "select * from {$this->table} where $where limit $offset, $limit";
}
return $this->db->getAll($sql);
}
}?>
如图所示
第三步 在模型层定义sql查询语句函数(AdminModel.class.php)
<?php
//后台管理员模型
class AdminModel extends Model{
//获取所有的管理员
public function getAdmins(){
$sql="SELECT*FROM {$this->table}";
//print_r($sql);
return $this->db->getAll($sql);
}
}
?>
如图所示
我们需要在控制层来执行这条语句
<?php
// 后台首页控制器
class IndexController extends Controller
{
public function indexAction()
{
include CUR_VIEW_PATH . 'index.html';
}
public function topAction()
{
include CUR_VIEW_PATH . 'top.html';
}
public function menuAction()
{
include CUR_VIEW_PATH . 'menu.html';
}
public function dragAction()
{
include CUR_VIEW_PATH . 'drag.html';
}
public function mainAction()
{
//实例化模型类
$adminModel=new AdminModel('admin');
$admins=$adminModel->getAdmins();
echo "<pre>";
var_dump($admins);
include CUR_VIEW_PATH . 'main.html';
}
}
?>
如图所示:
但这里有一个问题:就是需要配置数据库文件:config.php
<?php
//项目配置信息
return array(
'host'=>'localhost',
'user'=>'root',
'password'=>'root',
'dbname'=>'php39',
'port'=>'3306',
'charset'=>'utf8',
'prefix'=>'cz_'
);
?>
如图所示:
但还需要在Framework.class.php中载入配置文件
//载入配置文件
$GLOBALS['config']=include CONFIG_PATH."config.php";
//载入核心文件
include CORE_PATH.'Controller.class.php';
include CORE_PATH.'Model.class.php';
include DB_PATH.'Mysql.class.php';
整体代码如下:
<?php
//核心启动类
class Framework{
public static function run(){
// echo "hello,wrold!";
self::init();
self::autoload();
self::dispatch();
}
//初始化方法
private static function init(){
//定义路径常量
define("DS",DIRECTORY_SEPARATOR);
define("ROOT",getcwd().DS);
define("APP_PATH",ROOT."application".DS);
define("FRAMEWORK_PATH",ROOT."framework".DS);
define("PUBLIC_PATH",ROOT.'public'.DS);
define("CONFIG_PATH",APP_PATH."config".DS);
define("CONTROLLER_PATH",APP_PATH."controllers".DS);
define("MODEL_PATH",APP_PATH."models".DS);
define("VIEW_PATH",APP_PATH."views".DS);
define("CORE_PATH",FRAMEWORK_PATH."core".DS);
define("DB_PATH",FRAMEWORK_PATH."databases".DS);
define("LIB_PATH",FRAMEWORK_PATH."libraries".DS);
define("HELPER_PATH",FRAMEWORK_PATH."helpers".DS);
define("UPLOAD_PATH",PUBLIC_PATH."uploads".DS);
//获取参数p ,c,a
define('PLATFORM', isset($_GET['p'])?$_GET['p']:"admin");
define('CONTROLLER',isset($_GET['c'])?ucfirst($_GET['c']):"Index");
define('ACTION',isset($_GET['a'])?$_GET['a']:"index");
//设置当前控制器和视图
define("CUR_CONTROLLER_PATH",CONTROLLER_PATH.PLATFORM.DS);
define("CUR_VIEW_PATH",VIEW_PATH.PLATFORM.DS);
//echo CONFIG_PATH;
//载入配置文件
$GLOBALS['config']=include CONFIG_PATH."config.php";
//载入核心文件
include CORE_PATH.'Controller.class.php';
include CORE_PATH.'Model.class.php';
include DB_PATH.'Mysql.class.php';
}
//路由方法
private static function dispatch(){
//获取控制器名称
$controller_name=CONTROLLER."Controller";
//获取方法名
$action_name=ACTION."Action";
//实例化控制器对象
$controller=new $controller_name();
//调用方法
$controller->$action_name();
}
//注册为自动加载
private static function autoload(){
$arr=array(__CLASS__,'load');
//__CLASS__获取当前的类名
spl_autoload_register($arr);
}
//自动加载功能,实现控制器和数据库模型
//GoodsController GoodsModel
private static function load($classname){
if(substr($classname,-10)=="Controller"){
include CUR_CONTROLLER_PATH."{$classname}.class.php";
}elseif (substr($classname, -5)=='Model'){
//载入数据库模型
include MODEL_PATH."{$classname}.class.php";
}else{
}
}
}
?>