本想學pear的，可是網上看到的幾篇帖子對adodb的評價相當高，所以改學了這個。
ADODB的優點有這幾個(網上說的，不是我說的)：
1、速度比pear快一倍；
2、支持的數據庫類型比pear多很多，甚至可以支持ACCESS;
3、無須安裝，無須服務器支持(對新手來說，這點很重要吧)
不知道adodb是什麼或是想下載adodb的朋友可以去這個鏈接看看：http://www.phpe.net/class/106.shtml
另外，如果哪位兄弟翻譯了README的全文或知道哪裡有譯文請給我回個帖，謝謝。
Tutorial
Example 1: Select Statement
任務: 連接一個名為Northwind的Access數據庫, 顯示 每條記錄 的前兩個字段.
在這個實例裡, 我們新建了一個ADOC連接(ADOConnection)對象, 並用它來連接一個數據庫. 這個連接采用PConnect 方法, 這是一個持久 連接. 當我們要查詢數據 庫時, 我們可以隨時調 用這個連接的Execute()函數. 它會返回一個ADORecordSet對象 which is actually a cursor that holds the current row in the array fields[]. 我們使用MoveNext()從一個記錄轉向下一個記錄 .
NB: 有一 個非常實用的函數 SelectLimit在本例中沒有用到, 它可以控制顯示的記錄數(如只顯示前十條記錄 ，可用作分頁顯示 ).
PHP:--------------------------------------------------------------------------------
<?
include('adodb.inc.php'); #載入ADOdb
$conn = &ADONewConnection('access'); # 新建一個連接
$conn->PConnect('northwind'); # 連接到一個名為northwind的MS-Access數據庫
$recordSet = &$conn->Execute('select * from products'); #從products數據表中搜索所有數據
if (!$recordSet)
print $conn->ErrorMsg(); //如果數據搜索發生錯誤顯示錯誤信息
else
while (!$recordSet->EOF) {
print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>';
$recordSet->MoveNext(); //指向下一個記錄
} //列表顯示數據
$recordSet->Close(); //可選
$conn->Close(); //可選
?>
--------------------------------------------------------------------------------
$recordSet在$recordSet->fields中返回當前數組, 對字段進行數字索引(從0開始). 我們用MoveNext() 函數移動到下一個記錄 . 當數據庫搜索到結尾時EOF property被 設置 為true. 如果Execute()發生錯誤 , recordset返回flase.
$recordSet->fields[]數組產生於PHP的數據庫擴展。有些數據庫擴展只能按數字索引而不能按字段名索引.如果堅持要使用字段名索引，則應采用SetFetchMode函數.無論采用哪種格式索引，recordset都可以由Execute()或SelectLimit()創建。
PHP:--------------------------------------------------------------------------------
$db->SetFetchMode(ADODB_FETCH_NUM);
$rs1 = $db->Execute('select * from table'); //采用數字索引
$db->SetFetchMode(ADODB_FETCH_ASSOC);
$rs2 = $db->Execute('select * from table'); //采用字段名索引
print_r($rs1->fields); # shows array([0]=>'v0',[1] =>'v1')
print_r($rs2->fields); # shows array(['col1']=>'v0',['col2'] =>'v1')--------------------------------------------------------------------------------
如果要獲取記錄號,你可以使用$recordSet->RecordCount()。如果沒有當前記錄則返回-1。
實例 2: Advanced Select with Field Objects
搜索表格，顯示前兩個字段. 如果第二個字段是時間或日期格式,則將其改為美國標准時間格式顯示.
PHP:--------------------------------------------------------------------------------
<?
include('adodb.inc.php'); ///載入adodb
$conn = &ADONewConnection('access'); //新建一個連接
$conn->PConnect('northwind'); //連接名為northwind的MS-Access數據庫
$recordSet = &$conn->Execute('select CustomerID,OrderDate from Orders'); //從Orders表中搜索CustomerID和OrderDate兩個字段
if (!$recordSet)
print $conn->ErrorMsg(); //如果數據庫搜索錯誤,顯示錯誤信息
else
while (!$recordSet->EOF) {
$fld = $recordSet->FetchField(1); //把第二個字段賦值給$fld
$type = $recordSet->MetaType($fld->type); //取字段值的格式
if ( $type == 'D' || $type == 'T')
print $recordSet->fields[0].' '.
$recordSet->UserDate($recordSet->fields[1],'m/d/Y').'<BR>'; //如果字段格式為日期或時間型,使其以美國標准格式輸出
else
print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>'; //否則以原樣輸出
$recordSet->MoveNext(); //指向下一個記錄
}
$recordSet->Close(); //可選
$conn->Close(); //可選
?>
--------------------------------------------------------------------------------
在這個例子裡, 我們用FetchField()函數檢查了第二個字段的格式. 它返回了一個包含三個變量的對象
name: 字段名
type: 字段在其數據庫中的真實格式
max_length:字段最大長度,部分數據庫不會返回這個值,比如MYSQL,這種情況下max_length值等於-1.
我們使用MetaType()把字段的數據庫格式轉化為標准的字段格式
C: 字符型字段,它應該可以在<input type="text">標簽下顯示.
X: 文本型字段,存放比較大的文本,一般作用於<textarea>標簽
B: 塊,二進制格式的大型對象,如圖片
D: 日期型字段
T: 時間型字段
L: 邏輯型字段 (布爾邏輯或bit-field)
I: 整型字段
N: 數字字段. 包括自動編號(autoincrement), 數字(numeric), 浮點數(floating point), 實數(real)和整數(integer).
R: 連續字段. 包括serial, autoincrement integers.它只能工作於指定的數據庫.
如果metatype是日期或時戳類型的，我們用用戶定義的日期格式UserDate()函數來輸出，UserDate()用來轉換PHP SQL 日期字符串格式到用戶定義的格式，MetaType()的另一種用法是在插入和替換前確認數據有效性.
實例 3: Inserting
在訂單數據表中插入一個包含日期和字符型數據的記錄,插入之前必須先進行轉換, eg: the single-quote in the word John's.
PHP:--------------------------------------------------------------------------------
<?
include('adodb.inc.php'); // 載入adodb
$conn = &ADONewConnection('access'); //新建一個連接
$conn->PConnect('northwind'); //連接到ACCESS數據庫northwind
$shipto = $conn->qstr("John's Old Shoppe");
$sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) ";
$sql .= "values ('ANATR',2,".$conn->DBDate(time()).",$shipto)";
if ($conn->Execute($sql) === false) {
print 'error inserting: '.$conn->ErrorMsg().'<BR>';
} //如果插入不成功輸出錯誤信息
?>
--------------------------------------------------------------------------------
在這個例子中,我們看到ADOdb可以很容易地處理一些高級的數據庫操作. unix時間戳 (一個長整數)被DBDate()轉換成正確的Access格式, and the right escape character is used for quoting the John's Old Shoppe, which is John''s Old Shoppe and not PHP's default John's Old Shoppe with qstr().
觀察執行語句的錯誤處理. 如果Execute()發生錯誤, ErrorMsg()函數會返回最後一個錯誤提示. Note: php_track_errors might have to be enabled for error messages to be saved.
實例 4: Debugging
<?
include('adodb.inc.php'); // 載入adodb
$conn = &ADONewConnection('access'); //新建一個連接
$conn->PConnect('northwind'); //連接到ACCESS數據庫northwind
$shipto = $conn->qstr("John's Old Shoppe");
$sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) ";
$sql .= "values ('ANATR',2,".$conn->FormatDate(time()).",$shipto)";
$conn->debug = true;
if ($conn->Execute($sql) === false) print 'error inserting';
?>
在上面這個例子裡，我們設置了debug = true.它會在執行前顯示所有SQL信息, 同時，它也會顯示所有錯誤提示. 在這個例子裡，我們不再需要調用ErrorMsg() . 要想顯示recordset, 可以參考 rs2html()實例.
也可以參閱 Custom Error Handlers 的部分內容。
實例 5: MySQL and Menus
連接到MySQL數據庫agora, 並從SQL聲明中產生一個<select>下拉菜單 ，菜單的 <option> 選項顯示為第一個字段, 返回值為第二個字段.
PHP:--------------------------------------------------------------------------------
<?
include('adodb.inc.php'); # load code common to ADOdb
$conn = &ADONewConnection('mysql'); //eate a connection
$conn->PConnect('localhost','userid','','agora'); //SQL數據庫，數據庫名為agora
$sql = 'select CustomerName, CustomerID from customers'; //搜索字段name用於顯示,id用於返回值
$rs = $conn->Execute($sql);
print $rs->GetMenu('GetCust','Mary Rosli'); //顯示菜單
?>
--------------------------------------------------------------------------------
在這裡我們定義了一個名為GetCust的菜單，其中的'Mary Rosli'被選定. See GetMenu(). 我們還有一個把記錄值返回到數組的函數: GetArray(), and as an associative array with the key being the first column: GetAssoc().
實例 6: Connecting to 2 Databases At Once
PHP:--------------------------------------------------------------------------------
<?
include('adodb.inc.php'); # load code common to ADOdb
$conn1 = &ADONewConnection('mysql'); # create a mysql connection
$conn2 = &ADONewConnection('oracle'); # create a oracle connection
$conn1->PConnect($server, $userid, $password, $database);
$conn2->PConnect(false, $ora_userid, $ora_pwd, $oraname);
$conn1->Execute('insert ...');
$conn2->Execute('update ...');
?> //同時連接兩個數據庫
--------------------------------------------------------------------------------
7: Generating Update and Insert SQL
ADOdb 1.31以上的版本支持兩個新函數: GetUpdateSQL( ) 和 GetInsertSQL( ). This allow you to perform a "SELECT * FROM table query WHERE...", make a copy of the $rs->fields, modify the fields, and then generate the SQL to update or insert into the table automatically.
我們來看看這兩個函數在這個工作表中是如何執行的: (ID, FirstName, LastName, Created).
Before these functions can be called, you need to initialize the recordset by performing a select on the table. Idea and code by Jonathan Younger jyounger#unilab.com.
PHP:--------------------------------------------------------------------------------
<?
#==============================================
# SAMPLE GetUpdateSQL() and GetInsertSQL() code
#==============================================
include('adodb.inc.php');
include('tohtml.inc.php'); // 奇怪，這句似乎有沒有都一樣，哪位朋友知道原因請給個解釋
#==========================
# This code tests an insert
$sql = "SELECT * FROM ADOXYZ WHERE id = -1"; #查找一個空記錄 $conn = &ADONewConnection("mysql"); # create a connection
$conn->debug=1;
$conn->PConnect("localhost", "admin", "", "test"); # connect to MySQL, testdb
$rs = $conn->Execute($sql); # 獲取一個空記錄
$record = array(); # 建立一個數組准備插入
# 設置插入值$record["firstname"] = "Bob";
$record["lastname"] = "Smith";
$record["created"] = time();
# Pass the empty recordset and the array containing the data to insert
# into the GetInsertSQL function. The function will process the data and return
# a fully formatted insert sql statement.# 插入前會格式化變量
$insertSQL = $conn->GetInsertSQL($rs, $record);
$conn->Execute($insertSQL); # 在數據庫中插入數據
#==========================
# 下面這段程序演示修改數據，大致與上一段程序相同
$sql = "SELECT * FROM ADOXYZ WHERE id = 1";
# Select a record to update
$rs = $conn->Execute($sql); # Execute the query and get the existing record to update
$record = array(); # Initialize an array to hold the record data to update
# Set the values for the fields in the record
$record["firstname"] = "Caroline";
$record["lastname"] = "Smith"; # Update Caroline's lastname from Miranda to Smith
# Pass the single record recordset and the array containing the data to update
# into the GetUpdateSQL function. The function will process the data and return
# a fully formatted update sql statement with the correct WHERE clause.
# If the data has not changed, no recordset is returned
$updateSQL = $conn->GetUpdateSQL($rs, $record);
$conn->Execute($updateSQL); # Update the record in the database
$conn->Close();
?>
--------------------------------------------------------------------------------
實例 8 Implementing Scrolling with Next and Previous
下面的演示是個很小的分頁浏覽程序.
PHP:--------------------------------------------------------------------------------
include_once('../adodb.inc.php');
include_once('../adodb-pager.inc.php');
session_start();
$db = NewADOConnection('mysql');
$db->Connect('localhost','root','','xphplens');
$sql = "select * from adoxyz ";
$pager = new ADODB_Pager($db,$sql);
$pager->Render($rows_per_page=5);--------------------------------------------------------------------------------
運行上面這段程序的結果如下:
|< << >> >|
ID First Name Last Name Date Created
36 Alan Turing Sat 06, Oct 2001
37 Serena Williams Sat 06, Oct 2001
38 Yat Sun Sun Sat 06, Oct 2001
39 Wai Hun See Sat 06, Oct 2001
40 Steven Oey Sat 06, Oct 2001
Page 8/10
調用Render($rows)方法可以分頁顯示數據.如果你沒有給Render()輸入值, ADODB_Pager默認值為每頁10個記錄.
你可以在 SQL裡選擇顯示任意字段並為其定義名稱:
$sql = 'select id as "ID", firstname as "First Name",
lastname as "Last Name", created as "Date Created" from adoxyz';
以上代碼你可以在adodb/tests/testpaging.php 中找到, ADODB_Pager 對象在adodb/adodb-pager.inc.php中. 你可以給ADODB_Pager 的代碼加上圖像和改變顏色，你可以通過設置$pager->htmlSpecialChars = false來顯示HTML代碼.
Some of the code used here was contributed by Iván Oliva and Cornel G.
Example 9: Exporting in CSV or Tab-Delimited Format
We provide some helper functions to export in comma-separated-value (CSV) and tab-delimited formats:
PHP:--------------------------------------------------------------------------------
include_once('/path/to/adodb/toexport.inc.php');include_once('/path/to/adodb/adodb.inc.php');
$db = &NewADOConnection('mysql');$db->Connect($server, $userid, $password, $database);$rs = $db->Execute('select fname as "First Name", surname as "Surname" from table');
print "<pre>";print rs2csv($rs); # return a string, CSV formatprint '<hr>'; $rs->MoveFirst(); # note, some databases do not support MoveFirstprint rs2tab($rs,false); # return a string, tab-delimited
# false == suppress field names in first lineprint '<hr>';$rs->MoveFirst();rs2tabout($rs); # send to stdout directly (there is also an rs2csvout function)
print "</pre>";
$rs->MoveFirst();$fp = fopen($path, "w");
if ($fp) { rs2csvfile($rs, $fp); # write to file (there is also an rs2tabfile function)
fclose($fp);}--------------------------------------------------------------------------------
Carriage-returns or newlines are converted to spaces. Field names are returned in the first line of text. Strings containing the delimiter character are quoted with double-quotes. Double-quotes are double-quoted again. This conforms to Excel import and export guide-lines.
All the above functions take as an optional last parameter, $addtitles which defaults to true. When set to false field names in the first line are suppressed.
Example 10: Recordset Filters
Sometimes we want to pre-process all rows in a recordset before we use it. For example, we want to ucwords all text in recordset.
PHP:--------------------------------------------------------------------------------
include_once('adodb/rsfilter.inc.php');
include_once('adodb/adodb.inc.php');
// ucwords() every element in the recordset
function do_ucwords(&$arr,$rs)
{
foreach($arr as $k => $v) {
$arr[$k] = ucwords($v);
}
}
$db = NewADOConnection('mysql');
$db->PConnect('server','user','pwd','db');
$rs = $db->Execute('select ... from table');
$rs = RSFilter($rs,'do_ucwords');--------------------------------------------------------------------------------
The RSFilter function takes 2 parameters, the recordset, and the name of the filter function. It returns the processed recordset scrolled to the first record. The filter function takes two parameters, the current row as an array, and the recordset object. For future compatibility, you should not use the original recordset object.
