<?php
/**
* author zcs
* version 2.1
*/
class mysql{
var $max_cache_time = 86400; // 最大的缓存时间，以秒为单位,默认24h
var $cache_dir = '/cache/';
var $_dbhash = '';
var $setting = array();
var $dblink = NULL;
var $trans_depth = 0; //事务开启记录
var $query = null; //查询句柄
var $query_str = ''; //记录最后一次查询的语句
static private $_instance = NULL; //储存对象
var $mysql_disable_cache_tables = array(); // 不允许被缓存的表，遇到将不会进行缓存
//$dbhost = 'localhost', $dbuser = 'root', $dbpwd = '', $dbname = 'test', $dbcharset = 'utf8', $pconnect = 0
//私有化构造函数
private function __construct(){
$num_args=func_num_args();
if($num_args > 0){
$this->setting = func_get_args();
$this->_initialize();
}
}
//防止被克隆
private function __clone(){}
//初始化函數
private function _initialize()
{
try {
if($this->setting[5]){
if(!($this->dblink = @mysql_pconnect($this->setting[0], $this->setting[1], $this->setting[2]))){
throw new Exception($this->error_message('鏈接數據庫失敗'));
}
}else{
if(!($this->dblink = @mysql_connect($this->setting[0], $this->setting[1], $this->setting[2]))){
throw new Exception($this->error_message('鏈接數據庫失敗'));
}
}
if($this->select_db($this->setting[3], $this->dblink) === false) {
throw new Exception($this->error_message());
}
//$this->cache_dir = ROOT_PATH. $this->cache_dir;
//$this->_dbhash = md5(ROOT_PATH . $this->setting[0] . $this->setting[1] . $this->setting[2] . $this->setting[3]);
mysql_query("SET NAMES '".$this->setting[4]."'");
}catch(Exception $e){
echo $e->getMessage();
}
}
public static function connect($dbhost = 'localhost', $dbuser = 'root', $dbpwd = '', $dbname = '', $dbcharset = 'utf8', $pconnect = 0)
{
if(false == (self::$_instance instanceof self)){
self::$_instance = new self($dbhost, $dbuser, $dbpwd, $dbname, $dbcharset, $pconnect);
}
return self::$_instance;
}
/**
* 插入数据
*
* @access public
* @param string $table
* @param array $field_values array('field'=>values)
* @return mixed
*/
public function insert($table, $field_values, $escape = false, $replace = false) {
$fields = array ();
$values = array ();
$field_names = $this->get_column('DESC '.$table);
if(is_array($field_values)){
foreach ( $field_names as $value ) {
if (array_key_exists($value, $field_values ) == true) {
$fields[] = $value;
$values[] = $escape ? "'" . $this->escape_str($field_values[$value]) . "'" : "'" . $field_values[$value] . "'";
}
}
}
if ($fields && $values) {
$sql = ($replace ? 'REPLACE' : 'INSERT') . ' INTO '.$table.' ('.implode(',',$fields).') VALUES ('.implode( ',',$values ).')';
if($this->query($sql)){
return $this->affected_rows();
}
}
return false;
}
/**
* 删除数据/ $where数组的时候默认是 AND 关系 要是实现更复杂的关系则传入字符串
*
* @access public
* @param string $table
* @param mixed $where
* @return mixed
*/
public function delete($table, $where = null, $escape = false){
$sql = 'DELETE FROM '.$table;
if(!empty($where)){
if(is_array($where)){
$arr = array();
foreach($where as $field => $val){
$arr[] = $field . ' = ' . $escape ? $this->escape_str($val) : $val;
}
if($arr){
$where_str = implode(' AND ', array_unique($arr));
$sql .= ' WHERE '. $where_str;
}
}else{
$sql .= ' WHERE ' .$escape ? $this->escape_str($where) : $where;
}
}
if($this->query($sql)){
return $this->affected_rows();
}else{
return false;
}
}
/**
*$where数组的时候默认是 AND 关系 要是实现更复杂的关系则传入字符串
*
* @access public
* @param string $table
* @param array $field_values array('field'=>values)
* @param mixed $where
* @return mixed
*/
public function update($table, $field_values, $where = null, $escape = false) {
$field_names = $this->get_column( 'DESC ' . $table );
$sets = array ();
if(is_array($field_values)){
foreach($field_names as $value ){
if (array_key_exists($value, $field_values ) == true) {
$sets[] = $value . " = '" . $escape ? $this->escape_str($field_values[$value]) : $field_values[$value] . "'";
}
}
}
if(!empty($sets)){
$sql = 'UPDATE ' . $table . ' SET ' . implode( ', ', $sets );
if(is_array($where)){
$arr = array();
foreach($where as $field => $val){
$arr[] = $field . ' = ' . $escape ? $this->escape_str($val) : $val;
}
if($arr){
$where_str = implode(' AND ', array_unique($arr));
$sql .= ' WHERE '. $where_str;
}
}else{
$sql .= ' WHERE '. $escape ? $this->escape_str($where) : $where;
}
}else{
return false;
}
if($sql && $this->query($sql)){
return $this->affected_rows();
}else{
return false;
}
}
/**
* 查詢数据
*
* @access public
* @param string $table
* @param string $fields
* @return mixed
*/
public function find($table, $fields = '*', $where = null, $options = array(), $escape = false)
{
$sql = 'SELECT '.$fields.' FROM '. $table;
if($where != null)
{
if(is_array($where)){
$arr = array();
foreach($where as $field => $val){
$arr[] = $field . ' = ' . $escape ? $this->escape_str($val) : $val;
}
if($arr){
$where_str = implode(' AND ', array_unique($arr));
$sql .= ' WHERE '. $where_str;
}
}else{
$sql .= ' WHERE '. $escape ? $this->escape_str($where) : $where;
}
}
if(isset($options['order_by']))
{
$sql .= ' ORDER BY '.$options['order_by'];
if(isset($options['order_dir']))
{
$sql .= ' '.strtoupper($options['order_dir']);
}
}
if(isset($options['limit_start']) && isset($options['limit']))
{
$sql .= ' LIMIT '.$options['limit_start'].', '.$options['limit'];
}
elseif(isset($options['limit']))
{
$sql .= ' LIMIT '.$options['limit'];
}
return $this->query($sql);
}
/**
*
* @param string $sql_statement
* @param $index_key是否以主鍵形式返回
* @param $result_type
* @return array
*/
function result_array($arg, $index_key = false)
{
$rtn = array();
//若是sql查詢語句
if(is_string($arg)){
$query = $this->query($arg);
}elseif(is_resource($arg)){
$query = $arg;
}else{
return $rtn;
}
while ($row = $this->fetch_assoc($query)) {
if($index_key == false){
$rtn[] = $row;
} elseif (is_array($index_key)) {
$index = '';
foreach ($index_key as $k) {
if(isset($row[$k])){
$index .= $index == '' ? $row[$k] : '_' . $row[$k];
}else{
return $rtn;
}
}
$rtn[$index] = $row;
} else {
if(isset($row[$index_key])){
$rtn[$row[$index_key]] = $row;
}else{
return $rtn;
}
}
}
return $rtn;
}
/**
*
* @param string $sql_statement
* @param $index_key是否以主鍵形式返回
* @return object
*/
function result_object($arg, $index_key = false)
{
$rtn = array();
//若是sql查詢語句
if(is_string($arg)){
$query = $this->query($arg);
}elseif(is_resource($arg)){
$query = $arg;
}else{
return $rtn;
}
while ($row = $this->fetch_object($query)) {
if($index_key == false){
$rtn[] = $row;
} elseif (is_array($index_key)) {
$index = '';
foreach ($index_key as $k) {
if(isset($row->$k)){
$index .= $index == '' ? $row->$k : '_' . $row->$k;
}else{
return $rtn;
}
}
$rtn[$index] = $row;
} else {
if(isset($row->$index_key)){
$rtn[$row->$index_key] = $row;
}else{
return $rtn;
}
}
}
return $rtn;
}
/*
* 发送一条 MySQL指令
*/
public function query($sql)
{
$this->query_str = $sql;
if(!($this->query = mysql_query($this->query_str, $this->dblink))){
echo $this->error_message();
return false;
}
return $this->query;
}
/**
* mysql_fetch_array MYSQL_NUM MYSQL_ASSOC MYSQL_BOTH
*/
public function fetch_array($query, $result_type = MYSQL_ASSOC)
{
return mysql_fetch_array($query, $result_type);
}
/**
* mysql_fetch_assoc
*/
public function fetch_assoc($query)
{
return mysql_fetch_assoc($query);
}
/**
* mysql_fetch_object
*/
public function fetch_object($query)
{
return mysql_fetch_object($query);
}
/**
* mysql_fetch_row
*/
public function fetch_row($query)
{
return mysql_fetch_row($query);
}
//获取列
public function get_column($sql) {
$res = $this->query($sql);
if($res !== false) {
$arr = array();
$row = $this->fetch_row($res);
while ($row) {
$arr [] = $row[0];
$row = $this->fetch_row($res);
}
return $arr;
}else{
return false;
}
}
function select_db($dbname)
{
return mysql_select_db($dbname, $this->dblink);
}
//影响行数 delete insert
public function affected_rows()
{
return @mysql_affected_rows($this->dblink);
}
//影响行数 select
public function num_rows($res)
{
return @mysql_num_rows($res);
}
//自增插入ID
public function insert_id()
{
return @mysql_insert_id($this->dblink);
}
//最后查询一次的sql
public function last_query()
{
return $this->query_str;
}
/**
* 转义字符串或数组
* @param bool $str 是否使用做like条件的时候
* @return string or 数据
*/
public function escape_str($str, $like = false)
{
if(is_array($str)){
foreach ($str as $key => $val){
$str[$key] = $this->escape_str($val, $like);
}
return $str;
}
if(function_exists('mysql_real_escape_string') && is_resource($this->dblink)){
$str = mysql_real_escape_string($str, $this->dblink);
}elseif(function_exists('mysql_escape_string')){
$str = mysql_escape_string($str);
}else{
$str = addslashes($str);
}
// escape LIKE condition wildcards
if ($like === true){
$str = str_replace(array('%', '_'), array('\\%', '\\_'), $str);
}
return $str;
}
/**
* 开始一个事务.
*/
public function begin(){
if ($this->_trans_depth > 0){
$this->_trans_depth += 1;
return;
}
mysql_query('SET AUTOCOMMIT=0', $this->dblink);
mysql_query('START TRANSACTION', $this->dblink);
}
/**
* 提交一个事务.
*/
public function commit(){
if ($this->_trans_depth > 1){
$this->_trans_depth -= 1;
return true;
}
mysql_query('COMMIT', $this->dblink);
}
/**
* 回滚一个事务.
*/
public function rollback(){
if ($this->_trans_depth > 0){
return true;
}
mysql_query('ROLLBACK', $this->dblink);
return true;
}
/*创建像这样的查询: "IN('a','b')";
* @param mix $item_list 列表数组或字符串,如果为字符串时,字符串只接受数字串
* @param string $field_name 字段名称
*/
function db_create_in($item_list, $field_name = '')
{
if(empty($item_list)) {
return $field_name . " IN ('') ";
}else{
if(!is_array($item_list)){
$item_list = explode(',', $item_list);
foreach ($item_list as $k => $v) {
$item_list[$k] = $v;
}
}
$item_list = array_unique($item_list);
$item_list_tmp = '';
foreach($item_list as $item){
if($item !== ''){
$item_list_tmp .= $item_list_tmp ? ",'$item'" : "'$item'";
}
}
if(empty($item_list_tmp)){
return $field_name . " IN ('') ";
}else{
return $field_name . ' IN (' . $item_list_tmp . ') ';
}
}
}
//错误输出
function error_message($message = '')
{
if($message == ''){
$message = @mysql_error($this->dblink);
}
return $message;
}
public function free_result()
{
return mysql_free_result($this->query);
}
public function close(){
return mysql_close($this->dblink);
}
}
/* End of file mysql.class.php */
$db = mysql::connect($dbhost = 'localhost', $dbuser = 'root', $dbpwd = '', $dbname = 'test', $dbcharset = 'utf8', $pconnect = 0);
$query = $db->find('pl_user', '*', null, array('order_by'=>'user_auto_id','limit'=>'5'));
$arr = $db->result_array($query);
//or
$arr = $db->result_array('select * from pl_user limit 5');
var_dump($arr);
浙公网安备 33010602011771号