PHP ADODB Introduction _php Basics

Although PHP is a powerful tool for building WEB systems, PHP's ability to access databases has not been standardized, and each database uses a different and incompatible application interface (API). In order to fill this shortcoming, there is ADODB. Once the interface to the database is standardized, it is possible to hide the differences between the various databases, and it becomes very easy to convert to different databases.
The latest version of ADODB is V4.62, which supports a variety of databases, such as: MySQL, PostgreSQL, InterBase, Informix, Oracle, MS SQL 7, Foxpro, Access, ADO, Sybase, DB2 and General ODBC (where PostgreSQL, Informix, Sybase's driver are contributed by the free software community).
One of the greatest advantages of using ADODB is that: regardless of the backend database, the way in which the database is accessed is consistent, and the development designer does not have to learn a different set of access methods for a particular set of databases, which greatly reduces the knowledge burden of the developer, and the knowledge of the past continues to be used in the future. When you transfer a database platform, the program code does not have to make much changes.
In fact, ADODB such a development concept, is not the first, DBI than ADODB appeared earlier, it provides Perl access to the database, using a consistent API call interface. I believe that using Perl + DBI friends, and then to use ADODB, there will be a sense of déjà vu.
In addition, ADODB for the use of the ASP, should not be unfamiliar, such friends of ADODB should be very easy to accept.
ADODB official: http://adodb.sourceforge.net/
PHP can use the least energy and the most fun to build Dynamic Web site, to build dynamic site we need to use the database to retrieve login account information, release 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, access to all databases in PHP is slightly different. With MySQL link you will 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 in different links are not the same, and some databases say Po-tato (the pronunciation of potatoes), and other databases say pota-to (another pronunciation of potatoes), oh ... oh, my God.
We're not giving up.
When you need to ensure that your program is portable, a database packet-link library 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, the query results will be printed out 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 above program code uses color to mark the segment, the first paragraph is the connected part, the second paragraph executes the SQL instruction, the last section is the Display 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");
$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 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");
$db = 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 classes (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 drivers.
We then generate a new object entity from the link category by calling Newadoconnection (), and finally we use the $db->connect () to link the database.
Execute SQL directives
$result = $db->execute ("SELECT * FROM Employees");
if ($result = = False) Die ("failed");
Direct transfer of SQL instructions to the server, and when executed successfully, execute () returns a Recordset object, which you can check $result as listed above.
One easy question for beginners is that there are two types of objects, linked objects, and Recordset objects in ADODB, when do we use these objects?
The linked object ($DB) is responsible for linking the database and formatting your SQL queries. The Recordset object ($result) is responsible for retrieving the results and normalizing the response data into words or arrays.
The only thing I need to add is that ADODB provides a number of useful functions to make the insert and update instructions 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 example of getting the data is much like reading data from a file, we first check to see if the end of the file (EOF), if not to the end, loop through 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, and some database extensions do not establish an index of the array with the field name, forcing the array to be indexed by name, using $adodb_fetch_mode's universal 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 ');
Print_r ($rs 1->fields); Shows array ([0]=> ' V0 ', [1] => ' v1 ')
Print_r ($rs 2->fields); Shows 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 instructions and have a set of tool functions to standard format SQL instructions, such as association and date format.
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 back, which is not a problem, because you can use the staging record to the quick fetch to simulate backward scrolling.
$recordset->recordcount () returns the number of record pens accessed by the SQL instruction, 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); /* Recordset to HTML table/*
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 data.
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 problem is the notation for single quotes (don ' t), which can be used directly in MySQL (Don ' t), but in other databases such as Sybase, Access, Microsoft SQL Server, two single quotes (don ' t), Qstr () function to resolve this 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 data columns affected by the last update or delete instruction, and the $recordset->insert_id () function, Returns the data column number that was eventually automatically generated by the insert instruction, reminding everyone that no database has provided these two functions.
Metatypes
You can get more information about the field, and pass the recordset's method Fetchfield ($fieldoffset) back to the object's 3 properties: 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 patterns:
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");
$f 0 = $recordset->fetchfield (0);
$type = $recordset->metatype ($f 0->type, $f 0->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 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 also simulates the way it is provided.
Get backup.
ADODB allows you to temporarily hold the recordset's data in your file system, and in $connection->cacheexecute ($ secs2cache, $sql) and $connection-> Cacheselectlimit ($secs 2cache, $sql, $nrows, $offset), and so on when the time interval arrives, really do the database query to save time.
PHP4 Session Support
ADODB also support PHP4 session handler, you can store your session variables in the database, related functions please refer to Http://php.weblogs.com/adodb-sessions
Encourage business use
If you are planning to write a commercial PHP application to sell, you can also use ADODB, and we publish the ADODB under the GPL, which means you can legitimately refer to the commercial application 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.