ADODB Database Envelope Library _php Digest

PHP can use the least effort and the most fun to build Dynamic Web site, to build dynamic site we need to use the database to retrieve login account information, spread dynamic news, storage discussion area articles. With the most common MySQL data, your company has done such a fantastic job of making your site more famous than you can imagine. Then you also find that MySQL is not able to cope with the actual workload, it is time to replace the database system.

Unfortunately, in There is a slight difference in access to all the databases in PHP. Link with MySQL you want to use mysql_connect (), when you decide to upgrade to Oracle or Microsoft sql Server , you have to switch to Ocilogon () or mssql_connect (). What's worse is that the parameters used in different links are not the same, some databases say po-tato ( potato pronunciation ), and other databases say pota-to ( another pronunciation of potatoes ) , OH ... Oh, my God.

We're not giving up.
When you need to ensure the portability of your program, a database package called ADODB has emerged. It provides a common application interface to communicate with all supported databases, so you don't have to give up!

ADODB is the abbreviation for active Data Object database (I'm sorry!) Sometimes it's not very original to play computer. ADODB currently supports MySQL, PostgreSQL, Oracle, InterBase, Microsoft SQL Server, Access, FoxPro, Sybase, ODBC, and ADO, you can go from http:// Php.weblogs.com/adodb download ADODB.

Examples of MySQL
The most common database in PHP is MySQL, so I think you'll like the following program code, which links to the localhost MySQL server, the database name is Mydab, and executes an SQL Select Command Query, and the query results are printed in a column of columns.

mysql_connect ("localhost", "root", "password");
mysql_select_db ("MyDB", $db);
 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 program code uses color to mark the segment, the first paragraph is the connected part, the second paragraph executes the SQL command, and the last section shows the field, while the loop scans each column of the result, and the For loop scans the fields for each column.

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

include ("adodb.inc.php");
 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 to point to the Oracle database, the program code as long as the second line to become newadoconnection (' Oracle '), let's look at the complete program code ...

Connecting to the database

include ("adodb.inc.php");
newadoconnection (' MySQL ');
$db->Connect("localhost", "root", "password", "MyDB");

The linked program code is a bit more sophisticated than the original MySQL program code, because we need to be more sophisticated. In ADODB we use object-oriented methods to manage the complexity of multiple databases, we use different types (class) to control different databases. If you are unfamiliar with object-oriented programming, don't worry! All the complex things are hidden behind the newadoconnection () function.

To save memory, we only load the PHP code associated with the database you're connected to, and we do this by calling Newadoconnection (Databasedriver), the legitimate database driver contains mysql,mssql,oracle, Oci8,postgres,sybase,vfp,access,ibase and many other driver programs.

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

Execute SQL command

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

Direct transfer of SQL commands to the server, and when executed successfully, execute () returns a Recordset object that you can check $result as listed above.

One easy question for beginners is that there are two types of objects, links, and Recordset objects in the ADODB, when do we use these objects?

The link 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 a number of useful functions to make the INSERT and update commands easier, as we mentioned in the Advanced section.

Retrieving data

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

The previous examples of access to information are much like reading from files, in each row we first check to see if the end of the file (EOF), if not to the end, the Loop sweep the field in each column, and then move to the next line (MoveNext) and then repeat the same thing.

$result->fields[] Arrays are generated by the PHP database extension system, and some database extensions do not establish the array's index with a field name, 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 = ADODB_FETCH_ASSOC;
	$rs 2 = $db->execute (' SELECT * from table ');
	Array ([0]=> ' V0 ', [1] => ' v1 ')
	Array ([' col1 ']=> ' v0 ', [' col2 '] => ' v1 ')

As you can see in the above example, two recordsets store and use different usage patterns, and then set $adodb_fetch_mode when the recordset is generated by execute ().

ADOConnection

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

Other useful functions

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

$recordset->recordcount () returns the number of record pens accessed by the SQL command, and some databases are returned-1 because they are not supported.

$recordset->getarray () returns the result as an array.

The rs2html ($recordset) function converts the incoming recordset into an HTML table format. The following example shows the related usage in bold text:

  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)

There are many other useful functions listed in the file, which can be found at the following Web site Http://php.weblogs.com/adodb_manual

Advanced Topics

New and updated

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

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

The second issue is the notation for single quotes (don ' t) , which can be used directly in MySQL with single quotes (don ' t), but in other databases such as Sybase, Access, Microsoft SQL Server, Two single quotes (don ' t), the qstr () function resolves the problem.

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 rows 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 everyone that no database has provided these two functions.

Metatypes

You can get more information about the field, through the Recordset method Fetchfield ($fieldoffset) returns the object's 3 attributes: Name,type,max_length.

An example is provided:

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

The result $f0->name the 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 different names for the same data type, such as the timestamp type is called DateTime in a database, and the other 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
D:date
T:timestamp
L:logical (Boolean)
I:integer
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 select command

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

Get backup.

ADODB allows you to hold the recordset's information in your file system, and in $connection->cacheexecute ($secs 2cache, $sql) and $connection-> Cacheselectlimit ($secs 2cache, $sql, $nrows, $offset) and so on when the time interval arrives, only then actually does the database query to save the time.

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://php.weblogs.com/adodb-sessions

Encourage business use

If you are planning to write commercially available PHP applications for sale, you can also use ADODB, and we publish ADODB under the GPL, which means you can legitimately refer to the commercial application software and retain the ownership of your program code. It is strongly encouraged to ADODB commercial applications and is being used for this reason within ourselves.