Adodb的十個執行個體(清晰版)

Adodb的十個執行個體(清晰版)_PHP教程

最後更新：2016-07-21 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

本想學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].'
';
$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').'
'; //如果欄位格式為日期或時間型,使其以美國標準格式輸出
else
print $recordSet->fields[0].' '.$recordSet->fields[1].'
'; //否則以原樣輸出

$recordSet->MoveNext(); //指向下一個記錄
}
$recordSet->Close(); //可選
$conn->Close(); //可選

?>
--------------------------------------------------------------------------------

在這個例子裡, 我們用FetchField()函數檢查了第二個欄位的格式. 它返回了一個包含三個變數的對象

name: 欄位名
type: 欄位在其資料庫中的真實格式
max_length:欄位最大長度,部分資料庫不會返回這個值,比如MYSQL,這種情況下max_length值等於-1.
我們使用MetaType()把欄位的資料庫格式轉化為標準的欄位格式

C: 字元型欄位,它應該可以在標籤下顯示.
X: 文本型欄位,存放比較大的文本,一般作用於標籤 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().' '; } //如果插入不成功輸出錯誤資訊 ?> -------------------------------------------------------------------------------- 在這個例子中,我們看到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 class="brush:php;toolbar:false">";print rs2csv($rs); # return a string, CSV formatprint ''; $rs->MoveFirst(); # note, some databases do not support MoveFirstprint rs2tab($rs,false); # return a string, tab-delimited # false == suppress field names in first lineprint '';$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. http://www.bkjia.com/PHPjc/317529.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/317529.htmlTechArticle本想學pear的，可是網上看到的幾篇文章對adodb的評價相當高，所以改學了這個。 ADODB的優點有這幾個(網上說的，不是我說的)： 1、速度比...<li >

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More

Adodb的十個執行個體(清晰版)_PHP教程

聯繫我們

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support