ADODB entry 1th/2 page _mysql

1. Foreword

ADODB is the abbreviation for the Active data Objects data base, which is a functional component of PHP access to the database. Now SFS3 system (Campus free software Exchange Network Learning System) program host Prementrual light teacher, decided to adopt this component, in order to allow more interested partners to participate in the project will be able to successfully join the ranks of development, younger brother think it is necessary to ADODB the introduction of Chinese to write out to facilitate the reference for the partners.

Although PHP is a powerful tool for building WEB systems, PHP's ability to 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 ADODB. Once the interface to the database is standardized, it is possible to hide the differences between the various databases, and it becomes very easy to convert to different databases.

Currently ADODB supports a very wide variety of databases, such as MySQL, PostgreSQL, InterBase, Informix, Oracle, MS SQL 7, Foxpro, Access, ADO, Sybase, DB2, and general ODBC (where the PostgreSQL, Informix, Sybase driver are made after the development of the free software community).

One of the greatest advantages of using ADODB is that: regardless of the backend database, the way in which the database is accessed is consistent, and the development designer does not have to learn a different set of access methods for a particular set of databases, which greatly reduces the knowledge burden of the developer, and the knowledge of the past continues to be used in the future. When you transfer a database platform, the program code does not have to make much changes.

In fact, ADODB such a development concept, is not the first, DBI than ADODB appeared earlier, it provides Perl access to the database, using a consistent API call interface. I believe that using Perl + DBI friends, and then to use ADODB, there will be a sense of déjà vu.

In addition, ADODB for the use of the ASP, should not be unfamiliar, such friends of ADODB should be very easy to accept.

Let's take a look at the simple use of ADODB:

<?php
Introduce ADODB's Inc file to call the ADODB-supplied function
Include (' adodb/adodb.inc.php ');

Select the type of database you are connecting to to create online objects,
Once an object is established, its member functions can be used to process the database.
The following $conn that object (object)
$conn = &adonewconnection (' mysql ');

To not display the debug message, False, true.
$conn->debug = false;

$conn->debug = true;

Connecting to a database
Usage: $conn->connect (' Host ', ' User ', ' password ', ' database ');
Case:
$conn->connect (' localhost ', ' piza ', ' ooo123 ', ' test ');

If you want to use a continuous connection, the upper-type can be exchanged 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 and, if $rs = = False, call $conn the member function of the 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!

Following, step-by-step for you to introduce: the use of ADODB key methods.

2. Installation

ADODB Home page: Http://php.weblogs.com/ADOdb, currently (2002/10/24) the latest version is: 2.42 version, can be downloaded to the ADODB or to Tainan County to the center of the network FTP download.

Installation of ADODB method is extremely simple, as long as the download, decompression, put in the appropriate position, you can complete! As shown below:

1. Download:

$ ncftp ftp.tnc.edu.tw

CD Sysop/adodb

Get adodb242.tgz

2. Decompression:

Let's say 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 omitted ....

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

3. Introduction of ADODB

Once the ADODB is installed, you should introduce the ADODB related files into your program before use. It doesn't matter where the ADODB directory is located, as long as you can point to the correct path file name. In general, your program code only needs to introduce adodb.inc.php.

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 a directory Somedir:

.
..
ADOdb
somedir/something.php

You must use:

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

In addition to adodb.inc.php, which contains filing, ADODB also offers a number of adodb-*.inc.php filing, which are mostly designed to drive specific uses of certain databases.

The introduction of adodb-session.php allows you to store the session in the database to maintain the application.

If the introduction of adodb-pager.inc.php, you can make it easier to do pagination display.

Introducing adodb-errorhandler.inc.php allows you to customize error handling messages.

If included in adodb-pear.inc.php, you can use the PHP4 's pear DB syntax for ADODB. At this point, it is acceptable to use the DSN connection database string setting. If $dsn = "Mysql://piza:ooo123@localhost/test";

If the introduction of tohtml.inc.php, can help you in the program code, to facilitate the removal of records, into the HTML table (table) to display.

Introducing toexport.inc.php allows you to easily output data files for CSV files or tab-delimited fields.

The introduction of rsfilter.inc.php allows you to filter before using records.

The introduction of pivottable.inc.php allows you to use the Pivot table function (commonly known as cross-tabulations).

Attention! Adodb.inc.php must be introduced, others, depending on the function you want to use, and then introduce the containing filing can be.

4. Choose database type, set up online object

Since ADODB uses object-oriented practices, after you introduce a file, follow the type of your backend database to create an online object. Practice is as follows:

Take the MySQL database for example:

$conn = &adonewconnection (' mysql ');

Note: Newadoconnection and adonewconnection are the same, both can be used.

The "MySQL" in the example above refers to the type of drvier of the database, ADODB will call the corresponding database driver accordingly.

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 object $conn a ADOConnection object, which represents the connection to the database, and is handled through this object. The ADOConnection object provides a number of methods of processing, object-oriented, which are called member functions, which are interfaces that the outside world accesses to this object.

Once the online objects are established, there are a number of object functions you can use! See the next section for an introduction.

5. Debug mode

The process of program development, in order to facilitate the detection of problems where possible, usually we will open the debug mode, once the program function is really stable, then turn it off. ADODB provides debug mode, which shows how it works when it accesses a database.

Open debug mode, using the method:

$conn->debug=true

To turn off debug mode, use the method:

$conn->debug=false

6. Connecting to the database

Next, connect a specific database using a Connect or Pconnect function $conn online object, at which point the data Source Names must be supplied, and 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, the above four are to be provided.

The function returns TRUE or FALSE to indicate whether the connection succeeded.

Case:

Format: $conn->connect (' Host ', ' User ', ' password ', ' database ');

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

Alternatively, a continuous connection is adopted:

Format: $conn->pconnect (' Host ', ' User ', ' password ', ' database ');

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

To discover if there is an online success, you can use a variable to access the return value:

$MCH = "localhost";
$user = "Piza";
$pwd = "ooo123";
$database = "Test";

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

Alternatively, a continuous connection is adopted:

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

if (! $cok) {Echo cannot connect to the database $database; exit;}

7. Set SQL command syntax, execute SQL command

Next, you can design the SQL command syntax that you want to execute, and then put it into execution.

$sql = "Put SQL command syntax here";

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

Where the $rs is the result of the return, and if $rs = = False, the execution fails, and you must examine it carefully.

You do not have to put the command syntax in the $sql variable, or you can put it directly into the Execute () bracket. If the command is short anyway, if the command is longer, I suggest you use a variable $sql to set the command string bar!

The following section begins by introducing the basic commands for SQL, such as Insert, Select, Update, Delete, and so on.

8. Inserting records (insert)

Insert uses the following:

$name is a string, $year is a number
$name = ' ABCDE ';
$year = 18;

Insert a record, the case of the command is OK, but the data table T and 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 results for error handling, and if normal, continue with other actions ....
if (! $rs) print $conn->errormsg ();

.... The following omitted ....

ErrorMsg () is an error-displayed function that takes out the error message and displays it.

In addition, ADODB provides a recordset (Recordset) function Getinsertsql () that can help you generate the syntax for inserts.

Examples are as follows:

<?php

Introduction of ADODB
Include (' adodb/adodb.inc.php ');

Create an Online object
$conn = &adonewconnection (' mysql ');

Error detection
$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 Records (Select)

The use of Select is as follows:

<?php

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 a Select from table T to fetch data.
It returns a Adorecordset Recordset object $rs (Recordset)
In fact $rs is a cursor indicator, which has the current record (row or records),
The contents of all field data for the record, stored in the fields array
, with numbers as the index, the first starting from 0
$rs = & $conn->execute (' select * from T ');

If $rs false, the error message is shown
if (! $rs) {
Print $conn->errormsg ();
} else {

When the end of the recordset $rs (Eof:end of File) has not yet been reached (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 Next record
$rs->movenext ();

Change columns
echo "<br>\n";
}
}

$rs->close (); You don't have to.
$conn->close (); You don't have to.
?>

$rs->fields[] Arrays are generated by the PHP database extensions feature, and some extensions do not support using field names as indexes.

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.

The following settings: Use digital index $ADODB _fetch_mode= adodb_fetch_num;

The following settings: Use the name index $ADODB _fetch_mode= Adodb_fetch_assoc;

The following is an example of using a name index:

<?php

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);

Specify the use of the name index before executing SQL
$ADODB _fetch_mode = ADODB_FETCH_ASSOC;

Executes a Select, which returns a Adorecordset Recordset object $rs
In fact $rs is a cursor metric that has the current record content,
This record is stored in the fields array.
$rs = & $conn->execute (' select * from T ');

If $rs 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 the fields

Print $rs->fields[' name ']. " " . $rs->fields[' year '];

Move to Next record
$rs->movenext ();

Change columns
echo "<br>\n";
}
}

$rs->close (); You don't have to.
$conn->close (); You don't have to.
?>

10. Take Out records (use 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 "<br>\n";
}

}

Fetchrow () returns the retrieved records, which you can use as an array.

Attention! Using Fetchrow () eliminates the need to use MoveNext (), which automatically completes the move to the next record within Fetchrow.

Current 1/2 page 12 Next read the full text