adodb Detailed explanation of ADODB class in PHP

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 (the drivers for PostgreSQL, Informix and Sybase are Contributed after development by the free software community).
One of the biggest advantages of using ADODB is: regardless of the back-end database, the way to access the database is the same. Developers and designers do not have to learn another set of different access methods for a certain set of databases. This This greatly reduces 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 original. 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 easily able to accept ADODB.
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 type of database to connect to create a connection object.
// Once the object is created, its member functions can be used to process the database.
//The following $conn is this object (object)
$conn = &ADONewConnection('mysql');
//Whether to display the debugging message, false is not required, true is required.
// $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 connection, the above formula can be Use PConnect instead:
// $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 of the $conn object ErrorMsg()
if (!$rs) print $conn->ErrorMsg(); else print "OK!";
?>
The result is as follows:
-------------------------- ----------------------------------
(mysql): insert into t values ​​("abcde",18)
-- ---------------------------------------------
OK!
If you check it If it is turned off by mistake, that is, $conn->debug=false, the result is as follows:
OK!
The following is a step-by-step introduction to the key methods of using ADODB.
2. Installation
ADODB’s homepage is at: http://php.weblogs.com/ADOdb. Currently (2002/10/24) the latest version is: version 2.42. You can download it from ADODB or FTP from Tainan County Education Network Center .
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 into /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. Introduce 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 and adodb are in the same directory:
.
..
adodb/
something.php*
Then:
include('adodb/adodb.inc.php');
That’s it.
If the location is in a directory somedir:
.
..
adodb
somedir/something.php
You must use:
include('../adodb/adodb.inc.php');
Except adodb. The inc.php include file, ADODB also provides many adodb-*.inc.php include files, these are mostly designed to drive 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 you introduce tohtml.inc.php, it can help you convert the retrieved records into an HTML table in the program code. show.
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 introduced. 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 and both can be used.
'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 the connection transaction 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
In the process of program development, 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 database
Next, use $conn connection object Connect or PConnect function to connect to a specific database, at this time DSN (Data Source Names) related data must be provided. DSN may include: host name, database user, database password, database name. Different database types, DSN may be able to omit some of these items. In the case of MySQL, all four of the above are 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, choose Persistent 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 command
Next, you can design the sql command syntax you want to execute and then execute it.
$sql = "Put SQL command syntax here";
$rs = $conn->Execute($sql);
Among them, $rs is the returned result. If $rs == false, it means the execution failed. , you have to 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, Usage of Delete and so on.
8. Insert record (Insert)
Insert is used as follows:
// $name is a string, $year is a number
$name='abcde';
$year=18;
// Insert a record, command The case of t does not matter, but the case of data table t and variables is different!
$sql = "INSERT INTO t VALUES ('$name', $year)";
// $sql = "insert into t values ​​(' $name', $year)"; can also be used.
//Execution
$rs = $conn->Execute($sql);
//Check the execution results and handle errors; if normal, continue other actions....
if (!$rs) print $ conn->ErrorMsg();
....The following is omitted....
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.
Examples are as follows:
//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="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 the GetInsertSQL function to create a complete sql command. This sql command is placed in $insertSQL
$insertSQL = $conn->GetInsertSQL($rs , $r);
//Execute 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. Retrieve records (Select)
Select is used as follows:
//Introduce ADODB
include('adodb/adodb.inc.php');
//Establish a 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, the first one starts from 0
$rs = &$conn->Execute('select * from t');
//If $rs is false, display the error message
if (!$rs) {
print $conn->ErrorMsg();
} else {
// When the record set has not yet been reached $ When the end position of rs (EOF: End Of File), (that is: when there are still records that have not been taken out)
while (!$rs->EOF) {
// Show all fields, $FieldCount() will return 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(); / / Can be used
$conn->Close(); // Can be used
?>
$rs->fields[] array is generated by the database extension function of PHP. Some extension functions do not support the use of fields. The name is used as an index.
If you want to use the name as an index, which is commonly known as hash or associative arrays, you need to use the global variable $ADODB_FETCH_MODE to specify it.
The following settings: Use 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:
//Introduction ADODB
include('ado db /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 return an ADORecordSet record set object $rs
// Actual Above $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, display the 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(); // Not needed
$conn->Close(); // Is it necessary?
?>
10. Fetch records (using 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 return the fetched record, and you can use an array to receive it.
Attention! When using 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 GetUpdateSQL function to make 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, just use the traditional method: $sql = "DELETE FROM t WHERE year= 18";
$rs = $conn->Execute($sql);
13. Using 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 one 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 value or bit
I: Integer
N: Number type, including: auto-increment, numeric value, floating point number, real number and integer
R : serial, auto-increment
Use case:
$f = $rs->FetchField(1);
//Print field name and field type code
print $f->name . ":" . $rs ->MetaType($f->type);
14. Simple paging (Pager)
ADODB provides a simple paging method to display records. Before use, adodb-pager.inc.php must be introduced.
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 Show 5 records on one page
$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 row number is not passed to Render(), the default value is 10 rows 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 a method to output CSV file. Toexport.inc.php must be imported before use.
include('adodb/adodb.inc.php');
//Introduce 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. Use rs2tab as follows:
print rs2tab($rs, false);
Note: false means not to display the field Name
The results are as follows:
abcde 45
yyy 20
ppp 34
mmm 13
hhh 41
rrr 65
kkk 29
miso 154
sss 89
ab c 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
uyt 58
john 28
The results can also be displayed by standard output (STDOUT). The method of using rs2tabout is as follows:
print rs2tabout($rs);
The execution results are as follows:
Figure 1. Display the results in 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) {
// Then write to CSV
rs2csvfile($rs, $fhd);
// You can also use rs2tabfile($rs, $fhd);
// Close the file
fclose($fhd);
}
The results are 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 a certain number of records (using SelectLimit)
ADODB provides an ADOConnect function SelectLimit for you to retrieve For 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 records, after the first one
// Show these 3 records
if ($rs) {
while( $ar = $rs->FetchRow()) {
print $ar ['name'] ." " . $ar['year'];
print "
n";
}
}
The above formula means: after the 1st 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! SelectLimit The writing method is exactly the opposite of MySQL syntax!

The above introduces the detailed explanation of the ADODB class in adodb PHP, including the content of adodb. I hope it will be helpful to friends who are interested in PHP tutorials.