Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2773869
  • 博文数量: 77
  • 博客积分: 10204
  • 博客等级: 上将
  • 技术积分: 5035
  • 用 户 组: 普通用户
  • 注册时间: 2006-03-05 22:13
文章分类
文章存档

2013年(1)

2010年(1)

2009年(17)

2008年(58)

我的朋友

分类:

2008-04-07 10:48:39

PEAR MDB2入门指南

How to use PHP and PEAR MDB2 Tutorial 

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.

What is PEAR::MDB2?

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.

Getting started

It's recommended you install it using the Pear installer, like the following :

pear install MDB2
pear 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.

Connecting to a database

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);

Error checking

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

Security (SQL Injection)

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 .

Issuing Queries on the database

Select queries (returning data)

// 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";
    }
}

Non returning queries (update, insert, delete)

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.

Prepared statements (or how to safely pass parameters into a database query)

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";
}

Suggestions for Usage

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;
}

 

PEARMDB2使用心得

 

 

说使用心得还有点谈不上,刚接触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
提供两种连接方式:DSNSocket,首选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
* 2007-12-6
**********************************************************/

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()是请求并取值,它一般用于SELECTSHOW等,返回符合条件的数据;而exec()是执行的意思,所以它一般用于INSERTUPDATEDELETE,返回语句成功执行后所影响的行数。
例如:

//假设你已经建立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.
其中clobblob是两种存储不限制长度的大数据对象格式,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的一般使用方法总结

2007-06-19          点击: 1107

 

MDB2的一般使用方法总结         

 

When I started using MDB2, I found the possibilities quite overwhelming and so I decided to summarize everything you may need to start using it or want as a reference if you already use it and don't want to dig through the manuals for everything.Questions and hints on clarification are appreciated.

init a connection

 

require_once 'MDB2.php';

$dsn = 'mysql://user:pass@host/db';

 



$mdb2 =& MDB2::factory(mixed $dsn, [ $options = false]);



if (PEAR::isError($mdb2)) {

   
($mdb2->getMessage());

}

 

 

querying


//Send a query to the database and return any results

$result = $mdb2->query(string $query, [mixed $types = null]);

 



//Execute the specified query, fetch all the rows of the result set into a two dimensional array and then frees the result set.

$result = $mdb2->queryAll(string $query, [ $types = null], [int $fetchmode = MDB2_FETCHMODE_DEFAULT], [boolean $rekey = false]);

//$rekey = if set to true, the $all will have the first column as its first dimension

 



//Execute the specified query, fetch the values from the first row of the result set into an array and then frees the result set.

$result = $mdb2->queryRow($string $query, [string $type = null]);

 

 

results


//Define the list of types to be associated with the columns of a given result set.

$types = ('integer', 'text', 'timestamp');

$result->setResultTypes($types)

 



//Fetch and return a row of data

$result->fetchRow([int $fetchmode = MDB2_FETCHMODE_DEFAULT], [int $rownum = null]);

 



//Fetch single column from the first row from a result set

$result->fetchOne([int $colnum = 0]);

 



//Fetch and return a column of data (it uses fetchRow for that)

$result->fetchAll([int $fetchmode = MDB2_FETCHMODE_DEFAULT], [boolean $rekey = false]);

//$rekey = if set to true, the $all will have the first column as its first dimension

 



//Fetch and return a column of data (it uses current for that)

$result->fetchCol([int $colnum = 0]);

 



//Retrieve the names of columns returned by the DBMS in a query result or from the cache.

$result->getColumnNames();

 



//Count the number of columns returned by the DBMS in a query result.

$result->numCols();

 



//returns the number of rows in a result object

$result->numRows();

 



//seek to a specific row in a result set

$result->seek($int);

 



//Move the internal result pointer to the next available result

$result->nextResult();

 



//Free the internal resources associated with result.

$result->free();

 

 

fetch modes

 

/*

MDB2_FETCHMODE_DEFAULT

MDB2_FETCHMODE_ORDERED

MDB2_FETCHMODE_ASSOC

MDB2_FETCHMODE_ORDERED | MDB2_FETCHMODE_FLIPPED

MDB2_FETCHMODE_ASSOC | MDB2_FETCHMODE_FLIPPED

MDB2_FETCHMODE_OBJECT

*/

 

security

 

$mdb2->quote($stuff, $mode);

text, integer, boolean, decimal, float, , , timestamp, blob

 

 

misc


//returns the autoincrement ID if supported or $id

$mdb2->lastInsertID();

$mdb2->nextID();

 

 

阅读(2889) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~