PHP is all about creating dynamic web-sites with the least fuss and the most fun. To create these websites we need to use databases to retrieve login information, to splash dynamic news onto the web page and store forum postings. So let's say we were using the popular MySQL database for this. Your company has done such a fantastic job that the Web site is more popular than your wildest dreams. You find that MySQL cannot scale to handle the workload; time to switch databases.
Unfortunately in PHP every database is accessed slightly differently. To connect to MySQL, you would use mysql_connect(); when you decide to upgrade to Oracle or Microsoft SQL Server, you would use ocilogon() or mssql_connect() respectively. What is worse is that the parameters you use for the different connect functions are different also.. One database says po-tato, the other database says pota-to. Oh-oh.
Let's NOT call the whole thing off
A database wrapper library such as ADODB comes in handy when you need to ensure portability. It provides you with a common API to communicate with any supported database so you don't have to call things off.
ADODB stands for Active Data Objects DataBase (sorry computer guys are sometimes not very original). ADODB currently supports MySQL, PostgreSQL, Oracle, Interbase, Microsoft SQL Server, Access, FoxPro, Sybase, ODBC and ADO. You can download ADODB from http://php.weblogs.com/adodb.
MySQL Example
The most common database used with PHP is MySQL, so I guess you should be familiar with the following code. It connects to a MySQL server at localhost, database mydb, and executes an SQL select statement. The results are printed, one line per row.
$db = mysql_connect("localhost", "root", "password");
mysql_select_db("mydb",$db);
$result = mysql_query("SELECT * FROM employees",$db);
if ($result === false) die("failed");
while ($fields = mysql_fetch_row($result)) {
for ($i=0, $max=sizeof($fields); $i < $max; $i++) {
print $fields[$i].' ';
}
print "<br>\n";
}
The above code has been color-coded by section. The first section is the connection phase. The second is the execution of the SQL, and the last section is displaying the fields. The while loop scans the rows of the result, while the for loop scans the fields in one row.
Here is the equivalent code in ADODB
include("adodb.inc.php");
$db = NewADOConnection('mysql');
$db->Connect("localhost", "root", "password", "mydb");
$result = $db->Execute("SELECT * FROM employees");
if ($result === false) die("failed");
while (!$result->EOF) {
for ($i=0, $max=$result->FieldCount(); $i < $max; $i++)
print $result->fields[$i].' ';
$result->MoveNext();
print "<br>\n";
}
Now porting to Oracle is as simple as changing the second line to NewADOConnection('oracle'). Let's walk through the code...
Connecting to the Database
include("adodb.inc.php");
$db = NewADOConnection('mysql');
$db->Connect("localhost", "root", "password", "mydb");
The connection code is a bit more sophisticated than MySQL's because our needs are more sophisticated. In ADODB, we use an object-oriented approach to managing the complexity of handling multiple databases. We have different classes to handle different databases. If you aren't familiar with object-oriented programing, don't worry -- the complexity is all hidden away in the NewADOConnection() function.
To conserve memory, we only load the PHP code specific to the database you are connecting to. We do this by calling NewADOConnection(databasedriver). Legal database drivers include mysql, mssql, oracle, oci8, postgres, sybase, vfp, access, ibase and many others.
Then we create a new instance of the connection class by calling NewADOConnection(). Finally we connect to the database using $db->Connect().
Executing the SQL
$result = $db->Execute("SELECT * FROM employees");
if ($result === false) die("failed");
Sending the SQL statement to the server is straight forward. Execute() will return a recordset object on successful execution. You should check $result as we do above.
An issue that confuses beginners is the fact that we have two types of objects in ADODB, the connection object and the recordset object. When do we use each?
The connection object ($db) is responsible for connecting to the database, formatting your SQL and querying the database server. The recordset object ($result) is responsible for retrieving the results and formatting the reply as text or as an array.
The only thing I need to add is that ADODB provides several helper functions for making INSERT and UPDATE statements easier, which we will cover in the Advanced section.
Retrieving the Data
while (!$result->EOF) {
for ($i=0, $max=$result->FieldCount(); $i < $max; $i++)
print $result->fields[$i].' ';
$result->MoveNext();
print "<br>\n";
}
The paradigm for getting the data is that it's like reading a file. For every line, we check first whether we have reached the end-of-file (EOF). While not end-of-file, loop through each field in the row. Then move to the next line (MoveNext) and repeat.
The $result->fields[] array is generated by the PHP database extension. Some database extensions do not index the array by field name. To force indexing by name - that is associative arrays - use the $ADODB_FETCH_MODE global variable.
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
$rs1 = $db->Execute('select * from table');
$ADODB_FETCH_MODE = 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')
As you can see in the above example, both recordsets store and use different fetch modes based on the $ADODB_FETCH_MODE setting when the recordset was created by Execute().
ADOConnection
Object that performs the connection to the database, executes SQL statements and has a set of utility functions for standardising the format of SQL statements for issues such as concatenation and date formats.
Other Useful Functions
$recordset->Move($pos) scrolls to that particular row. ADODB supports forward scrolling for all databases. Some databases will not support backwards scrolling. This is normally not a problem as you can always cache records to simulate backwards scrolling.
$recordset->RecordCount() returns the number of records accessed by the SQL statement. Some databases will return -1 because it is not supported.
$recordset->GetArray() returns the result as an array.
rs2html($recordset) is a function that is generates a HTML table based on the $recordset passed to it. An example with the relevant lines in bold:
include('adodb.inc.php');
include('tohtml.inc.php'); /* includes the rs2html function */
$conn = &ADONewConnection('mysql');
$conn->PConnect('localhost','userid','password','database');
$rs = $conn->Execute('select * from table');
rs2html($rs); /* recordset to html table */
There are many other helper functions that are listed in the documentation available at http://php.weblogs.com/adodb_manual.
Advanced Material
Inserts and Updates
Let's say you want to insert the following data into a database.
ID = 3
TheDate=mktime(0,0,0,8,31,2001) /* 31st August 2001 */
Note= sugar why don't we call it off
When you move to another database, your insert might no longer work.
The first problem is that each database has a different default date format. MySQL expects YYYY-MM-DD format, while other databases have different defaults. ADODB has a function called DBDate() that addresses this issue by converting converting the date to the correct format.
The next problem is that the don't in the Note needs to be quoted. In MySQL, we use don\'t but in some other databases (Sybase, Access, Microsoft SQL Server) we use don''t. The qstr() function addresses this issue.
So how do we use the functions? Like this:
$sql = "INSERT INTO table (id, thedate,note) values ("
. $ID . ','
. $db->DBDate($TheDate) .','
. $db->qstr($Note).")";
$db->Execute($sql);
ADODB also supports $connection->Affected_Rows() (returns the number of rows affected by last update or delete) and $recordset->Insert_ID() (returns last autoincrement number generated by an insert statement). Be forewarned that not all databases support the two functions.
MetaTypes
You can find out more information about each of the fields (I use the words fields and columns interchangebly) you are selecting by calling the recordset method FetchField($fieldoffset). This will return an object with 3 properties: name, type and max_length.
For example:
$recordset = $conn->Execute("select adate from table");
$f0 = $recordset->FetchField(0);
Then $f0->name will hold 'adata', $f0->type will be set to 'date'. If the max_length is unknown, it will be set to -1.
One problem with handling different databases is that each database often calls the same type by a different name. For example a timestamp type is called datetime in one database and time in another. So ADODB has a special MetaType($type, $max_length) function that standardises the types to the following:
C: character and varchar types
X: text or long character (eg. more than 255 bytes wide).
B: blob or binary image
D: date
T: timestamp
L: logical (boolean)
I: integer
N: numeric (float, double, money)
In the above date example,
$recordset = $conn->Execute("select adate from table");
$f0 = $recordset->FetchField(0);
$type = $recordset->MetaType($f0->type, $f0->max_length);
print $type; /* should print 'D' */
Select Limit and Top Support
ADODB has a function called $connection->SelectLimit($sql,$nrows,$offset) that allows you to retrieve a subset of the recordset. This will take advantage of native SELECT TOP on Microsoft products and SELECT ... LIMIT with PostgreSQL and MySQL, and emulated if the database does not support it.
Caching Support
ADODB allows you to cache recordsets in your file system, and only requery the database server after a certain timeout period with $connection->CacheExecute($secs2cache,$sql) and $connection->CacheSelectLimit($secs2cache,$sql,$nrows,$offset).
PHP4 Session Handler Support
ADODB also supports PHP4 session handlers. You can store your session variables in a database for true scalability using ADODB. For further information, visit http://php.weblogs.com/adodb-sessions
Commercial Use Encouraged
If you plan to write commercial PHP applications that you want to resell, you should consider ADODB. It has been released using the lesser GPL, which means you can legally include it in commercial applications, while keeping your code proprietary. Commercial use of ADODB is strongly encouraged! We are using it internally for this reason.
分享到:
相关推荐
graph slam tutorial :从推导到应用3(g2o版程序),包含文档读取,及后端优化
https://www.pyimagesearch.com/2018/07/19/opencv-tutorial-a-guide-to-learn-opencv/ 代码
The Java EE 6 Tutorial: Basic Concepts, Fourth Edition, is a task-oriented, example-driven guide to developing enterprise applications for the Java Platform, Enterprise Edition 6 (Java EE 6)....
Analog Circuit Design: A Tutorial Guide to Applications and Solutions by:Bob Dobkin, Jim Williams ISBN-10 书号: 0123851858 ISBN-13 书号: 9780123851857 Edition 版本: 1 出版日期: 2011-09-13 pages 页数: ...
mysql-tutorial::seedling:这是MySQL的教程。 在本教程中,您可以学习如何使用MySQL和优化SQL
Tutorial 22: Render to Texture Tutorial 23: Fog Tutorial 24: Clipping Planes Tutorial 25: Texture Translation Tutorial 26: Transparency Tutorial 27: Reflection Tutorial 28: Screen ...
matplotlib - tutorial:matplotlib 的源代码资源
本书是一本教程,教您如何使用NVIDIA与Microsoft紧密合作开发的Cg(可编程图形硬件的主要语言)编写程序。
MatConvNet tutorial:Train your own data.zip
本书教您如何使用Ruby on Rails开发和部署真正的,具有工业实力的Web应用程序,Ruby on Rails是为诸如Twitter,Hulu,GitHub和Yellow Pages等顶级网站提供支持的开源Web框架。
Java EE 7 Tutorial: The Duke's Bookstore Source Code
NIPS 2016 Tutorial: Generative Adversarial Networks(生成对抗网络) , by Ian Goodfellow
This tutorial provides an introduction to classification procedures using Landsat TM data from Cañon City, Colorado. Results of both unsupervised and supervised classifications are examined and post ...
mysql-tutorial:MySQL入门教程(MySQL教程书)
C++ Tutorial: Sockets - Server & Client using QT - 2015使用代码
In this tutorial, we aim to present to researchers and industry practitioners a broad overview of imitation learning techniques and recent applications. Imitation learning is a powerful and practical ...
Ruby.on.Rails.Tutorial,Learn.Web.Development.with.Rails,Third.Edition-中文版 文字版.pdf 个人收集电子书,仅用学习使用,不可用于商业用途,如有版权问题,请联系删除!
NULL 博文链接:https://liu-weiaa.iteye.com/blog/666585
This Book has been prepared for ...Title: Objective C Tutorial: Simply Easy Learning Author: Virender Singh Length: 309 pages Edition: 1 Language: English Publication Date: 2015-07-05 ISBN-10: B0112YNTDC
In order to provide dynamic content to Web users, 2-tier web applications were realized with the introduction of the Common Gateway Interface (CGI), which retrieves content from external data ...