[? 類庫代碼 ]

/**
?* 文件: database.inc.php
?* 描述: 數(shù)據(jù)庫操作類
?* 作者: heiyeluren
?* 創(chuàng)建: 2005-12-25
?* 修改: 2005-12-26
?* 說明: 本庫使用PHPLib DB庫作為核心, 同時增加一些實用方法, 詳細參考注釋
?*/

class DB_Sql
{
?
? /* public: connection parameters */
? var $Host???? = "";
? var $Database = "";
? var $User???? = "";
? var $Password = "";

? /* public: configuration parameters */
? var $Auto_Free???? = 1;???? ## Set to 1 for automatic mysql_free_result()
? var $Debug???????? = 0;???? ## Set to 1 for debugging messages.
? var $Halt_On_Error = "yes"; ## "yes" (halt with message), "no" (ignore errors quietly), "report" (ignore errror, but spit a warning)
? var $PConnect????? = 0;???? ## Set to 1 to use persistent database connections
? var $Seq_Table???? = "db_sequence";

? /* public: result array and current row number */
? var $Record?? = array();
? var $Row;

? /* public: current error number and error text */
? var $Errno??? = 0;
? var $Error??? = "";

? /* public: this is an api revision, not a CVS revision. */
? var $type???? = "mysql";
? //var $revision = "1.2";

? /* private: link and query handles */
? var $Link_ID? = 0;
? var $Query_ID = 0;

? var $locked?? = false;????? ## set to true while we have a lock

? /* public: constructor */
? function DB_Sql() {
??? $this->query($query);
? }
?
? /* public: some trivial reporting */
? function link_id() {
??? return $this->Link_ID;
? }

? function query_id() {
??? return $this->Query_ID;
? }

? /* public: connection management */
? function connect($Database = "", $Host = "", $User = "", $Password = "") {
??? /* Handle defaults */
??? if ("" == $Database)
????? $Database = $this->Database;

??? if ("" == $Host)
????? $Host???? = $this->Host;

??? if ("" == $User)
????? $User???? = $this->User;

??? if ("" == $Password)
????? $Password = $this->Password;

?????
??? /* establish connection, select database */
??? if ( 0 == $this->Link_ID ) {
???
????? if(!$this->PConnect) {
??????? $this->Link_ID = mysql_connect($Host, $User, $Password);
????? } else {
??????? $this->Link_ID = mysql_pconnect($Host, $User, $Password);
????? }
????? if (!$this->Link_ID) {
??????? $this->halt("connect($Host, $User, \$Password) failed.");
??????? return 0;
????? }

????? if (!@mysql_select_db($Database,$this->Link_ID)) {
??????? $this->halt("cannot use database ".$Database);
??????? return 0;
????? }
??? }
???
??? return $this->Link_ID;
? }

? /* public: discard the query result */
? function free() {
????? @mysql_free_result($this->Query_ID);
????? $this->Query_ID = 0;
? }

? /* public: perform a query */
? function query($Query_String) {
??? /* No empty queries, please, since PHP4 chokes on them. */
??? if ($Query_String == "")
????? /* The empty query string is passed on from the constructor,
?????? * when calling the class without a query, e.g. in situations
?????? * like these: '$db = new DB_Sql_Subclass;'
?????? */
????? return 0;

??? if (!$this->connect()) {
????? return 0; /* we already complained in connect() about that. */
??? };

??? # New query, discard previous result.
??? if ($this->Query_ID) {
????? $this->free();
??? }

??? if ($this->Debug)
????? printf("Debug: query = %s
\n", $Query_String);

??? $this->Query_ID = @mysql_query($Query_String,$this->Link_ID);
??? $this->Row?? = 0;
??? $this->Errno = mysql_errno();
??? $this->Error = mysql_error();
??? if (!$this->Query_ID) {
????? $this->halt("Invalid SQL: ".$Query_String);
??? }

??? # Will return nada if it fails. That's fine.
??? return $this->Query_ID;
? }

? /* public: walk result set */
? function next_record() {
??? if (!$this->Query_ID) {
????? $this->halt("next_record called with no query pending.");
????? return 0;
??? }

??? $this->Record = @mysql_fetch_array($this->Query_ID);
??? $this->Row?? += 1;
??? $this->Errno? = mysql_errno();
??? $this->Error? = mysql_error();

??? $stat = is_array($this->Record);
??? if (!$stat && $this->Auto_Free) {
????? $this->free();
??? }
??? return $stat;
? }

? /* public: position in result set */
? function seek($pos = 0) {
??? $status = @mysql_data_seek($this->Query_ID, $pos);
??? if ($status)
????? $this->Row = $pos;
??? else {
????? $this->halt("seek($pos) failed: result has ".$this->num_rows()." rows.");

????? /* half assed attempt to save the day,
?????? * but do not consider this documented or even
?????? * desireable behaviour.
?????? */
????? @mysql_data_seek($this->Query_ID, $this->num_rows());
????? $this->Row = $this->num_rows();
????? return 0;
??? }

??? return 1;
? }

? /* public: table locking */
? function lock($table, $mode = "write") {
??? $query = "lock tables ";
??? if(is_array($table)) {
????? while(list($key,$value) = each($table)) {
??????? // text keys are "read", "read local", "write", "low priority write"
??????? if(is_int($key)) $key = $mode;
??????? if(strpos($value, ",")) {
????????? $query .= str_replace(",", " $key, ", $value) . " $key, ";
??????? } else {
????????? $query .= "$value $key, ";
??????? }
????? }
????? $query = substr($query, 0, -2);
??? } elseif(strpos($table, ",")) {
????? $query .= str_replace(",", " $mode, ", $table) . " $mode";
??? } else {
????? $query .= "$table $mode";
??? }
??? if(!$this->query($query)) {
????? $this->halt("lock() failed.");
????? return false;
??? }
??? $this->locked = true;
??? return true;
? }
?
? function unlock() {

??? // set before unlock to avoid potential loop
??? $this->locked = false;

??? if(!$this->query("unlock tables")) {
????? $this->halt("unlock() failed.");
????? return false;
??? }
??? return true;
? }

? /* public: evaluate the result (size, width) */
? function affected_rows() {
??? return @mysql_affected_rows($this->Link_ID);
? }

? function num_rows() {
??? return @mysql_num_rows($this->Query_ID);
? }

? function num_fields() {
??? return @mysql_num_fields($this->Query_ID);
? }

? /* public: shorthand notation */
? function nf() {
??? return $this->num_rows();
? }

? function np() {
??? print $this->num_rows();
? }

? function f($Name) {
??? if (isset($this->Record[$Name])) {
????? return $this->Record[$Name];
??? }
? }

? function p($Name) {
??? if (isset($this->Record[$Name])) {
????? print $this->Record[$Name];
??? }
? }

? /* public: sequence numbers */
? function nextid($seq_name) {
??? /* if no current lock, lock sequence table */
??? if(!$this->locked) {
????? if($this->lock($this->Seq_Table)) {
??????? $locked = true;
????? } else {
??????? $this->halt("cannot lock ".$this->Seq_Table." - has it been created?");
??????? return 0;
????? }
??? }
???
??? /* get sequence number and increment */
??? $q = sprintf("select nextid from %s where seq_name = '%s'",
?????????????? $this->Seq_Table,
?????????????? $seq_name);
??? if(!$this->query($q)) {
????? $this->halt('query failed in nextid: '.$q);
????? return 0;
??? }
???
??? /* No current value, make one */
??? if(!$this->next_record()) {
????? $currentid = 0;
????? $q = sprintf("insert into %s values('%s', %s)",
???????????????? $this->Seq_Table,
???????????????? $seq_name,
???????????????? $currentid);
????? if(!$this->query($q)) {
??????? $this->halt('query failed in nextid: '.$q);
??????? return 0;
????? }
??? } else {
????? $currentid = $this->f("nextid");
??? }
??? $nextid = $currentid + 1;
??? $q = sprintf("update %s set nextid = '%s' where seq_name = '%s'",
?????????????? $this->Seq_Table,
?????????????? $nextid,
?????????????? $seq_name);
??? if(!$this->query($q)) {
????? $this->halt('query failed in nextid: '.$q);
????? return 0;
??? }
???
??? /* if nextid() locked the sequence table, unlock it */
??? if($locked) {
????? $this->unlock();
??? }
???
??? return $nextid;
? }

? /* public: return table metadata */
? function metadata($table = "", $full = false) {
??? $count = 0;
??? $id??? = 0;
??? $res?? = array();

??? /*
???? * Due to compatibility problems with Table we changed the behavior
???? * of metadata();
???? * depending on $full, metadata returns the following values:
???? *
???? * - full is false (default):
???? * $result[]:
???? *?? [0]["table"]? table name
???? *?? [0]["name"]?? field name
???? *?? [0]["type"]?? field type
???? *?? [0]["len"]??? field length
???? *?? [0]["flags"]? field flags
???? *
???? * - full is true
???? * $result[]:
???? *?? ["num_fields"] number of metadata records
???? *?? [0]["table"]? table name
???? *?? [0]["name"]?? field name
???? *?? [0]["type"]?? field type
???? *?? [0]["len"]??? field length
???? *?? [0]["flags"]? field flags
???? *?? ["meta"][field name]? index of field named "field name"
???? *?? This last one could be used if you have a field name, but no index.
???? *?? Test:? if (isset($result['meta']['myfield'])) { ...
???? */

??? // if no $table specified, assume that we are working with a query
??? // result
??? if ($table) {
????? $this->connect();
????? $id = @mysql_list_fields($this->Database, $table);
????? if (!$id) {
??????? $this->halt("Metadata query failed.");
??????? return false;
????? }
??? } else {
????? $id = $this->Query_ID;
????? if (!$id) {
??????? $this->halt("No query specified.");
??????? return false;
????? }
??? }
?
??? $count = @mysql_num_fields($id);

??? // made this IF due to performance (one if is faster than $count if's)
??? if (!$full) {
????? for ($i=0; $i??????? $res[$i]["table"] = @mysql_field_table ($id, $i);
??????? $res[$i]["name"]? = @mysql_field_name? ($id, $i);
??????? $res[$i]["type"]? = @mysql_field_type? ($id, $i);
??????? $res[$i]["len"]?? = @mysql_field_len?? ($id, $i);
??????? $res[$i]["flags"] = @mysql_field_flags ($id, $i);
????? }
??? } else { // full
????? $res["num_fields"]= $count;
???
????? for ($i=0; $i??????? $res[$i]["table"] = @mysql_field_table ($id, $i);
??????? $res[$i]["name"]? = @mysql_field_name? ($id, $i);
??????? $res[$i]["type"]? = @mysql_field_type? ($id, $i);
??????? $res[$i]["len"]?? = @mysql_field_len?? ($id, $i);
??????? $res[$i]["flags"] = @mysql_field_flags ($id, $i);
??????? $res["meta"][$res[$i]["name"]] = $i;
????? }
??? }
???
??? // free the result only if we were called on a table
??? if ($table) {
????? @mysql_free_result($id);
??? }
??? return $res;
? }

? /* public: find available table names */
? function table_names() {
??? $this->connect();
??? $h = @mysql_query("show tables", $this->Link_ID);
??? $i = 0;
??? while ($info = @mysql_fetch_row($h)) {
????? $return[$i]["table_name"]????? = $info[0];
????? $return[$i]["tablespace_name"] = $this->Database;
????? $return[$i]["database"]??????? = $this->Database;
????? $i++;
??? }
???
??? @mysql_free_result($h);
??? return $return;
? }

? /* private: error handling */
? function halt($msg) {
??? $this->Error = @mysql_error($this->Link_ID);
??? $this->Errno = @mysql_errno($this->Link_ID);

??? if ($this->locked) {
????? $this->unlock();
??? }

??? if ($this->Halt_On_Error == "no")
????? return;

??? $this->haltmsg($msg);

??? if ($this->Halt_On_Error != "report")
????? die("Session halted.");
? }

? function haltmsg($msg) {
??? printf("Database error: %s
\n", $msg);
??? printf("MySQL Error: %s (%s)
\n",
????? $this->Errno,
????? $this->Error);
? }

?//----------------------------------
?// 模塊: 自定義函數(shù)
?// 功能: 部分實用的數(shù)據(jù)庫處理方法
?// 作者: heiyeluren
?// 時間: 2005-12-26
?//----------------------------------

?/**
? * 方法: execute($sql)
? * 功能: 執(zhí)行一條SQL語句，主要針對沒有結(jié)果集返回的SQL
? * 參數(shù): $sql 需要執(zhí)行的SQL語句，例如：execute("DELETE FROM table1 WHERE id = '1'")
? * 返回: 更新成功返回True，失敗返回False
? */
?function execute($sql)
?{
? if (empty($sql))
? {
?? $this->error("Invalid parameter");
? }
? if (!$this->query($sql))
? {
?? return false;
? }
? return true;
?}

?/**
? * 方法: get_all($sql)
? * 功能: 獲取SQL執(zhí)行的所有記錄
? * 參數(shù): $sql? 需要執(zhí)行的SQL,例如: get_all("SELECT * FROM Table1")
? * 返回: 返回包含所有查詢結(jié)果的二維數(shù)組
? */
?function get_all($sql)
?{
? $this->query($sql);
? $result_array = array();
? while($this->next_record())
? {
??
?? $result_array[] = $this->Record;
? }
? if (count($result_array)? {
?? return 0;
? }
? return $result_array;
?}

?/**
? * 方法: get_one($sql)
? * 功能: 獲取SQL執(zhí)行的一條記錄
? * 參數(shù): $sql 需要執(zhí)行的SQL,例如: get_one("SELECT * FROM Table1 WHERE id = '1'")
? * 返回: 返回包含一條查詢結(jié)果的一維數(shù)組
? */
?function get_one($sql)
?{
? $this->query($sql);
? if (!$this->next_record())
? {
?? return 0;
? }
? return $this->Record;
?}

?/**
? * 方法: get_limit($sql, $limit)
? * 功能: 獲取SQL執(zhí)行的指定數(shù)量的記錄
? * 參數(shù):
? * $sql? 需要執(zhí)行的SQL,例如: SELECT * FROM Table1
? * $limit 需要限制的記錄數(shù)
? * 例如? 需要獲取10條記錄, get_limit("SELECT * FROM Table1", 10);
? *
? * 返回: 返回包含所有查詢結(jié)果的二維數(shù)組
? */
?function get_limit($sql, $limit)
?{
? $this->query($sql);
? $result_array = array();
? for ($i=0; $inext_record(); $i++)
? {
?? $result_array[] = $this->Record;
? }
? if (count($result_array) ? {
?? return 0;
? }
? return $result_array;
?}

?/**
? * 方法: limit_query($sql, $start=0, $offset=20, $order="")
? * 功能: 為分頁的獲取SQL執(zhí)行的指定數(shù)量的記錄
? * 參數(shù):
? * $sql? 需要執(zhí)行的SQL,例如: SELECT * FROM Table1
? * $start 記錄的開始數(shù), 缺省為0
? * $offset 記錄的偏移量，缺省為20
? * $order 排序方式，缺省為空，例如：ORDER BY id DESC
? * 例如? 需要獲取從0到10的記錄并且按照ID號倒排, get_limit("SELECT * FROM Table1", 0, 10, "ORDER BY id DESC");
? *
? * 返回: 返回包含所有查詢結(jié)果的二維數(shù)組
? */
?function limit_query($sql, $start=0, $offset=20, $order="")
?{
? $sql = $sql ." $order? LIMIT $start,$offset";
? $this->query($sql);
? $result = array();
? while($this->next_record())
? {
?? $result[] = $this->Record;
? }
? if (count($result) ? {
?? return 0;
? }
? return $result;
?}

?/**
? * 方法: count($table,$field="*", $where="")
? * 功能: 統(tǒng)計表中數(shù)據(jù)總數(shù)
? * 參數(shù):
? * $table 需要統(tǒng)計的表名
? * $field 需要統(tǒng)計的字段，默認為*
? * $where 條件語句，缺省為空
? * 例如? 按照ID統(tǒng)計所有年齡小于20歲的用戶, count("user_table", "id", "user_age ? *
? * 返回: 返回統(tǒng)計結(jié)果的數(shù)字
? */
?function count($table,$field="*", $where="")
?{
? $sql = (empty($where) ? "SELECT COUNT($field) FROM $table" : "SELECT COUNT($field) FROM $table WHERE $where");
? $result = $this->get_one($sql);
? if (!is_array($result))
? {
?? return 0;
? }
? return $result[0];
?}

?/**
? * 方法: insert($table,$dataArray)
? * 功能: 插入一條記錄到表里
? * 參數(shù):
? * $table? 需要插入的表名
? * $dataArray 需要插入字段和值的數(shù)組，鍵為字段名，值為字段值，例如：array("user_name"=>"張三", "user_age"=>"20歲");
? * 例如?? 比如插入用戶張三，年齡為20, insert("users", array("user_name"=>"張三", "user_age"=>"20歲"))
? *
? * 返回: 插入記錄成功返回True，失敗返回False
? */
?function insert($table,$dataArray)
?{
? if (!is_array($dataArray) || count($dataArray)? {
?? $this->error("Invalid parameter");
? }
? while(list($key,$val) = each($dataArray))
? {
?? $field .= "$key,";
?? $value .= "'$val',";
? }
? $field = substr($field, 0, -1);
? $value = substr($value, 0, -1);
? $sql = "INSERT INTO $table ($field) VALUES ($value)";
? if (!$this->query($sql))
? {
?? return false;
? }
? return true;
?}

?/**
? * 方法: update($talbe, $dataArray, $where)
? * 功能: 更新一條記錄
? * 參數(shù):
? * $table? 需要更新的表名
? * $dataArray 需要更新字段和值的數(shù)組，鍵為字段名，值為字段值，例如：array("user_name"=>"張三", "user_age"=>"20歲");
? * $where? 條件語句
? * 例如?? 比如更新姓名為張三的用戶為李四，年齡為21
? *??? update("users",? array("user_name"=>"張三", "user_age"=>"20歲"),? "user_name='張三'")
? *
? * 返回: 更新成功返回True，失敗返回False
? */
?function update($talbe, $dataArray, $where)
?{
? if (!is_array($dataArray) || count($dataArray)? {
?? $this->error("Invalid parameter");
? }
? while(list($key,$val) = each($dataArray))
? {
?? $value .= "$key = '$val',";
? }
? $value = substr($value, 0, -1);
? $sql = "UPDATE $talbe SET $value WHERE $where";
? if (!$this->query($sql))
? {
?? return false;
? }
? return true;
?}

?/**
? * 方法: delete($table, $where)
? * 功能: 刪除一條記錄
? * 參數(shù):
? * $table? 需要刪除記錄的表名
? * $where? 需要刪除記錄的條件語句
? * 例如?? 比如要刪除用戶名為張三的用戶，delete("users",? "user_name='張三'")
? *
? * 返回: 更新成功返回True，失敗返回False
? */
?function delete($table, $where)
?{
? if (empty($where))
? {
?? $this->error("Invalid parameter");
? }
? $sql = "DELETE FROM $table WHERE $where";
? if (!$this->query($sql))
? {
?? return false;
? }
? return true;
?}

?/**
? * 方法: error($msg="")
? * 功能: 顯示錯誤信息后中止腳本
? * 參數(shù): $msg 需要顯示的錯誤信息
? * 返回: 無返回
? */
?function error($msg="")
?{
? echo "Error: $msg\n
\n";
? exit();
?}

?/**
? * 方法：get_insert_id()
? * 功能：獲取最后插入的ID
? * 參數(shù): 無參數(shù)
? * 返回：關(guān)閉成功返回ID，失敗返回0
? */
?function get_insert_id()
?{
? return mysql_insert_id($this->Link_ID);
?}

?/**
? * 方法：close()
? * 功能：關(guān)閉當前數(shù)據(jù)庫連接
? * 參數(shù): 無參數(shù)
? * 返回：關(guān)閉成功返回true，失敗返回false
? */
?function close()
?{
? return mysql_close($this->Link_ID);
?}
}
?>
?