|
|
ADODB的優(yōu)點(diǎn)有這幾個(gè)(網(wǎng)上說的，不是我說的)：
1、速度比pear快一倍；
2、支持的數(shù)據(jù)庫類型比pear多很多，甚至可以支持ACCESS;
3、無須安裝，無須服務(wù)器支持(對(duì)新手來說，這點(diǎn)很重要吧)
不知道adodb是什么或是想下載adodb的朋友可以去這個(gè)鏈接看看：http://www.phpe.NET/class/106.shtml
另外，如果哪位兄弟翻譯了README的全文或知道哪里有譯文請給我回個(gè)帖，謝謝。
Tutorial
Example 1: Select Statement
任務(wù): 連接一個(gè)名為Northwind的Access數(shù)據(jù)庫, 顯示 每條記錄 的前兩個(gè)字段.
在這個(gè)實(shí)例里, 我們新建了一個(gè)ADOC連接(ADOConnection)對(duì)象, 并用它來連接一個(gè)數(shù)據(jù)庫. 這個(gè)連接采用PConnect 方法, 這是一個(gè)持久 連接. 當(dāng)我們要查詢數(shù)據(jù) 庫時(shí), 我們可以隨時(shí)調(diào) 用這個(gè)連接的Execute()函數(shù). 它會(huì)返回一個(gè)ADORecordSet對(duì)象 which is actually a cursor that holds the current row in the array fields[]. 我們使用MoveNext()從一個(gè)記錄轉(zhuǎn)向下一個(gè)記錄 .
NB: 有一 個(gè)非常實(shí)用的函數(shù) SelectLimit在本例中沒有用到, 它可以控制顯示的記錄數(shù)(如只顯示前十條記錄 ，可用作分頁顯示 ).
php:--------------------------------------------------------------------------------
<?
include('adodb.inc.php'); #載入ADOdb
$conn = &ADONewConnection('access'); # 新建一個(gè)連接
$conn->PConnect('northwind'); # 連接到一個(gè)名為northwind的MS-Access數(shù)據(jù)庫
$recordSet = &$conn->Execute('select * from products'); #從products數(shù)據(jù)表中搜索所有數(shù)據(jù)
if (!$recordSet)
print $conn->ErrorMsg(); //如果數(shù)據(jù)搜索發(fā)生錯(cuò)誤顯示錯(cuò)誤信息
else
while (!$recordSet->EOF) {
print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>';
$recordSet->MoveNext(); //指向下一個(gè)記錄
} //列表顯示數(shù)據(jù)
$recordSet->Close(); //可選
$conn->Close(); //可選
?>
--------------------------------------------------------------------------------
$recordSet在$recordSet->fields中返回當(dāng)前數(shù)組, 對(duì)字段進(jìn)行數(shù)字索引(從0開始). 我們用MoveNext() 函數(shù)移動(dòng)到下一個(gè)記錄 . 當(dāng)數(shù)據(jù)庫搜索到結(jié)尾時(shí)EOF property被 設(shè)置 為true. 如果Execute()發(fā)生錯(cuò)誤 , recordset返回flase.
$recordSet->fields[]數(shù)組產(chǎn)生于php的數(shù)據(jù)庫擴(kuò)展。有些數(shù)據(jù)庫擴(kuò)展只能按數(shù)字索引而不能按字段名索引.如果堅(jiān)持要使用字段名索引，則應(yīng)采用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')--------------------------------------------------------------------------------
如果要獲取記錄號(hào),你可以使用$recordSet->RecordCount()。如果沒有當(dāng)前記錄則返回-1。
實(shí)例 2: Advanced Select with Field Objects
搜索表格，顯示前兩個(gè)字段. 如果第二個(gè)字段是時(shí)間或日期格式,則將其改為美國標(biāo)準(zhǔn)時(shí)間格式顯示.
php:--------------------------------------------------------------------------------
<?
include('adodb.inc.php'); ///載入adodb
$conn = &ADONewConnection('access'); //新建一個(gè)連接
$conn->PConnect('northwind'); //連接名為northwind的MS-Access數(shù)據(jù)庫
$recordSet = &$conn->Execute('select CustomerID,OrderDate from Orders'); //從Orders表中搜索CustomerID和OrderDate兩個(gè)字段
if (!$recordSet)
print $conn->ErrorMsg(); //如果數(shù)據(jù)庫搜索錯(cuò)誤,顯示錯(cuò)誤信息
else
while (!$recordSet->EOF) {
$fld = $recordSet->FetchField(1); //把第二個(gè)字段賦值給$fld
$type = $recordSet->MetaType($fld->type); //取字段值的格式
if ( $type == 'D' || $type == 'T')
print $recordSet->fields[0].' '.
$recordSet->UserDate($recordSet->fields[1],'m/d/Y').'<BR>'; //如果字段格式為日期或時(shí)間型,使其以美國標(biāo)準(zhǔn)格式輸出
else
print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>'; //否則以原樣輸出
$recordSet->MoveNext(); //指向下一個(gè)記錄
}
$recordSet->Close(); //可選
$conn->Close(); //可選
?>
--------------------------------------------------------------------------------
在這個(gè)例子里, 我們用FetchField()函數(shù)檢查了第二個(gè)字段的格式. 它返回了一個(gè)包含三個(gè)變量的對(duì)象
name: 字段名
type: 字段在其數(shù)據(jù)庫中的真實(shí)格式
max_length:字段最大長度,部分?jǐn)?shù)據(jù)庫不會(huì)返回這個(gè)值,比如MYSQL,這種情況下max_length值等于-1.
我們使用MetaType()把字段的數(shù)據(jù)庫格式轉(zhuǎn)化為標(biāo)準(zhǔn)的字段格式
C: 字符型字段,它應(yīng)該可以在<input type="text">標(biāo)簽下顯示.
X: 文本型字段,存放比較大的文本,一般作用于<textarea>標(biāo)簽
B: 塊,二進(jìn)制格式的大型對(duì)象,如圖片
D: 日期型字段
T: 時(shí)間型字段
L: 邏輯型字段 (布爾邏輯或bit-field)
I: 整型字段
N: 數(shù)字字段. 包括自動(dòng)編號(hào)(autoincrement), 數(shù)字(numeric), 浮點(diǎn)數(shù)(floating point), 實(shí)數(shù)(real)和整數(shù)(integer).
R: 連續(xù)字段. 包括serial, autoincrement integers.它只能工作于指定的數(shù)據(jù)庫.
如果metatype是日期或時(shí)戳類型的，我們用用戶定義的日期格式UserDate()函數(shù)來輸出，UserDate()用來轉(zhuǎn)換php SQL 日期字符串格式到用戶定義的格式，MetaType()的另一種用法是在插入和替換前確認(rèn)數(shù)據(jù)有效性.
實(shí)例 3: Inserting
在訂單數(shù)據(jù)表中插入一個(gè)包含日期和字符型數(shù)據(jù)的記錄,插入之前必須先進(jìn)行轉(zhuǎn)換, eg: the single-quote in the word John's.
php:--------------------------------------------------------------------------------
<?
include('adodb.inc.php'); // 載入adodb
$conn = &ADONewConnection('access'); //新建一個(gè)連接
$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>';
} //如果插入不成功輸出錯(cuò)誤信息
?>
--------------------------------------------------------------------------------
在這個(gè)例子中,我們看到ADOdb可以很容易地處理一些高級(jí)的數(shù)據(jù)庫操作. unix時(shí)間戳 (一個(gè)長整數(shù))被DBDate()轉(zhuǎn)換成正確的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í)行語句的錯(cuò)誤處理. 如果Execute()發(fā)生錯(cuò)誤, ErrorMsg()函數(shù)會(huì)返回最后一個(gè)錯(cuò)誤提示. Note: php_track_errors might have to be enabled for error messages to be saved.
實(shí)例 4: Debugging
<?
include('adodb.inc.php'); // 載入adodb
$conn = &ADONewConnection('access'); //新建一個(gè)連接
$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';
?>
在上面這個(gè)例子里，我們設(shè)置了debug = true.它會(huì)在執(zhí)行前顯示所有SQL信息, 同時(shí)，它也會(huì)顯示所有錯(cuò)誤提示. 在這個(gè)例子里，我們不再需要調(diào)用ErrorMsg() . 要想顯示recordset, 可以參考 rs2html()實(shí)例.
也可以參閱 Custom Error Handlers 的部分內(nèi)容。
實(shí)例 5: MySQL and Menus
連接到MySQL數(shù)據(jù)庫agora, 并從SQL聲明中產(chǎn)生一個(gè)<select>下拉菜單 ，菜單的 <option> 選項(xiàng)顯示為第一個(gè)字段, 返回值為第二個(gè)字段.
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'); //顯示菜單
?>
--------------------------------------------------------------------------------
在這里我們定義了一個(gè)名為GetCust的菜單，其中的'Mary Rosli'被選定. See GetMenu(). 我們還有一個(gè)把記錄值返回到數(shù)組的函數(shù): GetArray(), and as an associative array with the key being the first column: GetAssoc().
實(shí)例 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í)連接兩個(gè)數(shù)據(jù)庫
--------------------------------------------------------------------------------
7: Generating Update and Insert SQL
ADOdb 1.31以上的版本支持兩個(gè)新函數(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.
我們來看看這兩個(gè)函數(shù)在這個(gè)工作表中是如何執(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'); // 奇怪，這句似乎有沒有都一樣，哪位朋友知道原因請給個(gè)解釋
#==========================
# This code tests an insert
$sql = "SELECT * FROM ADOXYZ WHERE id = -1"; #查找一個(gè)空記錄 $conn = &ADONewConnection("mysql"); # create a connection
$conn->debug=1;
$conn->PConnect("localhost", "admin", "", "test"); # connect to MySQL, testdb
$rs = $conn->Execute($sql); # 獲取一個(gè)空記錄
$record = array(); # 建立一個(gè)數(shù)組準(zhǔn)備插入
# 設(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.# 插入前會(huì)格式化變量
$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();
?>
--------------------------------------------------------------------------------
實(shí)例 8 Implementing Scrolling with Next and Previous
下面的演示是個(gè)很小的分頁瀏覽程序.
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);--------------------------------------------------------------------------------
運(yùn)行上面這段程序的結(jié)果如下:
|< << >> >|
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
調(diào)用Render($rows)方法可以分頁顯示數(shù)據(jù).如果你沒有給Render()輸入值, ADODB_Pager默認(rèn)值為每頁10個(gè)記錄.
你可以在 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 對(duì)象在adodb/adodb-pager.inc.php中. 你可以給ADODB_Pager 的代碼加上圖像和改變顏色，你可以通過設(shè)置$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技術(shù)：Adodb的十個(gè)實(shí)例(清晰版)，轉(zhuǎn)載需保留來源！
鄭重聲明：本文版權(quán)歸原作者所有，轉(zhuǎn)載文章僅為傳播更多信息之目的，如作者信息標(biāo)記有誤，請第一時(shí)間聯(lián)系我們修改或刪除，多謝。
