Pear::DB_Table简介

DB_Table是什么？

DB_Table 是一个访问数据库Table的OO接口，它提供了一些自动创建、插入、更新和选择的方法。自动构建往往意味着要丧失一些灵活性，DB_Table也不能例外。

安装

pear的程序库的安装没什么悬念

pear install DB_Table

DB_Table上手

官方介绍上推荐不要直接使用DB_Table Class，而是对它进行扩展，在这之上再定义字段，索引，视图等等其它自定义内容。下面来看看如何对DB_Table扩展。

基本的例子：

<?php
class Guestbook extends DB_Table {
    // 稍后补充 We'll add more here later in the tutorial,
    // 注意，没有构造函数 but for now this is all we need. 
}
?>

接着，实例化一个Guestbook

<?php
// 必须的class
require_once 'DB.php';
require_once 'DB/Table.php';
require_once 'Guestbook.php';

// create a PEAR DB object
$dsn = "phptype://username:password@localhost/database";
$db = DB::connect($dsn);

// set up for the Guestbook and create it, connecting
// to a table called 'guestbook' (which does not exist
// yet, we'll get to that in the next section)
$book =& new Guestbook($db, 'guestbook'); // note the "=&" (very important!)
?>

可以用PEAR_Error来查看是否有错

<?php
if ($book->error) {
    // error handling code goes here; for example ...

    print_r($book->error);

    // ... although that's probably a bad idea as it will print
    // your database username and password.
}
?>

下面看看如何详细定义Guestbook类,这个例子里定义了guestbook表的各个字段类型：

<?php
class Guestbook extends DB_Table {
    var $col = array(
        
        // unique row ID
        'id' => array(
            'type'    => 'integer',
            'require' => true
        ),
        
        // first name
        'fname' => array(
            'type'    => 'varchar',
            'size'    => 32
        ),
        
        // last name
        'lname' => array(
            'type'    => 'varchar',
            'size'    => 64
        ),
        
        // email address
        'email' => array(
            'type'    => 'varchar',
            'size'    => 128,
            'require' => true
        ),
        
        // date-time signed
        'signdate' => array(
            'type'    => 'date',
            'require' => true
        )
    );
?>

如果要进一步定义guestbook表的索引，可以这样进行：

<?php
class Guestbook extends DB_Table {
    
    // snip: var $col = array(...);  这部分内容和上面那段一样，省略
    
    var $idx = array(
        'id' => array(
            'type' => 'unique',
            'cols' => 'id'
        ),
        'signdate' => array(
            'type' => 'normal',
            'cols' => 'signdate'
        )
    );
}
?>

如果索引和字段名是一样的，而且不是多字段索引，上面的定义可以简化为：

<?php
    var $idx = array(
        // unique index called 'id' based on the 'id' column
        'id' => 'unique',
        
        // normal index called 'signdate' based on the 'signdate' column
        'signdate' => 'normal'
    );
?>

定义好表结构之后，我们看看如何进行查询，看上去非常简单，没有sql语句的痕迹：

<?php
// [snip] create the $book Guestbook object

// get the 'list' view as an array
$rows = $book->select('list');
print_r($rows);

// get the 'emails' view as a DB_Result object
$result = $book->selectResult('emails');
print_r($result);

?>

按条件查询：


<?php

// [snip] create the $book Guestbook object

// get 'list' view rows signed on August 14, 2003, ordered ascending by
// last name and first name, starting at row 7 and getting 12 rows total.

$view = 'list';
$filter = "signdate = '2003-08-14'";
$order = 'lname, fname';
$start = 7;
$count = 12;

// as an array
$rows = $book->select($view, $filter, $order, $start, $count);
print_r($rows);

// as a DB_Result object
$rows = $book->selectResult($view, $filter, $order, $start, $count);
print_r($result);

?>

数据插入

<?php

// [snip] create the Guestbook object ($book)

// assign the fields and values
$cols_vals = array(
    'id'       => 1,
    'fname'    => 'Thomas',
    'lname'    => 'Anderson',
    'signdate' => '2003-10-12',
    'email'    => 'neo@matrix.net'
);

// insert into the table and print results
$result = $book->insert($cols_vals);
print_r($result);

?>

数据更新


<?php

// [snip] create the Guestbook object ($book)

// assign the updated fields and values
$cols_vals = array(
    'lname' => 'Jones'
);

// assign the WHERE clause
$where = "lname = 'Smith'";

// attempt the update and print the results
$result = $book->update($cols_vals, $where);
print_r($result);

?>

数据删除


<?php

// [snip] create the Guestbook object ($book)

// a where clause
$today = date('Y-m-d'); // formatted as yyyy-mm-dd
$where = "signdate < '$today'";

// attempt the delete and print the results
$result = $book->delete($where);
print_r($result);

?>

在上面的例子中已经没有太多sql语句的痕迹了，这可以在一定程度上简化开发过程中sql语句的编写，但是还是那句话，自动化的同时降低了灵活性，在使用DB_Table之前需要权衡。

参考

http://wiki.ciaweb.net/yawiki/index.php?area=DB_Table&page=HomePage

下载源码

http://pear.php.net/package/DB_Table