php mysql 小项目_一个小工程的准备工作 - Php MySql Class

我比较喜欢的一个。

/**

* 数据库操作类

*

* @author Moyo

* @package defaultPackage

*/

class Mysql

{

// 默认配置

private $_config_default = array

(

'debug' => false,

'host' => 'localhost:3306',

'username' => 'root',

'password' => '',

'database' => 'mysql',

'prefix' => '',

'charset' => 'utf-8',

'cached' => 'file://{root}/query_cache/'

);

public $CACHE_HASH_SALT = 'sql.cache.uuland.org';

public $CLIENT_MULTI_RESULTS = 131072;

// 配置信息

private $_config = array();

private $_debug = true;

private $_host = '';

private $_username = '';

private $_password = '';

private $_database = '';

private $_prefix = '';

private $_charset = '';

private $_cached = '';

private $_fc_path = '';

private $_mc_server = '';

// 运行时变量

private $_dbc_handle = null;

private $_query_handle = null;

public $sql = '';

private $_cache_key = '';

private $_result = array();

private $_need_cache = false;

// 数据库操作

private $_operate = '';

private $_column = '';

private $_where = array();

private $_order = array();

private $_limit = '';

private $_data = array();

private $_cache = '';

// 调试记录

private $_trace = array();

// 获取实例

public function getInstance()

{

return new self();

}

// 私有化构造函数，禁止外部实例化

private function __construct(){}

// 卸载实例时，自动释放资源，关闭连接

public function __destruct()

{

// 释放资源

$this->free();

// 关闭连接

$this->close();

}

// 载入配置

public function config($config)

{

$this->trace('public::config::load');

$this->_config = $config;

// 执行初始化

$this->init();

}

// 初始化

private function init()

{

$this->trace('private::config::init_default');

// 配置分析

foreach ($this->_config as $key => $val)

{

$mkey = '_'.$key;

$this->$mkey = isset($this->_config[$key]) ? $this->_config[$key] : $this->_config_default[$key];

}

// 清理非debug模式下，之前记录的调试信息

if (!$this->_debug) unset($this->_trace);

// 缓存配置

$this->trace('private::config::init_cache');

$cache_conf = explode('://', $this->_cached);

$this->_cached = $cache_conf[0];

if ($this->_cached == 'file')

{

$this->_fc_path = str_replace('{current}', dirname(__FILE__), str_replace('{root}', $_SERVER['DOCUMENT_ROOT'], $cache_conf[1]));

// 检测目录

if (!is_dir($this->_fc_path))

{

mkdir($this->_fc_path);

}

}

elseif ($this->_cached == 'memcache')

{

$this->_mc_server = $cache_conf[1];

}

unset($this->_config);

}

// 连接至数据库

private function connect()

{

$this->trace('public::server::connect');

// 连接服务器

$this->_dbc_handle = mysql_connect(

$this->_host,

$this->_username,

$this->_password,

true,

$this->CLIENT_MULTI_RESULTS

);

if (!$this->_dbc_handle)

{

$this->alert('Can\'t connect to Server [ '.$this->_username.'@'.$this->_host.' ]');

return false;

}

// 选择数据库

if (!mysql_select_db($this->_database, $this->_dbc_handle))

{

$this->alert('Can\'t select database ['.$this->_database.']');

return false;

}

$version = mysql_get_server_info($this->_dbc_handle);

// 设置数据库编码

if ($version >= '4.1')

{

//使用UTF8存取数据库 需要mysql 4.1.0以上支持

mysql_query('SET NAMES "'.$this->_charset.'"', $this->_dbc_handle);

}

//设置 sql_model

if($version > '5.0.1')

{

mysql_query('SET SQL_Mode=""', $this->_dbc_handle);

}

return true;

}

// 释放数据查询

private function free()

{

$this->trace('public::query::free');

if ($this->_query_handle && $this->_operate == 'SELECT')

{

mysql_free_result($this->_query_handle);

}

unset($this->_query_handle);

unset($this->_operate);

unset($this->_column);

unset($this->_where);

unset($this->_order);

unset($this->_limit);

unset($this->_data);

unset($this->_cache);

unset($this->_result);

return true;

}

// 关闭数据库连接

private function close()

{

if ($this->_dbc_handle)

{

$this->trace('public::server::close');

mysql_close($this->_dbc_handle);

unset($this->_dbc_handle);

}

}

//

// 增改删查

public function select($column)

{

$this->_operate = 'SELECT';

$this->_column = $column;

return $this;

}

public function update($column)

{

$this->_operate = 'UPDATE';

$this->_column = $column;

return $this;

}

public function insert($column)

{

$this->_operate = 'INSERT';

$this->_column = $column;

return $this;

}

public function delete($column)

{

$this->_operate = 'DELETE';

$this->_column = $column;

return $this;

}

// 条件

public function where($where)

{

$this->_where[] = $where;

return $this;

}

// 排序

public function order($order)

{

$this->_order[] = $order;

return $this;

}

// 限制返回结果数

public function limit($limit)

{

$this->_limit = $limit;

return $this;

}

// 数据存储

public function data($data)

{

$this->_data[] = $data;

return $this;

}

// 缓存设置

public function cache($cache)

{

$this->_cache = $cache;

return $this;

}

// 开始执行操作

public function done()

{

$this->trace('public::query::init');

// 数据表

$column = $this->_prefix.$this->_column;

// 组合SQL

switch ($this->_operate)

{

case 'SELECT':

$sql = 'SELECT * FROM `'.$column.'`'.$this->pack_where().$this->pack_order().$this->pack_limit();

break;

case 'UPDATE':

$sql = 'UPDATE `'.$column.'`'.$this->pack_data().$this->pack_where();

break;

case 'INSERT':

$sql = 'INSERT INTO `'.$column.'`'.$this->pack_data();

break;

case 'DELETE':

$sql = 'DELETE FROM `'.$column.'`'.$this->pack_where();

break;

default: break;

}

$this->sql = $sql;

// 缓存判断 [暂时只支持缓存查询]

if ($this->_operate == 'SELECT' && $this->cache_check())

{

$return = $this->_result;

// 清理变量池并返回

if ($this->free()) return $return;

}

// 连接判断

if (!$this->_dbc_handle) $this->connect();

// 开始执行SQL

$this->trace('public::query::begin['.$this->_operate.']');

$this->_query_handle = mysql_query($sql, $this->_dbc_handle);

if (!$this->_query_handle)

{

$this->alert('SQL run error.');

}

$this->trace('public::query::finish['.$this->_operate.']');

if ($this->_operate == 'SELECT')

{

if (mysql_num_rows($this->_query_handle) > 0)

{

while ($one_row = mysql_fetch_assoc($this->_query_handle))

{

$this->_result[] = $one_row;

}

mysql_data_seek($this->_query_handle, 0);

}

else

{

$this->_result = null;

}

// 写缓存

if ($this->_need_cache) $this->cache_write();

$return = $this->_result;

// 清理变量池并返回

if ($this->free()) return $return;

}

else

{

$return = mysql_affected_rows($this->_dbc_handle);

// 清理变量池并返回

if ($this->free()) return $return;

}

}

// 返回结果限制

private function pack_limit()

{

if ($this->_limit == '') return '';

if (is_numeric($this->_limit))

{

return ' LIMIT 0,'.$this->_limit;

}

elseif (is_string($this->_limit))

{

return ' LIMIT '.$this->_limit;

}

}

// 条件整合

private function pack_where()

{

if (!$this->_where) return '';

$sql_where = ' WHERE ';

foreach ($this->_where as $where)

{

if (is_array($where))

{

foreach ($where as $key => $val)

{

if (is_numeric($val))

{

$sql_where .= $key.'='.$val;

}

elseif (is_string($val))

{

$sql_where .= $key.'="'.$val.'"';

}

$sql_where .= ' and ';

}

}

elseif (is_string($where))

{

$conds = explode(',', $where);

foreach ($conds as $one_cond)

{

$sql_where .= $one_cond.' and ';

}

}

}

return substr($sql_where, 0, -5);

}

// 排序整合

private function pack_order()

{

if (!$this->_order) return '';

$sql_order = ' ORDER BY ';

foreach ($this->_order as $order)

{

if (is_array($order))

{

foreach ($order as $key => $type)

{

$sql_order .= $key.' '.$type.', ';

}

}

elseif (is_string($order))

{

$ords = explode(',', $order);

foreach ($ords as $one_ord)

{

$sql_order .= str_replace('.', ' ', $one_ord).', ';

}

}

}

return substr($sql_order, 0, -2);

}

// 数据整合

private function pack_data()

{

if (!$this->_data) return '';

$sql_data = ' SET ';

foreach ($this->_data as $data)

{

if (is_array($data))

{

foreach ($data as $key => $val)

{

if (is_numeric($val))

{

$sql_data .= $key.'='.$val;

}

elseif (is_string($val))

{

$sql_data .= $key.'="'.$val.'"';

}

$sql_data .= ', ';

}

}

elseif (is_string($data))

{

$datas = explode(',', $data);

foreach ($datas as $one_data)

{

$sql_data .= $one_data.', ';

}

}

}

return substr($sql_data, 0, -2);

}

// ]>

// 缓存检测

private function cache_check()

{

$this->trace('private::cache::check');

if ($this->_cache == '') return false;

$this->_cache_key = md5($this->sql.'@'.$this->CACHE_HASH_SALT);

$time_calc = array

(

's' => 1,

'm' => 60,

'h' => 3600,

'd' => 86400

);

$c_rule = explode(':', $this->_cache);

$c_time = $c_rule[0];

$c_long = (int)$c_rule[1];

if(time() - $this->cache_time() > $time_calc[$c_time]*$c_long)

{

$this->_need_cache = true;

return false;

}

$this->_result = $this->cache_read();

return true;

}

// 获取时间

private function cache_time()

{

$handle = 'cache_handle_'.$this->_cached.'_time';

return $this->$handle($this->_cache_key);

}

// 读缓存

private function cache_read()

{

$this->trace('private::cache::read');

$handle = 'cache_handle_'.$this->_cached.'_value';

return $this->$handle($this->_cache_key);

}

// 写缓存

private function cache_write()

{

$this->trace('private::cache::write');

$handle = 'cache_handle_'.$this->_cached.'_write';

$this->$handle($this->_cache_key, $this->_result);

$this->_need_cache = false;

}

//

// 文件缓存

private function cache_handle_file_time($key)

{

if (is_file($this->_fc_path.$key.'.sql'))

{

return filemtime($this->_fc_path.$key.'.sql');

}

else

{

return 0;

}

}

private function cache_handle_file_value($key)

{

if (is_file($this->_fc_path.$key.'.sql'))

{

return unserialize(file_get_contents($this->_fc_path.$key.'.sql'));

}

else

{

return false;

}

}

private function cache_handle_file_write($key, $val)

{

file_put_contents($this->_fc_path.$key.'.sql', serialize($val));

return true;

}

// memcache 缓存 [这里做的不怎么好，不过平常也用不到memcache的 ^_^]

private function cache_handle_memcache_time($key)

{

$mec = new Memcache();

$mec->connect($this->_mc_server);

$val = $mec->get($this->_cache_key.'_time');

$mec->close();

if ($val == '')

{

return 0;

}

else

{

return $val;

}

}

private function cache_handle_memcache_value($key)

{

$mec = new Memcache();

$mec->connect($this->_mc_server);

$val = $mec->get($this->_cache_key.'_value');

$mec->close();

if ($val == '')

{

return false;

}

else

{

return $val['value'];

}

}

private function cache_handle_memcache_write($key, $val)

{

$mec = new Memcache();

$mec->connect($this->_mc_server);

$mec->set($this->_cache_key.'_time', time());

$mec->set($this->_cache_key.'_value', array('cached'=>true,'value'=>$val));

$mec->close();

return true;

}

// ]>

//

// 功能后续添加

// ]>

// 调试信息

private function alert($message)

{

if (!$this->_debug) return;

echo '

';

echo $message;

echo '

---

';

echo mysql_error();

echo '

';

exit;

}

// 记录调试

private function trace($message)

{

if (!$this->_debug) return;

$this->_trace[] = array('timer'=>microtime(), 'mmusage'=>memory_get_usage(), 'message'=>$message);

}

// 输出调试

public function trace_output()

{

if (!$this->_debug) return;

echo '

';

echo '

• ';

foreach ($this->_trace as $i => $trace)

{

$timer_e = explode(' ', $trace['timer']);

$timer = (float)$timer_e[0];

$mmusage = $trace['mmusage'];

echo '

Time: '.$timer.' +'.($timer-$last_timer).' Memory: '.$trace['mmusage'].' +'.($mmusage-$last_mmusage).' Call: '.$trace['message'].'';

$last_timer = $timer;

$last_mmusage = $mmusage;

}

echo '

';

echo '

';

}

}

?>

具体使用方法：

include 'class.mysql.php';

echo '';

$conf_info = array

(

// 开启调试

'debug'=>true,

// MySQL主机

'host'=>'localhost:3306',

// 用户

'username'=>'run',

// 密码

'password'=>'moyo',

// 数据库

'database'=>'test',

// 数据表前缀

'prefix' => '',

// 数据库编码

'charset'=>'utf8',

// 缓存方式

// memcache缓存协议，“://” 后面的是服务器地址

//'cached'=>'memcache://127.0.0.1:11211'

// 文本缓存协议，“://”后面的是缓存地址。可用的标记：{root} 站点根目录， {current} 当前脚本目录

'cached'=>'file://{current}/query_cache/'

);

// 获取实例

$dbc = db_mysql::getInstance();

// 载入配置 [只支持数组方式]

$dbc->config($conf_info);

$start = explode(' ', microtime());

$memory_start = memory_get_usage();

// 插入数据

$affect = $dbc

// 操作表：user

->insert('user')

// 支持数组方式

->data(array('name'=>"Moyo"))

// 支持字符方式

->data('mail="moyo@mail"')

->done();

echo $dbc->sql;

echo '

echo 'INSERT 操作完成，影响行数：'.$affect;

echo '

---

';

// 修改数据

$affect = $dbc

->update('user')

// 支持数组方式

->where(array('name'=>'Moyo'))

// 支持字符方式

->where('mail="moyo@mail"')

->data(array('name'=>"Moyo.live", 'mail'=>'moyo@uuland'))

->done();

echo $dbc->sql;

echo '

echo 'UPDATE 操作完成，影响行数：'.$affect;

echo '

---

';

// 获取数据

$result = $dbc

->select('user')

->where('name like "%Moyo%"')

->order('id.desc')

->limit(3)

// 使用缓存，有效时间：10秒 [单位支持：d 天，h 时， m 分， s 秒]

->cache('s:10')

->done();

echo $dbc->sql;

echo '

echo 'SELECT 完成，记录数：'.count($result);

echo '

';

print_r($result);

echo '

';

echo '

---

';

// 删除数据

$affect = $dbc

->delete('user')

->where('name="Moyo.live"')

->done();

echo $dbc->sql;

echo '

echo 'DELETE 操作完成，影响行数：'.$affect;

echo '

---

';

$finish = explode(' ', microtime());

$memory_finish = memory_get_usage();

$start = $start[1]+$start[0];

$finish = $finish[1]+$finish[0];

$time = $finish-$start;

$memory = $memory_finish-$memory_start;

echo 'Trace (Time use '.$time.' Sec , Memory use '.$memory_finish.' Bytes , Incress '.$memory.' Bytes)';

echo '

---

';

$dbc->trace_output();

?>