Introduction to php adodb_PHP tutorial-PHP Tutorial-php.cn

Although PHP is a powerful tool for building Web systems, the function of PHP accessing databases has not been standardized. Each database uses a different and incompatible application programming interface (API). In order to fill this gap, ADODB appeared. Once the interface for accessing the database is standardized, the differences between various databases can be hidden, and it will be very easy to switch to other different databases.
At present, the latest version of ADODB is V4.62, which supports many types of databases, such as: MySQL, PostgreSQL, Interbase, Informix, Oracle, MS SQL 7, Foxpro, Access, ADO, Sybase, DB2 and general ODBC ( Among them, the drivers for PostgreSQL, Informix, and Sybase were contributed by the development of the free software community).
One of the biggest advantages of using ADODB is that no matter what the back-end database is, the method of accessing the database is the same. Developers and designers do not have to learn another set of different access methods for a certain set of databases. , which greatly reduces the knowledge burden on developers. Past knowledge can still be used in the future. When the database platform is transferred, the program code does not need to be changed too much.
In fact, the development concept of ADODB is not the first. DBI appeared earlier than ADODB. It provides Perl with a consistent API call interface when accessing the database. I believe that friends who have used Perl + DBI will feel familiar when they use ADODB again.
In addition, ADODB should be familiar to those who have used ASP, and such friends should be easily able to accept ADODB.
Adodb official: http://adodb.sourceforge.net/
PHP can build dynamic websites with the least effort and the most fun. To build a dynamic website, we need to use a database to retrieve login account information, Publish dynamic news and store articles in discussion forums. Your company has done such an amazing job making your website more famous than you could have ever imagined, using the most versatile MySQL data available. Then you also discovered that MySQL could no longer cope with the actual workload, and it was time to replace the database system.
Unfortunately, all database access in PHP is slightly different. To connect to MySQL you use mysql_connect(). When you decide to upgrade to Oracle or Microsoft SQL Server, you must use ocilogon() or mssql_connect() respectively. What's worse is that the parameters used in different links are also different. Some databases say po-tato (pronunciation of potato), and other databases say pota-to (another pronunciation of potato). Oh my God. .
Let’s not give up
When you need to ensure the portability of your program, a database package link library called ADODB has appeared. It provides a common API to communicate with all supported databases, so you don't have to give up!
ADODB is the abbreviation of Active Data Object DataBase (Sorry! People who play computers are sometimes not very original). ADODB currently supports MySQL, PostgreSQL, Oracle, Interbase, Microsoft SQL Server, Access, FoxPro, Sybase, ODBC and ADO. You can download ADODB from http://php.weblogs.com/adodb.
MySQL example
The most common database in PHP is MySQL, so I think you will like the following program code, which connects to the MySQL server on localhost, the database name is mydab, and executes a SQL select command query , the query results will be printed out one by one.
$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 "
n";
}
The program code listed above is divided into segments with colors. The first segment is the link part, the second segment is to execute the SQL command, and the last segment is to display the field. The while loop scans each column of the result, and the for loop Scan to the fields of each column.
The next step is to use the ADODB program code to get the same result:
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 "
n";
}
Now change it to point to the Oracle database. Just modify the second line of the program code to become NewADOConnection('oracle'). Let us take a look at the complete program code...
Connect with the database
include(" adodb.inc.php");
$db = NewADOConnection('mysql');
$db->Connect("localhost", "root", "password", "mydb");
The linked program code is more sophisticated than the original MySQL program code, because we just need to be more sophisticated. In ADODB we use an object-oriented approach to manage the complexity of diverse databases, and we use different classes to control different databases.If you're new to object-oriented programming, don't worry! All the complexity is hidden behind the NewADOConnection() function.
In order to save memory, we only load the PHP program code related to the database you are connected to. We do this by calling NewADOConnection(databasedriver). Legal database drivers include mysql, mssql, oracle, oci8, postgres, sybase, vfp, access, ibase and many other drivers.
Then we generate a new object entity from the connection class by calling NewADOConnection(), and finally we use $db->Connect() to connect to the database.
Execute SQL command
$result = $db->Execute("SELECT * FROM employees");
if ($result === false) die("failed");
Direct Sends the SQL command to the server. When successfully executed, Execute() will return a recordset object. You can check $result as listed above.
An easily confusing issue for beginners is that there are two types of objects in ADODB, connection objects and recordset objects. When do we use these objects?
The connection object ($db) is responsible for connecting to the database and formatting your SQL queries. The recordset object ($result) is responsible for retrieving the results and normalizing the response data into text or arrays.
The only thing I would add is that ADODB provides many useful functions to make the INSERT and UPDATE instructions easier, which we will mention in the advanced chapter.
Get data
while (!$result->EOF) {
for ($i=0, $max=$result->FieldCount(); $i < $max; $ i++)
print $result->fields[$i].' ';
$result->MoveNext();
print "
n";
}
The previous example of obtaining data is very similar to reading data from a file. In each row, we first check whether the end of the file (EOF) has been reached. If it has not reached the end, we loop through the fields in each column and then move to the next row (MoveNext ) and then repeat the same thing.
The $result->fields[] array is generated by the PHP database extension system. Some database extension systems do not index the array by field name. To force the array to be indexed by name, use $ADODB_FETCH_MODE general variables.
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
$rs1 = $db->Execute('select * from table');
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$rs2 = $db->Execute( 'select * from table');
print_r($rs1->fields); // shows array([0]=>'v0',[1] =>'v1')
print_r ($rs2->fields); // shows array(['col1']=>'v0',['col2'] =>'v1')
As you can see in the above example, two Each recordset is stored and uses different access modes. When the recordset is generated by Execute(), $ADODB_FETCH_MODE is set.
ADOConnection
An object connected to the database, executes SQL commands and has a set of tool functions to standardly format SQL commands, such as correlation and date format commands.
Other useful functions
$recordset->Move($pos) scrolls the current data column. ADODB supports forward scrolling of the entire database. Some databases do not support backward scrolling. This is not the case. This won't be a problem since you can simulate backward scrolling by using temporary records to cache.
$recordset->RecordCount() returns the number of records accessed by the SQL command. Some databases will return -1 because they do not support it.
$recordset->GetArray() returns the results in the form of an array.
The rs2html($recordset) function converts the passed recordset into HTML table format. The relevant usage is shown in bold in the following example:
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 URL: http: //php.weblogs.com/adodb_manual
Advanced themes
Add and update
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 If you switch to another database, you may not be able to add new data.
The first problem is that each database has a different default date format. MySQL uses the YYYY-MM-DD format, while other databases have different default formats. ADODB provides the DBDate() function to convert between different databases. The default format for dates between.
The next problem is the representation of single quotes (don't). In MySQL, single quotes (don't) can be used directly, but in other databases such as Sybase, Access, and Microsoft SQL Server, two single quotes are used. Indicates (don''t), the qstr() function can solve this problem.
How do we use these functions? 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 , returns the number of data columns affected by the last update or delete command, and the $recordset->Insert_ID() function returns the number of data columns automatically generated by the last insert command. To remind everyone in advance, no database provides this Two functions.
MetaTypes
You can get more information about the field by returning the three attributes of the object through the recordset method FetchField($fieldoffset): name, type, max_length.
Example:
$recordset = $conn->Execute("select adate from table");
$f0 = $recordset->FetchField(0);
Result $f0- The content of >name is 'adata', $f0->type will be 'date', and if max_length is not known, its content will be -1.
One problem when dealing with different databases is that each database has different names for the same data type. For example, the timestamp type is called datetime in one database, and time in another database, so ADODB provides MetaType($type,$max_length) function to standardize the following data types:
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 front In the example,
$recordset = $conn->Execute("select adate from table");
$f0 = $recordset->FetchField(0);
$type = $recordset-> ;MetaType($f0->type, $f0->max_length);
print $type; /* should print 'D' */
Limit and Top support for Select command
ADODB has The $connection->SelectLimit($sql,$nrows,$offset) function allows you to retrieve a partial collection of recordset. This is based on the SELECT TOP usage in Microsoft products and the SELECT...LIMIT usage in PostgreSQL and MySQL. Advantage, even if the original database does not provide this usage, this function also simulates providing this usage.
Cache support
ADODB allows you to temporarily store recordset data in your file system, and perform the following functions in $connection->CacheExecute($secs2cache,$sql) and $connection->CacheSelectLimit($secs2cache, $sql, $nrows, $offset) and other set time intervals are reached before actually querying the database to save time.
PHP4 Session support
ADODB also supports PHP4 session handler. You can store your session variables in the database. For related functions, please refer to http://php.weblogs.com/adodb-sessions
Encourage commercial use
If you plan to write commercial PHP application software for sale, you can also use ADODB. We publish ADODB under the GPL, which means you can legally quote it in commercial application software and retain the ownership of your program code. Commercial use of ADODB is strongly encouraged, and we are using it internally for this reason.