Introduction to ADODB Class Library in PHP (i)

Introduction to ADO
Why should I use a database class library?

The mistake that beginners (even some beginners) often make is the lack of consideration for the future when developing applications. If one day your program needs to use a different database, do you need to write a new program for this version of the database? This is not impossible, especially if the current database may not be appropriate for your current needs. But when you do all this, you'll find it's not as easy as you think: Each DBMS has a different function. For example, the function of connecting to a database in Mysql is mysql_connect (), whereas in MSSQL it is mssql_connect (). It's never a simple task to review all your code and then modify the functions and query syntax for all of the database operations. Programs are often bundled to the wrong database to achieve optimal performance at run time. This is how the database class library is created. It allows you to manipulate different databases through the same code. A well-designed database class library can be a perfect change for all of this. It allows you to transfer to other databases with minimal modifications: No matter what database management system you want to connect to, just use the same function and different parameters. For example, in ADOdb, simply modify the $DB = newadoconnection (' Access ') to $db = newadoconnection (' mysql ') so that you can easily transfer your program from the Access database to the Mysql database. Now there are a lot of such database libraries on the network, such as Pear,phplib, I have already used them in the work, perhaps you have used. But this article will focus only on the ADOdb that I am particularly concerned about. This article is a brief introduction that allows you to use it immediately to develop your next project. Later in the article we will be step-by-step to it into a more in-depth understanding.

Currently, the database supported by ADODB includes MySQL, Postgresql,interbase,firebird,informix,oracle,ms SQL 7,foxpro,access,ado,sybase,frontbase, DB2 and generic ODBC.

Installation of ADODB
Installing ADODB is an extremely easy thing to do, believe that you will not be hard to be smart.

First, make sure that the PHP you are running is a 4.0.4 or a newer version. If not, I strongly suggest you upgrade!
Download the. zip or. tgz file from the PHP everywhere site and unzip it to your chosen path.
This path should not be in the page directory (Wwwtree, translator Note: If your page is under/www/, then this directory should not be/www/here)! Although ADODB's include files already use the. inc.php extension, the server does not display these. inc files in the browser in plaintext, even in the worst configuration, but we have never advocated the behavior of placing library function files in the Web page directory. Then put the downloaded file running: TAR-ZXVF adodb350.tgz decompression, under Windows you can use one of your favorite decompression software to operate, so that you will get a ADODB directory under which many subdirectories are included.

Test your installation
OK, let's Test your installation. Test your installation for success by adding the following three lines of code to your script. Be careful to modify the parameters in your code into your own.

Include ("$adodb _path/adodb.inc.php"); Includes 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--These two are different names for the same function. Connected database variables $database _type need to change your situation to what you need. You can use one of the following lists (in parentheses, in the Description section, not in code):

Access (Microsoft Access/jet)
ADO (Generic ado, the base for all of 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 (Firebird)
informix72 (Informix databases before Informix 7.3)
Informix (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, ODBC drivers)
Odbc_mssql (MSSQL via ODBC)
Odbc_oracle (Oracle via ODBC)
Oracle (Oracle 7)
Postgres (PostgreSQL)
POSTGRES64 (PostgreSQL 6.4)
Postgres7 (PostgreSQL 7, currently identical to Postgres)
Sqlanywhere (Sybase SQL Anywhere)
Sybase (Sybase)
If your link code has the wrong hint, the first thing you need to check is on the path or the connected variable. Before you blame ADOdb, make sure you are using those variables correctly. (Many friends Changhua too much time to fix these obvious mistakes.) If the connection does not have any error prompts, then we can now use ADODB in our project.

Connect to the database through your script
Before you add the above code to your code, let's take a step back. We'd better be able to encapsulate the above code in our own way. This allows your program to become more flexible and more portable. If you insert the above code directly into every file in your project, if the path to the future project changes, it will be easy to create errors, if your password changes, you may need to modify all of your scripts, which will affect the purpose of using the library function. And, because your password information is under the Webtree, this will create a hidden danger. I recommend placing the password information in a separate include file, such as somewhere under the ADOdb installation directory. If you want to run your program on another server, you can't guarantee that the directory structure will be the same, so you want to make sure that the path is correct. I recommend using PHP's automatic inclusion feature 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 a persistent connection, not every time you create a new connection (this allows many Web applications to be accelerated, but be aware that some databases are not supported). You can use Pconnect to replace Connect.
File db_values.inc.php is our database information file (you need to change the variables in the following code to your own):

<?php
$database _type= "MySQL";
$host = "localhost"; Local Database
$user = "Ian"
$password = "Let_me_in"
?>

You can set the automatic inclusion of our profile in the php.ini configuration, and 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 file Defaults.inc contains the value of the $ADBDB _path:

?
$adodb _path = "/usr/local/build/apache/www/tool_lib/";
?>

There are other ways to implement it, but I find that this approach can reduce complexity relatively when porting.


Select (Select) action from a database
There are a number of ways to access a database when you are using both a well developed library function and a function provided by PHP itself. In what way, it depends entirely 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 could is returned
}
else {
while (! $rs->eof) {
Print $rs->fields[0]. ' '. $rs->fields[1]. ' <BR> ';
Fields[0] is surname, Fields[1] are age
$rs->movenext (); Moves to the next row
}
}


In the example above, $rs->fields is an array that contains the return value. The array index is given the initial number, and you can also specify its index in the following way:

$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 could is returned
}
else {
while (! $rs->eof) {
Print $rs->fields[' surname ']. " ". $rs->fields[' age ']." <BR> ";
$rs->movenext (); Moves to the next row
}//End While
}//End Else


Another optional way to browse the results is to return each record as an object. ADOdb has a fetchnextobject () function to implement this function, and the pointer automatically moves 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 could is returned
}
Loop through results
while ($row = $rs->fetchnextobject ()) {
The field names need to be uppercase
Print $row->surname. " ". $row->age." <BR> ";
}


Inserting, updating records
The basic INSERT operation is convenient, fast, and has the same syntax as the SELECT.

$sql = "INSERT into employees (surname, age) VALUES (' Clegg ', ' 43 ')";
if (!) ( $db->execute ($sql))) {
print ' Error inserting: '. $db->errormsg (). ' <BR> ';
}


The real advantage of library functions is that it allows you to put records into different databases through the same syntax, which was never possible before. There are usually two kinds of situations that occur.

The first type, the quotes. All quotes need to be replaced with a caret (i.e. ' symbol, top of the TAB key '), or it will cause grammatical errors. But some databases use one single quote, while others use two single quotes. So, you should use the QSTR () in ADOdb instead of the addslashes () in PHP. In this way, the return value will match the database you are using.

The second kind, the date. Many databases accept incompatible formats that are inconsistent with their date types. ADOdb has a DBDate () function that converts Unix timestamp, or ISO (y-m-d) formats into any format, to meet the needs of your database. 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 (). ' <BR> ';
}

You can update the database in exactly the same way, for example:

$sql = "UPDATE employees SET age= ' WHERE id= ' 121 ')";
if (!) ( $db->execute ($sql))) {
print ' Error updating: '. $db->errormsg (). ' <BR> ';
}

These are just some of the basics of Adodb-next time we'll be looking at some of the deeper things Adodb offers. If I have made your appetite open, and you can no longer wait, I suggest you go to PHP everywhere look, this site is ADODB professional site, there are a lot of useful help information.

The original address of this article: http://www.databasejournal.com/features/php/article.php/2222651