Although PHP is a powerful tool for building web systems, the functionality of PHP access databases has not been standardized, and each database uses another different and incompatible application program interface (API). In order to fill this shortcoming, ADODB appears. Once the interface to access the database is standardized, the differences between various databases can be hidden, and it will be very easy to convert to other different databases.
Currently, the latest version of ADODB is V4.62, and there are many types of databases supported, such as: MySQL, PostgreSQL, Interbase, Informix, Oracle, MS SQL 7, Foxpro, Access, ADO, Sybase, DB2 and general ODBC (the drivers of 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 backend database is, the way of accessing the database is the same. Developers do not have to learn another set of access methods for a certain set of databases, which greatly reduces the knowledge burden of developers. Past knowledge can still be used in the future. When transferring the database platform, the program code does not have to be changed too much.
In fact, the development concept of ADODB is not the first. DBI appeared earlier than ADODB. It provides Perl to use a consistent API call interface when accessing databases. I believe that friends who have used Perl + DBI will feel familiar when using ADODB again.
In addition, ADODB should be familiar to people who have used ASP. This type of friend should be easy to accept ADODB.
Adodb official:/
PHP can use the least energy and the most fun to build a dynamic website. To build a dynamic website, we need to use a database to capture login account information, publish dynamic news, and store articles in discussion areas. Just like using the most general MySQL data, your company has done such a magical job to make your website more famous than you can imagine. Then you also found that MySQL cannot cope with the actual workload, and it is time to replace the database system.
Unfortunately, access to all databases in PHP is somewhat subtle. Connecting with MySQL You want to use mysql_connect(). When you decide to upgrade to Oracle or Microsoft SQL Server, you must use ocilogon() or mssql_connect() instead. What's worse is that the parameters used by different links are also different. Some databases say po-tato (the pronunciation of potato), while other databases say potato-to (the other pronunciation of potato). Oh... Oh my God.
We Don't Give Up
When you need to ensure the portability of your program, a database packet link library called ADODB has appeared. It provides a common application program interface to communicate with all supported databases, so you don't have to give up!
ADODB is the abbreviation of Active Data Object DataBase (sorry! Sometimes people who play computers are not very original). ADODB currently supports MySQL, PostgreSQL, Oracle, Interbase, Microsoft SQL Server, Access, FoxPro, Sybase, ODBC and ADO. You can download ADODB from /adodb.
MySQL example
The most common database in PHP is MySQL, so I think you will like the program code below, which is linked to the localhost MySQL server, the database name is mydab, and a SQL select instruction query is executed, and the query results will be printed in columns.
$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 program code above lists is marked with color. The first section is the connected part, the second section is to execute SQL instructions, and the last section is to display fields, while loop scans each column of the result, and for loop scans to the fields of each column.
Next, use ADODB program code to get the same result:
include("");
$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 change to point to Oracle database. Just modify the second line to become NewADOConnection('oracle'), let's take a look at the complete program code...
Connect to the database
include("");
$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 diverse databases, and we use different classes to control different databases. If you are not familiar with object-oriented programming, don't worry! All the complex things are hidden behind the NewADOConnection() function.
In order to save memory, we only load PHP program code related to the database you are connecting to. We do this by calling NewADOConnection(databasedriver). The legitimate 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 link category 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");
Directly transfer SQL instructions to the server. After successful execution, Execute() will be passed back to a recordset object. You can check $result as listed above.
A topic that beginners are easy to confuse is that there are two types of objects in ADODB, link objects and recordset objects. When should we use these objects?
The link object ($db) is responsible for connecting to the database and formatting your SQL queries. The recordset object ($result) is responsible for picking up the results and normalizing the response data into text or arrays.
The only thing I need to add is that ADODB provides many useful functions to make INSERT and UPDATE instructions easier, which we will mention in the advanced chapter.
Retrieve information
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 data is very similar to reading data from an archive. In each row, we first check whether it has reached the end of the archive (EOF). If it has not reached the end, loop over 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 establish the index of the array by field names. To force the index of the array by name, use the common variable of $ADODB_FETCH_MODE.
$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 above example, two recordsets store and use different access modes. When the recordset is generated by Execute(), $ADODB_FETCH_MODE is set.
ADOConnection
Connect to the database object, execute SQL instructions and have a set of tool functions to standardize SQL instructions, such as association and date formatting.
Other useful functions
$recordset->Move($pos) swipes the current data column. ADODB supports forward scrolling of the entire database. Some databases do not support backward scrolling, which will not be a problem, because you can simulate backward scrolling using temporary records to cache.
$recordset->RecordCount() returns the number of records obtained by SQL commands, and some databases will pass back -1 because they are not supported.
$recordset->GetArray() returns the result as an array.
The rs2html($recordset) function converts the passed recordset into HTML table format. The relevant usages are displayed in bold in the following example:
include('');
include(''); /* 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. You can find /adodb_manual from the following URL.
Advanced subject matter
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 new data.
The first problem is that each database has a different built-in date format. MySQL uses the YYYY-MM-DD format, while other databases have different built-in formats. ADODB provides the DBDate() function to convert the date-in-index format between different databases.
The second problem is the notation 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, they are represented by two single quotes (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 passes back the number of data columns affected by the last update or delete instruction, and the $recordset->Insert_ID() function, which passes back the last data column number automatically generated by the insert instruction. It reminds everyone in advance that no database provides these two functions.
MetaTypes
You can get more information about fields, and pass back the object's three properties through the recordset method FetchField($fieldoffset): name, type, max_length.
Give an example:
$recordset = $conn->Execute("select adate from table");
$f0 = $recordset->FetchField(0);
The result is that the content of $f0->name is 'adata', $f0->type will be 'date', if max_length is not known, its content will be -1.
One problem with different databases is that each database will have different names for the same data type. For example, the timestamp type is called datetime in a certain database, and the other database is called time. Therefore, ADODB provides the 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 Select Directives
ADODB has a $connection->SelectLimit($sql,$nrows,$offset) function that allows you to retrieve part of the collection of recordsets. This is the advantage of using the SELECT TOP usage in Microsoft products, as well as the SELECT...LIMIT usage in PostgreSQL and MySQL. Even if the original database did not provide this usage, this function simulates the usage method.
Cache support
ADODB allows you to temporarily store recordset data in your archive system, and only after the set time intervals such as $connection->CacheExecute($secs2cache,$sql) and $connection->CacheSelectLimit($secs2cache,$sql,$nrows,$offset) are reached, you can actually do database query 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 /adodb-sessions.
Encourage commercial use
If you plan to write commercial PHP application software to sell, you can also use ADODB. We publish ADODB based on GPL, which means you can legally reference it in commercial application software and retain ownership of your program code. We are strongly encouraged to commercial applications of ADODB, and we are using them internally for this reason.
Currently, the latest version of ADODB is V4.62, and there are many types of databases supported, such as: MySQL, PostgreSQL, Interbase, Informix, Oracle, MS SQL 7, Foxpro, Access, ADO, Sybase, DB2 and general ODBC (the drivers of 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 backend database is, the way of accessing the database is the same. Developers do not have to learn another set of access methods for a certain set of databases, which greatly reduces the knowledge burden of developers. Past knowledge can still be used in the future. When transferring the database platform, the program code does not have to be changed too much.
In fact, the development concept of ADODB is not the first. DBI appeared earlier than ADODB. It provides Perl to use a consistent API call interface when accessing databases. I believe that friends who have used Perl + DBI will feel familiar when using ADODB again.
In addition, ADODB should be familiar to people who have used ASP. This type of friend should be easy to accept ADODB.
Adodb official:/
PHP can use the least energy and the most fun to build a dynamic website. To build a dynamic website, we need to use a database to capture login account information, publish dynamic news, and store articles in discussion areas. Just like using the most general MySQL data, your company has done such a magical job to make your website more famous than you can imagine. Then you also found that MySQL cannot cope with the actual workload, and it is time to replace the database system.
Unfortunately, access to all databases in PHP is somewhat subtle. Connecting with MySQL You want to use mysql_connect(). When you decide to upgrade to Oracle or Microsoft SQL Server, you must use ocilogon() or mssql_connect() instead. What's worse is that the parameters used by different links are also different. Some databases say po-tato (the pronunciation of potato), while other databases say potato-to (the other pronunciation of potato). Oh... Oh my God.
We Don't Give Up
When you need to ensure the portability of your program, a database packet link library called ADODB has appeared. It provides a common application program interface to communicate with all supported databases, so you don't have to give up!
ADODB is the abbreviation of Active Data Object DataBase (sorry! Sometimes people who play computers are not very original). ADODB currently supports MySQL, PostgreSQL, Oracle, Interbase, Microsoft SQL Server, Access, FoxPro, Sybase, ODBC and ADO. You can download ADODB from /adodb.
MySQL example
The most common database in PHP is MySQL, so I think you will like the program code below, which is linked to the localhost MySQL server, the database name is mydab, and a SQL select instruction query is executed, and the query results will be printed in columns.
$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 program code above lists is marked with color. The first section is the connected part, the second section is to execute SQL instructions, and the last section is to display fields, while loop scans each column of the result, and for loop scans to the fields of each column.
Next, use ADODB program code to get the same result:
include("");
$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 change to point to Oracle database. Just modify the second line to become NewADOConnection('oracle'), let's take a look at the complete program code...
Connect to the database
include("");
$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 diverse databases, and we use different classes to control different databases. If you are not familiar with object-oriented programming, don't worry! All the complex things are hidden behind the NewADOConnection() function.
In order to save memory, we only load PHP program code related to the database you are connecting to. We do this by calling NewADOConnection(databasedriver). The legitimate 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 link category 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");
Directly transfer SQL instructions to the server. After successful execution, Execute() will be passed back to a recordset object. You can check $result as listed above.
A topic that beginners are easy to confuse is that there are two types of objects in ADODB, link objects and recordset objects. When should we use these objects?
The link object ($db) is responsible for connecting to the database and formatting your SQL queries. The recordset object ($result) is responsible for picking up the results and normalizing the response data into text or arrays.
The only thing I need to add is that ADODB provides many useful functions to make INSERT and UPDATE instructions easier, which we will mention in the advanced chapter.
Retrieve information
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 data is very similar to reading data from an archive. In each row, we first check whether it has reached the end of the archive (EOF). If it has not reached the end, loop over 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 establish the index of the array by field names. To force the index of the array by name, use the common variable of $ADODB_FETCH_MODE.
$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 above example, two recordsets store and use different access modes. When the recordset is generated by Execute(), $ADODB_FETCH_MODE is set.
ADOConnection
Connect to the database object, execute SQL instructions and have a set of tool functions to standardize SQL instructions, such as association and date formatting.
Other useful functions
$recordset->Move($pos) swipes the current data column. ADODB supports forward scrolling of the entire database. Some databases do not support backward scrolling, which will not be a problem, because you can simulate backward scrolling using temporary records to cache.
$recordset->RecordCount() returns the number of records obtained by SQL commands, and some databases will pass back -1 because they are not supported.
$recordset->GetArray() returns the result as an array.
The rs2html($recordset) function converts the passed recordset into HTML table format. The relevant usages are displayed in bold in the following example:
include('');
include(''); /* 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. You can find /adodb_manual from the following URL.
Advanced subject matter
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 new data.
The first problem is that each database has a different built-in date format. MySQL uses the YYYY-MM-DD format, while other databases have different built-in formats. ADODB provides the DBDate() function to convert the date-in-index format between different databases.
The second problem is the notation 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, they are represented by two single quotes (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 passes back the number of data columns affected by the last update or delete instruction, and the $recordset->Insert_ID() function, which passes back the last data column number automatically generated by the insert instruction. It reminds everyone in advance that no database provides these two functions.
MetaTypes
You can get more information about fields, and pass back the object's three properties through the recordset method FetchField($fieldoffset): name, type, max_length.
Give an example:
$recordset = $conn->Execute("select adate from table");
$f0 = $recordset->FetchField(0);
The result is that the content of $f0->name is 'adata', $f0->type will be 'date', if max_length is not known, its content will be -1.
One problem with different databases is that each database will have different names for the same data type. For example, the timestamp type is called datetime in a certain database, and the other database is called time. Therefore, ADODB provides the 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 Select Directives
ADODB has a $connection->SelectLimit($sql,$nrows,$offset) function that allows you to retrieve part of the collection of recordsets. This is the advantage of using the SELECT TOP usage in Microsoft products, as well as the SELECT...LIMIT usage in PostgreSQL and MySQL. Even if the original database did not provide this usage, this function simulates the usage method.
Cache support
ADODB allows you to temporarily store recordset data in your archive system, and only after the set time intervals such as $connection->CacheExecute($secs2cache,$sql) and $connection->CacheSelectLimit($secs2cache,$sql,$nrows,$offset) are reached, you can actually do database query 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 /adodb-sessions.
Encourage commercial use
If you plan to write commercial PHP application software to sell, you can also use ADODB. We publish ADODB based on GPL, which means you can legally reference it in commercial application software and retain ownership of your program code. We are strongly encouraged to commercial applications of ADODB, and we are using them internally for this reason.