php mysql网站的mysql连接操作操作类
cls_mysql($dbhost, $dbuser, $dbpw, $dbname, $charset, $pconnect, $quiet);
}function cls_mysql($dbhost, $dbuser, $dbpw, $dbname = "", $charset = "gbk", $pcOnnect= 0, $quiet = 0) { if (defined("SD_CHARSET")) { $charset = strtolower(str_replace("-", "", SD_CHARSET)); } if (defined("ROOT_PATH") && !$this->root_path) { $this->root_path = ROOT_PATH; } if ($quiet) { $this->connect($dbhost, $dbuser, $dbpw, $dbname, $charset, $pconnect, $quiet); } else { $this->settings = array( "dbhost" => $dbhost, "dbuser" = $dbuser, "dbpw" => $dbpw, "dbname" => $dbname, "charset" => $charset, "pconnect" => $pconnect ); } } function connect($dbhost, $dbuser, $dbpw, $dbname = "", $charset = "utf8", $pcOnnect= 0, $quiet = 0) { if ($pconnect) { if (!($this->link_id = @mysql_pconnect($dbhost, $dbuser, $dbpw))) { if (!$quiet) { $this->ErrorMsg("Can"t pConnect MySQL Server($dbhost)!"); } return false; } } else { if (PHP_VERSION >= "4.2") { $this->link_id = @mysql_connect($dbhost, $dbuser, $dbpw, true); } else { $this->link_id = @mysql_connect($dbhost, $dbuser, $dbpw); mt_srand((double)microtime() * 1000000); // 对 PHP 4.2 以下的版本进行随机数函数的初始化工作 } if (!$this->link_id) { if (!$quiet) { $this->ErrorMsg("Can"t Connect MySQL Server($dbhost)!"); } return false; } } $this->dbhash = md5($this->root_path . $dbhost . $dbuser . $dbpw . $dbname); $this->version = mysql_get_server_info($this->link_id); /* 如果mysql 版本是 4.1 以上,需要对字符集进行初始化 */ if ($this->version > "4.1") { if ($charset != "latin1") { mysql_query("SET character_set_cOnnection=$charset, character_set_results=$charset, character_set_client=binary", $this->link_id); } if ($this->version > "5.0.1") { mysql_query("SET sql_mode=""", $this->link_id); } } $sqlcache_config_file = $this->root_path . $this->cache_data_dir . "sqlcache_config_file_" . $this->dbhash . ".php"; @include($sqlcache_config_file); $this->starttime = time(); if ($this->max_cache_time && $this->starttime > $this->mysql_config_cache_file_time $this->max_cache_time) { if ($dbhost != ".") { $result = mysql_query("SHOW VARIABLES LIKE "basedir"", $this->link_id); $row = mysql_fetch_assoc($result); if (!empty($row["Value"]{1}) && $row["Value"]{1} == ":" && !empty($row["Value"]{2}) && $row["Value"]{2} == "\\") { $this->platform = "WINDOWS"; } else { $this->platform = "OTHER"; } } else { $this->platform = "WINDOWS"; } if ($this->platform == "OTHER" && ($dbhost != "." && strtolower($dbhost) != "localhost:3306" && $dbhost != "127.0.0.1:3306") || (PHP_VERSION >= "5.1" && date_default_timezone_get() == "UTC")) { $result = mysql_query("SELECT UNIX_TIMESTAMP() AS timeline, UNIX_TIMESTAMP("" . date("Y-m-d H:i:s", $this->starttime) . "") AS timezone", $this->link_id); $row = mysql_fetch_assoc($result); if ($dbhost != "." && strtolower($dbhost) != "localhost:3306" && $dbhost != "127.0.0.1:3306") { $this->timeline = $this->starttime - $row["timeline"]; } if (PHP_VERSION >= "5.1" && date_default_timezone_get() == "UTC") { $this->timezOne= $this->starttime - $row["timezone"]; } } $cOntent= "starttime . ";\r\n" . "$this->timeline = " . $this->timeline . ";\r\n" . "$this->timezOne= " . $this->timezone . ";\r\n" . "$this->platform = " . """ . $this->platform . "";\r\n?" . ">"; @file_put_contents($sqlcache_config_file, $content); } /* 选择数据库 */ if ($dbname) { if (mysql_select_db($dbname, $this->link_id) === false ) { if (!$quiet) { $this->ErrorMsg("Can"t select MySQL database($dbname)!"); } return false; } else { return true; } } else { return true; } } function select_database($dbname) { return mysql_select_db($dbname, $this->link_id); } function set_mysql_charset($charset) { /* 如果mysql 版本是 4.1 以上,需要对字符集进行初始化 */ if ($this->version > "4.1") { if (in_array(strtolower($charset), array("gbk", "big5", "utf-8", "utf8"))) { $charset = str_replace("-", "", $charset); } if ($charset != "latin1") { mysql_query("SET character_set_cOnnection=$charset, character_set_results=$charset, character_set_client=binary", $this->link_id); } } } function fetch_array($query, $result_type = MYSQL_ASSOC) { return mysql_fetch_array($query, $result_type); } function query($sql, $type = "") { if ($this->link_id === NULL) { $this->connect($this->settings["dbhost"], $this->settings["dbuser"], $this->settings["dbpw"], $this->settings["dbname"], $this->settings["charset"], $this->settings["pconnect"]); $this->settings = array(); } if ($this->queryCount queryLog[] = $sql; } if ($this->queryTime == "") { if (PHP_VERSION >= "5.0.0") { $this->queryTime = microtime(true); } else { $this->queryTime = microtime(); } } /* 当当前的时间大于类初始化时间的时候,自动执行 ping 这个自动重新连接操作 */ if (PHP_VERSION >= "4.3" && time() > $this->starttime 1) { mysql_ping($this->link_id); } if (!($query = mysql_query($sql, $this->link_id)) && $type != "SILENT") { $this->error_message[]["message"] = "MySQL Query Error"; $this->error_message[]["sql"] = $sql; $this->error_message[]["error"] = mysql_error($this->link_id); $this->error_message[]["errno"] = mysql_errno($this->link_id); $this->ErrorMsg(); return false; } if (defined("DEBUG_MODE") && (DEBUG_MODE & 8) == 8) { $logfilename = $this->root_path . DATA_DIR . "/mysql_query_" . $this->dbhash . "_" . date("Y_m_d") . ".log"; $str = $sql . "\n\n"; if (PHP_VERSION >= "5.0") { file_put_contents($logfilename, $str, FILE_APPEND); } else { $fp = @fopen($logfilename, "ab "); if ($fp) { fwrite($fp, $str); fclose($fp); } } } return $query; } function affected_rows() { return mysql_affected_rows($this->link_id); } function error() { return mysql_error($this->link_id); } function errno() { return mysql_errno($this->link_id); } function result($query, $row) { return @mysql_result($query, $row); } function num_rows($query) { return mysql_num_rows($query); } function num_fields($query) { return mysql_num_fields($query); } function free_result($query) { return mysql_free_result($query); } function insert_id() { return mysql_insert_id($this->link_id); } function fetchRow($query) { return mysql_fetch_assoc($query); } function fetch_fields($query) { return mysql_fetch_field($query); } function version() { return $this->version; } function ping() { if (PHP_VERSION >= "4.3") { return mysql_ping($this->link_id); } else { return false; } } function escape_string($unescaped_string) { if (PHP_VERSION >= "4.3") { return mysql_real_escape_string($unescaped_string); } else { return mysql_escape_string($unescaped_string); } } function close() { return mysql_close($this->link_id); } function ErrorMsg($message = "", $sql = "") { if(!file_exists(ROOT_PATH."data/sql_log")) { mkdir(ROOT_PATH."data/sql_log"); } if ($message) { //echo "MySQL 服务错误信息: $message\n\n "; include (ROOT_PATH ."/templates/default/500error.html"); } else { include (ROOT_PATH ."/templates/default/500error.html"); $msg="MySQL 服务错误信息:\r\n".print_r($this->error_message,true); @file_put_contents(ROOT_PATH."data/sql_log/".date("Y-m-d-H-i-s",time()).".txt",$msg); } exit; } /* 仿真 Adodb 函数 */ function selectLimit($sql, $num, $start = 0) { if ($start == 0) { $sql .= " LIMIT " . $num; } else { $sql .= " LIMIT " . $start . ", " . $num; } return $this->query($sql); } function getOne($sql, $limited = false) { if ($limited == true) { $sql = trim($sql . " LIMIT 1"); } $res = $this->query($sql); if ($res !== false) { $row = mysql_fetch_row($res); if ($row !== false) { return $row[0]; } else { return ""; } } else { return false; } } function getOneCached($sql, $cached = "FILEFIRST") { $sql = trim($sql . " LIMIT 1"); $cachefirst = ($cached == "FILEFIRST" || ($cached == "MYSQLFIRST" && $this->platform != "WINDOWS")) && $this->max_cache_time; if (!$cachefirst) { return $this->getOne($sql, true); } else { $result = $this->getSqlCacheData($sql, $cached); if (empty($result["storecache"]) == true) { return $result["data"]; } } $arr = $this->getOne($sql, true); if ($arr !== false && $cachefirst) { $this->setSqlCacheData($result, $arr); } return $arr; } function getAll($sql) { $res = $this->query($sql); if ($res !== false) { $arr = array(); while ($row = mysql_fetch_assoc($res)) { $arr[] = $row; } return $arr; } else { return false; } } function getAllCached($sql, $cached = "FILEFIRST") { $cachefirst = ($cached == "FILEFIRST" || ($cached == "MYSQLFIRST" && $this->platform != "WINDOWS")) && $this->max_cache_time; if (!$cachefirst) { return $this->getAll($sql); } else { $result = $this->getSqlCacheData($sql, $cached); if (empty($result["storecache"]) == true) { return $result["data"]; } } $arr = $this->getAll($sql); if ($arr !== false && $cachefirst) { $this->setSqlCacheData($result, $arr); } return $arr; } function getRow($sql, $limited = false) { if ($limited == true) { $sql = trim($sql . " LIMIT 1"); } $res = $this->query($sql); if ($res !== false) { return mysql_fetch_assoc($res); } else { return false; } } function getRowCached($sql, $cached = "FILEFIRST") { $sql = trim($sql . " LIMIT 1"); $cachefirst = ($cached == "FILEFIRST" || ($cached == "MYSQLFIRST" && $this->platform != "WINDOWS")) && $this->max_cache_time; if (!$cachefirst) { return $this->getRow($sql, true); } else { $result = $this->getSqlCacheData($sql, $cached); if (empty($result["storecache"]) == true) { return $result["data"]; } } $arr = $this->getRow($sql, true); if ($arr !== false && $cachefirst) { $this->setSqlCacheData($result, $arr); } return $arr; } function getCol($sql) { $res = $this->query($sql); if ($res !== false) { $arr = array(); while ($row = mysql_fetch_row($res)) { $arr[] = $row[0]; } return $arr; } else { return false; } } function getColCached($sql, $cached = "FILEFIRST") { $cachefirst = ($cached == "FILEFIRST" || ($cached == "MYSQLFIRST" && $this->platform != "WINDOWS")) && $this->max_cache_time; if (!$cachefirst) { return $this->getCol($sql); } else { $result = $this->getSqlCacheData($sql, $cached); if (empty($result["storecache"]) == true) { return $result["data"]; } } $arr = $this->getCol($sql); if ($arr !== false && $cachefirst) { $this->setSqlCacheData($result, $arr); } return $arr; } function autoExecute($table, $field_values, $mode = "INSERT", $where = "", $querymode = "") { $field_names = $this->getCol("DESC " . $table); $sql = ""; if ($mode == "INSERT") { $fields = $values = array(); foreach ($field_names AS $value) { if (array_key_exists($value, $field_values) == true) { $fields[] = $value; $values[] = """ . $field_values[$value] . """; } } if (!empty($fields)) { $sql = "INSERT INTO " . $table . " (" . implode(", ", $fields) . ") VALUES (" . implode(", ", $values) . ")"; } } else { $sets = array(); foreach ($field_names AS $value) { if (array_key_exists($value, $field_values) == true) { $sets[] = $value . " = "" . $field_values[$value] . """; } } if (!empty($sets)) { $sql = "UPDATE " . $table . " SET " . implode(", ", $sets) . " WHERE " . $where; } } if ($sql) { return $this->query($sql, $querymode); } else { return false; } } function autoReplace($table, $field_values, $update_values, $where = "", $querymode = "") { $field_descs = $this->getAll("DESC " . $table); $primary_keys = array(); foreach ($field_descs AS $value) { $field_names[] = $value["Field"]; if ($value["Key"] == "PRI") { $primary_keys[] = $value["Field"]; } } $fields = $values = array(); foreach ($field_names AS $value) { if (array_key_exists($value, $field_values) == true) { $fields[] = $value; $values[] = """ . $field_values[$value] . """; } } $sets = array(); foreach ($update_values AS $key => $value) { if (array_key_exists($key, $field_values) == true) { if (is_int($value) || is_float($value)) { $sets[] = $key . " = " . $key . " " . $value; } else { $sets[] = $key . " = "" . $value . """; } } } $sql = ""; if (empty($primary_keys)) { if (!empty($fields)) { $sql = "INSERT INTO " . $table . " (" . implode(", ", $fields) . ") VALUES (" . implode(", ", $values) . ")"; } } else { if ($this->version() >= "4.1") { if (!empty($fields)) { $sql = "INSERT INTO " . $table . " (" . implode(", ", $fields) . ") VALUES (" . implode(", ", $values) . ")"; if (!empty($sets)) { $sql .= "ON DUPLICATE KEY UPDATE " . implode(", ", $sets); } } } else { if (empty($where)) { $where = array(); foreach ($primary_keys AS $value) { if (is_numeric($value)) { $where[] = $value . " = " . $field_values[$value]; } else { $where[] = $value . " = "" . $field_values[$value] . """; } } $where = implode(" AND ", $where); } if ($where && (!empty($sets) || !empty($fields))) { if (intval($this->getOne("SELECT COUNT(*) FROM $table WHERE $where")) > 0) { if (!empty($sets)) { $sql = "UPDATE " . $table . " SET " . implode(", ", $sets) . " WHERE " . $where; } } else { if (!empty($fields)) { $sql = "REPLACE INTO " . $table . " (" . implode(", ", $fields) . ") VALUES (" . implode(", ", $values) . ")"; } } } } } if ($sql) { return $this->query($sql, $querymode); } else { return false; } } function setMaxCacheTime($second) { $this->max_cache_time = $second; } function getMaxCacheTime() { return $this->max_cache_time; } function getSqlCacheData($sql, $cached = "") { $sql = trim($sql); $result = array(); $result["filename"] = $this->root_path . $this->cache_data_dir . "sqlcache_" . abs(crc32($this->dbhash . $sql)) . "_" . md5($this->dbhash . $sql) . ".php"; $data = @file_get_contents($result["filename"]); if (isset($data{23})) { $filetime = substr($data, 13, 10); $data = substr($data, 23); if (($cached == "FILEFIRST" && time() > $filetime $this->max_cache_time) || ($cached == "MYSQLFIRST" && $this->table_lastupdate($this->get_table_name($sql)) > $filetime)) { $result["storecache"] = true; } else { $result["data"] = @unserialize($data); if ($result["data"] === false) { $result["storecache"] = true; } else { $result["storecache"] = false; } } } else { $result["storecache"] = true; } return $result; } function setSqlCacheData($result, $data) { if ($result["storecache"] === true && $result["filename"]) { @file_put_contents($result["filename"], "" . time() . serialize($data)); clearstatcache(); } } /* 获取 SQL 语句中最后更新的表的时间,有多个表的情况下,返回最新的表的时间 */ function table_lastupdate($tables) { if ($this->link_id === NULL) { $this->connect($this->settings["dbhost"], $this->settings["dbuser"], $this->settings["dbpw"], $this->settings["dbname"], $this->settings["charset"], $this->settings["pconnect"]); $this->settings = array(); } $lastupdatetime = "0000-00-00 00:00:00"; $tables = str_replace("`", "", $tables); $this->mysql_disable_cache_tables = str_replace("`", "", $this->mysql_disable_cache_tables); foreach ($tables AS $table) { if (in_array($table, $this->mysql_disable_cache_tables) == true) { $lastupdatetime = "2037-12-31 23:59:59"; break; } if (strstr($table, ".") != NULL) { $tmp = explode(".", $table); $sql = "SHOW TABLE STATUS FROM `" . trim($tmp[0]) . "` LIKE "" . trim($tmp[1]) . """; } else { $sql = "SHOW TABLE STATUS LIKE "" . trim($table) . """; } $result = mysql_query($sql, $this->link_id); $row = mysql_fetch_assoc($result); if ($row["Update_time"] > $lastupdatetime) { $lastupdatetime = $row["Update_time"]; } } $lastupdatetime = strtotime($lastupdatetime) - $this->timezone $this->timeline; return $lastupdatetime; } function get_table_name($query_item) { $query_item = trim($query_item); $table_names = array(); /* 判断语句中是不是含有 JOIN */ if (stristr($query_item, " JOIN ") == "") { /* 解析一般的 SELECT FROM 语句 */ if (preg_match("/^SELECT.*?FROM\s*((?:`?\w `?\s*\.\s*)?`?\w `?(?:(?:\s*AS)?\s*`?\w `?)?(?:\s*,\s*(?:`?\w `?\s*\.\s*)?`?\w `?(?:(?:\s*AS)?\s*`?\w `?)?)*)/is", $query_item, $table_names)) { $table_names = preg_replace("/((?:`?\w `?\s*\.\s*)?`?\w `?)[^,]*/", "\1", $table_names[1]); return preg_split("/\s*,\s*/", $table_names); } } else { /* 对含有 JOIN 的语句进行解析 */ if (preg_match("/^SELECT.*?FROM\s*((?:`?\w `?\s*\.\s*)?`?\w `?)(?:(?:\s*AS)?\s*`?\w `?)?.*?JOIN.*$/is", $query_item, $table_names)) { $other_table_names = array(); preg_match_all("/JOIN\s*((?:`?\w `?\s*\.\s*)?`?\w `?)\s*/i", $query_item, $other_table_names); return array_merge(array($table_names[1]), $other_table_names[1]); } } return $table_names; } /* 设置不允许进行缓存的表 */ function set_disable_cache_tables($tables) { if (!is_array($tables)) { $tables = explode(",", $tables); } foreach ($tables AS $table) { $this->mysql_disable_cache_tables[] = $table; } array_unique($this->mysql_disable_cache_tables); } } ?>