mysql+php數據庫操作類
class DbQueryForMysql {
/**
* select方法返回的最大記錄數
*/
const MAX_ROW_NUM = 1000;
/**
* 數據查詢結果集對象
* @var object $dataSet
*/
public $dataSet = NULL ;
/**
* 數據源對象
* @var object $ds
*/
public $ds = NULL ;
/**
* 查詢的SQL語句
* @var string $sql
*/
public $sql = '' ;
public $transCnt = 0;
/**
* 執行查詢的模式，值為 OCI_COMMIT_ON_SUCCESS 或 OCI_DEFAULT
* @var string $excuteMode
*/
public $executeMode = 'OCI_COMMIT_ON_SUCCESS' ;
/**
* 構造函數
* @param object $ds 數據庫
* @param string $sql 要初始化查詢的SQL語句
*/
function __construct($ds=NULL , $sql=NULL) {
if (!$ds) {
$this->error(DbException::DB_UNCONNECTED, '數據庫還
未連接。');
} else {
$this->ds = $ds;
if ($sql) {
$this->open($sql);
}
}
}
/**
* 釋放所占用的內存
* @param object $dataSet 需要釋放資源的結果集
* @access public
*/
public function close($dataSet=NULL) {
if ($dataSet) {
@mysql_free_result($dataSet);
} else {
@mysql_free_result($this->dataSet);
$this->eof = false ;
$this->recordCount = 0 ;
$this->recNo = -1 ;
}
}
/**
* 對$pass進行數據庫加密,返回加密之後的值
* @param string $pass 要加密的字符串
* @return string
* @access public
*/
public function encodePassword($pass) {
return $this->getValue("SELECT password('$pass') AS pass");
}
/**
* 得到錯誤信息和錯誤代號
* @param integer $queryResult 查詢結果
* @return array
* @access protected
*/
protected function errorInfo($queryResult = NULL) {
$result['message'] = @mysql_error($this->ds->connect);
$result['code'] = @mysql_errno($this->ds->connect);
return $result;
}
/**
* 錯誤信息處理
* @param string $errorId 錯誤ID
* @param string $errorMessage 錯誤信息
* @access protected
*/
protected function error($errorId, $errorMessage) {
throw new DbException($errorMessage, $errorId);
}
/**
* 執行SQL語句
* @param string $sql SQL語句
* @return object
* @param int $rowFrom 啟始行號，行號從1開始
* @param int $rowTo 結束行號，值為0表示
* @access public
* @see DbQuery::open
*/
public function execute($sql = '', $rowFrom = 0, $rowTo =
self::MAX_ROW_NUM, $error = true) {
if ($rowTo != self::MAX_ROW_NUM || $rowFrom!=0) {
$nrows = $rowTo - $rowFrom + 1;
$start = $rowFrom - 1;
$start = ($start>=0) ? ((integer)$start) . ',' : '';
$sql .= ' limit ' . $start . $nrows;
}
//echo $sql.'<br>';
$dataSet = @mysql_query($sql, $this->ds->connect);
if (!$dataSet && $error) {
$sqlError = $this->errorInfo();
$errorMessage = '執行[<b><font color="#FF0000">' .
$sql
. '</font></b>]出錯！<br> <font
color=#FF0000> ['
. $sqlError['code'] . ']: '
. $sqlError['message'] . '</font>' ;
$this->error(DbException::DB_QUERY_ERROR,
$errorMessage);
}
return $dataSet;
}
/**
* 執行SQL語句，結果集保存到屬性$dataSet中
* @param string $sql SQL語句
* @param int $rowFrom 啟始行號，行號從1開始
* @param int $rowTo 結束行號，值為0表示
* @return object
* @access public
* @see DbQuery::execute
*/
public function open($sql='', $rowFrom = 0, $rowTo =
self::MAX_ROW_NUM) {
$this->dataSet = $this->execute($sql, $rowFrom, $rowTo);
$this->sql = $sql ;
return $this->dataSet;
}
/**
* 將一行的各字段值拆分到一個數組中
* @param object $dataSet 結果集
* @param integer $resultType 返回類型，OCI_ASSOC、OCI_NUM 或
OCI_BOTH
* @return array
*/
public function fetchRecord($dataSet=NULL, $resultType=MYSQL_BOTH) {
$result = @mysql_fetch_array(($dataSet) ? $dataSet : $this-
>dataSet, $resultType);
return $result;
}
/**
* 取得字段數量
* @param object $dataSet 結果集
* @return integer
*/
public function getFieldCount($dataSet = NULL) {
return mysql_num_fields(($dataSet) ? $dataSet : $this-
>dataSet);
}
/**
* 取得下一條記錄。返回記錄號，如果到了記錄尾，則返回FALSE
* @return integer
* @access public
* @see getPrior()
*/
public function next() {
return $this->fetchRecord();
}
/**
* 得到當前數據庫時間，格式為：yyyy-mm-dd hh:mm:ss
* @return string
* @access public
*/
public function getNow() {
return $this->getValue('SELECT NOW() AS dateOfNow');
}
/**
* 根據SQL語句從數據表中取數據，只取第一條記錄的值，
* 如果記錄中只有一個字段，則只返回字段值。
* 未找到返回 FALSE
*
* @param string $sql SQL語句
* @return array
* @access public
*/
public function getValue($sql = '') {
$dataSet = $this->execute($sql, 1, 1);
if ($result = $this->fetchRecord($dataSet)) {
$fieldCount = $this->getFieldCount($dataSet);
$this->close($dataSet);
return ($fieldCount<=1) ? $result[0] : $result;
} else {
return false ;
}
}
public function getInsertId() {
return $this->getValue('SELECT LAST_INSERT_ID()');
}
public function getSeq($seq = '') {
$this->execute('BEGIN TRANSACTION adodbseq');
$ok = $this->execute("update $seq with (tablock,holdlock)
set id = id + 1", 0, self::MAX_ROW_NUM, false);
if (!$ok) {
$this->execute("create table $seq (id float(53))");
$ok = $this->execute("insert into $seq with
(tablock,holdlock) values(1)", 0, self::MAX_ROW_NUM, false);
if (!$ok) {
$this->execute('ROLLBACK TRANSACTION
adodbseq');
return false;
}
$this->execute('COMMIT TRANSACTION adodbseq');
return 1;
}
$num = $this->getValue("select id from $seq");
$this->execute('COMMIT TRANSACTION adodbseq');
return $num;
}
/**
* 表是否存在，返回true
* @param string $tableName 要查詢的表名
* @return bool
* @access public
*/
public function tableIsExists($tableName) {
$result = @mysql_query('SELECT * FROM ' . $tableName . '
LIMIT 0,1', $this->db->connect);
return $result!==false;
}
/**
* 開始事務
* @access public
*/
public function begin() {
$this->transCnt += 1;
$this->execute('BEGIN TRAN');
return true;
}
/**
* 提交事務
* @access public
*/
public function commit() {
if ($this->transCnt) {
$this->transCnt -= 1;
}
$this->execute('COMMIT TRAN');
return true;
}
/**
* 回滾事務
* @access public
*/
public function rollback() {
if ($this->transCnt){
$this->transCnt -= 1;
}
$this->execute('ROLLBACK TRAN');
return true;
}
/**
* 插入一條記錄
* @param string $tableName 表名
* @param array $fieldArray 字段數組
* @param string $whereForUnique 唯一性條件
* @return int
* @access public
*/
public function insert($tableName, $fieldArray, $whereForUnique =
NULL) {
if (!$tableName || !$fieldArray || !is_array($fieldArray)) {
throw new Exception('參數 $tableName 或 $fieldArray
的值不合法！');
}
if ($whereForUnique) {
$where = ' WHERE ' . $whereForUnique;
$isExisted = $this->getValue('SELECT COUNT(*) FROM '
. $tableName . $where);
if ($isExisted) {
throw new DbException('記錄已經存在！',
DbException::DB_RECORD_IS_EXISTED);
}
}
$fieldNameList = array();
$fieldValueList = array();
foreach ($fieldArray as $fieldName => $fieldValue) {
if (!is_int($fieldName)) {
$fieldNameList[] = $fieldName;
$fieldValueList[] = ''' . $fieldValue .
''';
}
}
$fieldName = implode(',', $fieldNameList);
$fieldValue = implode(',', $fieldValueList);
$sql = 'INSERT INTO ' . $tableName . '('
. $fieldName . ') VALUES (' .
$fieldValue . ')';
return $this->execute($sql);
}
/**
* 更新一條記錄
* @param string $tableName 表名
* @param array $fieldArray 字段數組
* @param string $whereForUpdate 查詢條件
* @param string $whereForUnique 唯一性條件
* @return int
* @access public
*/
public function update($tableName, $fieldArray,
$whereForUpdate=NULL, $whereForUnique=NULL) {
if (!$tableName || !$fieldArray || !is_array($fieldArray)) {
throw new Exception('參數 $tableName 或 $fieldArray
的值不合法！');
}
if ($whereForUnique) {
$where = ' WHERE ' . $whereForUnique;
$isExisted = $this->getValue('SELECT COUNT(*) FROM '
. $tableName . $where);
if ($isExisted) {
throw new DbException('記錄已經存在！',
DbException::DB_RECORD_IS_EXISTED);
}
}
$fieldNameValueList = array();
foreach ($fieldArray as $fieldName => $fieldValue) {
if (!is_int($fieldName)) {
$fieldNameValueList[] = $fieldName . '='' .
$fieldValue . ''';
}
}
$fieldNameValue = implode(',', $fieldNameValueList);
if ($whereForUpdate) {
$whereForUpdate = ' WHERE ' . $whereForUpdate;
}
$sql = 'UPDATE ' . $tableName
. ' SET ' . $fieldNameValue .
$whereForUpdate;
return $this->execute($sql);
}
/**
* 選擇一條記錄
* @param string $sql sql語句
* @param string $dataFormat 返回數據格式, 值
有"array","hashmap","hashmap_str","dataset"
* @param int $rowFrom 啟始行號，行號從1開始
* @param int $rowTo 結束行號，值為0表示
* @result array
* @access public
*/
public function select($sql, $dataFormat = 'array', $rowFrom = 0,
$rowTo = self::MAX_ROW_NUM) {
$dataSet = $this->execute($sql, $rowFrom, $rowTo);
switch ($dataFormat) {
case 'array': //數組
$result = array();
$isMultiField = ($this->getFieldCount($dataSet) >
1);
$i = 0;
while ($data = $this->fetchRecord($dataSet)) {
$result[$i] = ($isMultiField) ? $data :
$data[0];
$i++;
}
$this->close($dataSet);
break;
case 'hashmap': //散列表
$result = array();
while ($data = $this->fetchRecord($dataSet)) {
$result[ $data[0] ] = $data[1];
}
$this->close($dataSet);
break;
case 'hashmap_str': //散列表字符串
$result = array();
while ($data = $this->fetchRecord($dataSet,
OCI_NUM)) {
$result[] = $data[0] . '=' . $data[1];
}
$result = implode('|', $result);
$this->close($dataSet);
break;
default: //dataset 數據集，當返回數據格式為數據集時，select
方法的功能與execute方法相同
$result = $dataSet;
}
return $result;
}
/**
* 返回最大值
* @param string $tableName 表名
* @param string $idField 字段名
* @param string $where 查詢條件
* @return int
* @access public
*/
public function getMax($tableName, $idField, $where = NULL) {
$where = ($where) ? (' WHERE ' . $where) : '';
return $this->getValue('SELECT MAX(' . $idField . ') FROM '
. $tableName . $where);
}
}
?>
