谁给我一个数据库操作的类 类里面能配置数据库服务器地址,账号密码等信息,然后能有执行SQL语句的方法并返回结果集,最重要的是得关闭数据库连接和释放结果集。。谁有比较完善的类吗。发个给我 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient;using System.Data;using System.Configuration;namespace DALDoc{ public static class DBHelper { private static SqlConnection connection; public static SqlConnection Connection { get { connection = new SqlConnection("server=.;databse=数据库名;uid=帐号;pwd=密码"); connection.Open(); return connection; } } public static int ExecuteCommand(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); int result = cmd.ExecuteNonQuery(); cmd.Connection.Close(); cmd.Connection.Dispose(); return result; } public static int ExecuteCommand(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); int result = cmd.ExecuteNonQuery(); cmd.Connection.Close(); cmd.Connection.Dispose(); return result; } public static int GetScalar(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); int result = Convert.ToInt32(cmd.ExecuteScalar()); cmd.Connection.Close(); cmd.Connection.Dispose(); return result; } public static int GetScalar(params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(); cmd.Connection = Connection; cmd.CommandText = "Pro_InsertOrder"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(values); int result = Convert.ToInt32(cmd.ExecuteScalar()); cmd.Connection.Close(); cmd.Connection.Dispose(); return result; } public static int GetScalar(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); int result = Convert.ToInt32(cmd.ExecuteScalar()); cmd.Connection.Close(); cmd.Connection.Dispose(); return result; } public static SqlDataReader GetReader(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader; } public static SqlDataReader GetReader(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader; } public static DataTable GetDataSet(string safeSql) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); return ds.Tables[0]; } public static DataTable GetDataSet(string sql, params SqlParameter[] values) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); return ds.Tables[0]; } }} class mysql{ //MYSQL版本 private $version = ''; //配置 private $config = ''; //连接是否开启 private $connected = false; //是否启用pconnect private $pconnect = false; //数据集ID private $queryID = 0; //SQL语句 private $queryStr = ''; //连接ID private $linkID = ''; //操作影响的数据行数 public $numRows = 0; //INSERT 以后返回的ID public $lastInsID = 0; //总页数 private $total; //分页数量 public $perpage = 0; //分页url private $url = ''; //分页名 public $pname ='go'; //分页样式 private $style = 0; /** * 架构函数 * @access public * @param array $config */ public function __construct($config = ''){ $this->config = $this->parseConfig($config); } /** * 分析数据库链接数组 * @access private * @param array $db_config */ private function parseConfig($dbConfig = ''){ if(!empty($dbConfig)){ return $dbConfig; }elseif (empty($dbConfig)){ //若配置为空,则读取配置文件 global $cfg; $dbConfig = array( 'host' => $cfg['db']['host'], 'name' => $cfg['db']['name'], 'user' => $cfg['db']['user'], 'password' => $cfg['db']['password'], 'pre' => $cfg['db']['pre'], 'charset' => $cfg['db']['charset'], 'hostport' => $cfg['db']['hostport'] ); return $dbConfig; } } /** * 连接数据库 * @access public * */ public function connect(){ if(!$this->connected){ $config = $this->config; if($this->pconnect) { $this->linkID = mysql_pconnect( $config['host'], $config['user'], $config['password']); }else{ $this->linkID = mysql_connect( $config['host'], $config['user'], $config['password']); } if(!$this->linkID || (!empty($config['name']) && !mysql_select_db($config['name'], $this->linkID)) ) { mysql_error() && die('Can not connect to MySQL server'); } $this->version = mysql_get_server_info($this->linkID); if($this->version >= '4.1'){ $charset = !empty($config['charset']) && in_array(strtolower($config['charset']),array('gbk','big5','utf-8')) ? str_replace('-','',$config['charset']) : 'utf8'; mysql_query("SET NAMES '".$charset."'", $this->linkID); } $this->connected = true; } } /** * 释放查询结果 * @access public */ public function free(){ mysql_free_result($this->queryID); $this->queryID = 0; } /** * 执行查询等操作 * 该方法针对SELECT * 返回数据集 * @access public * @param string $str */ public function query($str = ''){ $this->connect(); if($str != '') $this->queryStr = $str; //释放上次的查询结果 if($this->queryID){ $this->free(); } $this->queryID = mysql_query($this->queryStr, $this->linkID); if($this->queryID){ $this->numRows = mysql_num_rows($this->queryID); return $this->getAll(); } } /** * 执行sql操作 * 该方法针对 UPDATE INSERT DELETE * 返回数字 * @access public * @param string $str * @return integer */ public function execute($str = ''){ $this->connect(); if ( $str != '' ) $this->queryStr = $str; //释放前次的查询结果 if ($this->queryID){ $this->free(); } $result = mysql_query($this->queryStr, $this->linkID) ; if ( true === $result) { $this->lastInsID = mysql_insert_id(); $this->numRows = mysql_affected_rows($this->linkID); return $this->numRows; } } /** * 解析传过来的数组 * 与表中的字段进行对比,不相同的予以剔除 * 返回数组 * @access private * @param array $ety * @return array */ private function parseFields($ety,$table){ $this->connect(); $fields = array(); $result = mysql_query("SHOW COLUMNS FROM ".$this->getTableName($table)); while($r = mysql_fetch_array($result)){ $fields[] = $r['Field']; } foreach($ety AS $key=>$value){ if(!in_array($key,$fields)){ unset($ety[$key]); } } return $ety; } /** * 插入方法 * @access public * @param array $ety * @param string $table * @return integer */ public function insert($ety,$table){ $ety = $this->parseFields($ety,$table); $sql = "INSERT INTO ".$this->getTableName($table) ." (`".implode('`,`', array_keys($ety))."`) VALUES('".implode("','", $ety)."')"; return $this->execute($sql); } /** * 修改方法 * @access public * @param array $ety //数组中key跟参数key相同时内容可为单一 1 也可为连续字符 如 1,2,3,4 * @param string $table * @param string key 要根据什么字段来删除,也可为空 直接写后面的条件语句, * @return integer */ public function update($ety,$table,$key='id',$sqlWhere=''){ $ety = $this->parseFields($ety,$table); foreach($ety as $k=>$v){ if($k != $key){ $sql .= ",`$k`='$v'"; } } //截取第一位的',' $sql = substr($sql, 1); if(empty($sqlWhere)){ $sqlWhere = " WHERE `$key` in ('".$ety[$key]."')"; } $sql = "UPDATE ".$this->getTableName($table)." SET ".$sql.$sqlWhere; return $this->execute($sql); }我也是新手 这是上个项目自己写的~~~ 接上面的/** * 删除方法 * @access public * @param string $id 可为单一 1 也可为连续字符 如 1,2,3,4 * @param string $table * @param string key 要根据什么字段来删除,也可为空 直接写后面的条件语句 * @return integer */ public function delete($id,$table,$key='id',$sqlWhere=''){ if(is_array($id)){ $id = implode("','", $id); } if(empty($sqlWhere)){ $sqlWhere = " WHERE `$key` in ('".$id."')"; } $sql = "DELETE FROM ".$this->getTableName($table).$sqlWhere; return $this->execute($sql); } /** * ---------------------------------------------------------- * 查询方法 * @access public * @param string $sql * @param string $limit * @param integer $perpage * */ public function select($sql,$limit=''){ if(!empty($limit)){ if(strpos($limit,',')){ $this->query($sql); $this->total = $this->numRows; } } return $this->query($sql.$this->parseLimit($limit)); } /** * 编辑调取单条数据的方法 * @access public * @param string $table * @param integer $id //id * @param string 默认为id * @param string 字段名 默认为* 也可为array类型 string类型则需要加上字段符号`,字段用,隔开 例如 `id`,`key`,`name`,`age` */ public function fetchId($table,$id,$key = "id",$keys = "*") { if(is_array($keys)){ foreach($keys as $k=>$v){ $keys[$k] = $this->addSpecialChar($v); } $keys = implode(',',$keys); } $sql = "SELECT $keys FROM ".$this->getTableName($table)." WHERE `$key`='$id'"; $result = $this->query($sql); return $result[0]; } /** * 解析limit * @access private * @param string $limit * @param integer $page * @param integer $perpage */ private function parseLimit($limit = ''){ if(!empty($limit)){ if(strpos($limit,',')){ $limit = explode(',',$limit); $this->perpage = $limit[1]; $page = $this->getCurrPage(); $offset = intval($this->perpage)*(intval($page)-1); return ' LIMIT '.$offset.','.intval($this->perpage).' '; }else{ return !empty($limit)?' LIMIT '.$limit.' ':''; } } } /** * 给所有的字段和表添加· * 保证该sql语句不会因为有关键字而出错 * 返回字符串 * @access private * @param string $key * @return string */ private function addSpecialChar($key){ $key = trim($key); if( false !== strpos($key,' ') || false !== strpos($key,',') || false !== strpos($key,'*') || false !== strpos($key,'(') || false !== strpos($key,'.') || false !== strpos($key,'`')) { }else{ $key = '`'.$key.'`'; } return $key; } /** * 获得所有查询的数据 * 返回数组 * @access private * @return array $result */ private function getAll(){ $result = array(); if($this->numRows>0){ while($row = mysql_fetch_assoc($this->queryID)){ $result[] = $row; } mysql_data_seek($this->queryID,0); } return $result; } /** * 关闭数据库 * @access public */ public function close() { if (!empty($this->queryID)) mysql_free_result($this->queryID); if (is_resource($this->linkID) && !mysql_close($this->linkID)){ mysql_error() && die('Cannot link msyql server'); } $this->linkID = 0; } /** * 析构函数 * @access public */ public function __destruct(){ //关闭连接 $this->close(); } /** * sql指令安全过滤 * @access public * @param string $str * @return string */ public function escape_string($str){ return mysql_escape_string($str); } /** * 获取加前缀的表名 * @access public * @param string $table * @return string */ public function getTableName($table){ return $this->addSpecialChar($this->config['pre'].$table); } 费那事干什么? mysqli 不就是累了吗?PDO 不更好?什么数据库都可操作 LSD挂那么多的勋章?3楼的写那么多,只要其中的一部分就行了 <?php/** * 创建连接数据库的连接 * @author chenqiao * @date 2010-8-18 */class Model_CommConnect{ private $_logger; private $_host; private $_userName; private $_password; private $_dbName; private $_conn; private static $_stat; public function __construct() { $this->_logger = Service_Logger::getInstance(); $this->_host = Config::$dbHost; $this->_userName = Config::$dbUserName; $this->_password = Config::$dbPwd; $this->_dbName = Config::$dbName; } /** * 创建数据库连接,并返回连接 * @author chenqiao * @date 2010-8-18 * @return 返回一个数据库连接 */ public function dbConnect() { $this->_logger->log('[Model_CommConnect.dbConnect]:创建数据库连接,并返回连接。'); self::$_stat = true; $this->_conn = mysql_connect($this->_host , $this->_userName , $this->_password); if(!$this->_conn) { $this->_logger->log('[Model_CommConnect.dbConnect]:【error】Could not connect:'. mysql_error()); } mysql_select_db($this->_dbName , $this->_conn) or $this->_logger->log('[Model_CommConnect.dbConnect]:【error】Could not select database euroweb :'. mysql_error()); mysql_query('set names "utf8"'); return $this->_conn; } /** * 数据库查询类 * @author chenqiao * @date 2010-8-24 * @return 返回查询结果 */ public function query($sql) { $this->_logger->log('[Model_CommConnect.query]:数据库查询。'); $query = mysql_query($sql,$this->_conn); $result = array(); while ($row = mysql_fetch_array($query,MYSQL_ASSOC)){ $result[] = $row; } return $result; } /** * 数据库修改操作 * @author chengan * @date 2010-09-08 * @param string $table 操作数据表 * @param array $set 修改的内容数组 * @param string $where 修改的条件 * @return 返回修改成功影响的记录数 */ public function update($table,$arr_set,$where){ $this->_logger->log('[Model_CommConnect.update]:数据库修改操作。'); $sql = "update $table set "; foreach ($arr_set as $k=>$v){ if (gettype($v) == 'string'){ $sql .= "$k='$v',"; }else{ $sql .= "$k=$v,"; } } $sql = substr($sql,0,strlen($sql)-1); $sql .= " where $where"; if(mysql_query($sql,$this->_conn)){ return mysql_affected_rows(); }else{ $this->_logger->log('[Model_CommConnect.update]:数据库修改操作失败。'); return 0; } } /** * 数据库删除操作 * @author chengan * @date 2010-09-09 * @param string $table 操作数据表 * @param string $where 删除的条件 * @return 返回删除成功影响的记录数 */ public function delete($table,$where){ $this->_logger->log('[Model_CommConnect.delete]:数据库删除操作。'); $sql = "delete from $table where $where"; if(mysql_query($sql,$this->_conn)){ return mysql_affected_rows(); }else{ $this->_logger->log('[Model_CommConnect.delete]:数据库删除操作失败。'); return 0; } } /** * 数据库添加操作 * @author chengan * @date 2010-09-10 * @param string $table 操作表名 * @param array $row 添加数据数组,以"列名"=>"数据"的格式格式构造插入数组,插入数据行 * @param boolean $isGetId 操作成功是否返回插入记录ID true为返回ID falsh为返回影响记录数 * @return 返回插入记录ID或返回影响记录数 */ public function insert($table,$row,$isGetId = false){ $this->_logger->log('[Model_CommConnect.insert]:数据库添加操作。'); $sql = "insert into $table set "; foreach ($row as $k=>$v){ if (gettype($v) == 'string'){ $sql .= "$k='$v',"; }else{ $sql .= "$k=$v,"; } } $sql = substr($sql,0,strlen($sql)-1); if(mysql_query($sql,$this->_conn)){ if ($isGetId){ return mysql_insert_id(); } return mysql_affected_rows(); }else{ $this->_logger->log('[Model_CommConnect.insert]:数据库添加操作失败。'); return 0; } } /** * 关闭数据库连接 * @author chengan * @date 2010-09-30 */ public function closeConnection(){ if(self::$_stat){ mysql_close($this->_conn); } self::$_stat = false; $this->_logger->log('[Model_CommConnect.closeConnection]:关闭数据库连接。'); } }把log去掉 crul抓取网页失败 求一Mysql 优化方案 sql 查询等于某几个id的数据的语句怎么写 对象为什么不自动初始化 mysql_connect 连接数据库出错后下面的代码就不执行,是这样的吗? php在北京? 在线等啊!!!请高手指点指点.php与oracle连接问题!!!! 我在php自定义函数中用eregi函数,怎么没用?? 很急啊,能帮我理解这篇文章的意思吗? 想pdo同时操作两个数据库,只能做两个pdo的连线吗 关于弹出登陆框的问题 如何删除一条数据的同时将这条数据插入另一张表
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DALDoc
{
public static class DBHelper
{ private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
connection = new SqlConnection("server=.;databse=数据库名;uid=帐号;pwd=密码");
connection.Open();
return connection;
}
} public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
cmd.Connection.Dispose();
return result;
} public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
cmd.Connection.Dispose();
return result;
} public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Connection.Close();
cmd.Connection.Dispose();
return result;
} public static int GetScalar(params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = "Pro_InsertOrder";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Connection.Close();
cmd.Connection.Dispose();
return result;
} public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Connection.Close();
cmd.Connection.Dispose();
return result;
} public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
} public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
} public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
} public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
}
}
class mysql{
//MYSQL版本
private $version = '';
//配置
private $config = '';
//连接是否开启
private $connected = false;
//是否启用pconnect
private $pconnect = false;
//数据集ID
private $queryID = 0;
//SQL语句
private $queryStr = '';
//连接ID
private $linkID = '';
//操作影响的数据行数
public $numRows = 0;
//INSERT 以后返回的ID
public $lastInsID = 0;
//总页数
private $total;
//分页数量
public $perpage = 0;
//分页url
private $url = '';
//分页名
public $pname ='go';
//分页样式
private $style = 0;
/**
* 架构函数
* @access public
* @param array $config
*/
public function __construct($config = ''){
$this->config = $this->parseConfig($config);
}
/**
* 分析数据库链接数组
* @access private
* @param array $db_config
*/
private function parseConfig($dbConfig = ''){
if(!empty($dbConfig)){
return $dbConfig;
}elseif (empty($dbConfig)){
//若配置为空,则读取配置文件
global $cfg;
$dbConfig = array(
'host' => $cfg['db']['host'],
'name' => $cfg['db']['name'],
'user' => $cfg['db']['user'],
'password' => $cfg['db']['password'],
'pre' => $cfg['db']['pre'],
'charset' => $cfg['db']['charset'],
'hostport' => $cfg['db']['hostport']
);
return $dbConfig;
}
}
/**
* 连接数据库
* @access public
*
*/
public function connect(){
if(!$this->connected){
$config = $this->config;
if($this->pconnect) {
$this->linkID = mysql_pconnect( $config['host'], $config['user'], $config['password']);
}else{
$this->linkID = mysql_connect( $config['host'], $config['user'], $config['password']);
}
if(!$this->linkID || (!empty($config['name']) && !mysql_select_db($config['name'], $this->linkID)) ) {
mysql_error() && die('Can not connect to MySQL server');
}
$this->version = mysql_get_server_info($this->linkID);
if($this->version >= '4.1'){
$charset = !empty($config['charset']) && in_array(strtolower($config['charset']),array('gbk','big5','utf-8')) ? str_replace('-','',$config['charset']) : 'utf8';
mysql_query("SET NAMES '".$charset."'", $this->linkID);
}
$this->connected = true;
}
}
/**
* 释放查询结果
* @access public
*/
public function free(){
mysql_free_result($this->queryID);
$this->queryID = 0;
}
/**
* 执行查询等操作
* 该方法针对SELECT
* 返回数据集
* @access public
* @param string $str
*/
public function query($str = ''){
$this->connect();
if($str != '') $this->queryStr = $str;
//释放上次的查询结果
if($this->queryID){
$this->free();
}
$this->queryID = mysql_query($this->queryStr, $this->linkID);
if($this->queryID){
$this->numRows = mysql_num_rows($this->queryID);
return $this->getAll();
}
}
/**
* 执行sql操作
* 该方法针对 UPDATE INSERT DELETE
* 返回数字
* @access public
* @param string $str
* @return integer
*/
public function execute($str = ''){
$this->connect();
if ( $str != '' ) $this->queryStr = $str;
//释放前次的查询结果
if ($this->queryID){
$this->free();
}
$result = mysql_query($this->queryStr, $this->linkID) ;
if ( true === $result) {
$this->lastInsID = mysql_insert_id();
$this->numRows = mysql_affected_rows($this->linkID);
return $this->numRows;
}
}
/**
* 解析传过来的数组
* 与表中的字段进行对比,不相同的予以剔除
* 返回数组
* @access private
* @param array $ety
* @return array
*/
private function parseFields($ety,$table){
$this->connect();
$fields = array();
$result = mysql_query("SHOW COLUMNS FROM ".$this->getTableName($table));
while($r = mysql_fetch_array($result)){
$fields[] = $r['Field'];
}
foreach($ety AS $key=>$value){
if(!in_array($key,$fields)){
unset($ety[$key]);
}
}
return $ety;
}
/**
* 插入方法
* @access public
* @param array $ety
* @param string $table
* @return integer
*/
public function insert($ety,$table){
$ety = $this->parseFields($ety,$table);
$sql = "INSERT INTO ".$this->getTableName($table) ." (`".implode('`,`', array_keys($ety))."`) VALUES('".implode("','", $ety)."')";
return $this->execute($sql);
}
/**
* 修改方法
* @access public
* @param array $ety //数组中key跟参数key相同时内容可为单一 1 也可为连续字符 如 1,2,3,4
* @param string $table
* @param string key 要根据什么字段来删除,也可为空 直接写后面的条件语句,
* @return integer
*/
public function update($ety,$table,$key='id',$sqlWhere=''){
$ety = $this->parseFields($ety,$table);
foreach($ety as $k=>$v){
if($k != $key){
$sql .= ",`$k`='$v'";
}
}
//截取第一位的','
$sql = substr($sql, 1);
if(empty($sqlWhere)){
$sqlWhere = " WHERE `$key` in ('".$ety[$key]."')";
}
$sql = "UPDATE ".$this->getTableName($table)." SET ".$sql.$sqlWhere;
return $this->execute($sql);
}我也是新手 这是上个项目自己写的~~~
接上面的/**
* 删除方法
* @access public
* @param string $id 可为单一 1 也可为连续字符 如 1,2,3,4
* @param string $table
* @param string key 要根据什么字段来删除,也可为空 直接写后面的条件语句
* @return integer
*/
public function delete($id,$table,$key='id',$sqlWhere=''){
if(is_array($id)){
$id = implode("','", $id);
}
if(empty($sqlWhere)){
$sqlWhere = " WHERE `$key` in ('".$id."')";
}
$sql = "DELETE FROM ".$this->getTableName($table).$sqlWhere;
return $this->execute($sql);
}
/**
* ----------------------------------------------------------
* 查询方法
* @access public
* @param string $sql
* @param string $limit
* @param integer $perpage
*
*/
public function select($sql,$limit=''){
if(!empty($limit)){
if(strpos($limit,',')){
$this->query($sql);
$this->total = $this->numRows;
}
}
return $this->query($sql.$this->parseLimit($limit));
}
/**
* 编辑调取单条数据的方法
* @access public
* @param string $table
* @param integer $id //id
* @param string 默认为id
* @param string 字段名 默认为* 也可为array类型 string类型则需要加上字段符号`,字段用,隔开 例如 `id`,`key`,`name`,`age`
*/
public function fetchId($table,$id,$key = "id",$keys = "*") {
if(is_array($keys)){
foreach($keys as $k=>$v){
$keys[$k] = $this->addSpecialChar($v);
}
$keys = implode(',',$keys);
}
$sql = "SELECT $keys FROM ".$this->getTableName($table)." WHERE `$key`='$id'";
$result = $this->query($sql);
return $result[0];
}
/**
* 解析limit
* @access private
* @param string $limit
* @param integer $page
* @param integer $perpage
*/
private function parseLimit($limit = ''){
if(!empty($limit)){
if(strpos($limit,',')){
$limit = explode(',',$limit);
$this->perpage = $limit[1];
$page = $this->getCurrPage();
$offset = intval($this->perpage)*(intval($page)-1);
return ' LIMIT '.$offset.','.intval($this->perpage).' ';
}else{
return !empty($limit)?' LIMIT '.$limit.' ':'';
}
}
}
/**
* 给所有的字段和表添加·
* 保证该sql语句不会因为有关键字而出错
* 返回字符串
* @access private
* @param string $key
* @return string
*/
private function addSpecialChar($key){
$key = trim($key);
if( false !== strpos($key,' ') || false !== strpos($key,',') || false !== strpos($key,'*') || false !== strpos($key,'(') || false !== strpos($key,'.') || false !== strpos($key,'`')) {
}else{
$key = '`'.$key.'`';
}
return $key;
}
/**
* 获得所有查询的数据
* 返回数组
* @access private
* @return array $result
*/
private function getAll(){
$result = array();
if($this->numRows>0){
while($row = mysql_fetch_assoc($this->queryID)){
$result[] = $row;
}
mysql_data_seek($this->queryID,0);
}
return $result;
}
/**
* 关闭数据库
* @access public
*/
public function close() {
if (!empty($this->queryID))
mysql_free_result($this->queryID);
if (is_resource($this->linkID) && !mysql_close($this->linkID)){
mysql_error() && die('Cannot link msyql server');
}
$this->linkID = 0;
}
/**
* 析构函数
* @access public
*/
public function __destruct(){
//关闭连接
$this->close();
}
/**
* sql指令安全过滤
* @access public
* @param string $str
* @return string
*/
public function escape_string($str){
return mysql_escape_string($str);
}
/**
* 获取加前缀的表名
* @access public
* @param string $table
* @return string
*/
public function getTableName($table){
return $this->addSpecialChar($this->config['pre'].$table);
}
PDO 不更好?什么数据库都可操作
<?php
/**
* 创建连接数据库的连接
* @author chenqiao
* @date 2010-8-18
*/class Model_CommConnect
{
private $_logger;
private $_host;
private $_userName;
private $_password;
private $_dbName;
private $_conn;
private static $_stat;
public function __construct()
{
$this->_logger = Service_Logger::getInstance();
$this->_host = Config::$dbHost;
$this->_userName = Config::$dbUserName;
$this->_password = Config::$dbPwd;
$this->_dbName = Config::$dbName;
}
/**
* 创建数据库连接,并返回连接
* @author chenqiao
* @date 2010-8-18
* @return 返回一个数据库连接
*/
public function dbConnect() {
$this->_logger->log('[Model_CommConnect.dbConnect]:创建数据库连接,并返回连接。');
self::$_stat = true;
$this->_conn = mysql_connect($this->_host , $this->_userName , $this->_password);
if(!$this->_conn) {
$this->_logger->log('[Model_CommConnect.dbConnect]:【error】Could not connect:'. mysql_error());
}
mysql_select_db($this->_dbName , $this->_conn)
or $this->_logger->log('[Model_CommConnect.dbConnect]:【error】Could not select
database euroweb :'. mysql_error());
mysql_query('set names "utf8"');
return $this->_conn;
}
/**
* 数据库查询类
* @author chenqiao
* @date 2010-8-24
* @return 返回查询结果
*/
public function query($sql)
{
$this->_logger->log('[Model_CommConnect.query]:数据库查询。');
$query = mysql_query($sql,$this->_conn);
$result = array();
while ($row = mysql_fetch_array($query,MYSQL_ASSOC)){
$result[] = $row;
}
return $result;
}
/**
* 数据库修改操作
* @author chengan
* @date 2010-09-08
* @param string $table 操作数据表
* @param array $set 修改的内容数组
* @param string $where 修改的条件
* @return 返回修改成功影响的记录数
*/
public function update($table,$arr_set,$where){
$this->_logger->log('[Model_CommConnect.update]:数据库修改操作。');
$sql = "update $table set ";
foreach ($arr_set as $k=>$v){
if (gettype($v) == 'string'){
$sql .= "$k='$v',";
}else{
$sql .= "$k=$v,";
}
}
$sql = substr($sql,0,strlen($sql)-1);
$sql .= " where $where";
if(mysql_query($sql,$this->_conn)){
return mysql_affected_rows();
}else{
$this->_logger->log('[Model_CommConnect.update]:数据库修改操作失败。');
return 0;
}
}
/**
* 数据库删除操作
* @author chengan
* @date 2010-09-09
* @param string $table 操作数据表
* @param string $where 删除的条件
* @return 返回删除成功影响的记录数
*/
public function delete($table,$where){
$this->_logger->log('[Model_CommConnect.delete]:数据库删除操作。');
$sql = "delete from $table where $where";
if(mysql_query($sql,$this->_conn)){
return mysql_affected_rows();
}else{
$this->_logger->log('[Model_CommConnect.delete]:数据库删除操作失败。');
return 0;
}
}
/**
* 数据库添加操作
* @author chengan
* @date 2010-09-10
* @param string $table 操作表名
* @param array $row 添加数据数组,以"列名"=>"数据"的格式格式构造插入数组,插入数据行
* @param boolean $isGetId 操作成功是否返回插入记录ID true为返回ID falsh为返回影响记录数
* @return 返回插入记录ID或返回影响记录数
*/
public function insert($table,$row,$isGetId = false){
$this->_logger->log('[Model_CommConnect.insert]:数据库添加操作。');
$sql = "insert into $table set ";
foreach ($row as $k=>$v){
if (gettype($v) == 'string'){
$sql .= "$k='$v',";
}else{
$sql .= "$k=$v,";
}
}
$sql = substr($sql,0,strlen($sql)-1);
if(mysql_query($sql,$this->_conn)){
if ($isGetId){
return mysql_insert_id();
}
return mysql_affected_rows();
}else{
$this->_logger->log('[Model_CommConnect.insert]:数据库添加操作失败。');
return 0;
}
}
/**
* 关闭数据库连接
* @author chengan
* @date 2010-09-30
*/
public function closeConnection(){
if(self::$_stat){
mysql_close($this->_conn);
}
self::$_stat = false;
$this->_logger->log('[Model_CommConnect.closeConnection]:关闭数据库连接。');
}
}
把log去掉