数据库类进化 (1)

时间：2007-09-12 来源：一颗小白菜

在开始学习PHP的一两年中，我一直徘徊在PHP4对MySQL数据库操作的苦海中，每次做一个新的项目时都重复着书写一些固定的代码：

设置数据库参数

打开数据库连接
执行准备好的SQL语句
获取SQL语句执行结果
释放内存
关闭已经打开的数据库连接
每次做完以后，我都在想，这些过程每次都是固定的模式，它们都使用相同的流程顺序，都使用类似的代码来完成相同的目的，那么为什么我不去总结一下在这个过程中我所需要的数据和我所要求的结果呢？
随着总结经验的递增，我逐渐累计了一些我所常用的方法，并通过归纳将其组合成了一个class。这样就形成了我在PHP4中使用的MySQL操作集合：
MySQL4 Class
/**
* Operating Database (MySQL)
* @author feeling [email protected]>
* @version 1.0
* @exception This class was only for PHP4
**/
class MySQL4
{
/**
   * Configure for the database server
   *
   * @var Array
   **/
var $config = array();
/**
   * Script version
   *
   * @var String
   */
var $VERSION = '1.0';
/**
   * The current connection to database server
   *
   * @var Resource
   **/
var $connect_id = FALSE;
/**
   * The current query
   *
   * @var Resource
   **/
var $query_id = NULL;
/**
   * Result of SQL query
   *
   * @var Array
   **/
var $records = array();
/**
   * Error stack
   *
   * @var Array
   **/
var $error_messages = array();
/**
   * Constructor
   */
function MySQL4()
{
      $this->config = array(
         'host'    => 'localhost',
         'user'    => 'root',
         'password' => '',
         'dbname' => '',
         'prefix'       => '',
         'socket'       => '/tmp/mysql.sock',
         'port'          => 3306,
         'persistent' => FALSE
      );
}
/**
   * Open a connection to a MySQL Server
   * @return Resource Returns a MySQL connection on success, or FALSE on failure
   **/
function connect()
{
      $fun_connect = ((TRUE == $this->config['persistent']) ? 'mysql_pconnect' : 'mysql_connect');
      if (TRUE == empty($this->config['password'])) $this->connect_id = @$fun_connect($this->config['host'], $this->config['user']);
      else $this->connect_id = @$fun_connect($this->config['host'], $this->config['user'], $this->config['password']);
      if (FALSE == $this->connect_id) $this->error_messages[] = array('code' => mysql_errno(), 'file' => __FILE__, 'line' => __LINE__);
      elseif (FALSE == empty($this->config['dbname'])) @mysql_select_db($this->config['dbname'], $this->connect_id);
      return $this->connect_id;
}
/**
   * Close MySQL connection
   * @param Resource $connect_id The MySQL connection. If it was not specified, the current connection is assumed
   * @return Boolean Returns TRUE on success or FALSE on failure. If using a persistent connection, it always return TRUE
   **/
function close($connect_id = NULL)
{
      if (FALSE == @is_resource($connect_id)) $connect_id = & $this->connect_id;
      if (FALSE != $this->connect_id && FALSE == $this->config['persistent']) return @mysql_close($connect_id);
      return (TRUE == $this->config['persistent'] || FALSE);
}
/**
   * Send a MySQL query
   * @param String $query_string SQL query sentence
   * @param Resource $connect_id The MySQL connection. If it was not specified, the current connection is assumed
   * @return Boolean For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, it returns a resource on success, or FALSE on error.br />For other type of SQL statements, UPDATE, DELETE, DROP, etc, it returns TRUE on success or FALSE on error.
   **/
function query($query_string, $connect_id = NULL)
{
      if (FALSE == @is_resource($connect_id)) $connect_id = & $this->connect_id;
      if (TRUE == @is_resource($connect_id))
      {
         if (FALSE == ($this->query_id = mysql_query($query_string, $connect_id))) $this->error_messages[] = array('code' => mysql_errno(), 'file' => __FILE__, 'line' => __LINE__);
         return $this->query_id;
      }
      return FALSE;
}
/**
   * Escapes special characters in a string for use in a SQL statement
   * @param String $query_string The string that is to be escaped
   * @param Resource $connect_id The MySQL connection. If it was not specified, the current connection is assumed
   * @return Returns the escaped string, or FALSE on error
   **/
function escape($query_string, $connect_id = NULL)
{
      if (NULL == $connect_id) $connect_id = &$this->connect_id;
      if (FALSE == empty($connect_id))
      {
         return @mysql_real_escape_string($query_string, $connect_id);
      }
      return '';
}
/**
   * Free result memory
   * @param Resource $query_id The query, If it was not specified, the current query is assumed
   * @return Boolean Returns TRUE on success or FALSE on failure
   **/
function freeResult($query_id = NULL)
{
      $this->records = array();
      if (TRUE == empty($query_id)) $query_id = & $this->query_id;
      if (TRUE == @is_resource($query_id))
      {
         if (FALSE == @mysql_free_result($query_id)) $this->error_messages[] = array('code' => mysql_errno(), 'file' => __FILE__, 'line' => __LINE__);
         else return TRUE;
      }
      return FALSE;
}
/**
   * Fetch a result row
   * @param String $query_type The type of array that is to be fetched. It's a constant and can take the following values: ASSOC, NUM, and the default value of BOTH.
   * @param Resource $query_id The query, If it was not specified, the current query is assumed
   * @return Array Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows.
   **/
function fetchArray($query_type = 'BOTH', $query_id = NULL)
{
      if (TRUE == empty($query_id)) $query_id = & $this->query_id;
      if (TRUE == @is_resource($query_id))
      {
         switch ($query_type)
         {
            case 'NUM': $type = MYSQL_NUM; break;
            case 'ASSOC': $type = MYSQL_ASSOC; break;
            default: $type = MYSQL_BOTH; break;
         }
         $result = @mysql_fetch_array($query_id, $type);
         if (FALSE == $result) $this->error_messages[] = array('code' => mysql_errno(), 'file' => __FILE__, 'line' => __LINE__);
         else $this->records[] = $result;
      }
      else $result = array();
      return $result;
}
/**
   * Fetch all result rows
   * @param String $query_type The type of array that is to be fetched. It's a constant and can take the following values: ASSOC, NUM, and the default value of BOTH.
   * @param Resource $query_id The query, If it was not specified, the current query is assumed
   * @return Array Returns an array of strings that corresponds to all fetched rows, or FALSE if there are no more rows.
   **/
function fetchArrayBat($query_type = 'BOTH', $query_id = NULL)
{
      if (TRUE == empty($query_id)) $query_id = & $this->query_id;
      if (TRUE == @is_resource($query_id))
      {
         switch ($query_type)
         {
            case 'NUM': $type = MYSQL_NUM; break;
            case 'ASSOC': $type = MYSQL_ASSOC; break;
            default: $type = MYSQL_BOTH; break;
         }
         while (TRUE == ($tmp = @mysql_fetch_array($query_id, $type))) $this->records[] = $tmp;
      }
      return $this->records;
}
/**
   * Get the ID generated from the previous INSERT operation
   * @param Resource $connect_id The MySQL connection. If it was not specified, the current connection is assumed
   * @return Integer The ID generated for an AUTO_INCREMENT column by the previous INSERT query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established.
   **/
function getInsertId($connect_id = NULL)
{
      if (TRUE == empty($connect_id)) $connect_id = & $this->connect_id;
      if (TRUE == @is_resource($connect_id)) return @mysql_insert_id($connect_id);
      return FALSE;
}
/**
   * Get number of affected rows in previous MySQL operation
   * @param Resource $connect_id The MySQL connection. If it was not specified, the current connection is assumed
   * @return Integer Returns the number of affected rows on success, and -1 if the last query failed.
   * @see getNumRows()
   **/
function getAffectedRows($connect_id = NULL)
{
      if (TRUE == empty($connect_id)) $connect_id = & $this->connect_id;
      if (TRUE == @is_resource($connect_id)) return @mysql_affected_rows($connect_id);
      return FALSE;
}
/**
   * Get number of rows in result
   * @param Resource $query_id The query, If it was not specified, the current query is assumed
   * @return Integer The number of rows in a result set on success, or FALSE on failure
   * @see getAffectedRows()
   **/
function getNumRows($query_id = NULL)
{
      if (TRUE == empty($query_id)) $query_id = & $this->query_id;
      if (TRUE == @is_resource($query_id)) return @mysql_num_rows($query_id);
      return FALSE;
}
/**
   * Get server information
   * @param String $info_name information names, separated with comma. If it was not provided, all information would be returned.
   * @param Resource $connect_id The MySQL connection. If it was not specified, the current connection is assumed
   * @return Array Return a array including all selected server informations
   **/
function getServerInfo($info_name = NULL, $connect_id = NULL)
{
      $server_info = array();
      if (NULL == $connect_id) $connect_id = & $this->connect_id;
      $valid_info_names = array('version', 'protocol', 'connection', 'cversion', 'charset');
      if (TRUE == empty($info_name)) $info_names = $valid_info_names;
      else
      {
         $tmp = explode(',', $info_name);
         for ($i = 0; $i  count($tmp); $i++)
         {
            if (TRUE == in_array(trim($tmp[$i]), $valid_info_names)) $info_names[] = trim($tmp[$i]);
         }
      }
      for ($i = 0; $i  count($info_names); $i++)
      {
         switch ($info_names[$i])
         {
            case 'version':
                  $matched = array();
                  $tmp = @mysql_get_server_info($connect_id);
                  preg_match('/^(\d+)\.(\d+)\.(\d+).*/i', $tmp, $matched);
                  $tmp = $matched[1] * 10000 + $matched[2] * 100 + $matched[3];
                  break;
            case 'protocol': $tmp = @mysql_get_proto_info($connect_id); break;
            case 'connection': $tmp = @mysql_get_host_info($connect_id); break;
            case 'cversion':
                  $matched = array();
                  $tmp = @mysql_get_client_info();
                  preg_match('/^(\d+)\.(\d+)\.(\d+).*/i', $tmp, $matched);
                  $tmp = $matched[1] * 10000 + $matched[2] * 100 + $matched[3];
                  break;
            case 'charset': $tmp = @mysql_client_encoding($connect_id); break;
         }
         $server_info[$info_names[$i]] = $tmp;
      }
      return $server_info;
}
/**
   * Get current system status
   * @param Resource $connect_id The MySQL connection. If it was not specified, the current connection is assumed
   * @return String return a string including uptime, threads, queries, open tables, flush tables and queries per second, else return NULL
   **/
function getServerStatus($connect_id = NULL)
{
      if (NULL == $connect_id) $connect_id = & $this->connect_id;
      $stat_string = @mysql_stat($connect_id); // Like as Uptime: 272 Threads: 1 Questions: 5340 Slow queries: 0 Opens: 13 Flush tables: 1 Open tables: 0 Queries per second avg: 19.632
      $matched = array();
      preg_match('/Uptime:\s*(\d+)\s*Threads:\s*(\d+)\s*Questions:\s*(\d+)\s*Slow queries:\s*(\d+)\s*Opens:\s*(\d+)\s*Flush tables:\s*(\d+)\s*Open tables:\s*(\d+)\s*Queries per second avg:\s*([\.\d]+)\s*/is', $stat_string, $matched);
      $status = array(
         'uptime' => $matched[1], /<SPAN style="COLOR: %