分类：
2008-04-07 10:48:39
PEAR DB已经不推荐使用了,官方网站推荐使用MDB2来开发程序，这是一篇关于MDB2的使用入门指南。
While writing some PHP Training materials for Pale Purple, I thought I'd add an updated guide on PHP and database access. I've already done one on PEAR::DB, but PEAR::MDB2 is it's successor and has a slightly different API.... and as PEAR::DB is now deprecated, it's probably about time I rewrote it anyway.
is yet another database API for PHP. It's written in PHP, so can be used in a cross platform manner. Because it's written in PHP, it's not going to be as 'fast' as the library, however it's portability may make up for that.
Compared to the native bundled legacy PHP libraries (mysqli_*, pgsql_*, sqlite_*, mysql_), changing which database you are using can be as simple as changing the connection parameters to your database. The PDO libraries offer the same advantage, however they require PHP5.
It's recommended you install it using the Pear installer, like the following :
pear install MDB2pear install MDB2_Driver_$db
Where $db would be one of e.g. mysql, pgsql, sqlite, ori8, mssql etc.
You'll note, that unlike PEAR::DB, it's necessary to download a specific driver for each database you wish to support.
One of the advantages of MDB2 over PEAR::DB is that it offers a number of different ways of providing a DB connection - either lazy (MDB2::factory()), optimistic (MDB2::connect()) or singleton (MDB2::singleton()) .
I personally tend to just go with the lazy 'factory' method where the connection isn't created until it's used.
As with PEAR::DB, you specify which database to connect to using a JDBC style URL, which looks something like : $db_driver://$user:$password@$host/$database_name, so for example pgsql://web:password@dbserver/my_database would be a valid url.
require_once("MDB2.php");$con = MDB2::factory($url);
As always, there is plenty of scope for something to go wrong. Perhaps the database server is offline, or your username/password are incorrect. In any case, error handling with MDB2 follows the PEAR convention of using . So in our case, our code could now look a bit like :
require_once("MDB2.php");$con = MDB2::factory($url);if(PEAR::isError($con)) { die("Error while connecting : " . $con->getMessage());}// use $con
When ever talking to a relational database, care has to be taken that any parameters passed into a query have been appropriately sanitised. Failure to sanitise data will make your site vulnerable to SQL injection, data loss or compromise. If you don't know what SQL Injection is, try reading the following article at .
// load library as above.// connect to db as above. ($con = ... )// check $con validity as per above example. $sql = "SELECT * FROM the_table";$resultset = $con->query($sql);if(PEAR::isError($resultset)) { die('Failed to issue query, error message : ' . $resultset->getMessage());} while($row = $resultset->fetchRow(MDB2_FETCHMODE_ASSOC)) { foreach($row as $field => $value) { echo "$key / $value \n"; }}
This is largely the same as above, apart from the fact there is no need to do the while loop / fetchRow section, so the code becomes somewhat simpler and would look a bit like :
// load library// connect to db, and check for errors$sql = "UPDATE table SET column = 5 WHERE id = 1";$result = $con->query($sql);if(PEAR::isError($result)) { die("Failed to issue query, error message : " . $result->getMessage();}
If you need to add in user supplied data into the query, you can use $save_var = $con->quote($var). An optional second parameter can be given to determine whether the data should be reformatted as a boolean, date, integer or text.
A safer (and potentially better performing) approach to insert user supplied data into queries is to use Prepared Statements. These can be faster, as if the underlying database supports it, the statement itself can be compiled and cached, so saving the database engine from having to reparse the same SQL each time. They are safer, because SQL injection is not possible when using them - because the database knows what goes where. Some database engines (e.g. MySQL v4) do not support prepared statements, in which case, like PEAR::DB, PEAR::MDB2 will emulate the functionality in the background.
A prepared statement example:
// load library, get connection etc (as per above) $sql = "UPDATE table SET column = ? WHERE id = ?";$types = array('integer', 'text');$statement = $con->prepare($sql, $types, MDB2_PREPARE_MANIP);$data = array(5, 'blah');$affected_rows = $statment->execute($data);if(PEAR::isError($affected_rows)) { // die etc.}
MDB2_PREPARE_MANIP is required if you wish to manipulate data, and not return any values. MDB2_PREPARE_RESULT is used if you wish to run a prepared statement and have data returned, for instance :
The $types array is not required (you could replace it with TRUE), as MDB2 can automatically handle it.
// load library, get db connection etc.$types = array("text");$statement = $con->prepare("SELECT * FROM table WHERE column = ?", $types, MDB2_PREPARE_RESULT);$data = array('foo');$resultset = $statement->execute($data);if(PEAR::isError($resultset)) { die('failed... ' . $resultset->getMessage());}$statement->Free();while($row = $resultset->fetchRow(MDB2_FETCHMODE_ASSOC)) { echo "Found : " . $row['column'] . "\n";}
I'm not particularly keen on writing the same thing twice, so I tend to use functions like the following :
require_once("MDB2.php");function connect() { $con = MDB2::factory($url); if(PEAR::isError($con)) { die("Error while connecting : " . $con->getMessage()); } return $con;}
Then something like the following to undertake queries :
function execute_query($sql, $values=array()) { $con = connect(); $results = array(); if(sizeof($values) > 0) { $statement = $con->prepare($sql, TRUE, MDB2_PREPARE_RESULT); $resultset = $statement->execute($values); if(PEAR::isError($resultset)) { die('DB Error... ' . $resultset->getMessage()); } $statement->Free(); } else { $resultset = $con->query($sql); } while($row = $resultset->fetchRow(MDB2_FETCHMODE_ASSOC)) { $results[] = $row; } return $results;}
说使用心得还有点谈不上，刚接触MDB2不到两周。由于服务器升级，新的服务器系统不再安装DB包，PEAR的官方站也明确表示“This package has been superseded, but is still maintained for bugs and security fixes. Use MDB2 instead.”。不得已，研究了一个下午，终于入门了。到现虽然只在两个小项目上小试牛刀，还是有些心得体会的。现在信息扩展之迅速，技术更新之快，倘若你的头脑缓存周期太长，恐怕你费尽心血写出来的东西还没来得及上线下一个版本就出来了。
入题：
"PEAR MDB2 is a merge of the PEAR DB and Metabase php database abstraction layers.”，这是PEAR官网上的一句话，大致是说“PEAR是建立在PHP和数据库之间的一个层”。既然是一个中间层，那么它一定有丰富的扩展功能和灵活的可移植性。就是说你可以把mysql数据库转换为sql server数据库而不需要重写程序，只需要把连接数据库的函数修改一下即可。这点好处我还没有体会到。不过官方文档中还说它有更方便的错误机制处理更安全，Agree.
这里不谈安装，只讲使用。因为打包特别简单，到下载解压到你的php include path里即可，不要忘记下载MDB2 Driver哦。或者直接使用命令"$ pear install MDB2 $ pear install MDB2#mysql”。
第一步：连接数据库
MDB2提供两种连接方式：DSN＆Socket，首选DSN。提供三个连接函数：factory()、connect()、singleton()，分别表示Efficient, Eager, Available连接，就是说factory()是最有效率的连接函数，因为在你只调用factory()的时候它并没有去连接数据库，只有在你发送query请求的时候才会连接；connect()调用时立即连接数据库；而singleton()顾名思义“单独”，为你一个进程只建立一个连接时方便使用，它同样是一个"lazy connecting".
示例函数如下：（其中有一项:$mdb2->setFetchMode(2)是设置数据结果返回的形式为数组）
/*********************************************************
* MDB2 function
* connect to primary database & slave database
*
* Ziming
*
**********************************************************/
require_once("MDB2.php");
//Connecting primary database;
function &open_pdb()
{
$dsn = array(
'phptype' => "mysql",
'username' => "root",
'password' => "",
'hostspec' => "localhost",
'port' => 3306,
'database' => "test",
);
$mdb2 = &MDB2::singleton($dsn);
if (PEAR::isError($mdb2))
{
exit($mdb2->getMessage());
}
$mdb2->setFetchMode(2);
return $mdb2;
}
//Connecting slave database;
function &open_sdb()
{
$dsn = array(
'phptype' => "mysql",
'username' => "root",
'password' => "",
'hostspec' => "localhost",
'port' => 3306,
'database' => "test",
);
$mdb2 = &MDB2::singleton($dsn);
if (PEAR::isError($mdb2))
{
exit($mdb2->getMessage());
}
$mdb2->setFetchMode(2);
return $mdb2;
}
//Disconnecting a database
function close_db(&$mdb2)
{
if(is_object($mdb2))
{
$mdb2->disconnect();
$mdb2 = "";
}
}
?>
第二步：发送请求
MDB2有两种Performing queries方法：query()和exec(),它们分别产生两种结果：成功时返回执行结果和失败时返回MDB2_Error对象。但它们又有不同，query()是请求并取值，它一般用于SELECT，SHOW等，返回符合条件的数据；而exec()是执行的意思，所以它一般用于INSERT、UPDATE或DELETE，返回语句成功执行后所影响的行数。
例如：
//假设你已经建立MDB2对象$mdb2
$sql = "SELECT * FROM tableA";
$res = $mdb2->query($sql);
//习惯性检查语句是否被正确执行
if (PEAR::isError($res))
{
exit($res->getMessage());
}
$sql = "INSERT INTO tableB (`id`, `nickname`) VALUES ($id, '$nickname')";
$affected = $mdb2->exec($sql);
//习惯性检查语句是否被正确执行
if (PEAR::isError($affected))
{
die($res->getMessage());
}
?>
注意：当你使用$mdb2->query()后得到的并不是数组矩阵，而是$mdb2对象，你需要使用fetchOne(),fetchRow(),fetchCol()或fetchAll()来获得你想要的值，这将在接下来讲到。
第三：引用包含Quoting and escaping
MDB2提供两个函数来引用SQL语句里的值，分别是Quoting() and escaping()。
在DB库发展到后期，开发人员推荐使用quoteSmart()或escapeSimple()来代替quote().
"This method has been deprecated. Use quoteSmart() or escapeSimple() instead."
但是在MDB2里，我们需要更多地使用quote()，它有4个参数，quote(要quote的值，值的类型，是否使用quote对值进行处理，是否escape通配符)，只有第一个参数是必须的。例如：
$sql = "INSERT INTO tableA (`id`, `nickname`, `is_member`) VALUES ("
.$mdb2->quote($id, "integer").","
.$mdb2->quote($nickname, "text", true).","
.$mdb2->quote($is_member, "boolean", false).")";
echo $sql;
//输出结果形如
//INSERT INTO tableA (`id`, `nickname`, `is_member`) VALUES (2, 'jory', 1)
?>
允许的类型有：text, boolean, integer, decimal, float, time stamp, date, time, clob, blob.
其中clob和blob是两种存储不限制长度的大数据对象格式，clob表示以字符串形式存储，blob表示以二进制的形式存储。它们的区别请查阅相关资料。
这样做对SQL语句的严格要求来减少数据库的安全隐患。用的时候你会发现如果类型为text时，当传入变量值为空时，quote后的SQL语句对应值为null，所以我们在设计数据库结构的时候要注意不能把所有的字段都设为not null,允许为空的字段要设计为null.
第四：取得结果数据
上面说到使用query()函数得到的返回值是MDB2对象而不是我们需要的数据，那怎样获得我们存储的数据内容呢，就需要在请求query()后再去取，四种方法：
fetchOne(), fetchRow(), fetchCol() and fetchAll().使用过DB库的朋友们看这些函数是不是很眼熟，对的，它就是获取结果集：取一个，取一行，取一列，取所有（比如一个数组矩阵），配合setFetchMode()方法获取你想要的数据形式。
例如：（由于我已经在前的数据库连接函数内使用了$mdb2->setFetchMode(2)，这里的返回结果是字符串索引的数组）
$sql = "SELECT `id`, `nickname` FORM tableA";
$res = $mdb2->query($sql);
if (PEAR::isError($res))
{
exit($res->getMessage());
}
$data = $res->fetchAll();
print_r($data);
/*
将打印出如下形式数据
Array
(
[0] => Array
(
[0] => 1
[1] => jory
)
[1] => Array
(
[0] => 2
[1] => ziming
)
[2] => Array
(
[0] => 3
[1] => gong
)
)
*/
?>
这样每次取数据都要写两行程序，使用MDB2库并没有减少我们敲击键盘的次数。别急，当然更好的方法能让你一步达成：
queryOne(), queryRow(), queryCol() and queryAll(). "All of the fetch methods are also available in a variant that executes a query directly"
ex.
//建立数据库连接对象$mdb2后
$sql = "SELECT * FROM tableA";
//一步达成
$data = $mdb2->queryAll($sql);
//结果显示
print_r($data);
?>
第五：预编译及批量处理Prepare & Execute
很多人都喜欢使用prepare()和execute()方法，而我就不太喜欢。我觉得她功能强大但使用起来过于复杂，但事实是她使用起来也很方便的。我喜欢简约一些的，或许是我还没有跨过那道门槛吧。正在尝试...相信有一天我会和她混熟的。
既然是“预编译＋批量处理”，那么你只需要简单的两步就能实现对一堆数据的处理：
第一步，整理你的SQL语句(prepare the statement)。第二步，执行(execute it)。
例如：
//已经建立一个数据库连接对象$mdb2
$types = array('integer', 'text', 'text');
$sql = "INSERT INTO tableA VALUES (?, ?, ?)";
$sth = $mdb2->prepare($sql, $types);
$data = array(1, 'jory', 'I do');
$affectedRows = $sth->execute($data);
?>
OK, MDB2的基本使用就说到这里吧，其它的关于事务处理、扩展模块、API接口等等非三二句话能讲得清楚的，欲深究请查阅手册和相关技术文档。
|
MDB2的一般使用方法总结 | |||
|
| |||
|
| |||
|