Introduction to The ADOdb class library in PHP (1)

Preface why use the database class library? A common mistake for Beginners (or even non-beginners) is the lack of future considerations when developing applications. If one day your program needs to use other databases, do you still need to write a new program for this database version? This is not impossible, especially when the current database can be preface
Why use the database class library?
A common mistake for Beginners (or even non-beginners) is the lack of future considerations when developing applications. If one day your program needs to use other databases, do you still need to write a new program for this database version? This is not impossible, especially when the current database may not be suitable for your current needs. But when you do this, you will find that it is not as easy as you think: every DBMS has different functions. For example, the function used to connect to the database in Mysql is mysql_connect (), but MSSQL is mssql_connect (). Review all your code and modify the functions and query syntax of all database operations. this is by no means a simple task. Programs are often bound to wrong databases, so that they cannot achieve optimal performance during running. The database class library is generated in this way. It allows you to operate different databases using the same code. A well-designed database class library can perfectly change all this. It allows you to transmit data to other databases through minimal modification: no matter what database management system you want to connect to, you only need to use the same function and different parameters. For example, in ADOdb, you only need to simply change $ db = NewADOConnection ('access') to $ db = NewADOConnection ('mysql, you can easily transfer your program from the Access database to the Mysql database. Now there are a lot of such database class libraries on the network, such as Pear and PHPlib. I have already used them at work, and you may have used them. However, this article only focuses on the ADOdb that I pay special attention. This article only briefly introduces how to use it to develop your next project. In future articles, we will gradually learn more about it.
Currently, ADOdb supports databases including MySQL, PostgreSQL, Interbase, Firebird, Informix, Oracle, ms SQL 7, Foxpro, Access, ADO, Sybase, FrontBase, DB2 and generic ODBC.
Install ADOdb
Installing ADOdb is a very easy task. I believe you will not feel hard when you are smart.
First, make sure that the PHP version you are running is 4.0.4 or a new version. If not, we recommend that you upgrade it!
Download the. zip or. tgz file from the PHP Everywhere site and decompress it to the path you selected.
This path should not be in the webpage Directory (WWWTREE, translator's note: If your webpage is under/www/, then this directory should not be/www/here! Although the inclusion file of ADOdb is already used. inc. php extension, so that even in the worst configuration, the server will not put these. inc files are displayed in the browser in plaintext mode, but we do not advocate placing library function files in the web directory. Run tar-zxvf adodb350.tgz to decompress the downloaded file. in Windows, you can use your favorite decompression software, you will get a directory of adodb that contains many subdirectories.
Test your installation
Okay. let's test your installation. Add the following three lines of code to the script to test whether your installation is successful. Make sure to change the parameters in the code to your own.
Include ("$ adodb_path/adodb. inc. php"); // includes des the adodb library
$ Db = NewADOConnection ('$ database_type'); // A new connection
$ Db-> Connect ("$ host", "$ user", "$ password", "$ database_name ");
Now you have a database connection object $ db. You can also use ADONewConnection to replace NewADOConnection -- the two are different names of the same function. The connected database variable $ database_type needs to be changed to what you need according to your actual situation. You can use one of the following lists (the description section in parentheses is not used in the code ):

Access (Microsoft Access/Jet)
Ado (Generic ADO, the base for all the other ADO drivers)
Ado_access (Microsoft Access/Jet using ADO)
Ado_mssql (Microsoft SQL Server using ADO)
Db2 (DB2)
Vfp (Microsoft Visual FoxPro)
Fbsql (FrontBase)
Ibase (Interbase 6 or before)
Firebird)
Informix72 (Informix databases before Informix 7.3)
Informix)
Maxsql (MySQL with transaction support)
Mssql (Microsoft SQL Server 7)
Mssqlpo (Portable mssql driver)
Mysql (MySQL without transaction support)
Mysqlt (MySQL with transaction support, identical to maxmysql)
Oci8 (Oracle 8/9)
Oci805 (Oracle 8.0.5)
Oci8po (Oracle 8/9 portable driver)
Odbc (Generic ODBC, the base for all the other ODBC drivers)
Odbc_mssql (MSSQL via ODBC)
Odbc_oracle (Oracle via ODBC)
Oracle (Oracle 7)
Postgres (PostgreSQL)
Ipvs64 (PostgreSQL 6.4)
PostgreSQL 7, currently identical to IPVS)
Sqlanywhere (Sybase SQL Anywhere)
Sybase (Sybase)
If an error occurs in your link code, you must first check the path or connected variable. Before you blame ADOdb, make sure that you have used those variables correctly. (Many friends often spend too much time correcting these obvious mistakes .) If there is no error message for the connection, we can use ADodb in our project now.
Connect to the database through your script
Before adding the above code to your code, let's take a step back. We 'd better encapsulate the above code in our own method. This will make your program more flexible and portable. If you directly insert the above code into each file of your project, if the path of the project changes in the future, errors will easily occur. if your password changes, you may need to modify all your scripts, which will affect the original intention of using library functions. In addition, because your password information is under the WEBTREE, this will cause potential risks. I recommend placing password information in an independent include file, for example, somewhere in the ADOdb installation directory. If you want to run your program on another server, you cannot guarantee that the directory structure will be the same. therefore, make sure that the path is correct. I recommend that you use the PHP automatic inclusion function to automatically include this file.

Include ("$ adodb_path/db_values.inc.php ");
Include ("$ adodb_path/adodb. inc. php ");
$ Db = NewADOConnection ('$ database_type ');
$ Db-> Connect ("$ host", "$ user", "$ password", "employees ");

If you also want to use persistent connections, instead of creating a new connection each time (this accelerates many WEB applications, but note that some databases do not support it ). You can use PConnect to replace Connect.
The file db_values.inc.php is our database information file with the content (you need to change the variables in the following code to your own ):
$ Database_type = "mysql ";
$ Host = "localhost"; // Local database
$ User = "ian"
$ Password = "let_me_in"
?>

You can set to automatically include our configuration file in the php. ini configuration. you can modify the following lines of PHP. ini:
; Automatically add files before or after any PHP document.
Auto_prepend_file =/usr/local/build/apache/www/tool_lib/defaults. inc
Auto_append_file =


The defaults. inc file contains the value of $ adbdb_path:
$ Adodb_path = "/usr/local/build/apache/www/tool_lib /";
?>

There are other ways to implement it, but I found that this method can reduce complexity relatively during porting.

SELECT from a database
When using well-developed Library functions and functions provided by PHP, you can access the database in multiple ways. What method is used depends on your own preferences.
Here is a simple example:
$ SQL = "SELECT surname, age FROM employees ";
$ Rs = & $ db-> Execute ($ SQL );
If (! $ Rs ){
Print $ db-> ErrorMsg (); // Displays the error message if no results cocould be returned
}
Else {
While (! $ Rs-> EOF ){
Print $ rs-> fields [0]. ''. $ rs-> fields [1].'
';
// Fields [0] is surname, fields [1] is age
$ Rs-> MoveNext (); // Moves to the next row
}
}


In the preceding example, $ rs-> fields is an array containing the returned values. The array index is assigned an initial number. you can specify the index as follows:
$ SQL = "SELECT surname, age FROM employees ";
$ Db-> SetFetchMode (ADODB_FETCH_ASSOC); // Return associative array
$ Rs = & $ db-> Execute ($ SQL );
If (! $ Rs ){
Print $ db-> ErrorMsg (); // Displays the error message if no results cocould be returned
}
Else {
While (! $ Rs-> EOF ){
Print $ rs-> fields ['surname']. "". $ rs-> fields ['age']."
";
$ Rs-> MoveNext (); // Moves to the next row
} // End while
} // End else


Another optional method for browsing results is to return each record as an object. ADOdb has a FetchNextObject () function to implement this function. the pointer is automatically moved to the next record.
$ SQL = "SELECT surname, age FROM employees ";
$ Db-> SetFetchMode (ADODB_FETCH_ASSOC); // Return associative array
$ Rs = & $ db-> Execute ($ SQL );
If (! $ Rs ){
Print $ db-> ErrorMsg (); // Displays the error message if no results cocould be returned
}
// Loop through results
While ($ row = $ rs-> FetchNextObject ()){
// The field names need to be uppercase
Print $ row-> SURNAME. "". $ row-> AGE ."
";
}


Insert and update records
Basic INSERT operations are convenient and quick, and have the same syntax as SELECT.
$ SQL = "INSERT INTO employees (surname, age) values ('Clegg ', '43 ')";
If (! ($ Db-> Execute ($ SQL ))){
Print 'error inserting: '. $ db-> ErrorMsg ().'
';
}


The real advantage of a library function is that it allows you to put records into different databases using the same syntax, which is absolutely impossible in the past. There are usually two scenarios.
First, quotation marks. All quotation marks must be replaced by delimiters (that is, the key bit is on top of the Tab key). Otherwise, a syntax error occurs. However, some databases use one single quotes, while others use two single quotes. Therefore, you should use qstr () in ADOdb instead of addslashes () in PHP (). In this way, the returned value will be consistent with the database you are using.
Second, date. Many databases accept formats that are inconsistent with their date types and are not compatible. ADOdb has a DBDate () function that converts Unix timestamp or ISO (Y-m-d) format to any format to meet your database needs. See the following example:
$ Employee_surname = $ db-> qstr ("d 'Angelo ");
$ Arrival_time = $ db-> DBDate (time ());
// The above two functions also add the enclosing quotes, so, $ arrival_time, not '$ arrival_time'
$ SQL = "INSERT INTO employee_arrival (arrival_time, surname) values ($ arrival_time, $ employee_surname )";
If (! ($ Db-> Execute ($ SQL ))){
Print 'error inserting: '. $ db-> ErrorMsg ().'
';
}

You can update the database in the same way. for example:
$ SQL = "UPDATE employees SET age = '44' WHERE id = '000000 ')";
If (! ($ Db-> Execute ($ SQL ))){
Print 'error updating: '. $ db-> ErrorMsg ().'
';
}

The above are just some basic operations of Adodb-next time we will pay attention to some deeper things provided by ADOdb. If I have made your appetite wide open and you can no longer wait, I suggest you go to PHP Everywhere to check that this site is a professional website of ADOdb, which contains a lot of useful help information.
Address: http://www.databasejournal.com/features/php/article.php/2222651