Tutorial on using ADODB class _ PHP

Use the ADODB class. MySQL is the most common database in PHP, 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

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"
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"
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"
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 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 single quotes.(Don't)In MySQL, you can directly use single quotes(Don't ),However, in other databases such as Sybase, Access, and Microsoft SQL Server, two single quotes are used for representation.(Don't ),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 this usage 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), and so on, the database query can be performed only after the specified time interval is reached 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.

MySQL is the most common database in PHP, 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...