MSSQL内外连接(INNER JOIN)语句详解

转载自:http://www.jb51.net/article/4848.htm

在之前,我对MSSQL中的内连接和外连接所得出的数据集不是很清楚。这几天重新温习了一下SQL的书本,现在的思路应该是很清楚了,现在把自己的理解发出来给大家温习下。希望和我一样对SQL的连接语句不太理解的朋友能够有所帮助。(发这么菜的教程,各位大大们别笑话偶了,呵:D )

有两个表A和表B。

表A结构如下:
Aid:int;标识种子,主键,自增ID
Aname:varchar

数据情况,即用select * from A出来的记录情况如下图1所示:

图1:A表数据

表B结构如下:
Bid:int;标识种子,主键,自增ID
Bnameid:int

数据情况,即用select * from B出来的记录情况如下图2所示:


图2:B表数据

为了把Bid和Aid加以区分,不让大家有误解,所以把Bid的起始种子设置为100。
有SQL基本知识的人都知道,两个表要做连接,就必须有个连接字段,从上表中的数据可以看出,在A表中的Aid和B表中的Bnameid就是两个连接字段。
下图3说明了连接的所有记录集之间的关系:

图3:连接关系图

现在我们对内连接和外连接一一讲解。
1.内连接:利用内连接可获取两表的公共部分的记录,即图3的记录集C
语句如下:Select * from A JOIN B ON A.Aid=B.Bnameid
运行结果如下图4所示:

图4:内连接数据

其实select * from A,B where A.Aid=B.Bnameid与Select * from A JOIN B ON A.Aid=B.Bnameid的运行结果是一样的。
2.外连接:外连接分为两种,一种是左连接(Left JOIN)和右连接(Right JOIN)
(1)左连接(Left JOIN):即图3公共部分记录集C+表A记录集A1。
语句如下:select * from A Left JOIN B ON A.Aid=B.Bnameid
运行结果如下图5所示:


图5:左连接数据

说明:
在语句中,A在B的左边,并且是Left Join,所以其运算方式为:A左连接B的记录=图3公共部分记录集C+表A记录集A1
在图3中即记录集C中的存在的Aid为:2 3 6 7 8
图1中即表A所有记录集A中存在的Aid为:1 2 3 4 5 6 7 8 9
表A记录集A1中存在的Aid=(图1中即A表中所有Aid)-(图3中即记录集C中存在的Aid),最终得出为:1 4 5 9
由此得出图5中A左连接B的记录=图3公共部分记录集C+表A记录集A1,
最终得出的结果图5中可以看出Bnameid及Bid非NULL的记录都为图3公共部分记录集C中的记录;Bnameid及Bid为NULL的Aid为1 4 5 9的四笔记录就是表A记录集A1中存在的Aid。

(2)右连接(Right JOIN):即图3公共部分记录集C+表B记录集B1。
语句如下:select * from A Right JOIN B ON A.Aid=B.Bnameid
运行结果如下图6所示:


图6:右连接数据

说明:
在语句中,A在B的左边,并且是Right Join,所以其运算方式为:A右连接B的记录=图3公共部分记录集C+表B记录集B1
在图3中即记录集C中的存在的Aid为:2 3 6 7 8
图2中即表B所有记录集B中存在的Bnameid为:2 3 6 7 8 11
表B记录集B1中存在的Bnameid=(图2中即B表中所有Bnameid)-(图3中即记录集C中存在的Aid),最终得出为:11
由此得出图6中A右连接B的记录=图3公共部分记录集C+表B记录集B1,
最终得出的结果图6中可以看出Aid及Aname非NULL的记录都为图3公共部分记录集C中的记录;Aid及Aname为NULL的Aid为11的记录就是表B记录集B1中存在的Bnameid。

总结:

通过上面的运算解说,相信很多人已经想到,上面的情况(包括图3的关系图)说明的都只是A在B的左边的情况,
以下语句B在A的右边的又会出现什么情况呢??
select * from B Left JOIN A ON A.Aid=B.Bnameid
select * from B Right JOIN A ON A.Aid=B.Bnameid

其实对图3左右翻转一下就可以得出以下结论:
select * from B Left JOIN A ON A.Aid=B.Bnameid和select * from A Right JOIN B ON A.Aid=B.Bnameid所得出的记录集是一样的

select * from B Right JOIN A ON A.Aid=B.Bnameid和select * from A Left JOIN B ON A.Aid=B.Bnameid所得出的记录集也是一样的。

第一次写类似教程的贴子,希望和我一样对SQL的连接语句不太理解的朋友能够有所帮助。谢谢大家支持。

主从数据库的IP地址更换时,用到的自动替换功能

主从数据库的IP地址更换时,用到的自动替换功能
这也是工作中遇到的,自己想办法做的自动替换功能,节约很多的时间,办公中能自动的东西就尽量去自动化,节约时间,提高效率。

<?php 
/*
 * 在文件配置中大多数的数据库链接定义的格式是define("TEST_MASTER_DB_HOST", "192.168.1.1"); 现在要替换成define("TEST_MASTER_DB_HOST", "192.168.1.10");
 * 该PHP代码就是用来自动替换Ip地址
 */
$dbNameArr      =    array("testDb",'newDb');
$prefixArr[]    =    array('_MASTER_','_SLAVE_');
$dirPath        =    'D:/wamp/www/test/';
$pathArr        =    array();
$replaceArr     =    array();
// replace start ====================================================================
foreach ($prefixArr[0] as $v)
{
    foreach ($dbNameArr as $val)
    {
        $dbNameUp        =    strtoupper($val);
        $val1            =    'define("'.$dbNameUp.$v.'DB_HOST", "192.168.65.1");';
        $val2            =    'define("'.$dbNameUp.$v.'DB_HOST", "192.168.65.2");';
        $val3            =    'define("'.$dbNameUp.$v.'DB_HOST", "192.168.85.3");';
        $replaceArr[]    =    array($val1,$val3);
        $replaceArr[]    =    array($val2,$val3);
    }
}
foreach ($prefixArr[1] as $v)
{
    foreach ($dbNameArr as $val)
    {
        $dbNameUp        =    strtoupper($val);
        $val1            =    'define("'.$dbNameUp.$v.'DB_HOST", "192.168.65.10");';
        $val2            =    'define("'.$dbNameUp.$v.'DB_HOST", "192.168.65.20");';
        $val3            =    'define("'.$dbNameUp.$v.'DB_HOST", "192.168.85.30");';
        $replaceArr[]    =    array($val1,$val3);
        $replaceArr[]    =    array($val2,$val3);
    }
}
$pathArr    =    file_list($dirPath);
//print_r($pathArr);exit;
 
foreach ($pathArr as $filePath)
{
    replace($filePath,$replaceArr);
}
// replace end ====================================================================
 
function replace($filePath,$replaceArr)
{
    $oldData    =    file_get_contents($filePath);
    foreach ($replaceArr as $v)
    {
        $oldData    =    str_replace($v[0],$v[1],$oldData);
    }
    file_put_contents($filePath,$oldData);
}
function file_list($path)
{
    global $pathArr;
    if ($handle = opendir($path))
    {
        while (false !== ($file = readdir($handle)))
        {
            if ($file != "." && $file != "..")
            {
                //echo $path.": ".$file."<br>";
                if (is_dir($path."/".$file))
                {
                    file_list($path."/".$file);
                }
                else
                {
                    $pathArr[]    = $path."/".$file;
                }
            }
        }
    }
    return $pathArr;
}
?>

常用MYSQL中的SQL语句记录

数据库中的范式说明,以前在文豆培训的时候,宋老师说了什么第一第二第三范式,当时也不是太懂,现在又回头看了一下,看了网上几个简单的例子,觉得不是太难,贴上来,现在在公司经常会涉及到设计数据库,兵家打仗,粮草先行,做网站,数据库必须先设计好。
第一范式:(1NF)无重复的列
表:编号 学生姓名 学生身高 班级名称 班级大小
第二范式:(2NF)属性完全依赖于主键
表:编号 学生姓名 学生身高 班级名称 班级大小
改为
表1: 编号 学生姓名 学生身高
表2: 编号 班级名称 班级大小
第三范式:(3NF)属性不依赖于其它非主属性
表1: 编号 学生姓名 学生身高 班级编号(此时这里不应该再有 班级名称、班级大小这些字段信息)
表2: 编号 班级名称 班级大小

<?php
// ========================================================
// 下面这段代码展示了如何创建主数据库,并创建主从用户名、密码,并赋予主数据库所有权限,从数据库只读权限
$dbName		=	'test_db';
$userName	=	'test_db_user';
$userPass	=	'test_db_pass';
 
$readDbName		=	'readtest_db';
$readUserName	=	'readtest_db_user';
$readUserPass	=	'readtest_db_pass';
 
$sql[0]=	"CREATE DATABASE  `$dbName` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
$sql[1]=	"CREATE USER '$userName'@'%' IDENTIFIED BY  '$userPass';";
$sql[2]=	"GRANT USAGE ON * . * TO  '$userName'@'%' IDENTIFIED BY  '$userPass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
$sql[3]=	"GRANT ALL PRIVILEGES ON  `$dbName` . * TO  '$userName'@'%' WITH GRANT OPTION ;";
$sql[4]=	"CREATE USER '$userName'@'localhost' IDENTIFIED BY  '$userPass';";
$sql[5]=	"GRANT USAGE ON * . * TO  '$userName'@'localhost' IDENTIFIED BY  '$userPass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
$sql[6]=	"GRANT ALL PRIVILEGES ON  `$dbName` . * TO  '$userName'@'localhost' WITH GRANT OPTION ;";
 
$sql[7]=	"CREATE USER '$readUserName'@'%' IDENTIFIED BY  '$readUserPass';";
$sql[8]=	"GRANT USAGE ON * . * TO  '$readUserName'@'%' IDENTIFIED BY  '$readUserPass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
$sql[9]=	"GRANT SELECT ON  `$readDbName` . * TO  '$readUserName'@'%';";
$sql[10]=	"CREATE USER '$readUserName'@'localhost' IDENTIFIED BY  '$readUserPass';";
$sql[11]=	"GRANT USAGE ON * . * TO  '$readUserName'@'localhost' IDENTIFIED BY  '$readUserPass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
$sql[12]=	"GRANT SELECT ON  `$readDbName` . * TO  '$readUserName'@'%';";
 
mysql_connect('localhost','root','') or die('die db');
 
foreach ($sql as $v)
{
	$re	=	mysql_query($v)==true ? 'true' : 'false'; 	
	echo $re.'<br />';
}
 
// ========================================================
// 下面这些SQL片段是摘自w3school的学习笔记,大家可以参考
 
//显示数据库	
$sql	=	"SHOW DATABASES;";
//创建数据库	
$sql	=	"CREATE DATABASE  `testaa` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
//删除数据库	
$sql	=	"DROP DATABASE  `testaa`;";
//创建表	
$sql	=	"CREATE TABLE  `testaa`.`test` (`id` INT UNSIGNED NULL AUTO_INCREMENT PRIMARY KEY) ENGINE = MYISAM ;";
//删除表	
$sql	=	"DROP TABLE `test`;";
//显示表	
$sql	=	"SHOW TABLES;";
//描述表	
$sql	=	"DESCRIBE `test`;";
//插入表	
$sql	=	"INSERT INTO `testaa`.`test` (`id`) VALUES ('2'), ('3');";
 
// ========================================================
// SQL用法
 
//ORDER BY用法
$sql	=	"SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber;";
$sql	=	"SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC;";
 
//TOP用法
$sql	=	"SELECT TOP 2 * FROM Persons;";
$sql	=	"SELECT TOP 50 PERCENT * FROM Persons;";
 
//LIKE和NOT LIKE用法 
$sql	=	"SELECT * FROM Persons WHERE City LIKE '%g';";
$sql	=	"SELECT * FROM Persons WHERE City NOT LIKE '%lon%';";
 
//_通配符,匹配单个字符,(可以匹配Carter)
$sql	=	"SELECT * FROM Persons WHERE LastName LIKE 'C_r_er';";  
 
//[]通配符
//现在,我们希望从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人:
$sql	=	"SELECT * FROM Persons WHERE City LIKE '[ALN]%';";
//现在,我们希望从上面的 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人:
$sql	=	"SELECT * FROM Persons WHERE City LIKE '[!ALN]%';";
 
// IN用法
$sql	=	"SELECT * FROM Persons WHERE LastName IN ('Adams','Carter');";
 
//BETWEEN  AND和NOT BETWEEN  AND用法
$sql	=	"SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter';";
 
//AS用法
$sql	=	"SELECT LastName AS Family, FirstName AS Name FROM Persons;";
 
// 多表查询
$sql	=	"SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P;";
 
// JOIN用法
$sql	=	"SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName;";
 
/*
下面列出了您可以使用的 JOIN 类型,以及它们之间的差异。
JOIN: 如果表中有至少一个匹配,则返回行
LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配,就返回行
*/
 
//UNION和UNION ALL用法,UNION会去掉重复的值,UNION ALL不会去除重复
$sql	=	"SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA;";
$sql	=	"SELECT E_Name FROM Employees_China UNION ALL SELECT E_Name FROM Employees_USA;";
 
//SELECT INTO用法(可以用来制作备份文件)
$sql	=	"SELECT * INTO Persons_backup FROM Persons;";
$sql	=	"SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P;";
 
// SQL UNIQUE约束,CONSTRAINT是用来重命名的
$sql	=	"CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
);";
 
// 添加UNQIUE约束
$sql	=	"ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);";
// 删除UNQIUE约束
$sql	=	"ALTER TABLE Persons DROP INDEX uc_PersonID;";
 
//SQL PRIMARY KEY 约束 (类似UNIQUE)
 
//SQL FOREIGN KEY 外键约束
$sql	=	"CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
);";
 
// 添加FOREIGN KEY 外键约束
$sql	=	"ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P);";
// 删除FOREIGN KEY 外键约束
$sql	=	"ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;";
 
// SQL CHECK 约束
// CHECK 约束用于限制列中的值的范围。
$sql	=	"CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
);";
 
// 添加SQL CHECK 约束
$sql	=	"ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes');";
// 删除SQL CHECK 约束
$sql	=	"ALTER TABLE Persons DROP CONSTRAINT chk_Person;";
 
//Default默认值
$sql	=	"CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
);";
 
// 索引
 
// 创建索引
$sql	=	"CREATE INDEX index_name ON table_name (column_name);";  
// 创建唯一索引
$sql	=	"CREATE UNIQUE INDEX index_name ON table_name (column_name);"; 
// 创建索引(这个有点看不懂)
$sql	=	"CREATE INDEX PersonIndex ON Person (LastName, FirstName);"; 
// 删除索引
$sql	=	"ALTER TABLE table_name DROP INDEX index_name;"; 
 
//清空表中的数据
//表名称
$sql	=	"TRUNCATE TABLE;";  
 
// ALTER TABLE 语句用于在已有的表中添加、修改或删除列。
// 添加字段:
$sql	=	"ALTER TABLE Persons ADD Birthday date;";
// 修改字段:
$sql	=	"ALTER TABLE Persons ALTER COLUMN Birthday year;";
// 删除字段:
$sql	=	"ALTER TABLE Persons DROP COLUMN Birthday;";
 
//AUTO_INCREMENT用法
$sql	=	"CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
);";
 
 
//判断字段值是否是NULL,使用IS NULL或者IS NOT NULL
$sql	=	"SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL;";
 
// mysql中的IFNULL和COALESCE用法
$sql	=	"SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products;";
 
/*
SQL数据类型
TEXT类型中的BLOB\ENUM\SET类型比较少用到,简单介绍
BLOB:用于存储二进制大对象
ENUM:类似于单选下拉框值
SET:类似于多选选下拉框值,多个值用逗号分割
*/
 
// SQL 函数 以下内容参考W3SCHOOL.COM.CN
// SELECT function(列) FROM 表
 
//平均值
$sql	=	"SELECT AVG(OrderPrice) AS OrderAverage FROM Orders;";
 
//高于平均值
$sql	=	"SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)";
 
// COUNT统计
$sql	=	"SELECT COUNT(DISTINCT column_name) FROM table_name;";
 
// FIRST
$sql	=	"SELECT FIRST(column_name) FROM table_name;";
 
// LAST
$sql	=	"SELECT LAST(column_name) FROM table_name;";
 
//MAX
$sql	=	"SELECT MAX(column_name) FROM table_name;";
 
//MIN
$sql	=	"SELECT MIN(column_name) FROM table_name;";
 
//SUM
$sql	=	"SELECT SUM(column_name) FROM table_name;";
 
/*
 * GROUP BY 语句
 * GROUP BY 多个列可能会在统计中常用到,可以参考示例
 */
$sql	=	"SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer;";
 
//HAVING 子句
$sql	=	"SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000;";
 
// UCASE 函数把字段的值转换为大写
$sql	=	"SELECT UCASE(column_name) FROM table_name;";
 
// LCASE 函数把字段的值转换为小写
$sql	=	"SELECT LCASE(column_name) FROM table_name;";
 
// MID 函数用于从文本字段中提取字符,会取出City字段的三位字符
$sql	=	"SELECT MID(City,1,3) as SmallCity FROM Persons;";
 
// LEN 函数返回文本字段中值的长度
$sql	=	"SELECT LEN(City) as LengthOfCity FROM Persons;";
 
// ROUND 函数用于把数值字段舍入为指定的小数位数
$sql	=	"SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products;";
 
// NOW 函数返回当前的日期和时间
$sql	=	"SELECT ProductName, UnitPrice, Now() as PerDate FROM Products;";
 
// FORMAT 函数用于对字段的显示进行格式化
$sql	=	"SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate FROM Products;";
 
//LENGTH 用法,查找字段长度为9的所有字段值
$sql	=	"SELECT * FROM  `abc` WHERE LENGTH(  `abcd` ) =9";
 
// 获取字段的类型/名称/长度
<?php
    mysql_connect("localhost", "mysql_username", "mysql_password");
    mysql_select_db("mysql");
    $result = mysql_query("SELECT * FROM func");
    $fields = mysql_num_fields($result);
    $rows   = mysql_num_rows($result);
    $table = mysql_field_table($result, 0);
    echo "Your '".$table."' table has ".$fields." fields and ".$rows." record(s)\n";
    echo "The table has the following fields:\n";
    for ($i=0; $i < $fields; $i++) {
        $type  = mysql_field_type($result, $i);
        $name  = mysql_field_name($result, $i);
        $len   = mysql_field_len($result, $i);
        $flags = mysql_field_flags($result, $i);
        echo $type." ".$name." ".$len." ".$flags."\n";
    }
    mysql_free_result($result);
    mysql_close();
?>

mysql数据库事务,没想到也不是很难

<?php 
mysql_connect('localhost','root','');
mysql_select_db('test');
mysql_query ( "SET AUTOCOMMIT=0" ); //設置mysql不自動提交,需自行用commit語句提交
$sql 	= "INSERT INTO  `test`.`testTable` (`id` ,`a` ,`b` ,`wTime`)VALUES (NULL ,  '10',  '20', NOW( ))";
$sql1 	= "INSERT INTO  `test`.`testTable` (`id` ,`a` ,`b` ,`wTime`)VALUES (NULL ,  '101',  '201', NOW( ))";
$res 	= mysql_query ( $sql );
$res1 	= mysql_query ( $sql1 );
//因爲使用了事務,則兩個insert都執行失敗
if ($res && $res1) {
	mysql_query ( "COMMIT" );
} else {
	mysql_query ( "ROLLBACK" );
}
 
mysql_query ( "SET AUTOCOMMIT=1" );//設置mysql自動提交
$sql3	=	"INSERT INTO  `test`.`testTable` (`id` ,`a` ,`b` ,`wTime`)VALUES (NULL ,  '100',  '200', NOW( ))";
mysql_query ( $sql3 );
 
/*
 * 创建数据库文件,注意一定要是INNODB
CREATE TABLE  `test`.`testTable` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`a` VARCHAR( 50 ) NOT NULL ,
`b` VARCHAR( 50 ) NOT NULL ,
`wTime` DATETIME NOT NULL
) ENGINE = INNODB;
*/
?>

参考:
http://www.cnblogs.com/fightLonely/archive/2011/06/09/2076092.html
http://koda.iteye.com/blog/471713

13 php mysql数据库类

在顶部写了几个实例,类是从ecshop上面弄下来的,有缓存功能,还是很好用的,有待更新。

<?php
/*
 * mysql类
 */
 
define('EC_CHARSET', 'utf8');
define('ROOT_PATH', 'D:/wamp/www/');
$DB		=	new cls_mysql('localhost', 'root', '', 'guestbook');
$sql	=	"SELECT * FROM  `messageclass`";
 
$re		=	$DB->getOne($sql);
print_r($re);
$DB->max_cache_time	=	2;
$re		=	$DB->getOneCached($sql);
print_r($re);
$re		=	$DB->getAll($sql);
print_r($re);
$re		=	$DB->getAllCached($sql);
print_r($re);
$re		=	$DB->getCol($sql);
print_r($re);
$re		=	$DB->getColCached($sql);
print_r($re);
$re		=	$DB->getRow($sql);
print_r($re);
$re		=	$DB->getRowCached($sql);
print_r($re);
 
$re		=	$DB->autoExecute('message', array('title'=>'1','content'=>'1'));
$re		=	$DB->get_table_name($sql);
var_dump($re);
//$re		=	$DB->autoReplace($table, $field_values, $update_values, $where = '', $querymode = '');
 
 
 
 
class cls_mysql
{
    var $link_id    = NULL;
    var $settings   = array();
    var $queryCount = 0;
    var $queryTime  = '';
    var $queryLog   = array();
    var $max_cache_time = 300; // 最大的缓存时间,以秒为单位
    var $cache_data_dir = 'temp/query_caches/';
    var $root_path      = '';
    var $error_message  = array();
    var $platform       = '';
    var $version        = '';
    var $dbhash         = '';
    var $starttime      = 0;
    var $timeline       = 0;
    var $timezone       = 0;
    var $mysql_config_cache_file_time = 0;
    var $mysql_disable_cache_tables = array(); // 不允许被缓存的表,遇到将不会进行缓存
 
    function __construct($dbhost, $dbuser, $dbpw, $dbname = '', $charset = 'utf8', $pconnect = 0, $quiet = 0)
    {
        $this->cls_mysql($dbhost, $dbuser, $dbpw, $dbname, $charset, $pconnect, $quiet);
    }
 
    function cls_mysql($dbhost, $dbuser, $dbpw, $dbname = '', $charset = 'utf8', $pconnect = 0, $quiet = 0)
    {
        if (defined('EC_CHARSET'))
        {
            $charset = strtolower(str_replace('-', '', EC_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 = '<' . "?php\r\n" .
                       '$this->mysql_config_cache_file_time = ' . $this->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++ <= 99)
        {
            $this->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 ($message)
        {
            echo "<b>Info</b>: $message\n\n<br /><br />";
          }
        else
        {
            echo "<b>MySQL server error report:";
            print_r($this->error_message);
         }
 
        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')
    {
        $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)
    {
 
        $res = $this->query($sql);
        if ($res !== false)
        {
            return mysql_fetch_assoc($res);
        }
        else
        {
            return false;
        }
    }
 
    function getRowCached($sql, $cached = 'FILEFIRST')
    {
 
        $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'], '<?php exit;?>' . 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);
    }
}
 
?>

other from ucenter

<?php
 
/*
	[Discuz!] (C)2001-2009 Comsenz Inc.
	This is NOT a freeware, use is subject to license terms
 
	$Id: db.class.php 976 2009-12-03 03:02:44Z zhaoxiongfei $
*/
 
if(!defined('IN_COMSENZ')) {
	exit('Access Denied');
}
 
class dbstuff {
	var $querynum = 0;
	var $link;
	var $histories;
	var $time;
	var $tablepre;
 
	function connect($dbhost, $dbuser, $dbpw, $dbname = '', $dbcharset, $pconnect = 0, $tablepre='', $time = 0) {
		$this->time = $time;
		$this->tablepre = $tablepre;
		if($pconnect) {
			if(!$this->link = mysql_pconnect($dbhost, $dbuser, $dbpw)) {
				$this->halt('Can not connect to MySQL server');
			}
		} else {
			if(!$this->link = mysql_connect($dbhost, $dbuser, $dbpw, 1)) {
				$this->halt('Can not connect to MySQL server');
			}
		}
 
		if($this->version() > '4.1') {
			if($dbcharset) {
				mysql_query("SET character_set_connection=".$dbcharset.", character_set_results=".$dbcharset.", character_set_client=binary", $this->link);
			}
 
			if($this->version() > '5.0.1') {
				mysql_query("SET sql_mode=''", $this->link);
			}
		}
 
		if($dbname) {
			mysql_select_db($dbname, $this->link);
		}
 
	}
 
	function fetch_array($query, $result_type = MYSQL_ASSOC) {
		return mysql_fetch_array($query, $result_type);
	}
 
	function result_first($sql, &$data) {
		$query = $this->query($sql);
		$data = $this->result($query, 0);
	}
 
	function fetch_first($sql, &$arr) {
		$query = $this->query($sql);
		$arr = $this->fetch_array($query);
	}
 
	function fetch_all($sql, &$arr) {
		$query = $this->query($sql);
		while($data = $this->fetch_array($query)) {
			$arr[] = $data;
		}
	}
 
	function cache_gc() {
		$this->query("DELETE FROM {$this->tablepre}sqlcaches WHERE expiry<$this->time");
	}
 
	function query($sql, $type = '', $cachetime = FALSE) {
		$func = $type == 'UNBUFFERED' && @function_exists('mysql_unbuffered_query') ? 'mysql_unbuffered_query' : 'mysql_query';
		if(!($query = $func($sql, $this->link)) && $type != 'SILENT') {
			$this->halt('MySQL Query Error', $sql);
		}
		$this->querynum++;
		$this->histories[] = $sql;
		return $query;
	}
 
	function affected_rows() {
		return mysql_affected_rows($this->link);
	}
 
	function error() {
		return (($this->link) ? mysql_error($this->link) : mysql_error());
	}
 
	function errno() {
		return intval(($this->link) ? mysql_errno($this->link) : mysql_errno());
	}
 
	function result($query, $row) {
		$query = @mysql_result($query, $row);
		return $query;
	}
 
	function num_rows($query) {
		$query = mysql_num_rows($query);
		return $query;
	}
 
	function num_fields($query) {
		return mysql_num_fields($query);
	}
 
	function free_result($query) {
		return mysql_free_result($query);
	}
 
	function insert_id() {
		return ($id = mysql_insert_id($this->link)) >= 0 ? $id : $this->result($this->query("SELECT last_insert_id()"), 0);
	}
 
	function fetch_row($query) {
		$query = mysql_fetch_row($query);
		return $query;
	}
 
	function fetch_fields($query) {
		return mysql_fetch_field($query);
	}
 
	function version() {
		return mysql_get_server_info($this->link);
	}
 
	function close() {
		return mysql_close($this->link);
	}
 
	function halt($message = '', $sql = '') {
		show_error('run_sql_error', $message.'<br /><br />'.$sql.'<br /> '.mysql_error(), 0);
	}
}
 
?>