Description of the ADODB class in PHP

1. preface ADODB is short for ActiveDataObjectsDataBase. it is a function component for accessing the database in PHP. Chen Yingguang, the host of the SFS3 system (Campus Free Software Exchange Network student affairs system) plan, decided to use this component, in order to allow more partners who are interested in the project to smoothly join the development process, the younger brother believes that it is necessary to write the introduction to the Chinese language of ADODB for reference by partners. 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, ADODB supports many 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.

Let's take a look at the simple usage of ADODB:


// Introduce the inc file of adodb to call the function provided by adodb

Include ('adodb/adodb. inc. php ');

// Select the connected database type to create an online object,

// Once an object is created, its member function can be used to process the database.

// The following $ conn is the object)

$ Conn = & ADONewConnection ('mysql ');

// Do you want to display the error message, false or true.

// $ Conn-> debug = false;



$ Conn-> debug = true;

// Connect to the database

// Usage: $ conn-> Connect ('host', 'user', 'password', 'database ');

// Use case:

$ Conn-> Connect ('localhost', 'piza ', 'ooo123', 'test ');

// If you want to use persistent connections, you can use PConnect in the above format:

// $ Conn-> PConnect ('localhost', 'piza ', 'ooo123', 'test ');

// Set the SQL command

$ SQL = "insert into t values ('ABCDE', 18 )";

// Execute the SQL command

$ Rs = $ conn-> Execute ($ SQL );

// Check the execution result. if $ rs = false, call the member function ErrorMsg () of the $ conn object ()

If (! $ Rs) print $ conn-> ErrorMsg (); else print "OK! ";

?>



The result is as follows:

---------------------------------------------

(Mysql): insert into t values ("abcde", 18)

---------------------------------------------

OK!



If you disable the debug function, that is, $ conn-> debug = false, the result is as follows:

OK!



The following describes how to use ADODB.

2. Installation
The home page of ADODB is http://php.weblogs.com/adodb. the latest version is 2.42 (). you can download it from ADODB or from the FTP center of Tainan County Education Network.

The method for installing ADODB is extremely simple. you only need to download, decompress, and place it in the appropriate location! As follows:

1. download:

$ Ncftp ftp.tnc.edu.tw

Cd sysop/ADODB

Get adodb242.tgz

2. decompress:

Suppose I put adodb242.tgz in/var/www/html

$ Cp adodb242.tgz/var/www/html

$ Tar xvzf adodb242.tgz

As follows:

Adodb/adodb-cryptsession.php

Adodb/adodb-csvlib.inc.php

Adodb/adodb-errorhandler.inc.php

Adodb/adodb-errorpear.inc.php

Adodb/adodb-lib.inc.php

Adodb/adodb-pager.inc.php

... The following content is omitted ....



Now, you have installed adodb in/var/www/html/ADODB.

3. introduce ADODB
Once you have installed ADODB, you should introduce the ADODB-related inclusion files into your program before use. It doesn't matter if the adodb directory is placed anywhere, as long as it can point to the correct path file name. Generally, your program code only needs to introduce adodb. inc. php.

The procedure is as follows:

In your PHP program:

Include ('path/adodb. inc. php ');

Or

Include_once ('path/adodb. inc. php ');



Example:

If your program and adodb are in the same directory:

.

..

Adodb/

Something. php *



Then:

Include ('adodb/adodb. inc. php ');

You can.

If the location is in a directory named somedir:

.

..

Adodb

Somedir/something. php



You must use:

Include ('../adodb. inc. php ');



In addition to the inclusion file of adodb. inc. php, ADODB also provides many inclusion files of adodb-*. inc. php, most of which are designed to drive the special usage of some databases.

The introduction of adodb-session.php allows you to store sessions in the database for maintenance and application.

If a adodb-pager.inc.php is introduced, it makes it easy for you to display by page.

If an adodb-errorhandler.inc.php is introduced, you can customize error handling messages.

If the adodb-pear.inc.php is included, you can use the pear db syntax of PHP4 to use ADODB. In this case, you can use DSN to set the string used to connect to the database. For example, $ dsn = "mysql: // piza: ooo123 @ localhost/test ";

If tohtml. inc. php is introduced, you can easily convert the retrieved records into HTML tables for display in the program code.

If toexport. inc. php is introduced, you can easily output CSV files or data files with tab-separated fields.

Rsfilter. inc. php allows you to pre-filter records before using them.

If pluttable. inc. php is introduced, you can use the lateral table function (commonly known as cross-tabulations ).

Note! Adodb. inc. php must be introduced. for others, you can introduce this include file as needed.

4. select a database type and create an online object
Because ADODB uses the object-oriented approach, you can create an online object based on the type of your backend database after introducing the file. The procedure is as follows:

Take MySQL database as an example:

$ Conn = & ADONewConnection ('mysql ');

Note: NewADOConnection and ADONewConnection are the same and both can be used.



In the above example, 'mysql' refers to the drvier type of the database, and ADODB calls the corresponding database driver accordingly.

Other commonly used drivers include: access, ado, ado_access, ado_mssql, db2, vfp, ibase, borland_ibase, informix, imformix72, mssql, oci8, odbc, IPVs, ipvs64, ipvs7, sqlanywhere, sybase .... and so on.

We call the created object $ conn as an ADOConnection object, which represents the connection transaction with the database and is processed through this object. The ADOConnection object provides many processing methods, which are object-oriented. these methods are called member functions, which are interfaces for external access to this object.

Once an online object is created, there are many object types available for you to call! See the introduction in the next section.

5. Error detection mode
In the process of program development, in order to easily find out where problems may occur, we usually turn on the error detection mode and close the program once its functions are stable. ADODB provides an error detection mode. when accessing a database, the operation mode is displayed.

To enable the error detection mode, use the following method:

$ Conn-> debug = true



Disable the error detection mode. use the following method:

$ Conn-> debug = false

6. connect to the database
Then, use the Connect or PConnect function of the $ conn online object to Connect to a specific database. in this case, Data Source Names (DSN) Data must be provided. the DSN may include: host name, database user, database password, and database name. Different database types may be omitted by DSN. For MySQL, all the above four are provided.

This function returns true or false to indicate whether the connection is successful.

Use case:

// Format: $ conn-> Connect ('host', 'user', 'password', 'database ');

$ Conn-> Connect ('localhost', 'piza ', 'ooo123', 'test ');

Or, continuous connection:

// Format: $ conn-> PConnect ('host', 'user', 'password', 'database ');

$ Conn-> PConnect ('localhost', 'piza ', 'ooo123', 'test ');

To check whether a connection is successful, you can use a variable to obtain the returned value:

$ Mch = "localhost ";

$ User = "piza ";

$ Pwd = "ooo123 ";

$ Database = "test ";

$ Cok = $ conn-> Connect ($ mch, $ user, $ pwd, $ database );

Or, continuous connection:

$ Cok = $ conn-> PConnect ($ mch, $ user, $ pwd, $ database );

If (! $ Cok) {echo "unable to connect to database $ database"; exit ;}

7. set SQL command syntax and execute SQL commands
Next, you can design the SQL command syntax you want to execute and then put it into execution.

$ SQL = "SQL command syntax ";

$ Rs = $ conn-> Execute ($ SQL );



$ Rs indicates the return result. if $ rs = false, the execution fails. you must check the result carefully.

You do not have to place the command syntax in the $ SQL variable, or directly put it in the Execute () brackets. If the command is short, you can use a variable $ SQL to set the command string!

The next section introduces basic SQL commands, such as Insert, Select, Update, and Delete.

8. Insert)
Insert is used as follows:

// $ Name is a string and $ year is a number.

$ Name = 'ABCDE ';

$ Year = 18;

// Insert a record. the command is case-insensitive, but the data table t and variables are case-insensitive!

$ SQL = "INSERT INTO t VALUES ('$ name', $ year )";

// $ SQL = "insert into t values ('$ name', $ year.

// Execute

$ Rs = $ conn-> Execute ($ SQL );

// Check the execution result and handle the error. if it is normal, continue with other actions ....

If (! $ Rs) print $ conn-> ErrorMsg ();

... The following content is omitted ....



ErrorMsg () is the form of the error display. it retrieves the error message and displays it.

In addition, ADODB provides a RecordSet function GetInsertSQL () to help you generate the Insert syntax.

Example:


// Introduce ADODB

Include ('adodb/adodb. inc. php ');

// Create an online object

$ Conn = & ADONewConnection ('mysql ');

// Error detection

$ Conn-> debug = true;

// Configure four basic data items of DSN

$ Mch = "localhost ";

$ User = "root ";

$ Pwd = "jack168 ";

$ Database = "test ";

// Connect to the database test

$ Conn-> PConnect ($ mch, $ user, $ pwd, $ database );

// Generate an empty record

$ SQL = "select * from t where year =-1 ";

$ Rs = $ conn-> Execute ($ SQL );



// Use an empty array to load the data to be updated

$ R = array ();

$ R ['name'] = 'John ';

$ R ['Year'] = 28;

// Use the GetInsertSQL function to create a complete SQL command, which is placed in $ insertSQL

$ InsertSQL = $ conn-> GetInsertSQL ($ rs, $ r );

// Execute insert

$ Conn-> Execute ($ insertSQL );

$ Conn-> Close ();

?>



The error message is as follows:

-----------------------------------------------------------

(Mysql): select * from t where year =-1

-----------------------------------------------------------

(Mysql): insert into t (name, year) VALUES ('John', 28)

-----------------------------------------------------------

9. Select)
The usage of Select is as follows:


// Introduce ADODB

Include ('adodb/adodb. inc. php ');

// Create an online object

$ Conn = & ADONewConnection ('mysql ');

// No error detected

$ Conn-> debug = false;

// Configure four basic data items of DSN

$ Mch = "localhost ";

$ User = "piza ";

$ Pwd = "ooo123 ";

$ Database = "test ";

// Connect to the database test

$ Conn-> PConnect ($ mch, $ user, $ pwd, $ database );

// Execute Select to retrieve data from table t,

// It returns an ADORecordSet record set Object $ rs (RecordSet)

// In fact, $ rs is a cursor indicator, which has the current record (row or record ),

// Content of all the field data of the record, which is stored in the fields array

//, Index by number, the first one starts from 0

$ Rs = & $ conn-> Execute ('select * from t ');

// If $ rs is false, an error message is displayed.

If (! $ Rs ){

Print $ conn-> ErrorMsg ();

} Else {

// When the End position (EOF: End Of File) Of the record set $ rs has not been reached, (that is, when there are still records not Retrieved)

While (! $ Rs-> EOF ){

// Show all fields. $ FieldCount () returns the total number of fields.

For ($ I = 0, $ max = $ rs-> FieldCount (); $ I <$ max; $ I ++ ){

Print $ rs-> fields [$ I]. "";

}

// Move to the next record

$ Rs-> MoveNext ();



// Change columns

Echo"
\ N ";

}

}

$ Rs-> Close (); // optional

$ Conn-> Close (); // optional

?>



$ Rs-> fields [] arrays are generated by PHP Database Extensions. some extensions do not support using field names as indexes.

If you want to use the name as an index, that is, hash or associative arrays, you need to use the global variable $ ADODB_FETCH_MODE to specify.

The following settings: Use the numeric index $ ADODB_FETCH_MODE = ADODB_FETCH_NUM;



The following settings: use name index $ ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;



The following is an example of using name index:


// Introduce ADODB

Include ('adodb/adodb. inc. php ');

// Create an online object

$ Conn = & ADONewConnection ('mysql ');

// No error detected

$ Conn-> debug = false;

// Configure four basic data items of DSN

$ Mch = "localhost ";

$ User = "root ";

$ Pwd = "jack168 ";

$ Database = "test ";

// Connect to the database test

$ Conn-> PConnect ($ mch, $ user, $ pwd, $ database );

// Specify the name index before executing the SQL statement

$ ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;

// Execute Select, which returns an ADORecordSet record set Object $ rs

// In fact, $ rs is a cursor indicator, which has the current record content,

// This record is stored in the fields array.

$ Rs = & $ conn-> Execute ('select * from t ');

// If $ rs is false, an error message is displayed.

If (! $ Rs ){

Print $ conn-> ErrorMsg ();

} Else {

// When the end position (EOF) of the record set has not been reached,

While (! $ Rs-> EOF ){

// Show all fields

Print $ rs-> fields ['name']. "". $ rs-> fields ['Year'];

// Move to the next record

$ Rs-> MoveNext ();



// Change columns

Echo"
\ N ";

}

}

$ Rs-> Close (); // optional

$ Conn-> Close (); // optional

?>

10. retrieve records (using FetchRow)
Here we demonstrate the usage of FetchRow:

$ SQL = "select * from t ";

$ Rs = $ conn-> Execute ($ SQL );

If ($ rs ){

While ($ ar = $ rs-> FetchRow ()){

Print $ ar ['name']. "". $ ar ['Year'];

Print"
\ N ";

}

}



FetchRow () returns the retrieved records. you can use an array to obtain the records.

Note! You do not need to use MoveNext () when using FetchRow (). FetchRow automatically moves to the next record.

11. Update record)
You can use the traditional method:

$ SQL = "UPDATE t SET name = 'John', year = 28 WHERE year = 18 ";

$ Conn-> Execute ($ SQL );



You can also use the following method:


// Introduce ADODB

Include ('adodb/adodb. inc. php ');

// Create an online object

$ Conn = & ADONewConnection ('mysql ');

// Error detection

$ Conn-> debug = true;

// Configure four basic data items of DSN

$ Mch = "localhost ";

$ User = "piza ";

$ Pwd = "ooo123 ";

$ Database = "test ";

// Connect to the database test

$ Conn-> PConnect ($ mch, $ user, $ pwd, $ database );

// Select the record to be updated

$ SQL = "select * from t where year = 18 ";

$ Rs = $ conn-> Execute ($ SQL );



// Use an empty array to load the data to be updated

$ R = array ();

$ R ['name'] = 'John ';

$ R ['Year'] = 28;

// Use the GetUpdateSQL function to create a complete SQL command, which is placed in $ updateSQL

$ UpdateSQL = $ conn-> GetUpdateSQL ($ rs, $ r );

// Execute update

$ Conn-> Execute ($ updateSQL );

$ Conn-> Close ();

?>



The error message is as follows:

-------------------------------------------------------------

(Mysql): select * from t where year = 18

-------------------------------------------------------------

(Mysql): UPDATE t SET name = 'John', year = 28 WHERE year = 18

-------------------------------------------------------------

12. Delete a record)
Deleting a record is simple. you can use the traditional method: $ SQL = "DELETE FROM t WHERE year = 18 ";

$ Rs = $ conn-> Execute ($ SQL );

13. use Field Objects)
Here we demonstrate the usage of the field object FetchField to obtain the field name and field type:

$ SQL = "select * from t ";

$ Rs = & $ conn-> Execute ($ SQL );

If ($ rs ){

While (! $ Rs-> EOF ){

// Retrieve the Second Field

$ F = $ rs-> FetchField (1 );

// Print the field name and field type

Print $ f-> name. ":". $ f-> type;

$ Rs-> MoveNext ();

Print"
\ N ";

}

}



In addition, ADODB provides a RecordSet function that converts the original field type to the general state code:

C: Character

X: text

B: blob

D: Date

T: timestamp

L: Boolean value or bit

I: integer

N: number type, including: auto increment, value, floating point number, real number, and integer

R: serial, auto-increment



Use case:

$ F = $ rs-> FetchField (1 );

// Print the code for the field name and field type

Print $ f-> name. ":". $ rs-> Variable Ype ($ f-> type );

14. Pager)
ADODB provides a simple way to display records by page, to introduce the adodb-pager.inc.php before use.


Include ('adodb/adodb. inc. php ');

// Introduce the paging function

Include ('adodb/adodb-pager.inc.php ');

// Start the session

Session_start ();

$ Db = ADONewConnection ('mysql ');

$ Mch = "localhost ";

$ User = "piza ";

$ Pwd = "ooo123 ";

$ Database = "test ";

$ Db-> Connect ($ mch, $ user, $ pwd, $ database );

$ SQL = "select * from t ";

// Generate a pager object

$ Pager = new ADODB_Pager ($ db, $ SQL );

// Show 5 records per page

$ Pager-> Render ($ rows_per_page = 5 );

?>



The result is as follows:



Figure 1. simple paging



The number of records displayed on each page is controlled by Render (). if the specified row number is not input to Render (), the default value is 10 records per page.

In addition, the field name can be changed as follows:


Include ('adodb/adodb. inc. php ');

// Introduce the paging function

Include ('adodb/adodb-pager.inc.php ');

// Start the session

Session_start ();

$ Db = ADONewConnection ('mysql ');

$ Mch = "localhost ";

$ User = "piza ";

$ Pwd = "ooo123 ";

$ Database = "test ";

$ Db-> Connect ($ mch, $ user, $ pwd, $ database );

$ SQL = "select name as 'name', year as 'age' from t ";

// Generate a pager object

$ Pager = new ADODB_Pager ($ db, $ SQL );

// Show 5 records per page

$ Pager-> Render ($ rows_per_page = 5 );

?>



The result is as follows:



Figure 2. change the field name



15. output CSV files
ADODB provides a CSV file output method. before using the file, you must introduce toexport. inc. php.


Include ('adodb/adodb. inc. php ');

// Import the CSV file output function

Include ('adodb/toexport. inc. php ');

$ Db = ADONewConnection ('mysql ');

$ Mch = "localhost ";

$ User = "piza ";

$ Pwd = "ooo123 ";

$ Database = "test ";

$ Db-> Connect ($ mch, $ user, $ pwd, $ database );

$ SQL = "select name as 'name', year as 'age' from t ";

$ Rs = $ db-> Execute ($ SQL );

// Show the CSV format

Print rs2csv ($ rs );

?>



The result is as follows:

Name, age

Abcde, 45

Yyy, 20

Ppp, 34

Mmm, 13

Hhh, 41

Rrr, 65

Kkk, 29

Miso, 154

Sss, 89

Abc, 18

Abcde, 0

Uyt, 58

John, 28



You can also use tab to separate fields. the rs2tab method is as follows:

Print rs2tab ($ rs, false );

Note: false indicates that the field name is not displayed.

The result is as follows:

Abcde 45

Yyy 20

Ppp 34

Mmm 13

Hhh 41

Rrr 65

Kkk 29

Miso154

Sss 89

Abc 18

Abcde 0

Uyt 58

John 28

If print rs2tab ($ rs, true );

The result is as follows:

Name age

Abcde 45

Yyy 20

Ppp 34

Mmm 13

Hhh 41

Rrr 65

Kkk 29

Miso154

Sss 89

Abc 18

Abcde 0

Uyt 58

John 28



You can also display the result by STDOUT. the rs2tabout method is as follows:

Print rs2tabout ($ rs );



The execution result is as follows:



Figure 1. display results on the console



You can also save it as a CSV file:

// File path

$ Path = "/tmp/test.csv ";

// Open file for writing

$ Fhd = fopen ($ path, "w ");

// If the file is opened successfully

If ($ fhd ){

// Write to CSV

Rs2csvfile ($ rs, $ fhd );

// You can also use rs2tabfile ($ rs, $ fhd );

// Close the archive

Fclose ($ fhd );

}

The result is as follows:

[Ols3 @ p web] $ cat/tmp/test.csv

Name, age

Abcde, 45

Yyy, 20

Ppp, 34

Mmm, 13

Hhh, 41

Rrr, 65

Kkk, 29

Miso, 154

Sss, 89

Abc, 18

Abcde, 0

Uyt, 58

John, 28

16. retrieve records with a certain number of records (using SelectLimit)
ADODB provides an ADOConnect function SelectLimit for you to retrieve a certain number of records. the usage is as follows:

$ Conn-> Connect ($ mch, $ user, $ pwd, $ database );

Rs = $ conn-> SelectLimit ("Select * from t", 3, 1 );

// 3 pens are taken out, and after 1st

// Show the three records

If ($ rs ){

While ($ ar = $ rs-> FetchRow ()){

Print $ ar ['name']. "". $ ar ['Year'];

Print"
\ N ";

}

}

In the above example, after 1st records, 3, 3, and 4 records are taken out.

The result is as follows:
--------------------------------------

(Mysql): select * from t LIMIT 1, 3

--------------------------------------

Note! SelectLimit is written in the opposite way as MySQL syntax!