封装了MySQL链接以及操做数据库经常使用方法等等,其余简单封装,有点strange...。php
一、MySQL链接mysql
1.一、配置文件,建立 config.phpsql
//项目配置文件
return $config = array(
'host' => '127.0.0.1',
'user' => 'root',
'password' => 'root',
'dbname' => 'shopcz',
'port' => '3306',
'charset' => 'utf8',
'prefix' => 'cz_',
'log' => 'true'
);
1.二、MySQL类 ,建立 Mysql.class.php数据库
include "config.php";
class Mysql{
protected $conn = false; //数据库链接资源
protected $sql; //sql语句
/**
* 构造函数,负责链接服务器、选择数据库、设置字符集等
* @param $config string 配置数组
*/
public function __construct(){
$host = isset($GLOBALS['config']['host'])?$GLOBALS['config']['host'] : 'localhost';
$port = isset($GLOBALS['config']['port'])? $GLOBALS['config']['port'] : '3306';
$user = isset($GLOBALS['config']['user'])? $GLOBALS['config']['user'] : 'root';
$password = isset($GLOBALS['config']['password'])? $GLOBALS['config']['password'] : '';
$dbname = isset($GLOBALS['config']['dbname'])? $GLOBALS['config']['dbname'] : '';
$charset = isset($GLOBALS['config']['charset'])? $GLOBALS['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语句,打印log信息到log.txt
* @access public
* @param $sql string 查询sql语句
* @return $result,成功返回资源,失败则输出错误信息,并退出
*/
public function query($sql){
//写日志
if ($GLOBALS['config']['log']) {
$str = "[" . date("Y-m-d H:i:s") . "] ". $sql . PHP_EOL; //常量PHP_EOL:'\n'
file_put_contents("log.txt", $str,FILE_APPEND); //写入文件追加到最后
}
$this->sql = $sql;
$result = mysql_query($this->sql,$this->conn);
//错误信息
if (! $result) {
die($this->errno().':'.$this->error().'
出错语句为'.$this->sql.'
');
}
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;
}
/**
* 获取上一步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数组
include "Mysql.class.php";
//模型类基类
class Model{
protected $db; //数据库链接对象
protected $table; //表名
protected $fields = array(); //字段列表
public function __construct($table){
$this->db = new Mysql();
$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);
}
}
三、模型类,建立 UserModel.class.php服务器
include "Model.class.php";
class UserModel extends Model{
//获取全部用户
public function getUsers(){
$sql = "SELECT * FROM {$this->table}";
return $users = $this->db->getAll($sql);
}
//获取查询用户
public function findUserByUsername($username, $password){
$sql = "SELECT * FROM {$this->table} where admin_name = '{$username}' and password = '{$password}'";
return $user = $this->db->getAll($sql);
}
}
四、控制层类,建立 UserController.php函数
include "UserModel.class.php";
class UserController {
//查询
public function findAll(){
$UserModel = new UserModel('admin');
return $users = $UserModel->getusers();
}
//查询
public function login($username, $password){
$UserModel = new UserModel('admin');
return $users = $UserModel->findUserByUsername($username, $password);
}
}
五、测试,建立 test.php测试
include "UserController.php";
/**
* 测试
*/
$username = 'sa';
$password = '123';
$UserController = new UserController();
$result = $UserController->login($username, $password);
echo '查询数据数量:'.count($result);
结果截图:fetch
mysql数据库就建立一张表:this
数据库名:shopcz
数据库字符集:utf8
数据库校对规则:utf8_general_ci
表名:cz_admin
admin表字段:admin_id,admin_name,password
admin表数据就一条:1,sa,123
以上就是我从一个例子源代码里扣出来的部分代码并改动过(不算 MVC了吧)...
仅供参考,其余具体待研究。