PHP Application Development With ADODB (part 2)

色胃康胶囊

　　版权声明：原创作品，允许转载，转载时请务必以超链接形式标明文章原始出版、作者信息和本声明。否则将追究法律责任。http://blog.csdn.net/mayongzhan - 马永占,myz,mayongzhan
PHP Application Development With ADODB (part 2)
PHP 使用ADODB进行应用程序开发 (部分 2)
Find out how ADODB can be used to optimize multiple-run queries, commit and roll back transactions, and improve performance by caching query results.
找出ADODB怎样优化 multiple-run 查询, 提交和退回处理, 利用高速缓冲存储查询结果.
Moving On

In the first part of this article, I introduced you to the ADODB database abstraction library, and showed you a little of how it works. I demonstrated how using it in your PHP application development could substantially reduce the time spent on code rewrites if your RDBMS decided to change shape, and also gave you a crash course in the basic functions built into the library.
在文章的开始, 我推荐你去看一下 ADODB 数据抽象库, 那里面有一点基础东西,myz.我要示范怎样在你的PHP程序开发使用它,在你的RDBMS更换的时候使开发时间缩短,尽量避免重写代码, 告诉你一些ADODB库中的基本函数.

Fortunately, that isn't all she wrote. ADODB comes with a whole bunch of bells and whistles, which allow you to do some fairly nifty new things in your PHP scripts. Over the next few pages, I'll be showing you some of them - so flip the page, and let's get started!
幸运的是, ADODB允许你做一些新的事情在你PHP程序中.myz:这段翻的不好. 在下面几页中, 我将向你展示, 让我们开始吧!

Rapid Execution

In the event that you need to execute a particular query multiple times with different values - for example, a series of INSERT statements - the ADODB class comes with two methods that can save you a huge amount of time and also reduce overhead. Consider the following example, which demonstrates:
在你需要执行多次类似的语句使用不同值的情况下 – 例如,一连串的 INSERT,ADODB可以节约大量的时间节约费用. 请看下面例子:


<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");

// include the ADODB library
include("adodb.inc.php");

// create an object instance
// configure library for a MySQL connection
$db = NewADOConnection("mysql");

// open connection to database
$db->Connect("localhost", "john", "doe", "db278") or die("Unable to connect!");

// prepare query
$query = $db->Prepare("INSERT INTO library (title, author) VALUES (?, ?)");

// read title-author list in from CSV file
$data = file("list.txt");

// iterate through each line in file
foreach ($data as $l)
{
// split on comma
$arr = explode(",", $l);
// insert values into prepared query
$result = $db->Execute($query, array($arr[0], $arr[1])) or die("Error in query: $query. " . $db->ErrorMsg());
}

// clean up
$db->Close;
?>


Prepare() function, which takes an SQL query as parameter, readies a query for execution, but does not execute it (kinda like the priest that walks down the last mile with you to the electric chair). Instead, prepare() returns a handle to the prepared query, which is stored and then passed to the Execute() method, which actually executes the query (bzzzt!).
Prepare() 函数, 把SQL query当作参数, 准备一个查询要执行. 当使用Exccute()方法时,传入当初Perpaer()缺少的信息,然后执行.

Note the two placeholders used in the query string passed to Prepare() - these placeholders are replaced by actual values each time Execute() runs on the prepared statement. The second argument to Execute() is a PHP array containing the values to be substituted in the query string.
两个占位符会用字符串替换传递给 Prepare() – 每次执行Execute()这些占位符会被替换成实际值. 除了用一个文件保存数据外,另一种方法是使用 PHP 数组包含将要替换的值.


It should be noted that using Prepare() can provide performance benefits when you have a single query to be executed a large number of times with different values. However, this benefit is only available to you if your database system supports prepared queries (MySQL does not at this time, although Interbase and Oracle do); in all other cases, only simulated functionality is available and Prepare() becomes equivalent to a simple Execute(), with no inherent performance gain.
需要注意的是当你使用一个单一查询使用很多不同值来执行时,使用 Prepare() 能提高性能.. 然而, 性能的提升只能用于你的数据库系统支持准备好的查询的时候; 在其他的地方, Prepare()和 Execute()性能乎没有变化.

A Fear Of Commitment

If your database system supports transactions (MySQL doesn't, but quite a few others do), you'll be pleased to hear that ADODB allows you to transparently use this feature in your scripts.
如果你的数据库系统支持事务(MySQL不支持,其他很多的数据库管理系统支持), 你将很高兴的了解到 ADODB允许你在你的程序中使用事务.

The following example demonstrates:
例如:

<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");

// include the ADODB library
include("adodb.inc.php");

// create an object instance
// configure library for a MySQL connection
$db = NewADOConnection("mysql");

// open connection to database
$db->Connect("localhost", "john", "doe", "db278") or die("Unable to connect!");

// turn off auto-commit
// begin transaction block
$db->BeginTrans();

// first query
$query = "INSERT INTO library (title, author) VALUES ('Title A', 'Author B')";
$result = $db->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg());

// use ID from first query in second query
if ($result)
{
$id = $db->Insert_ID();
$query = "INSERT INTO purchase_info (id, price) VALUES ($id, 'USD 39.99')";
$result = $db->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg());
}

// if no failures
if ($result)
{
// commit
$db->CommitTrans();
}
// else rollback
else
{
$db->RollbackTrans();
}

// clean up
$db->Close;
?>


The first step here is to turn off auto-committal of data to the database, via the BeginTrans() method; this method also marks the beginning of a transaction block, one which can be ended by either CommitTrans() or RollbackTrans(). Once auto-commit has been turned off, you can go ahead and execute as many queries as you like, secure in the knowledge that no changes have (yet) been made to the database.
第一步是关闭数据库数据自动委托,通过BeginTrans()方法;这个方法标记了事务块的开始,通过CommitTrans()或者RollbackTrans()来关闭事务,一旦自动委托被关闭,你就可以执行多条语句,安全的存到数据库中,而不必担心会因中途停止而出现错误.

Every call to Execute() within the transaction block returns either a true or false value, depending on whether or not the query was successful. These values can be tracked, and used to determine whether or not the entire transaction should be committed. Once you're sure that all is well, you can save your data to the database via a call to the CommitTrans() method. In the event that you realize you made a mistake, you can rewind gracefully with the RollbackTrans() function.
在事务块中调用Excute()会返回true或者false, 取决于查询是否成功. 这些值被追踪, 被用于当没有全部传送时还原. 一旦全部传送, 即调用CommitTrans(). 如果发生错误,即调用 RollbackTrans().

Cache Cow

One of the coolest things about ADODB has to be its support for cached queries. Why? Because caching your queries can result in a fairly significant performance improvement, especially if you're executing the same tired old SELECT every time.
ADODB支持缓存查询.缓存你的查询可以改进的程序, 特别是你每次总是执行一个SELECT的时候.

In order to illustrate the difference, let's take a look at how this normally works:
为了阐明差异,让我们看一下普通的代码:

<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");

// include the ADODB library
include("adodb.inc.php");

// create an object instance
// configure it for a MySQL connection
$db = NewADOConnection("mysql");

// open connection to database
$db->Connect("localhost", "john", "doe", "db278") or die("Unable to connect!");

// execute query
$query = "SELECT * FROM library";
$result = $db->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg());

// iterate through resultset
// print column data in format TITLE - AUTHOR
while (!$result->EOF)
{
echo $result->fields[1] . " - " . $result->fields[2] . "\n";
  $result->MoveNext();
}

// get and print number of rows in resultset
echo "\n[" . $result->RecordCount() . " rows returned]\n";

// close database connection
$db->Close();

?>


This should be familiar to you by now - it's a very basic SQL SELECT operation with ADODB. If this was your personal Web site, and you were getting 5000 hits a minute, you'd be running the query above 30,000 times an hour. As you might imagine, this will have your database server scurrying around like a hamster on cocaine - not to mention affecting the performance of your Web site.
你可能对此比较熟悉 – 这是非常基础的使用ADODB的SQL SELECT.如果这条语句使用在你的个人网站上,并且你的网站每分钟有5000次点击,那么你将要执行这个查询每个小时超过30000次.你可以想象,你的数据库服务器跑的像一只吃了兴奋剂的老鼠 – 你的个人网站性能需求太高了!

ADODB offers a better option - caching the results of the first SELECT query, and using this cached resultset in each subsequent run of the query. This reduces the load on the database server, and can also provide you with an incremental performance benefit.
ADODB 提出了一个更好的选择 – 缓存第一个 SELECT 语句, 用于以后每次执行这个查询. 这样减少了读取数据库, 程序可以更好的执行.


Here's what the revised script looks like:
修正的程序如下:


<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");

// include the ADODB library
include("adodb.inc.php");

// set cache location
$ADODB_CACHE_DIR = '.';

// create an object instance
// configure it for a MySQL connection
$db = NewADOConnection("mysql");

// open connection to database
$db->Connect("localhost", "john", "doe", "db278") or die("Unable to connect!");

// execute query
$query = "SELECT * FROM library";
$result = $db->CacheExecute(300,$query) or die("Error in query: $query. " . $db->ErrorMsg());

// iterate through resultset
// print column data in format TITLE - AUTHOR
while (!$result->EOF)
{
echo $result->fields[1] . " - " . $result->fields[2] . "\n";
  $result->MoveNext();
}

// get and print number of rows in resultset
echo "\n[" . $result->RecordCount() . " rows returned]\n";

// close database connection
$db->Close();

?>


The first argument to CacheExecute() is the number of seconds to cache the query results; the second is, obviously, the query string itself. The remainder of the script remains unchanged - a cached resultset is processed in exactly the same manner as a non-cached one.
CacheExecute() 的第一个变量是缓存的时间(秒); 第二个,很明显是查询语句. 剩余的代码不必改变 – 被缓存的结果和不被缓存的结果在使用上是相同的.


You can also use the CacheFlush() method to flush all queries from the cache.
你也可以使用 CacheFlush() 来重新缓冲所有的查询
What's On The Menu?

ADODB also comes with a couple of methods designed specifically for common Web development tasks. One of the most useful is the GetMenu() method, which retrieves and iterates over a resultset, and uses it to automatically build a form drop-down list containing the database records. This comes in very handy for dynamically-generated forms, when the items in the various form listboxes have to be dynamically built from a database.
ADODB 有几个方法用作通用Web开发任务. 其中一个最有用的是 GetMenu() 方法, 读取数据集的全部内容,然后自动建立一个列表,这个列表包括所有的数据内容. 非常敏捷的动态产生表, 这些不同的内容是动态的从数据库中建立的.


Here's an example of how it works:
例如:

<html>
<head></head>
<body>
<?php
// include the ADODB library
include("adodb.inc.php");

// create an object instance
// configure it for a MySQL connection
$db = NewADOConnection("mysql");

// open connection to database
$db->Connect("localhost", "john", "doe", "db278") or die("Unable to connect!");

// execute query
$query = "SELECT title, id FROM library";
$result = $db->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg());

// print HTML menu
print $result->GetMenu("library", '', false);

// close database connection
$db->Close();

?>
</body>
</html>


The GetMenu() method takes a number of arguments, which can be used to control the behaviour of the generated list box. The first argument is the name for the list ("library", in this case); the second is the default value for the list; the third lets you specify whether the first item in the list should be empty; and the fourth lets you control whether or not the list allows multiple selection.
GetMenu()方法有许多的属性, 有一些是控制生成的列表的. 第一个属性是名字(在上面的代码中叫"library"); 第二个是列表的默认值; 第三个指定列表中第一个项目是否为空; 第四个属性让你控制是否这个列表允许多项选择.

Here's the HTML code generated by the script above:
下面是输出代码:

<select name="library" >
<option value="15">Mystic River</option>
<option value="16">Where Eagles Dare</option>
<option value="17">XML and PHP</option>
</select>


As you can see, the contents of the list box are built from the resultset returned by the query; the first column of the resultset becomes the label for each list item, while the second is the corresponding value.
你可以看到,列表框的内容是通过查询产生的数据集的内容; 结果的第一栏变成列表的标签值, 第二个是相应的内容.


The GetMenu() method can simplify the task of developing a Web form substantially, significantly reducing the amount of code you have to write - consider using it the next time you need to build a list box from the records in a database.
GetMenu() 方法可以简化开发任务, 减少要写的代码的数量 – 考虑一下在下一次开发中使用此方法吧.myzhan.

A Rose By Any Other Name...

ADODB also allows you to export a resultset into a variety of different formats - comma-separated text, tab-separated text, or even an HTML table. These functions are not part of the ADODB class per se; rather, they are packaged as ancillary functions in a separate file, which needs to include()-d in your scripts. The following example demonstrates:
ADODB 允许你以不同的格式来输出结果集 – 以逗号分开的文本, 以tab分开的文本, 或者一个html表.这些不是ADODB本身函数; 这些函数在别的文件中, 需要include这些你需要的文件. 例如:


<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");

// include the ADODB library
include("adodb.inc.php");

// include conversion functions
include("toexport.inc.php");

// create an object instance
// configure it for a MySQL connection
$db = NewADOConnection("mysql");

// open connection to database
$db->Connect("localhost", "john", "doe", "db278") or die("Unable to connect!");

// execute query
$query = "SELECT title, id FROM library";
$result = $db->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg());

// return a CSV string
echo rs2csv($result);

// close database connection
$db->Close();

?>

here's the output:
输出如下:


title,id
Mystic River,15
Where Eagles Dare,16
XML and PHP,17


You can suppress the first line - the column list - by adding an extra argument to the call to rs2csv(), like this:
你可以让第一行的内容禁止显示 – 列表 - rs2csv()中加入一个属性, 例如:


<?php

// snip

// return a CSV string
echo rs2csv($result, false);

?>


And here's the revised output:
更正后的输出:

Mystic River,15
Where Eagles Dare,16
XML and PHP,17


You can format the data as a tab-separated string with the rs2tab() function,
你也可以用tab格式化字符串,

<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");

// include the ADODB library
include("adodb.inc.php");

// include conversion functions
include("toexport.inc.php");

// create