Detailed explanation of ADODB class in PHP_PHP tutorial-PHP Tutorial-php.cn

Although PHP is a powerful tool for building Web systems, the function of PHP accessing databases has not been standardized. Each database uses a different and incompatible application programming interface (API). In order to fill this shortcoming, ADODB appeared. Once the interface for accessing the database is standardized, the differences between various databases can be hidden, and it will be very easy to switch to other different databases.

Currently ADODB supports many types of databases, such as: MySQL, PostgreSQL, Interbase, Informix, Oracle, MS SQL 7, Foxpro, Access, ADO, Sybase, DB2 and general ODBC (including PostgreSQL, Informix , Sybase's driver is contributed by the development of the free software community).

One of the biggest advantages of using ADODB is that regardless of the back-end database, the way to access the database is the same. Developers and designers do not have to learn another different set of databases for a certain set of databases. Access methods, which greatly reduce the knowledge burden on developers. Past knowledge can still be used in the future. When the database platform is transferred, the program code does not need to be changed too much.

In fact, the development concept of ADODB is not the first. DBI appeared earlier than ADODB. It provides Perl with a consistent API call interface when accessing the database. I believe that friends who have used Perl + DBI will feel familiar when they use ADODB again.

In addition, ADODB should be familiar to those who have used ASP, and such friends should be very easy to accept ADODB.

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

//Introducing the inc file of adodb can call the functions provided by adodb

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

// Select the type of database to connect to to create an online object,

// Once the object is created, it can be used Its member functions handle the database.

// The following $conn is this object (object)

$conn = &ADONewConnection('mysql');

// Whether to display the debugging message, false No, true yes.

// $conn->debug = false;

$conn->debug = true;

// Connect to database

// Usage: $conn->Connect('Host', 'User', 'Password', 'Database');

// Use case:

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

// If you want to use a persistent connection, the above formula can be replaced with PConnect:

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

// Set sql command

$sql = "insert into t values ('abcde', 18)";

// Execute 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 results are as follows:

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

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

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

OK!

If debugging is turned off, i.e. $conn->debug=false, the result will be as follows:

OK!

The following will introduce you step by step: the key methods of using ADODB.

2. Installation
The homepage of ADODB is at: http://php.weblogs.com/ADOdb. The latest version currently (2002/10/24) is: version 2.42. You can download it from ADODB or go to Tainan County Education Network Center FTP download.

The method of installing ADODB is super simple, just download, unzip, and put it in the appropriate location, and you are done! As shown below:

1. Download:

$ ncftp ftp.tnc.edu.tw

cd sysop/ADODB

get adodb242.tgz

2. Unzip:

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

$ cp adodb242.tgz /var/www/html

$ tar xvzf adodb242.tgz

as shown below:

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 is omitted... .

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

3. Introduction of ADODB
Once ADODB is installed, the ADODB related include files should be introduced into your program before use. It doesn't matter where the adodb directory is placed, as long as it points to the correct path file name. Generally speaking, your program code only needs to include adodb.inc.php.

The method is as follows:

In your PHP program:

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

or

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

Example:

If your program is in the same directory as adodb Next:

.

..

adodb/

something.php*

then:

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

.

If the location is in a directory somedir:

.

..

adodb

somedir/something.php

must use:

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

except The include file adodb.inc.php, ADODB also provides many include files adodb-*.inc.php, most of which are designed to drive the special usage of certain databases.

If you introduce adodb-session.php, you can store the session in the database to maintain the application.

If you introduce adodb-pager.inc.php, it will be convenient for you to display pagination.

If you introduce adodb-errorhandler.inc.php, you can customize error handling messages.

If you include adodb-pear.inc.php, you can use PHP4's PEAR DB syntax to use ADODB. At this point, you can still use DSN to connect to the database string settings. For example $dsn="mysql://piza:ooo123@localhost/test";

If tohtml.inc.php is introduced, it can help you convert the retrieved records into HTML in the program code. Displayed in a table.

If you introduce toexport.inc.php, you can easily export CSV files or data files with tab-separated fields.

If you introduce rsfilter.inc.php, you can pre-filter before using the records.

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

Attention! adodb.inc.php must be imported. For other functions, it depends on which function you want to use, and then import the included file.

4. Select the database type and create a connection object
Since ADODB uses an object-oriented approach, after importing the file, you can then create a connection object depending on the type of your back-end database. The method is as follows:

Take the MySQL database as an example:

$conn = &ADONewConnection('mysql');

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

The 'mysql' in the above example refers to the type of database driver, and ADODB will call the corresponding database driver accordingly.

Other commonly used drivers are: access, ado, ado_access, ado_mssql, db2, vfp, ibase, borland_ibase, informix, imformix72, mssql, oci8, odbc, postgres, postgres64, postgres7, sqlanywhere, sybase... .etc.

We call the created object $conn an ADOConnection object, which represents connection transactions with the database and is processed through this object. The ADOConnection object provides many processing methods. In object-oriented terms, these methods are called member functions. This is the interface for the outside world to access this object.

Once the online object is created, there are many object functions at your disposal! Please see the introduction in the next section.

5. Debug mode
During the program development process, in order to facilitate the detection of possible problems, we usually turn on the debug mode and then turn it off after the program function is indeed stable. ADODB provides a debug mode that displays how the database operates when accessing it.

Turn on debugging mode, usage:

$conn->debug=true

Turn off debugging mode, usage:

$conn->debug=false

6. Connect to the database
Next, use the Connect or PConnect function of the $conn connection object to connect to the specific database. At this time, the DSN ( Data Source Names) related data, DSN may include: host name, database user, database password, database name. Depending on the database type, DSN may be able to omit some of these items. In the case of MySQL, all four of the above must be provided.

This function will return 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, use persistent connection:

// Format: $conn-> ;PConnect('Host', 'User', 'Password', 'Database');

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

If you want to check whether the connection is successful, you can use a variable to receive the return value:

$mch="localhost";

$user="piza" ;

$pwd="ooo123";

$database="test";

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

Or, use persistent connection:

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

if (!$cok) { echo "Unable to connect to database $database"; exit; }

7. Set the sql command syntax and execute the sql command
Next, you can design The sql command syntax you want to execute and then execute it.

$sql = "SQL command syntax here";

$rs = $conn->Execute($sql);

Among them, $rs is the returned result. If $rs == false, it means the execution failed. You must check it carefully.

You don’t have to put the command syntax in the $sql variable, you can also put it directly in Execute() brackets. If the command is short, it doesn’t matter. If the command is long, I suggest you use a variable $sql to set the command string!

Starting from the next section, I will introduce you to the basic commands of SQL, such as: Insert , Select, Update, Delete, etc. usage.

8. Insert record (Insert)
Insert is used as follows:

// $name is a string, $year is a number

$name='abcde ';

$year=18;

// Insert a record, the case of the command does not matter, but the case of the data table t and variables is different!

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

// $sql = "INSERT INTO t values ('$name', $year)"; Also available.

// Execution

$rs = $conn->Execute($sql);

// Check the execution results and handle errors; if normal, continue Other actions....

if (!$rs) print $conn->ErrorMsg();

....Omitted below....

ErrorMsg() is an error display function. It will take out the error message and display it.

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

An example is as follows:

//Introducing ADODB

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

// Create connection object

$conn = &ADONewConnection('mysql');

// Debug

$conn->debug=true;

// DSN four basic data settings

$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 hold the data to be updated

$r = array();

$r['name']='john';

$r['year']=28;

// Use GetInsertSQL function to make a complete sql command, This sql command is placed in $insertSQL

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

// Perform insert

$conn- >Execute($insertSQL);

$conn->Close();

?>

The debugging message is as follows:

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

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

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

(mysql): INSERT INTO t ( name, year ) VALUES ( 'john', 28 )

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

9. Take out the record ( Select)
Select is used as follows:

//Introducing ADODB

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

// Create connection object

$conn = &ADONewConnection('mysql');

// No debugging

$conn->debug= false;

// DSN four basic data settings

$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 will return an ADORecordSet record set object $rs (RecordSet)

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

// The contents of all field data of the record are stored in the fields array

//, indexed by numbers, starting from 0

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

/ / If $rs is false, display the error message

if (!$rs) {

print $conn->ErrorMsg();

} else {

//When the end position (EOF: End Of File) of the record set $rs has not been reached (ie: when there are still records that have not been taken out)

while (!$rs-> ;EOF) {

// Show all fields, $FieldCount() will return the total number of fields

for ($i=0, $max=$rs->FieldCount() ; $i < $max; $i++) {

print $rs->fields[$i] . " ";

}

// Move to Next record

$rs->MoveNext();

// Change columns

echo "
n";

}

}

$rs->Close(); // Not necessary

$conn->Close(); // Can not be used

?>

$rs->fields[] array is generated by the database extension function of PHP. Some extension functions are not supported. Field names are used as indexes.

If you want to use names as indexes, commonly known as hash or associative arrays, you need to use the global variable $ADODB_FETCH_MODE to specify it.

The following setting: Use numeric index $ADODB_FETCH_MODE= ADODB_FETCH_NUM;

The following setting: Use name index $ADODB_FETCH_MODE= ADODB_FETCH_ASSOC; >
The following is an example of using name index:

//Introducing ADODB

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

// Create connection object

$conn = &ADONewConnection('mysql');

// No debugging

$conn-> debug=false;

// DSN four basic data settings

$mch="localhost";

$user="root";

$pwd="jack168";

$database="test";

// Connect to the database test

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

// Before executing sql, specify the name index to use

$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;

// Execute Select, it will pass Return an ADORecordSet record set object $rs

// Actually $rs is a cursor indicator, which holds the current record content,

// The record is stored in the fields array

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

// If $rs is false, show an error message

if (!$rs) {

print $conn->ErrorMsg();

} else {

// When the end of the record set (EOF) 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 (); // Can be used

$conn->Close(); // Can be used

?>

10. Fetch records (use FetchRow)
Here is a demonstration of 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() will take out The records are returned, you can use an array to receive them.

Attention! If you use FetchRow(), you don’t need to use MoveNext(). FetchRow will automatically complete the action of moving to the next record internally.

11. Update record (Update)
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 a connection object

$conn = &ADONewConnection(' mysql');

// Debug

$conn->debug=true;

// DSN four basic data settings

$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 hold the data to be updated

$r = array();

$r['name']='john';

$r['year']=28;

// Use the GetUpdateSQL function to create a complete sql command. This sql command is placed in $updateSQL

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

//Execute update

$conn->Execute($updateSQL);

$conn-> Close();

?>

The debugging message is as follows:

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

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

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

(mysql): UPDATE t SET name = 'john' , year = 28 WHERE year=18

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

12. Delete records (Delete)
Deleting records is very simple, using the traditional method. Available: $sql = "DELETE FROM t WHERE year=18";

$rs = $conn->Execute($sql);

13. Use Field Objects
Here is a demonstration of 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) {

// Take out the second field

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

// Print field name and field type

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

$rs->MoveNext();

print "
n";

}

}

In addition, ADODB provides a RecordSet function MetaType(), which can convert the original field type into a general type code:

C: Characters

X : text

B : blob

D : date

T : timestamp

L : boolean or bit

I: Integer

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

R: serial, auto-increment

Use case:

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

//Print the field name and field type code

print $f->name . ":" . $rs->MetaType($f->type);

14. Simple paging (Pager)
ADODB provides a simple To display records in paging, adodb-pager.inc.php must be introduced before use.

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

// Introducing paging function

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

// Start 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 pager object

$pager = new ADODB_Pager($db, $sql);

// Each page shows 5 records

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

?>

The results are as follows:

Figure 1. Simple paging function

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

In addition, the field name can also be changed, as shown below:

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

//Introducing paging function

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

// Start 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 pager object

$pager = new ADODB_Pager($db, $sql);

// Show 5 records per page

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

?>

The results are as follows:

Figure 2. Change field name

15. Output CSV file
ADODB provides output of CSV file Method, before use, toexport.inc.php must be introduced.

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

//Introducing the output CSV file 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 CSV format

print rs2csv($rs);

?>

The results are 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 method of using rs2tab is as follows:

print rs2tab($rs, false);

Note: false means not to display the field name

The result is as follows:

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

if print rs2tab ($rs, true);

The results are 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 Display, use rs2tabout method as follows:

print rs2tabout($rs);

The execution result is as follows:

Figure 1 . Display the results in the console

or save it as a CSV file:

// File path

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

// Open the file for writing

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

// If the file is opened successfully

if ($fhd) {

// Then write to CSV

rs2csvfile($rs, $fhd);

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

//Close file

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. Take out certain Number of records (using SelectLimit)
ADODB provides an ADOConnect function SelectLimit, which allows 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);

// Take out 3 transactions, at the 1st After pen

// Show these 3 records

if ($rs) {

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

print $ar['name'] ." " . $ar['year'];

print "
n";

}

}

The above formula means: after the first record, take out 3 records, that is, the 2nd, 3rd, and 4th records.

The results are as follows:
-----------------------------------------

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

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

Attention! The writing method of SelectLimit is just opposite to the syntax of MySQL!