ADODB Database Encapsulation Library _php Tutorial

Source: Internet
Author: User
Tags php database sybase
PHP can use the least amount of effort and the most fun to build a dynamic site, to create dynamic sites we need to use the database to retrieve login information, distribute news, storage discussion area of the article. In terms of using the most common MySQL data, your company has done such a fantastic job to make your site more famous than you can imagine. Then you also find that MySQL is unable to cope with the actual workload, it is time to replace the database system.

Unfortunately, access to all databases in PHP is slightly different. Connect with MySQL you want to use mysql_connect (), and when you decide to upgrade to Oracle or Microsoft SQL Server, you must switch to Ocilogon () or Mssql_connect (), respectively. What's worse is that the parameters used for the different links are not the same, some databases say Po-tato (the potato's pronunciation), other databases say pota-to (another pronunciation of potatoes), oh ... oh, my God.

We don't give up.
When you need to make sure that your program is portable, a database envelope library called ADODB has already appeared. It provides a common application interface to communicate with all supported databases, so you don't have to give up!

ADODB is an abbreviation for active Data Object database (sorry!). Sometimes it is not very original to play computer. ADODB currently supports MySQL, PostgreSQL, Oracle, Interbase, Microsoft SQL Server, Access, FoxPro, Sybase, ODBC, and ADO, and you can download ADODB.

Examples of MySQL
PHP is the most common database is MySQL, so I think you will like the following program code, it is linked to the MySQL server localhost, the database name is Mydab, and execute a SQL SELECT command query, the query results are listed in a column.

mysql_connect ("localhost", "root", "password"); mysql_select_db mysql_query ("SELECT * FROM Employees", $DB) ;if ($result === false) die("failed");  while ($fields = mysql_fetch_row for ($i =0, $max =sizeof ($fields); $i < $max; $i + +) {    print Print "

The program code above is marked with a color, the first paragraph is the link, the second is the execution of the SQL command, the last paragraph is the display field, while the loop scans the results of each column, and the for loop scan to the columns of each column.

The next step is to get the same result with ADODB's program code:

include (""); newadoconnection (' MySQL '); $db,Connect("localhost", "root", "password", "MyDB"); $result = $db,Execute if ($result === false) die("failed");   while (! $result->eof) {   for ($i =0, $max =$ Result->FieldCount(); $i < $max; $i + +)      print $result->fields[$i]. ' ';  $result-MoveNext();  Print "

Now instead of pointing to the Oracle database, the program code just modifies the second line to become newadoconnection (' Oracle '), let's take a look at the complete program code ...

Link to database

include (""); newadoconnection (' MySQL '); $db,Connect("localhost", "root", "password", "MyDB");

The program code of the link is more sophisticated than the original MySQL program code, because we need to be more sophisticated. In ADODB we use an object-oriented approach to managing the complexity of diverse databases, we use different types (classes) to control different databases. If you're not familiar with object-oriented programming, don't worry! All complex things are hidden behind the newadoconnection () function.

In order to save memory, we only load the PHP code associated with the database you are connected to, we do this through call Newadoconnection (Databasedriver), the legitimate database driver contains mysql,mssql,oracle, Oci8,postgres,sybase,vfp,access,ibase and many other drivers.

We then generate a new object entity from the link type by calling Newadoconnection (), and finally we use $db->connect () to connect to the database.

Execute SQL command

$result = $db->Execute("SELECT * FROM employees");
if ($result === false) die("failed");

Transfer the SQL command directly to the server, and execute () will return a Recordset object when executed successfully, and you can check the $result as listed above.

A beginner's easy-to-confuse issue is that there are two types of objects in the ADODB, linked objects, and Recordset objects, when do we use these objects?

The linked object ($DB) is responsible for linking the database and formatting your SQL query. The Recordset object ($result) is responsible for retrieving the results and normalizing the response data into text or arrays.

The only thing I need to add is that ADODB provides many useful functions to make the INSERT and update commands easier, as we'll mention in the advanced chapters.

Retrieving data

 while (! $result->eof) {   for ($i =0, $max = $result,fieldcount(); $i < $max; $i + +)    print $result fields[$i]. ' ';  $result-MoveNext();  Print "
n ";}

The previous example of obtaining information is much like reading from a file, in each line we first check whether the end of the file (EOF), if not to the end, the loop swept through the columns in each column, and then moved to the next line (MoveNext) then repeat the same thing.

$result->fields[] Arrays are generated by the PHP database extension system, and some database extension systems do not index the array with field names, forcing the array to be indexed by name, using $adodb_fetch_mode's common variables.

$Adodb_fetch_mode = adodb_fetch_num; $rs 1 = $db->execute (' SELECT * from table '); $Adodb_fetch_mode  Array ([0]=> ' V0 ', [1] = ' v1 ')Array ([' col1 ']=> ' v0 ', [' col2 '] = ' v1 ')

As you can see in the example above, two recordsets are stored and used in different fetch modes, and when the recordset is generated by execute () then set $adodb_fetch_mode.


Objects linked to a database, execute SQL commands, and have a set of tool functions for standard formatting of SQL commands, such as association and date format commands.

Other useful functions

$recordset->move ($pos) Scroll the current data column, ADODB support the entire database to scroll forward, some databases do not support the subsequent scrolling, this is not a problem, because you can use the temporary record to the cache to simulate the scroll.

$recordset->recordcount () returns the number of recorded pens accessed by the SQL command, and some databases return 1 for non-support.

$recordset->getarray () returns the result in array mode.

The rs2html ($recordset) function converts the passed-in recordset to the HTML table format. The following example displays the relevant usage in bold:

  Include (' ');   include (' '); /* Includes the rs2html function *  /$conn = &adonewconnection (' mysql ');   $conn->pconnect (' localhost ', ' userid ', ' Password ', ' database ');  rs2html ($rs)

There are many other useful functions listed in the file, which can be found at the following URLs

Advanced Topics

New and updated

Suppose you want to add the following information to the 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 switch to a different database, you may not be able to add information.

The first problem is that each database has its own default date format, MySQL uses the YYYY-MM-DD format, and other databases have different default formats, and ADODB provides dbdate () functions to convert the date-default format between different databases.

The second issue is the single quotation mark (don ' t) notation, which can be used directly in MySQL , but in other databases such as Sybase, Access, Microsoft SQL Server, The qstr () function resolves this problem by using two single quotation marks ("T").

How do we use these functions? Just like this:

$sql = "INSERT into table (ID, Thedate,note) VALUES ("   . $ID . ', '  . $db->dbdate ($thedate). ', '  . $db->qstr ($Note). ")"; $db->execute ($sql);

ADODB also has the $connection->affected_rows () function, which returns the number of columns of data affected by the last update or delete command, and the $recordset->insert_id () function, Returns the data column number that was automatically generated by the Insert command, reminding you that there are no databases that provide these two functions.


You can get more information about the field, and Fetchfield ($fieldoffset) returns the 3 properties of the object through the recordset: Name,type,max_length.

To illustrate:

$recordset = $conn->execute ("Select adate from Table"), $f 0 = $recordset->fetchfield (0);

The result $f0->name content is ' adata ', $f 0->type will be ' date ', if max_length do not know, its content will be-1.

One problem with different databases is that each database has a different name for the same data type, such as that the timestamp type is called DateTime in a database, and the other database is called time, so ADODB provides Metatype ($type, $max _length) function to standardize the following data types:

C:character and varchar types
X:text or long character (eg. more than 255 bytes wide).
B:blob or binary image
L:logical (Boolean)
N:numeric (float, double, money)

In the previous 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'

Limit and top support for the SELECT command

ADODB has a $connection->selectlimit ($sql, $nrows, $offset) function that lets you take a partial collection of the recordset, using the Select top usage in Microsoft products, and PostgreSQL and MySQL in the Select ... The advantage of the limit usage is that even if the original database does not provide this usage, this function simulates providing that usage.

Quick Access Support

ADODB allows you to hold the recordset's data in your file system and in $connection->cacheexecute ($secs 2cache, $sql) and $connection Cacheselectlimit ($secs 2cache, $sql, $nrows, $offset) wait until the set time interval is reached, so you can save time by actually doing database queries.

PHP4 Session Support

ADODB also support PHP4 session handler, you can store your session variables in the database, related function information please refer to Http://

Encourage commercial use

If you plan to write a commercial PHP application software to sell, you can also use ADODB, we publish the ADODB according to the GPL, that is, you can legitimately reference in the commercial application software, and retain the ownership of your program code. Strongly encourage the commercial application of ADODB, and we ourselves are using it for this reason. true techarticle PHP can use the least effort and the most fun to build a dynamic site, to build a dynamic site we need to use the database to retrieve login information, distribute dynamic news, storage ...

  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

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

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

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

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.