ADODB database package Library

PHP can use the least effort and the most fun to build a dynamic website. To create a dynamic website, we need to use a database to retrieve account information, spread dynamic news, and store Forum articles. In terms of using the most common MySQL Data, your company has done so amazing work that makes your website more famous than you can imagine. Then you will find that MySQL cannot cope with the actual workload. It is time to replace the database system.

Unfortunately, the access to all databases in PHP is slightly different. To connect to MySQL, you need to use mysql_connect (). When you decide to upgrade to Oracle or Microsoft SQL Server, you must switch to ocilogon () or mssql_connect () respectively (). What's worse, the parameters used by different links are also different. Some databases say po-tato (Potato pronunciation), and other databases say pota-to (another potato pronunciation ), oh ..... Oh, my God.

Don't give up
When you need to ensure the portability of your program, a database package library called ADODB has already appeared. It provides a common application interface to communicate with all supported databases, so you do not have to give up!

ADODB is short for Active Data Object DataBase (sorry! Computers 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 in PHP is MySQL, so I think you will like the following program code. It is linked to the MySQL server of localhost. The database name is mydab and runs a SQL select command query, the query results are printed in a column.

$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 program code in the above column is marked by color. The first section is the link section, the second section is the execution of SQL commands, and the last section is the display column, while loop scan results in each column, while for loop scan to the column of each column.

The following figure shows the same result using the ADODB program code:

 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 it is changed to point to the Oracle database. As long as the program code modifies the second line to NewADOConnection ('oracle '), let's take a look at the complete program code...

Connect to database

include("adodb.inc.php");$db = NewADOConnection('mysql');$db->Connect("localhost", "root", "password", "mydb");

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

To save memory, we only load the PHP program code related to your connected database. We call NewADOConnection (databasedriver) to complete this. valid database drivers include mysql and mssql, oracle, oci8, postgres, sybase, vfp, access, ibase, and many other drivers.

Next, we call NewADOConnection () to generate a new object entity from the link type. Finally, we use $ db-> Connect () to Connect to the database.

Execute SQL commands

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

Directly send the SQL command to the server. After successful execution, Execute () will return a recordset object. You can check $ result as listed above.

A confusing topic for beginners is that there are two types of objects in ADODB, linking objects and recordset objects. When will we use these objects?

The linked object ($ db) is used to connect to the database and format your SQL query. The recordset object ($ result) is responsible for retrieving results and converting response data types 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, which we will mention in the advanced chapter.

Retrieve Data

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

The example of getting the data above is similar to reading the data from an archive. In each row, we first check whether the data has reached the end of the archive (EOF). If the data has not reached the end, we will circle the fields in each column, then move to the next line (MoveNext) and repeat the same thing.

$ Result-> fields [] arrays are generated by the PHP database extension system. Some database extension systems do not index the array by column name, to force the array to be indexed by name, use the common variable $ ADODB_FETCH_MODE.

$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 preceding example, two recordsets are stored and used in different access modes. When the recordset is generated by Execute (), set $ ADODB_FETCH_MODE.

ADOConnection

Link the objects to the database, execute SQL commands, and have a set of tool functions to standard format SQL commands, such as related and Date Format Commands.

Other useful functions

$ Recordset-> Move ($ pos) to roll the current data column. ADODB supports rolling the entire database forward. Some databases do not support rolling back. This is not a problem, because you can use the temporary record to quickly obtain it to simulate rolling.

$ Recordset-> RecordCount () returns the number of records accessed by the SQL command. Some databases return-1 because it is not supported.

$ Recordset-> GetArray () returns the result as an array.

The rs2html ($ recordset) function converts the uploaded recordset to the HTML table format. In the following example, the usage is displayed in bold characters:

  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 also many other useful functions listed in the file that can be found at the following URL for http://php.weblogs.com/adodb_manual

Advanced subjects

Add and update

Suppose you want to add the following information to the database.

ID= 3
TheDate= Mktime (2001,)/* 31st August */
Note= Sugar why don't we call it off

When you switch to another database, you may not be able to add new materials.

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

The second problem is single quotes.(Don't)In MySQL, you can directly use single quotes(Don't)But in other databases such as Sybase, Access, and Microsoft SQL Server, two single quotes are used for representation.(Don't), Qstr () function can solve this problem.

How do we use these functions? 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 data columns affected by the last update or delete command, and the $ recordset-> Insert_ID () function, returns the number of data columns automatically generated by the insert command. We recommend that no database provides these two functions.

Policypes

You can get more information about the field. You can use the recordset method FetchField ($ fieldoffset) to return the three attributes of the object: name, type, max_length.

Example:

$recordset = $conn->Execute("select adate from table");$f0 = $recordset->FetchField(0);

Result $ f0-> name indicates 'adata', and $ f0-> type indicates 'date'. If max_length is unknown, its content is-1.

One problem with processing different databases is that each database has different names for the same data type. For example, the timestamp type is called datetime in a database, while the other database is called time, therefore, ADODB provides the 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'

*/

Select command Limit and Top support

ADODB has a $ connection-> SelectLimit ($ SQL, $ nrows, $ offset) function that allows you to retrieve partial sets of recordset. This is the use of SELECT TOP in Microsoft products, and SELECT in PostgreSQL and MySQL... the advantage of LIMIT usage, even if the original database does not provide this usage, this function also provides this usage.

Cache support

ADODB allows you to save recordset data in your file system, and save it in $ connection-> CacheExecute ($ secs2cache, $ SQL) and $ connection-> CacheSelectLimit ($ secs2cache, $ SQL, $ nrows, $ offset), etc. After the specified time interval arrives, the database query can be performed to save time.

PHP4 Session support

ADODB also supports PHP4 session handler, you can store your session variables in the database, related function information please refer to the http://php.weblogs.com/adodb-sessions

Encourage commercial use

If you plan to write commercial PHP application software for sale, you can also use ADODB. We will publish ADODB Based on GPL, which means you can reference it legally in commercial application software, and keep the ownership of your program code. We strongly encourage the commercial use of ADODB, which we are using internally for this reason.