PHP高级 数据库处理类 第34天

2012-11-16 12:48:05 0  category: 第二阶段php

表单配置


<form action="userModel.class.php" method="post">
<input type="text" name="username" />
<input type="password" name="password" />
<input type="text" name="createtime" />
<input type="text" name="tx">
<input type="submit" value="修改"/>
</form>


数据库配置 config.php


<?php

define('DB_HOST','localhost');

define('DB_USER','root');

define('DB_NAME','37demo');

define('DB_PWD','phpha');


define('DB_CHARSET','utf8');

define('DB_PREFIX','phpemo_');


?>


数据库处理 Model.class.php


<?php
include 'config.php';
class Model{
//用户名
protected $user;
//密码
protected $pwd;
//主机
protected $host;
//库名,是一个数组
protected $dbName=array();
//字符集
protected $charset='utf8';
//连接资源是一个数组
protected $_link=array();
//通用表名
protected $tabName;
//真实表名
protected $trueTabName;
//表前缀
protected $prefix;
//字段缓存
protected $fields;
//创建表的sql语句
protected $createSql='CREATE TABLE IF NOT EXISTS __TABLENAME__(
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`username` char(15) NOT NULL,
`password` char(32) NOT NULL,
`createtime` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;';

//1,通过ID取余,得到真实表名 mod
//2,用户名截取前几位 substr
//3,md5 md5
//4,不带分库分表 none

protected $partition=array(
'type'=>'md5',

'rule'=>1,

);


public function __construct($tabName=''){
$this->user=DB_USER;
$this->host=DB_HOST;
$this->dbName[0]=DB_NAME;
$this->charset=DB_CHARSET;
$this->prefix=DB_PREFIX;
$this->pwd=DB_PWD;

if(empty($tabName)){
//userModel
//newModel
$this->tabName=$this->prefix.ucfirst(strtolower(substr(get_class($this),0,-5)));

}else{
$this->tabName=$this->prefix.$tabName;
}

$this->_link[0]=$this->connect($this->host,$this->user,$this->pwd,$this->dbName,$this->charset);

}

public function connect($host,$user,$pwd,$dbName,$charset,$linkId=0){
$conn=mysql_connect($host,$user,$pwd);


if(mysql_errno()){
$this->error(-1,$conn);
return false;
}


if(!$this->selectDb($dbName[$linkId],$conn)){
$this->error(-2,$conn);
return false;
}

if(!$this->setCharset($charset,$conn)){
$this->error(-3,$conn);
return false;
}


return $conn;



}


public function selectDb($dbName,$conn){
if(mysql_select_db($dbName,$conn)){

return true;
}else{
return false;
}
}

public function setCharset($charset,$conn){
if(mysql_set_charset($charset,$conn)){
return true;
}else{
return false;
}

}

public function addServer($host,$user,$pwd,$dbName,$charset,$linkId){
$this->dbName[$linkId]=$dbName;
$this->_link[$linkId]=$this->connect($host,$user,$pwd,$dbName,$charset,$linkId);

}

public function getTrueTable($content,$linkId=0){
switch($this->partition['type']){
case 'mod':
if(!is_int($content)){
$this->error(-4);
return false;
}
$string=$content%$this->partition['rule'];
break;
case 'substr':
$string=substr($content,0,$this->partition['rule']);
break;
case 'md5':
$string=substr(md5($content),0,$this->partition['rule']);
break;
case 'none':
$string=null;
break;
}

if(empty($string)){
$this->trueTableName=$this->tabName;

}else{
$this->trueTableName=$this->tabName.'_'.$string;
}

//第一,判断表是否存在,存在返回表字段缓存
//第二,不存在,则创建表,返回字段缓存

$this->existsTable($this->trueTableName,$linkId);


}
//表是否存在
//是否缓存了字段

protected function existsTable($tableName,$linkId=0){
$database=$this->dbName[$linkId];
$sql='select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`=\''.$database.'\' and `TABLE_NAME`=\''.$tableName.'\'';

if($this->execute($sql,$linkId)){
//表存在
if(file_exists('cache/'.md5($this->tabName).'.php')){
$this->fields=include 'cache/'.md5($this->tabName).'.php';
}else{
//暂时留着不写,待会来写
$this->fields=$this->getFieldCache($linkId);
}

}else{
//表不存在
$this->createTable($this->trueTableName,$linkId);
$this->fields=$this->getFieldCache($linkId);

}

}

protected function getFieldCache($linkId=0){
if(file_exists('cache/'.md5($this->tabName).'.php')){
$fields=include 'cache/'.md5($this->tabName).'.php';
return $fields;
}
$sql="desc $this->trueTableName";
$f=$this->query($sql,$linkId);

$fields=$this->writeFields($f);

return $fields;


}

protected function writeFields($f){
foreach($f as $key=>$value){
$fields[]=$value['Field'];

if($value['Key']=='PRI'){
$fields['_pk']=$value['Field'];
}
if($value['Extra']=='auto_increment'){
$fields['_auto']=$value['Field'];
}
}
$string="<?php \n return ".var_export($fields,true)."\n?>";

file_put_contents('cache/'.md5($this->tabName).'.php',$string);
return $fields;

}

protected function createTable($tabName,$linkId=0){
$sql=str_replace('__TABLENAME__',$tabName,$this->createSql);

$this->execute($sql,$linkId);
}

//不需要返回结果集我用execute方法
public function execute($sql,$linkId=0){
$conn=$this->_link[$linkId];

$result=mysql_query($sql,$this->_link[$linkId]);
if($result&&mysql_affected_rows()){

return mysql_affected_rows();
}else{
return false;
}

}


//需要返回结果集我用query方法
public function query($sql,$linkId=0){
$result=mysql_query($sql,$this->_link[$linkId]);

if($result&&mysql_affected_rows()){
while($row=mysql_fetch_assoc($result)){

$rows[]=$row;
}
}else{
return false;
}
return $rows;
}


public function error($num,$conn){
switch($num){
case -1:
$string='连接数据库服务器失败'.mysql_error($conn);
break;
case -2:
$string='选择数据失败';
break;
case -3:
$string='设置字符集失败';
break;
case -4:
$string='数据库路由时选择的是取余,传入的不是整型';
break;
}
}




//查最大值
public function max($field,$linkId=0){
if(!in_array($field,$this->fields)){
return false;
}
$sql="select max($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row;


}



//查最小值
public function min($field,$linkId=0){
if(!in_array($field,$this->fields)){
return false;
}
$sql="select min($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row;


}
//求和
public function sum($field,$linkId=0){
if(!in_array($field,$this->fields)){
return false;
}
$sql="select sum($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row;


}
//最平均数
public function avg($field,$linkId=0){
if(!in_array($field,$this->fields)){
return false;
}
$sql="select avg($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row;


}
//求总数
public function count($field='',$linkId=0){
if(empty($field)){
$field=$this->fields['_pk'];
}
$sql="select count($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row;
}
//
//删除
public function delete($data,$where='',$linkId=0,$order='',$limit=''){
//delete from 表 where 字段 order by 字段 limit

if(is_array($data)){
$value=join(',',$data);
}else{
$value=(int)$data;
}
$fields=$this->fields['_pk'];

if(empty($where)){

$sql="delete from $this->trueTableName where $fields in ($value)";
}else{
$where='where '.$where;
if(!empty($order)){
$order='order by '.$order;
}
if(!empty($limit)){
$limit='limit '.$limit;
}

$sql="delete from $this->trueTableName $where $order $limit";
}
return $this->execute($sql,$linkId);
}
//
//修改
public function save($data,$where,$linkId=0,$order='',$limit=''){

//update 表 set 字段=值,字段=值 where 条件 order limit
$key=array_keys($data);
$newKey=array_intersect($key,$this->fields);

foreach($data as $key=>$value){
if(!in_array($key,$newKey))
continue;
$update.=$key.'="'.$value.'",';

}
$update=rtrim($update,',');

if(!empty($order)){
$order='order by '.$order;
}
if(!empty($limit)){
$limit='limit '.$limit;
}

if(!empty($where)){
$where='where '.$where;
}
$sql="update $this->trueTableName set $update $where $order $limit";

echo $sql;
$result=$this->execute($sql,$linkId);
return $result;
}
//增加
public function add($data,$linkId=0){
//insert into 表(字段) values(值)
$key=array_keys($data);
$newKey=array_intersect($key,$this->fields);
foreach($data as $key=>$value){
if(!in_array($key,$newKey))
continue;
$values.="'".$value."',";
}
$values=trim($values,',');
$fields=join(',',$newKey);
$sql="insert into $this->trueTableName($fields) values($values)";
echo $sql;
$result=$this->execute($sql,$linkId);
return $result;
}
//单条查询
public function find($linkId=0,$where='',$order=''){
//select * from 表 where order limit 1
$field=join(',',$this->fields);
if(!empty($where)){
$where='where '.$where;
}
if(!empty($order)){
$order='order by '.$order;
}
$sql="select $field from $this->trueTableName $where $order limit 1";
$result=$this->query($sql,$linkId);
return $result[0];
}
//多条查询
public function select($field='',$linkId=0,$where='',$order='',$limit=''){
//select * from 表 where order limit
if(empty($field)){
$fields=join(',',$this->fields);
}else{
if(is_array($field)){
$newKey=array_intersect($field,$this->fields);
$fields=implode(',',$newKey);
}else{
$fields=$field;
}
}
if(!empty($where)){
$where='where '.$where;
}
if(!empty($order)){
$order='order by '.$order;
}
if(!empty($limit)){
$limit='limit '.$limit;
}
$sql="select $fields from $this->trueTableName $where $order $limit";
$result=$this->query($sql,$linkId);
return $result;
}
//按照字段来查询数据
}
?>



userModel.class.php


<?php
include 'Model.class.php';
class UserModel extends Model{


}

$m=new UserModel();




$m->getTrueTable($_POST['username']);
/*
$data=array(
'username'=>'xiaoxiao',
'password'=>123456,
'createtime'=>888,
);

//echo $m->add($data);


$result=$m->find();

var_dump($result);


var_dump($_POST);
$m->save($_POST,'id=1');

*/
$r=$m->select('password',0,'id>2');
var_dump($r);
?>


zModel.class.php


<?php
class Model{

//数据库连接资源
protected $link;
//用户名
protected $user;
//密码
protected $pwd;
//主机
protected $host;
//前缀
protected $prefix;
//库名
protected $dbName;
//字段
protected $fields;
//字符集
protected $charset;
//表名
protected $tabName;


function __construct($tabName=''){
$this->host=DB_HOST;
$this->user=DB_USER;
$this->pwd=DB_PWD;
$this->dbName=DB_NAME;
$this->prefix=DB_PREFIX;
$this->charset=DB_CHARSET;
if(empty($tabName)){
//userModel
$this->tabName=$this->prefix.substr(get_class($this),0,-5);
}else{
$this->tabName=$this->prefix.$tabName;
}
$this->link=$this->connect();
$this->fields=$this->getFields();

}

protected function connect(){
$conn=mysql_connect($this->host,$this->user,$this->pwd);
if(mysql_errno()){
echo mysql_error();
return false;
}
if(!mysql_select_db($this->dbName)){
echo mysql_error();
return false;
}
mysql_set_charset($this->charset);
return $conn;


}
protected function getFields(){
$filename='cache/'.md5($this->tabName).'.php';
if(file_exists($filename)){
return include $filename;
}else{
$field=$this->cacheFields();
return $field;
}

}

protected function cacheFields(){
$sql='desc '.$this->tabName;
if(!$result=$this->query($sql)){
echo '字段缓存读取失败';
return false;
}
foreach($result as $key=>$value){
$rows[]=$value['Field'];
if($value['Key']=='PRI'){
$rows['_pk']=$value['Field'];
}
if($value['Extra']=='auto_increment'){
$rows['_auto']=$value['Field'];
}

}
$string="<?php return \n ".var_export($rows,true).'?>';
file_put_contents('cache/'.md5($this->tabName).'.php',$string);
return $rows;


}



public function query($sql){
$result=mysql_query($sql);
var_dump($result);

if($result&&mysql_affected_rows()){
while($row=mysql_fetch_assoc($result)){
$rows[]=$row;
}
return $rows;
}else{

return false;
}
}

public function execute($sql){
$result=mysql_query($sql);
if($result&&mysql_affected_rows()){
if(substr($sql,0,6)=='insert'){
return mysql_insert_id();
}else{

return mysql_affected_rows();
}
}else{
return false;
}
}

function delete($value,$where='',$order='',$limit=''){
//delete from 表 where id= order by limit

if(is_array($value)){
$id=join(',',$value);
}else{
$id=$value;
}
$field=$this->fields['_pk'];

if(!empty($order)){
$order='order by '.$order;
}
if(!empty($limit)){
$limit='limit '.$limit[0].','.$limit[1];
}
$sql="delete from $this->tabName where $field in ($id) $where $limit";

$result=$this->execute($sql);
return $result;

}

function add($value){
//insert into 表(字段) values(值);
$keys=array_keys($value);
$arr=array_intersect($keys,$this->fields);
$fields=join(',',$arr); //join == implode
foreach($value as $key=>$value){
if(!in_array($key,$arr))
continue;
$val.="'".$value."',";
}
$val=rtrim($val,',');
$sql="insert into $this->tabName($fields) values($val)";
echo $sql;
$result=$this->execute($sql);
return $result;





}

function select($fields='',$where='',$order='',$limit='',$group=''){
if(is_array($fields)){
$arr=array_intersect($fields,$this->fields);
$fields=implode(',',$arr);

}else{
$fields='*';
}
if(!empty($where)){
$where='where '.$where;
}
if(!empty($order)){
$order='order by '.$order;
}
//array(1,4) 1,4
if(!empty($limit)){
if(is_array($limit)){
$limit='limit '.$limit[0].','.$limit[1];
}else{
$limit='limit '.$limit;
}

}
if(!empty($group)){
$group='group '.$group;
}
$sql="select $fields from $this->tabName $where $group $order $limit ";

$result=$this->query($sql);
return $result;

}

function find($where='',$order=''){
if(!empty($where)){
$where='where '.$where;
}
if(!empty($order)){
$order='order by '.$order;
}
$sql="select * from $this->tabName $where $order";
$result=$this->query($sql);
return $result[0];

}

function save($value,$where){
//update 表 set 字段=值 .... where
$keys=array_keys($value);
$arr=array_intersect($keys,$this->fields);
foreach($value as $key=>$value){
if(!in_array($key,$arr))
continue;
$val.=$key."='".$value."',";
}

$val=rtrim($val,',');
if(!empty($where)){
$where='where '.$where;
}
$sql="update $this->tabName set $val $where";
$result=$this->execute($sql);
return $result;

}

function count($field='',$where=''){
//select count(id) from 表 where
if(empty($field)){
$field=$this->fields['_pk'];
}
if(!empty($where)){
$where='where '.$where;
}
$sql="select count($field) as c from $this->tabName $where";
$result=$this->query($sql);
return $result[0]['c'];

}

function avg($field,$where=''){
//select count(id) from 表 where

if(!empty($where)){
$where='where '.$where;
}
$sql="select avg($field) as c from $this->tabName $where";
$result=$this->query($sql);
return $result[0]['c'];

}
function sum($field,$where=''){
//select count(id) from 表 where

if(!empty($where)){
$where='where '.$where;
}
$sql="select sum($field) as c from $this->tabName $where";
$result=$this->query($sql);
return $result[0]['c'];

}

function max($field='',$where=''){
//select count(id) from 表 where
if(empty($field)){
$field=$this->fields['_pk'];
}
if(!empty($where)){
$where='where '.$where;
}
$sql="select max($field) as c from $this->tabName $where";
$result=$this->query($sql);
return $result[0]['c'];

}

function min($field,$where=''){

if(!empty($where)){
$where='where '.$where;
}
$sql="select min($field) as c from $this->tabName $where";
$result=$this->query($sql);
return $result[0]['c'];

}



public function __call($func,$param){
if(strtolower(substr($func,0,5))=='getby'){
$field=substr($func,5);
$value=$param[0];
$sql="select * from $this->tabName where $field='{$value}'";
$result= $this->query($sql);
return $result;
}

}


//getByUser
//getByPassword
//getByEmail

//增
//
//删
//
//改
//
//
//查
//
//统计总数
//
//求平均数
//
//最大值
//
//最小值
//
//求和
//
//自动查询某个字段的某些值
}
?>


<?php
include 'config.php';
//大方法拆分成小方法
class Model{
//主机
protected $host;
//用户名
protected $user;
//密码
protected $pwd;
//数据库名
protected $dbName;
//表前缀
protected $prefix;
//字符集
protected $charset;
//SQL语句
protected $sql;
//字段缓存
protected $fields;
//连接资源
protected $link;
//表名
protected $tabName;


public function __construct($tabName=''){
$this->host=DB_HOST;
$this->user=DB_USER;
$this->pwd=DB_PWD;
$this->prefix=DB_PREFIX;
$this->dbName=DB_NAME;
$this->charset=DB_CHARSET;
if(empty($tabName)){
$this->tabName=$this->prefix.strtolower(substr(get_class($this),0,-5));
}else{
$this->tabName=$this->prefix.strtolower($tabName);
}
$this->link=$this->connect();
$this->fields=$this->getFields();
}


public function query($sql){
$result=mysql_query($sql);
if($result&&mysql_affected_rows()){
while($row=mysql_fetch_assoc($result)){
$rows[]=$row;
}
}else{
return false;
}

return $rows;
}

//无结果集查询方法
public function execute($sql){
$result=mysql_query($sql);
if($result&&mysql_affected_rows()){
return mysql_affected_rows();
}else{
return false;
}
}

protected function getFields(){
//如果字段当中存在字段缓存呢?
//如果不存在字段缓存呢?

if(file_exists('cache/'.$this->tabName.'.php')){
return include 'cache/'.$this->tabName.'.php';
}else{

return $this->cacheFields();

}
}

protected function cacheFields(){
$this->sql='desc '.$this->tabName;
$f=$this->query($this->sql);
foreach($f as $key=>$value){
$fields[]=$value['Field'];
if($value['Key']=='PRI'){
$fields['_pk']=$value['Field'];
}
if($value['Extra']=='auto_increment'){
$fields['_auto']=$value['Field'];
}


}

//把字段写到文件当中去
$this->writeCache($fields);

return $fields;
}
protected function writeCache($f){

$string="<?php \n return ".var_export($f,true)."\n ?>";
file_put_contents('cache/'.$this->tabName.'.php',$string);


}

protected function connect(){

$conn=mysql_connect($this->host,$this->user,$this->pwd);
if(mysql_errno()){
echo mysql_error();
return false;
}
$this->selectDb($this->dbName);
$this->setCharset();

return $conn;


}

protected function setCharset(){
mysql_set_charset($this->charset);
}

protected function selectDb($dbName){
mysql_select_db($dbName);

}


//增
public function add($data){
$keys=array_keys($data);
$newKey=array_intersect($keys,$this->fields);
foreach($data as $key=>$value){
if(!in_array($key,$newKey))
continue;
$values.='"'.$value.'",';
}
$values=rtrim($values,',');
$fields=join(',',$newKey);
$this->sql="insert into $this->tabName($fields) values($values)";
return $this->execute($this->sql);

}
//改
public function save($data,$where,$order='',$limit=''){
$keys=array_keys($data);
$newKey=array_intersect($keys,$this->fields);
foreach($data as $key=>$value){
if(!in_array($key,$newKey))
continue;
$fields.=$key.'="'.$value.'",';
}
$fields=rtrim($fields,',');
if(!empty($where)){
$where=' where '.$where;
}
if(!empty($order)){
$order=' order by '.$order;
}
if(!empty($limit)){
if(is_array($limit)){
$limit=' limit '.$limit[0].','.$limit[1];
}else{
$limit=' limit '.$limit;
}
}

$this->sql='update '.$this->tabName.' set '.$fields.$where.$order.$limit;
return $this->execute($this->sql);

}

public function select($fields='',$where='',$order='',$limit=''){
if(empty($fields)){
$fields=join(',',array_unique($this->fields));
}else{
if(is_array($fields)){
$newKey=array_intersect($fields,$this->fields);
$fields=join(',',$newKey);
}
}
if(!empty($where)){
$where=' where '.$where;
}
if(!empty($order)){
$order=' order by '.$order;
}
if(!empty($limit)){
if(is_array($limit)){
$limit=' limit '.$limit[0].','.$limit[1];
}else{
$limit=' limit '.$limit;
}
}

$this->sql="select $fields from $this->tabName $where $order $limit";
return $this->query($sql);

}




//删
public function delete($data,$where='',$order='',$limit=''){
//delete from 表 where id=;
//delete from 表 where id in();
//delete from 表 order by limit;
//delete from 表 where
if(!empty($where)){
$where=' where '.$where;

if(!empty($order)){
$order=' order by '.$order;
}

if(!empty($limit)){
if(is_array($limit)){
$limit=' limit '.$limit[0].','.$limit[1];
}else{
$limit=' limit '.$limit;
}
}
$this->sql="delete from ".$this->tabName.$where.$order.$limit;


}else{
if(is_array($data)){
$data=join(',',$data);
}
$fields=$this->fields['_pk'];
$this->sql="delete from ".$this->tabName." where $fields in ($data)";
}
return $this->execute($this->sql);
}


//最大值
public function max($fields){
if(!in_array($fields,$this->fields)){
return false;
}
$this->sql="select max($fields) as ab from ".$this->tabName;
$result=$this->query($this->sql);
return $result[0]['ab'];

}


//最小值
public function min($fields){
if(!in_array($fields,$this->fields)){
return false;
}
$this->sql="select min($fields) as ab from ".$this->tabName;
$result=$this->query($this->sql);
return $result[0]['ab'];

}
//平均值
public function avg($fields){
if(!in_array($fields,$this->fields)){
return false;
}
$this->sql="select avg($fields) as ab from ".$this->tabName;
$result=$this->query($this->sql);
return $result[0]['ab'];

}
//求和
public function sum($fields){
if(!in_array($fields,$this->fields)){
return false;
}
$this->sql="select sum($fields) as ab from ".$this->tabName;
$result=$this->query($this->sql);
return $result[0]['ab'];

}
//总数
public function count($fields=''){
if(empty($fields)){

$fields=$this->fields['_pk'];
}else{
if(!in_array($fields,$this->fields)){
return false;
}
}

$this->sql="select count($fields) as ab from ".$this->tabName;
$result=$this->query($this->sql);
return $result[0]['ab'];

}

//获得sql语句,来检测错误

public function getLastSql(){
return $this->sql;

}

//自动获得字段方法

public function __call($name,$param){
if(strtolower(substr($name,0,5))=='getby'){
$fields=strtolower(substr($name,5));
$value=$param[0];

$key=join(',',array_unique($this->fields));

$this->sql="select $key from $this->tabName where $fields='$value'";
return $this->query($this->sql);
}
}
}
?>