adodb.recordset php adodb introduction

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 (including PostgreSQL , Informix, and Sybase drivers are contributed by the development of the free software community).
　One of the biggest advantages of using ADODB is: regardless of the back-end database, 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. This This 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, Save forum articles. 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 accesses in PHP are 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. It connects to the MySQL server on localhost. The database name is mydab, and executes a SQL select command query. The query results will be Print them 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 segmented in color, and the first segment is linked part, the second paragraph is to execute the SQL command, and the last paragraph is to display the fields. The while loop scans each column of the result, and the for loop scans 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 it is changed to point to the Oracle database. Just modify the second line of the program code to become NewADOConnection('oracle') , let's take a look at the complete program code...
Connect to 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 need to be more sophisticated. In ADODB we use an object-oriented approach to manage the complexity of multiple 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(databaseddriver). 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 instructions
$result = $db->Execute("SELECT * FROM employees");
if ($result === false) die("failed");
Transmit SQL instructions directly to the server, when successful After execution, Execute() will return a recordset object and 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 query. 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 getting data is very similar to reading data from a file, in each row We first check if we have reached the end of file (EOF). If not, we loop through the fields in each column, then move to the next row (MoveNext) and 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 with field names. To force the array to be indexed by name, use the universal $ADODB_FETCH_MODE variable.
$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 recordsets are stored and use different access modes. When the recordset is Set $ADODB_FETCH_MODE after Execute() is generated.
ADOConnection
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 a problem. , because you can use temporary records to cache to simulate backward scrolling.
$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.
rs2html($recordset) function converts the passed recordset into HTML table format. 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 topics
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 you switch to another database, it may There is no way 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 dates between different databases. Date default format.
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 ( 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, which returns the data affected by the last update or delete command The number of columns, and the $recordset->Insert_ID() function returns the last data column number automatically generated by the insert command. Please remind everyone in advance that no database provides these 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);
The content of $f0->name is 'adata ', $f0->type will be 'date', and if max_length is not known, its content will be -1.
One problem in 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 the previous 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 the Select command
ADODB has a $connection->SelectLimit($sql,$nrows,$offset) function that allows you to retrieve a partial collection of recordset, which is adopted from Microsoft products The SELECT TOP usage, and the advantages of the SELECT...LIMIT usage in PostgreSQL and MySQL, even if the original database does not provide this usage, this function simulates and provides this usage.
Cache Support
ADODB allows you to temporarily store recordset data in your file system, and perform the following functions on $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
Commercial use is encouraged
If you plan to write for commercial use PHP application software is sold, and you can also use ADODB. We publish ADODB under the GPL, which means that 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.

The above introduces the introduction of adodb.recordset php adodb, including the content of adodb.recordset. I hope it will be helpful to friends who are interested in PHP tutorials.