Introduction to php adodb

Although PHP is a powerful tool for building a Web system, the function of accessing the database by PHP has never been standardized, use a different and incompatible application interface (API ). In order to fill this gap, ADODB emerged. Once the interfaces for accessing the database are standardized, the differences between different databases can be hidden. It is very easy to switch to other different databases.
Currently, the latest version of ADODB is V4.62, supporting a variety of database types, such as: MySQL, PostgreSQL, Interbase, Informix, Oracle, ms SQL 7, Foxpro, Access, ADO, Sybase, DB2 and ODBC (among which the driver of PostgreSQL, Informix, and Sybase is contributed by the development of the Free Software Community ).
One of the biggest advantages of using ADODB is that, regardless of the backend database, the methods for accessing the database are the same, so developers do not have, however, you must learn another set of different access methods, which greatly reduces the knowledge burden on developers. The previous knowledge can still be used in the future. When the database platform is transferred, the program code does not have to be too much updated.
In fact, the development concept of ADODB is not the first. DBI appeared earlier than ADODB. It provides Perl to access the database and uses consistent API call interfaces. I believe that friends who have used Perl + DBI will have a similar feeling when they use ADODB again.
In addition, ADODB should be familiar to people who have used ASP. Such friends should be very easy to accept.
Adodb official: http://adodb.sourceforge.net/
PHP can build dynamic websites with the least effort and the most fun. To build dynamic websites, we need to use databases to retrieve login account information, publish dynamic news, and store discussion board articles. With the use of 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 link library called ADODB has already appeared. It provides common application interfaces to communicate with all supported databases, so you do not have to give up!
ADODB is short for Active Data Object DataBase (sorry! Sometimes it is not very original to play with computers ). 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 part, the second section is the execution of SQL commands, and the last section is the display field. while, each column of the loop scan result is displayed, for loops scan fields in 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 object-oriented methods to manage the complexity of various databases. We use different 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 category. 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 SQL commands 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: linked 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 specifications into text or arrays.
The only thing I need to add is that ADODB provides many useful functions to make 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 data is similar to reading data from an archive. In each row, we first check whether the end of the archive is reached. If the end is not reached, the fields in each column are scanned cyclically, 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 field name, to forcibly index the array 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
Objects linked 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) is used to roll the current data column. ADODB supports the whole database to roll forward, and some databases do not support roll back. This is not a problem, because you can use the temporary record to quickly obtain the simulation results and then roll back.
$ 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 des 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 data to the database.
ID = 3
TheDate = mktime (2001,)/* 31st August */
Note = sugar why don't we call it off
When you use another database, you may not be able to add data.
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 the representation of single quotes (don't). in MySQL, you can directly use single quotes (don't), but in other databases such as Sybase, Access, Microsoft SQL Server, two single quotes (don't) are used. The 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
For more information about fields, you can use the recordset method FetchField ($ fieldoffset) to return 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-> cmdype ($ f0-> type, $ f0-> max_length );
Print $ type;/* shocould 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 the usage method in simulation.
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 is reached, the database query is performed to save time.
PHP4 Session support
ADODB also supports PHP4 session handler, you can store your session variables in the database, the relevant functions 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 legally reference it 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.