Although PHP is a powerful tool for building WEB systems, the functionality of PHP Access databases has not been standardized, and each database uses a different and incompatible application interface (API). In order to fill this shortcoming, there is only ADODB. Once the interface for accessing the database is standardized, it is possible to hide the differences of various databases, which can be very easy to convert to different databases.
Currently ADODB supports a wide variety of databases, such as MySQL, PostgreSQL, Interbase, Informix, Oracle, MS SQL 7, Foxpro, Access, ADO, Sybase, DB2, and general ODBC (where PostgreSQL, Informix, Sybase's driver are contributed by the development of the free software community).
One of the greatest advantages of using ADODB is that regardless of the backend database, access to the database is consistent, and the development designer does not need to learn a different set of access methods for a particular set of databases, which greatly reduces the developer's knowledge burden, the knowledge of the past can continue to use, The program code does not have to change too much when transferring the database platform.
In fact, ADODB such a development concept, is not the first, DBI appears earlier than ADODB, it provides Perl access to the database, using a consistent API call interface. I believe that using Perl + DBI friends, and then use ADODB, there will be a sense of déjà vu.
In addition, ADODB for the use of the ASP, should not be unfamiliar, this kind of friend to ADODB should be very easy to accept.
Let's take a look at the simple usage of ADODB:
By Introducing ADODB Inc, you can call the functions provided by ADODB
Include (' adodb/adodb.inc.php ');
Select the type of database you are connected to to create an online object,
Once an object is established, its member functions can be used to process the database.
The following $conn is the object (object)
$conn = &adonewconnection (' mysql ');
To display the debug message, False No, true to.
$conn->debug = false;
$conn->debug = true;
Connecting to a database
Usage: $conn->connect (' Host ', ' User ', ' password ', ' database ');
Case:
$conn->connect (' localhost ', ' piza ', ' ooo123 ', ' test ');
To use a continuous connection, the pconnect can be swapped with:
$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 results are as follows:
---------------------------------------------
(MySQL): INSERT INTO T values ("ABCDE", 18)
---------------------------------------------
Ok!
If the debugging is turned off, that is $conn->debug=false, the result is as follows:
Ok!
Here are some steps to follow: The key approach to using ADODB.
2. Installation
ADODB home in: Http://php.weblogs.com/ADOdb, the current (2002/10/24) The latest version is: 2.42 version, can be downloaded to ADODB or to the Tainan County Teaching Network Center FTP download.
The method of installing ADODB is extremely simple, as long as download, unzip, put in the appropriate location, you can complete! 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 ellipsis ....
Now you have the ADODB installed in/var/www/html/adodb.
3. Introduction of ADODB
Once the ADODB is installed, the ADODB-related included files should be introduced into your program before use. It doesn't matter where the ADODB directory is located, as long as it points to the correct path file name. Generally speaking, your program code only needs to introduce adodb.inc.php.
The practice is as follows:
In your PHP program:
Include (' Path/adodb/adodb.inc.php ');
Or
Include_once (' Path/adodb/adodb.inc.php ');
Cases:
If your program and ADODB are in the same directory:
.
..
adodb/
something.php*
The
Include (' adodb/adodb.inc.php ');
Can.
If the location is in one of the directory Somedir:
.
..
ADOdb
somedir/something.php
You must use:
Include ('.. /adodb/adodb.inc.php ');
In addition to adodb.inc.php, which contains was filed, ADODB also offers many adodb-*.inc.php with was filed, most of which are designed to drive specific uses of certain databases.
The introduction of adodb-session.php allows you to save the session to a database to maintain the application.
If the introduction of adodb-pager.inc.php, it is convenient for you to do pagination display.
Introducing adodb-errorhandler.inc.php allows you to customize the error handling message.
If you include adodb-pear.inc.php, you can use ADODB with the Pear DB syntax of PHP4. At this point, it is fair to use the DSN connection database string setting. such as $dsn = "Mysql://piza:ooo123@localhost/test";
If you introduce tohtml.inc.php, you can display it in your program code, to make it easy to turn the recorded records into an HTML form (table).
If you introduce toexport.inc.php, you can easily output a CSV file or a data file in a tab-delimited field.
Introducing rsfilter.inc.php allows you to pre-filter the records before using them.
Introducing pivottable.inc.php allows you to use the Pivot table function (commonly known as cross-tabulations).
Attention! Adodb.inc.php is a must to introduce, and others, depending on the function you want to use, and then introduce the was filed can be.
4. Select the type of database to set up an online object
Since ADODB uses object-oriented approach, you create an online object after you introduce the file, depending on the kind of backend database you have. The practice is as follows:
Take MySQL database as an example:
$conn = &adonewconnection (' mysql ');
Note: Newadoconnection and adonewconnection are the same, both can be used.
In the above example, ' MySQL ' refers to the type of database Drvier, ADODB will call the corresponding database driver.
Other commonly used driver are: Access, ADO, Ado_access, Ado_mssql, DB2, VFP, IBase, Borland_ibase, Informix, imformix72, MSSQL, OCI8, ODBC, Postgres, Postgres64, Postgres7, Sqlanywhere, Sybase .... Wait a minute.
We call the established object $conn a ADOConnection object, which represents the connection transaction to the database and is handled through this object. The ADOConnection object provides a number of methods of processing, object-oriented, called member functions, which are interfaces to the outside world to access this object.
Once the online object is established, there are many object functions available to you! Take a look at the introduction of the next section.
5. Debug mode
program development process, in order to facilitate the detection of possible problems where the problem, usually we will open debugging mode, once the program function is really stable, then shut it down. ADODB provides debug mode, and when accessing a database, it can show how it works.
To turn on debug mode, use the method:
$conn->debug=true
To turn off debug mode, use the method:
$conn->debug=false
6. Connect to the database
Next, use the Connect or Pconnect function of the $conn online object to connect to a specific database, and you must provide data Source Names, DSN may include: Host name, database consumer, database password, database name. Different database types, DSN may omit several of these items. In the case of MySQL, all four of them are provided.
The function returns TRUE or FALSE to indicate whether the connection was successful.
Case:
Format: $conn->connect (' Host ', ' User ', ' password ', ' database ');
$conn->connect (' localhost ', ' piza ', ' ooo123 ', ' test ');
Or, take a continuous connection:
Format: $conn->pconnect (' Host ', ' User ', ' password ', ' database ');
$conn->pconnect (' localhost ', ' piza ', ' ooo123 ', ' test ');
To probe for online success, you can use a variable to get back the value:
$MCH = "localhost";
$user = "Piza";
$pwd = "ooo123";
$database = "Test";
$cok = $conn->connect ($mch, $user, $pwd, $database);
Or, take a continuous connection:
$cok = $conn->pconnect ($mch, $user, $pwd, $database);
if (! $cok) {echo "Cannot connect to database $database"; exit;}
7. Set SQL command syntax, execute SQL command
Next, you can design the syntax of the SQL command you want to execute and then put it into action.
$sql = "Place SQL command syntax here";
$rs = $conn->execute ($sql);
Where $rs is the result of the callback, if $rs = = False, it means that the execution failed and you must examine it carefully.
You don't have to put the command syntax in $sql variable, or you can put it directly into Execute () parentheses. If the command is short, if the command is long, I suggest you use a variable $sql to set the command string!
The next section begins with the basic commands for SQL, such as Insert, Select, Update, Delete, and so on.
8. Inserting records (insert)
Insert uses the following:
$name as a string, $year as a number
$name = ' ABCDE ';
$year = 18;
Insert a record, the size of the command is no harm, but the data table T and the variables are case-sensitive!
$sql = "INSERT into t VALUES (' $name ', $year)";
$sql = "INSERT into t values (' $name ', $year)"; Also.
Perform
$rs = $conn->execute ($sql);
Check execution result, error handling, if normal, continue other action ....
if (! $rs) print $conn->errormsg ();
.... The following ellipsis ....
ErrorMsg () is an incorrectly displayed function that takes out an error message and displays it.
In addition, ADODB provides a recordset (Recordset) function Getinsertsql () that can help you produce the syntax of the Insert.
Examples are as follows:
Introduction of ADODB
Include (' adodb/adodb.inc.php ');
Create an Online object
$conn = &adonewconnection (' mysql ');
Debug error
$conn->debug=true;
DSN four basic data settings
$MCH = "localhost";
$user = "root";
$pwd = "jack168";
$database = "Test";
Connect to database Test
$conn->pconnect ($MCH, $user, $pwd, $database);
Generate a blank 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 make a complete SQL command, which is placed in the $insertSQL
$insertSQL = $conn->getinsertsql ($rs, $r);
Perform an Insert
$conn->execute ($insertSQL);
$conn->close ();
?>
The debug 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)
The use of Select is as follows:
Introduction of ADODB
Include (' adodb/adodb.inc.php ');
Create an Online object
$conn = &adonewconnection (' mysql ');
No debugging
$conn->debug=false;
DSN four basic data settings
$MCH = "localhost";
$user = "Piza";
$pwd = "ooo123";
$database = "Test";
Connect to database Test
$conn->pconnect ($MCH, $user, $pwd, $database);
Perform Select to fetch data from table T,
It returns a Adorecordset Recordset object $rs (Recordset)
In fact $rs is a cursor indicator that has the current record (row or record),
The contents of all the field data of the record, stored in the fields of this array
, indexed by number, first starting from 0
$rs = & $conn->execute (' select * from T ');
If $rs is false, the error message is shown
if (! $rs) {
Print $conn->errormsg ();
} else {
When the end of the recordset $rs has not been reached (Eof:end of File), (that is, when the record has not been removed)
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 (); Can not
$conn->close (); Can not
?>
$rs->fields[] Arrays are generated by PHP's database extension functionality, and some extensions do not support using field names as indexes.
To use a name as an index, known as a hash or associative arrays, you need to use the global variable $ADODB _fetch_mode to specify.
The following settings: Using the digital index $ADODB _fetch_mode= adodb_fetch_num;
The following settings: Using the name index $ADODB _fetch_mode= Adodb_fetch_assoc;
Here's an example of using a name index:
Introduction of ADODB
Include (' adodb/adodb.inc.php ');
Create an Online object
$conn = &adonewconnection (' mysql ');
No debugging
$conn->debug=false;
DSN four basic data settings
$MCH = "localhost";
$user = "root";
$pwd = "jack168";
$database = "Test";
Connect to database Test
$conn->pconnect ($MCH, $user, $pwd, $database);
Specifies that the name index is used before executing SQL
$ADODB _fetch_mode = ADODB_FETCH_ASSOC;
Executes a Select, which returns a Adorecordset Recordset object $rs
Actually $RS is a cursor indicator, which has the current record content,
The record is stored in the fields in this array
$rs = & $conn->execute (' select * from T ');
If $rs is false, the error message is shown
if (! $rs) {
Print $conn->errormsg ();
} else {
When the recordset end position (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 not
$conn->close (); Can not
?>
10. Remove the record (using Fetchrow)
Here is a demonstration of the use 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, which you can pick up with an array.
Attention! With Fetchrow (), you do not have to use MoveNext (), and the Fetchrow interior automatically completes the move to the next record.
11. Update record (updated)
You can do it in a traditional way:
$sql = "UPDATE t SET name= ' John ', year=28 WHERE year=18";
$conn->execute ($sql);
You can also do this in the following way:
Introduction of ADODB
Include (' adodb/adodb.inc.php ');
Create an Online object
$conn = &adonewconnection (' mysql ');
Debug error
$conn->debug=true;
DSN four basic data settings
$MCH = "localhost";
$user = "Piza";
$pwd = "ooo123";
$database = "Test";
Connect to database Test
$conn->pconnect ($MCH, $user, $pwd, $database);
Select the record that you want to update
$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 make a complete SQL command, which is placed in the $updateSQL
$updateSQL = $conn->getupdatesql ($rs, $r);
Perform the update
$conn->execute ($updateSQL);
$conn->close ();
?>
The debug message is as follows:
-------------------------------------------------------------
(mysql): select * from T where year=18
-------------------------------------------------------------
(MySQL): UPDATE t SET name = ' John ', year =-WHERE year=18
-------------------------------------------------------------
12. Deleting records (delete)
Deleting records is simple, traditional way: $sql = "Delete from t WHERE year=18";
$rs = $conn->execute ($sql);
13. Using Field objects (Objects)
Here is a demonstration of the use of Field object Fetchfield to obtain field names and field patterns:
$sql = "SELECT * from T";
$rs = & $conn->execute ($sql);
if ($rs) {
while (! $rs->eof) {
Take out the second field
$f = $rs->fetchfield (1);
Print field names and field patterns
Print $f->name. ":" . $f->type;
$rs->movenext ();
Print "
\ n ";
}
}
In addition, ADODB provides a RecordSet function Metatype () that converts the original field form to a generic code:
C: Character
X:text
B:blob
D: Date
T:timestamp
L: Boolean value or Bit
I: Integer
N: Digital patterns, including: Auto increment, value, floating point, real number and integer
R:serial, automatic increase
Case:
$f = $rs->fetchfield (1);
Code to print out field names and field patterns
Print $f->name. ":" . $rs->metatype ($f->type);
14. Simple Paging (Pager)
ADODB provides a simple way to display records, before use, to introduce adodb-pager.inc.php.
Include (' adodb/adodb.inc.php ');
Introduction to Paging Functionality
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";
Generating Pager objects
$pager = new Adodb_pager ($db, $sql);
5 Records per page
$pager->render ($rows _per_page=5);
?>
The results are as follows:
Figure 1. Simple Paging function
The number of records displayed per page is controlled by render (), and the default value is 10 strokes per page if no specified row number is passed to render ().
In addition, field names can be changed, as shown in the following example:
Include (' adodb/adodb.inc.php ');
Introduction to Paging Functionality
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";
Generating Pager objects
$pager = new Adodb_pager ($db, $sql);
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 way to output a CSV file, before use, to introduce toexport.inc.php.
Include (' adodb/adodb.inc.php ');
Import output CSV file feature
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 out 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
tab-delimited fields are also available, using the Rs2tab method as follows:
Print Rs2tab ($rs, false);
Note: False indicates that field names are not displayed
The results are 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
Uyt 58
John 28
You can also display the results by standard output (STDOUT), using the Rs2tabout method as follows:
Print rs2tabout ($RS);
The results of the implementation are as follows:
Figure 1. Displaying results in the console
can also be saved as a CSV file:
File path
$path = "/tmp/test.csv";
Open for writing
$FHD = fopen ($path, "w");
If the file is successfully opened
if ($FHD) {
The CSV is written
Rs2csvfile ($rs, $FHD);
You can also use Rs2tabfile ($rs, $FHD);
Close 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. Take out a record of a certain number of strokes (using Selectlimit)
ADODB provides a adoconnect function selectlimit that allows you to remove a certain number of records, using the following:
$conn->connect ($MCH, $user, $pwd, $database);
rs = $conn->selectlimit ("SELECT * from T", 3, 1);
Remove 3 strokes, after the 1th pen
Show these 3 records.
if ($rs) {
while ($ar = $rs->fetchrow ()) {
Print $ar [' name ']. " " . $ar [' Year '];
Print "
\ n ";
}
}
The above-mentioned: After the 1th record, take out 3 pens, that is, the 2nd, 3, 4 records.
The results are as follows:
--------------------------------------
(mysql): select * FROM T LIMIT 1,3
--------------------------------------
Attention! Selectlimit's writing is just the opposite of MySQL syntax!
The above describes the ADODB PHP ADODB class details, including the ADODB aspects of the content, I hope that the PHP tutorial interested in a friend helpful.
