ADODB class using _php tips

Examples of MySQL
The most common database in PHP is MySQL, so I think you'll like the following program code, which links to the localhost MySQL server, the database name is Mydab, and executes an SQL Select command query, the query results will be printed out in a column.

$db = mysql_connect ("localhost", "root", "password");
mysql_select_db ("MyDB", $db);
$result = mysql_query ("SELECT * FROM Employees", $DB);
if ($result = = False) Die ("failed");
while ($fields = Mysql_fetch_row ($result)) {
For ($i =0, $max =sizeof ($fields); $i < $max; $i + +) {
Print $fields [$i]. ' ';
}
print "<br>n";
}

The above program code uses color to mark the segment, the first paragraph is the connected part, the second paragraph executes the SQL instruction, the last section is the Display field, while the loop scans each column of the result, and the For loop scans the fields for each column.

The next step is to get the same result with the ADODB program code:

include ("adodb.inc.php");
$db = newadoconnection (' mysql ');
$db->connect ("localhost", "root", "password", "MyDB");
$result = $db->execute ("SELECT * FROM Employees");
if ($result = = False) Die ("failed");
while (! $result->eof) {
For ($i =0, $max = $result->fieldcount (); $i < $max; $i + +)
Print $result->fields[$i]. ' ';
$result->movenext ();
print "<br>n";
}

Now to point to the Oracle database, the program code as long as the second line to become newadoconnection (' Oracle '), let's look at the complete program code ...

Connecting to the database

Include ("adodb.inc.php");
$db = newadoconnection (' mysql ');
$db->connect ("localhost", "root", "password", "MyDB");

The linked program code is a bit more sophisticated than the original MySQL program code, because we need to be more sophisticated. In ADODB we use object-oriented methods to manage the complexity of multiple databases, we use different classes (class) to control different databases. If you are unfamiliar with object oriented programming, don't worry! All the complex things are hidden behind the newadoconnection () function.

To save memory, we only load the PHP code associated with the database you're connected to, and we do this by calling Newadoconnection (Databasedriver), the legitimate database driver contains mysql,mssql,oracle, Oci8,postgres,sybase,vfp,access,ibase and many other drivers.

We then generate a new object entity from the link category by calling Newadoconnection (), and finally we use the $db->connect () to link the database.

Execute SQL directives

$result = $db->execute ("SELECT * FROM Employees");
if ($result = = False) Die ("failed");

Direct transfer of SQL instructions to the server, and when executed successfully, execute () returns a Recordset object, which you can check $result as listed above.

One easy question for beginners is that there are two types of objects, linked objects, and Recordset objects in ADODB, when do we use these objects?

The linked object ($DB) is responsible for linking the database and formatting your SQL queries. The Recordset object ($result) is responsible for retrieving the results and normalizing the response data into words or arrays.

The only thing I need to add is that ADODB provides a number of useful functions to make the insert and update instructions easier, as we mentioned in the Advanced section.

Retrieving data

while (! $result->eof) {
For ($i =0, $max = $result->fieldcount (); $i < $max; $i + +)
Print $result->fields[$i]. ' ';
$result->movenext ();
print "<br>n";
}

The previous example of getting the data is much like reading data from a file, we first check to see if the end of the file (EOF), if not to the end, loop through the fields in each column, then move to the next line (MoveNext) and repeat the same thing.

$result->fields[] Arrays are generated by the PHP database extension system, and some database extensions do not establish an index of the array with the field name, forcing the array to be indexed by name, using $adodb_fetch_mode's universal variables.

$ADODB _fetch_mode = Adodb_fetch_num;
$rs 1 = $db->execute (' SELECT * from table ');
$ADODB _fetch_mode = ADODB_FETCH_ASSOC;
$rs 2 = $db->execute (' SELECT * from table ');
Print_r ($rs 1->fields); Shows array ([0]=> ' V0 ', [1] => ' v1 ')
Print_r ($rs 2->fields); Shows array ([' col1 ']=> ' v0 ', [' col2 '] => ' v1 ')

As you can see in the above example, two recordsets store and use different usage patterns, and then set $adodb_fetch_mode when the recordset is generated by execute ().

ADOConnection

Objects linked to the database execute SQL instructions and have a set of tool functions to standard format SQL instructions, such as association and date format.

Other useful functions

$recordset->move ($pos) scrolls the current data column, ADODB supports the entire database to scroll forward, and some databases do not support scrolling back, which is not a problem, because you can use the staging record to the quick fetch to simulate backward scrolling.

$recordset->recordcount () returns the number of record pens accessed by the SQL instruction, and some databases are returned-1 because they are not supported.

$recordset->getarray () returns the result as an array.

The rs2html ($recordset) function converts the incoming recordset into an HTML table format. The following example shows the related usage in bold text:

Include (' adodb.inc.php ');
include (' tohtml.inc.php '); /* Includes the rs2html function * *
$conn = &adonewconnection (' mysql ');
$conn->pconnect (' localhost ', ' userid ', ' Password ', ' database ');
$rs = $conn->execute (' SELECT * from table ');
rs2html ($rs); /* Recordset to HTML table/*

There are many other useful functions listed in the file, which can be found at the following Web site http://php.weblogs.com/adodb_manual

Advanced Topics

New and updated

Suppose you want to add the following data to the database.

ID = 3
thedate=mktime (0,0,0,8,31,2001)/* 31st August 2001 * *
note = sugar Why don ' t we call it off

When you switch to another database, you may not be able to add data.

The first problem is that each database has a different default date format, MySQL uses the YYYY-MM-DD format, while the other databases have different default formats, ADODB provides the dbdate () function to convert the date default format between different databases.

The second problem is the notation for single quotes (don ' t) , where you can use single quotes (don ' t) directly in MySQL, but in other databases such as Sybase, Access, Microsoft SQL Server, Two single quotes (don ' t), theqstr () function resolves the problem.

How do we use these functions? Just like this:

$sql = "INSERT into table (ID, Thedate,note) VALUES ("
. $ID . ','
. $db->dbdate ($thedate). ', '
. $db->qstr ($note) (")";
$db->execute ($sql);

ADODB also has the $connection->affected_rows () function, which returns the number of data columns affected by the last update or delete instruction, and the $recordset->insert_id () function, Returns the data column number that was eventually automatically generated by the insert instruction, reminding everyone that no database has provided these two functions.

Metatypes

You can get more information about the field, and pass the recordset's method Fetchfield ($fieldoffset) back to the object's 3 properties: Name,type,max_length.

An example is provided:

$recordset = $conn->execute ("Select adate from table");
$f 0 = $recordset->fetchfield (0);

The result $f0->name the content is ' Adata ', $f 0->type will be ' date ', if max_length do not know, its content will be 1.

One problem with different databases is that each database has different names for the same data type, such as the timestamp type is called DateTime in a database, and the other is called time, so ADODB provides Metatype ($type, $max _length) function to standardize the following data patterns:

C:character and varchar types
X:text or long character (eg. more than 255 bytes wide).
B:blob or binary image
D:date
T:timestamp
L:logical (Boolean)
I:integer
N:numeric (float, double, money)

In the previous example,

$recordset = $conn->execute ("Select adate from table");
$f 0 = $recordset->fetchfield (0);
$type = $recordset->metatype ($f 0->type, $f 0->max_length);
Print $type; /* Should print ' D ' *

Limit and top support for the SELECT command

ADODB has a $connection->selectlimit ($sql, $nrows, $offset) function that lets you retrieve a subset of the recordset, using the Select top usage in Microsoft products, and PostgreSQL and MySQL in the Select ... The advantage of limit usage is that even if the original database does not provide this usage, this function also simulates the way it is provided.

Get backup.

ADODB allows you to temporarily hold the recordset's data in your file system and $connection->cacheexecute ($secs 2cache, $sql) and $connection-> Cacheselectlimit ($secs 2cache, $sql, $nrows, $offset), and so on when the time interval arrives, really do the database query to save time.

PHP4 Session Support

ADODB also support PHP4 session handler, you can store your session variables in the database, related functions Please refer to http://php.weblogs.com/adodb-sessions

Encourage business use

If you are planning to write a commercial PHP application to sell, you can also use ADODB, and we publish the ADODB under the GPL, which means you can legitimately refer to the commercial application and retain the ownership of your program code. It is strongly encouraged to ADODB commercial applications and is being used for this reason within ourselves.