|
|
ADODB的優(yōu)點有這幾個(網(wǎng)上說的，不是我說的)：
1、速度比pear快一倍；
2、支持的數(shù)據(jù)庫類型比pear多很多，甚至可以支持ACCESS;
3、無須安裝，無須服務器支持(對新手來說，這點很重要吧)
不知道adodb是什么或是想下載adodb的朋友可以去這個鏈接看看：http://www.phpe.NET/class/106.shtml
另外，如果哪位兄弟翻譯了README的全文或知道哪里有譯文請給我回個帖，謝謝。
Tutorial
Example 1: Select Statement
任務: 連接一個名為Northwind的Access數(shù)據(jù)庫, 顯示 每條記錄 的前兩個字段.
在這個實例里, 我們新建了一個ADOC連接(ADOConnection)對象, 并用它來連接一個數(shù)據(jù)庫. 這個連接采用PConnect 方法, 這是一個持久 連接. 當我們要查詢數(shù)據(jù) 庫時, 我們可以隨時調 用這個連接的Execute()函數(shù). 它會返回一個ADORecordSet對象 which is actually a cursor that holds the current row in the array fields[]. 我們使用MoveNext()從一個記錄轉向下一個記錄 .
NB: 有一 個非常實用的函數(shù) SelectLimit在本例中沒有用到, 它可以控制顯示的記錄數(shù)(如只顯示前十條記錄 ，可用作分頁顯示 ).
php:--------------------------------------------------------------------------------
<?
include('adodb.inc.php'); #載入ADOdb
$conn = &ADONewConnection('access'); # 新建一個連接
$conn->PConnect('northwind'); # 連接到一個名為northwind的MS-Access數(shù)據(jù)庫
$recordSet = &$conn->Execute('select * from products'); #從products數(shù)據(jù)表中搜索所有數(shù)據(jù)
if (!$recordSet)
print $conn->ErrorMsg(); //如果數(shù)據(jù)搜索發(fā)生錯誤顯示錯誤信息
else
while (!$recordSet->EOF) {
print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>';
$recordSet->MoveNext(); //指向下一個記錄
} //列表顯示數(shù)據(jù)
$recordSet->Close(); //可選
$conn->Close(); //可選
?>
--------------------------------------------------------------------------------
$recordSet在$recordSet->fields中返回當前數(shù)組, 對字段進行數(shù)字索引(從0開始). 我們用MoveNext() 函數(shù)移動到下一個記錄 . 當數(shù)據(jù)庫搜索到結尾時EOF property被 設置 為true. 如果Execute()發(fā)生錯誤 , recordset返回flase.
$recordSet->fields[]數(shù)組產生于php的數(shù)據(jù)庫擴展。有些數(shù)據(jù)庫擴展只能按數(shù)字索引而不能按字段名索引.如果堅持要使用字段名索引，則應采用SetFetchMode函數(shù).無論采用哪種格式索引，recordset都可以由Execute()或SelectLimit()創(chuàng)建。
php:--------------------------------------------------------------------------------
$db->SetFetchMode(ADODB_FETCH_NUM);
$rs1 = $db->Execute('select * from table'); //采用數(shù)字索引
$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數(shù)據(jù)庫
$recordSet = &$conn->Execute('select CustomerID,OrderDate from Orders'); //從Orders表中搜索CustomerID和OrderDate兩個字段
if (!$recordSet)
print $conn->ErrorMsg(); //如果數(shù)據(jù)庫搜索錯誤,顯示錯誤信息
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()函數(shù)檢查了第二個字段的格式. 它返回了一個包含三個變量的對象
name: 字段名
type: 字段在其數(shù)據(jù)庫中的真實格式
max_length:字段最大長度,部分數(shù)據(jù)庫不會返回這個值,比如MYSQL,這種情況下max_length值等于-1.
我們使用MetaType()把字段的數(shù)據(jù)庫格式轉化為標準的字段格式
C: 字符型字段,它應該可以在<input type="text">標簽下顯示.
X: 文本型字段,存放比較大的文本,一般作用于<textarea>標簽
B: 塊,二進制格式的大型對象,如圖片
D: 日期型字段
T: 時間型字段
L: 邏輯型字段 (布爾邏輯或bit-field)
I: 整型字段
N: 數(shù)字字段. 包括自動編號(autoincrement), 數(shù)字(numeric), 浮點數(shù)(floating point), 實數(shù)(real)和整數(shù)(integer).
R: 連續(xù)字段. 包括serial, autoincrement integers.它只能工作于指定的數(shù)據(jù)庫.
如果metatype是日期或時戳類型的，我們用用戶定義的日期格式UserDate()函數(shù)來輸出，UserDate()用來轉換php SQL 日期字符串格式到用戶定義的格式，MetaType()的另一種用法是在插入和替換前確認數(shù)據(jù)有效性.
實例 3: Inserting
在訂單數(shù)據(jù)表中插入一個包含日期和字符型數(shù)據(jù)的記錄,插入之前必須先進行轉換, eg: the single-quote in the word John's.
php:--------------------------------------------------------------------------------
<?
include('adodb.inc.php'); // 載入adodb
$conn = &ADONewConnection('access'); //新建一個連接
$conn->PConnect('northwind'); //連接到ACCESS數(shù)據(jù)庫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可以很容易地處理一些高級的數(shù)據(jù)庫操作. unix時間戳 (一個長整數(shù))被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().
觀察執(zhí)行語句的錯誤處理. 如果Execute()發(fā)生錯誤, ErrorMsg()函數(shù)會返回最后一個錯誤提示. 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數(shù)據(jù)庫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.它會在執(zhí)行前顯示所有SQL信息, 同時，它也會顯示所有錯誤提示. 在這個例子里，我們不再需要調用ErrorMsg() . 要想顯示recordset, 可以參考 rs2html()實例.
也可以參閱 Custom Error Handlers 的部分內容。
實例 5: MySQL and Menus
連接到MySQL數(shù)據(jù)庫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數(shù)據(jù)庫，數(shù)據(jù)庫名為agora
$sql = 'select CustomerName, CustomerID from customers'; //搜索字段name用于顯示,id用于返回值
$rs = $conn->Execute($sql);
print $rs->GetMenu('GetCust','Mary Rosli'); //顯示菜單
?>
--------------------------------------------------------------------------------
在這里我們定義了一個名為GetCust的菜單，其中的'Mary Rosli'被選定. See GetMenu(). 我們還有一個把記錄值返回到數(shù)組的函數(shù): 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 ...');
?> //同時連接兩個數(shù)據(jù)庫
--------------------------------------------------------------------------------
7: Generating Update and Insert SQL
ADOdb 1.31以上的版本支持兩個新函數(shù): 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.
我們來看看這兩個函數(shù)在這個工作表中是如何執(zhí)行的: (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(); # 建立一個數(shù)組準備插入
# 設置插入值$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); # 在數(shù)據(jù)庫中插入數(shù)據(jù)
#==========================
# 下面這段程序演示修改數(shù)據(jù)，大致與上一段程序相同
$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)方法可以分頁顯示數(shù)據(jù).如果你沒有給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.
php技術：Adodb的十個實例(清晰版)，轉載需保留來源！
鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯(lián)系我們修改或刪除，多謝。
