10 Examples of ADODB (clear version) _php Tutorial

I want to learn pear, but on the internet to see a few posts on the evaluation of ADODB is quite high, so changed to learn this.
The advantages of ADODB have these few (on-line, not I said):
1, speed than pear one times faster;
2, the supported database type is much more than pear, can even support access;
3, no need to install, no server support (for beginners, this is very important it)
Do not know what ADODB is or want to download ADODB friends can go to this link to see: http://www.phpe.net/class/106.shtml
In addition, if any brother translated the full text of the Readme or know where there is a translation, please give me a post, thank you.
Tutorial
Example 1:select Statement
Task: Connect to an Access database named Northwind, showing the first two fields of each record.

In this example, we create a new ADOc connection (adoconnection) object and use it to connect to a database. This connection uses the Pconnect method, which is a persistent connection. When we are querying the database, we can call the Execute () function of the connection at any time. It returns a Adorecordset object which is actually a cursor, that holds, the current row in the array fields[]. We use MoveNext () to shift from one record to the next.

NB: There is a very useful function selectlimit is not used in this example, it can control the number of records displayed (such as displaying only the first 10 records, can be used as a pagination display).


PHP:--------------------------------------------------------------------------------

Include (' adodb.inc.php '); #载入ADOdb
$conn = &adonewconnection (' access '); # Create a new connection
$conn->pconnect (' Northwind '); # Connect to a ms-access database named Northwind
$recordSet = & $conn->execute (' SELECT * from products '); #从products数据表中搜索所有数据
if (! $recordSet)
Print $conn->errormsg (); If the data search error displays an error message
Else
while (! $recordSet->eof) {
Print $recordSet->fields[0]. ' '. $recordSet->fields[1]. '
';
$recordSet->movenext (); Point to Next record
}//List display data

$recordSet->close (); Options available
$conn->close (); Options available
?>
--------------------------------------------------------------------------------

$recordSet returns the current array in $recordset->fields, and the field is numerically indexed (starting at 0). We use the MoveNext () function to move to the next record. The EOF property is set to True when the database is searched to the end. If execute () has an error, the recordset returns Flase.

$recordSet->fields[] Array is generated from the database extension of PHP. Some database extensions can only be indexed numerically and not by field names. If you persist in using the field name index, you should use the Setfetchmode function. Regardless of which format index is used, the recordset can be created by execute () or Selectlimit ().


PHP:--------------------------------------------------------------------------------
$db->setfetchmode (Adodb_fetch_num);
$rs 1 = $db->execute (' SELECT * from table '); Using a digital Index
$db->setfetchmode (ADODB_FETCH_ASSOC);
$rs 2 = $db->execute (' SELECT * from table '); Use field name index
Print_r ($rs 1->fields); # shows Array ([0]=> ' V0 ', [1] = ' v1 ')
Print_r ($rs 2->fields); # shows Array ([' col1 ']=> ' v0 ', [' col2 '] = ' v1 ')-------------------------------------------------------------- ------------------


If you want to get the record number, you can use $recordset->recordcount (). Returns 1 if no current record is present.

Instance 2:advanced Select with Field Objects
Search for a table showing the first two fields. If the second field is a time or date format, it is displayed in the U.S. Standard Time format.

PHP:--------------------------------------------------------------------------------

Include (' adodb.inc.php '); Load ADODB
$conn = &adonewconnection (' access '); Create a new connection
$conn->pconnect (' Northwind '); Connect a ms-access database named Northwind
$recordSet = & $conn->execute (' Select customerid,orderdate from Orders '); Search CustomerID and OrderDate two fields from the Orders table
if (! $recordSet)
Print $conn->errormsg (); If the database search is incorrect, an error message is displayed
Else
while (! $recordSet->eof) {
$FLD = $recordSet->fetchfield (1); Assign a second field to $FLD
$type = $recordSet->metatype ($fld->type); Format of field values

if ($type = = ' D ' | | $type = = ' T ')
Print $recordSet->fields[0]. ' '.
$recordSet->userdate ($recordSet->fields[1], ' m/d/y '). '
'; If the field format is a date or a time type, make it output in US standard format
Else
Print $recordSet->fields[0]. ' '. $recordSet->fields[1]. '
'; Otherwise output as-is

$recordSet->movenext (); Point to Next record
}
$recordSet->close (); Options available
$conn->close (); Options available

?>
--------------------------------------------------------------------------------

In this example, we use the Fetchfield () function to check the format of the second field. It returns an object that contains three variables

Name: Field name
Type: The true format of the field in its database
Max_length: Field maximum length, some databases do not return this value, such as MySQL, in which case the max_length value equals-1.
We use Metatype () to convert the database format of a field into a standard field format

C: Character field, it should be available in The label is displayed.
X: Text-based field, storing larger text, generally acting on <textarea>label</textarea>
B: Blocks, large objects in binary format, slices
D: Date Type field
T: Time Type field
L: Logical Field (Boolean logic or Bit-field)
I: Integer field
N: Number field. Includes auto-numbering (AutoIncrement), numbers (numeric), floating-point numbers (floating points), real numbers (real), and integers (integer).
R: Continuous field. including serial, autoincrement integers. It works only with the specified database.
If Metatype is a date or timestamp type, we use the user-defined date format userdate () function to output, userdate () to convert the PHP SQL date string format to the user-defined format, Metatype () Another use is to confirm data validity before inserting and replacing.

Example 3:inserting
Insert a record containing date and character data into the order data table, which must be converted before inserting, eg:the single-quote in the word John ' s.

PHP:--------------------------------------------------------------------------------

Include (' adodb.inc.php '); Load ADODB
$conn = &adonewconnection (' access '); Create a new connection

$conn->pconnect (' Northwind '); Connect to an Access database Northwind
$shipto = $conn->qstr ("John ' s Old Shoppe");

$sql = "INSERT into orders (Customerid,employeeid,orderdate,shipname)";
$sql. = "VALUES (' Anatr ', 2,". $conn->dbdate (Time ()). ", $shipto)";

if ($conn->execute ($sql) = = = = False) {
print ' ERROR inserting: '. $conn->errormsg (). '
';
}//If the insert is not successful output error message
?>
--------------------------------------------------------------------------------

In this example, we see that ADODB can easily handle some of the advanced database operations. The Unix timestamp (a long integer) was converted by dbdate () to the correct access format, and the right escape character is used for quoting the John's Old Shoppe, which was John's Old Shoppe and not PHP ' s default John's Old Shoppe with Qstr ().

Observe the error handling of the execution statement. If execute () has an error, the ERRORMSG () function returns the last error prompt. Note:php_track_errors might has to is enabled for the error messages to be saved.

Example 4:debugging
Include (' adodb.inc.php '); Load ADODB
$conn = &adonewconnection (' access '); Create a new connection
$conn->pconnect (' Northwind '); Connect to an Access database Northwind
$shipto = $conn->qstr ("John ' s Old Shoppe");
$sql = "INSERT into orders (Customerid,employeeid,orderdate,shipname)";
$sql. = "VALUES (' Anatr ', 2,". $conn->formatdate (Time ()). ", $shipto)";
$conn->debug = true;
if ($conn->execute ($sql) = = = False) print ' Error inserting ';
?>

In the above example, we set debug = True. It will display all the SQL information before execution, and it will also display all error prompts. In this example, we no longer need to call ErrorMsg (). To display the recordset, you can refer to the rs2html () instance.

You can also refer to the Custom Error handlers section.

Instance 5:mysql and Menus
Connect to the MySQL database agora and generate a SQL declaration from the Drop-down menu, menuThe option is displayed as the first field and the return value is the second field.PHP:-------------------------------------------------------------------------------- Include (' adodb.inc.php '); # Load code common to ADOdb$conn = &adonewconnection (' mysql '); Eate a connection$conn->pconnect (' localhost ', ' userid ', ' ', ' Agora '); SQL database, the database is named Agora$sql = ' Select CustomerName, CustomerID from customers '; Search field name for display, ID for return value$rs = $conn->execute ($sql);Print $rs->getmenu (' getcust ', ' Mary Rosli '); Show Menu?>--------------------------------------------------------------------------------Here we define a menu called Getcust, where the ' Mary Rosli ' is selected. See GetMenu (). We also have a function to return the record value to the array: GetArray (), and as an associative array with the key being the first COLUMN:GETASSOC ().Example 6:connecting to 2 Databases at OncePHP:-------------------------------------------------------------------------------- Include (' adodb.inc.php '); # Load code common to ADOdb$conn 1 = &adonewconnection (' mysql '); # Create a MySQL connection$conn 2 = &adonewconnection (' Oracle '); # Create a Oracle connection$conn 1->pconnect ($server, $userid, $password, $database);$conn 2->pconnect (False, $ora _userid, $ora _pwd, $oraname);$conn 1->execute (' Insert ... ');$conn 2->execute (' Update ... ');?>//Simultaneous connection of two databases--------------------------------------------------------------------------------7:generating Update and Insert SQLADODB more than 1.31 versions support two new functions: Getupdatesql () and Getinsertsql (). This perform a "SELECT * from table query WHERE ...", make a copy of the $rs->fields, modify the D then generate the SQL to update or INSERT into the table automatically.Let's take a look at how these two functions work in this worksheet: (ID, FirstName, LastName, Created).Before these functions can is called, you need to initialize the recordset by performing a select on the table. Idea and code by Jonathan Younger jyounger#unilab.com.PHP:-------------------------------------------------------------------------------- #==============================================# SAMPLE Getupdatesql () and Getinsertsql () code#==============================================Include (' adodb.inc.php ');Include (' tohtml.inc.php '); Strange, this sentence seems to have all the same, which friend knows why please give an explanation#==========================# This code tests an insert$sql = "SELECT * from adoxyz WHERE id =-1"; #查找一个空记录 $conn = &adonewconnection ("MySQL"); # Create a connection$conn->debug=1;$conn->pconnect ("localhost", "admin", "" "," Test "); # Connect to MySQL, TestDB$rs = $conn->execute ($sql); # get an empty record$record = Array (); # Create an array ready to insert# Set the insertion value $record["FirstName"] = "Bob";$record ["LastName"] = "Smith";$record ["created"] = time ();# Pass The empty recordset and the array containing the data to insert# into the Getinsertsql function. The function would process the data and return# A fully formatted insert SQL statement.# the variable is formatted before inserting$insertSQL = $conn->getinsertsql ($rs, $record);$conn->execute ($insertSQL); # Inserting data into the database#==========================# The following program demonstrates modifying data, roughly the same as the previous program$sql = "SELECT * from adoxyz WHERE id = 1";# Select a record to update$rs = $conn->execute ($sql); # Execute the query and get the existing record to update$record = Array (); # Initialize An array to hold the record data to update# Set The values for the ' fields ' in the record$record ["FirstName"] = "Caroline";$record ["LastName"] = "Smith"; # Update Caroline ' s LastName from Miranda to Smith# Pass The single record Recordset and the array containing the data to update# into the Getupdatesql function. The function would process the data and return# A fully formatted update SQL statement with the correct WHERE clause.# IF The data has not changed, no Recordset is returned$updateSQL = $conn->getupdatesql ($rs, $record);$conn->execute ($updateSQL); # Update The record in the database$conn->close ();?>--------------------------------------------------------------------------------Example 8 implementing scrolling with Next and PreviousThe following demo is a very small paging-browsing program.PHP:--------------------------------------------------------------------------------Include_once ('.. /adodb.inc.php ');Include_once ('.. /adodb-pager.inc.php ');Session_Start ();$db = newadoconnection (' mysql ');$db->connect (' localhost ', ' root ', ' ', ' xphplens ');$sql = "SELECT * from ADOXYZ";$pager = new Adodb_pager ($db, $sql);$pager->render ($rows _per_page=5);-------------------------------------------------------------------------- ------The result of running the above program is as follows:|< << >> >|ID First Name Last name Date CreatedAlan Turing Sat, OCT 2001PNS Serena Williams Sat, OCT 2001Yat Sun Sun Sat, OCT 2001Wai Hun See Sat, OCT 2001Steven Oey Sat, OCT 2001Page 8/10Call the render ($rows) method to display the data in pagination. If you do not enter a value for render (), the Adodb_pager default value is 10 records per page.You can choose to display any field in SQL and define a name for it:$sql = ' SELECT ID as ' ID, FirstName as ' first Name ',LastName as "Last Name", created as "Date created" from adoxyz ';The above code can be found in adodb/tests/testpaging.php, Adodb_pager object in adodb/adodb-pager.inc.php. You can add and change the color of the Adodb_pager code, and you can display the HTML code by setting $pager->htmlspecialchars = False.Some of the code used here is contributed by Iván Oliva and Cornel G.Example 9:exporting in CSV or tab-delimited FormatWe provide some helper functions to export in Comma-separated-value (CSV) and tab-delimited formats:PHP:--------------------------------------------------------------------------------Include_once ('/path/to/adodb/toexport.inc.php '); include_once ('/path/to/adodb/adodb.inc.php ');$db = &newadoconnection (' mysql '); $db->connect ($server, $userid, $password, $database); $rs = $db->execute (' Select FName as "First Name", surname as "surname" from table ");Print "";p rint rs2csv ($RS); # return a string, CSV formatprint '; $rs->movefirst (); # Note, some databases do not support Movefirstprint Rs2tab ($rs, false); # return a string, tab-delimited# false = = Suppress field names in first Lineprint "; $rs->movefirst (); Rs2tabout ($RS); # Send to stdout directly (there are also an rs2csvout function)Print "";$rs->movefirst (); $fp = fopen ($path, "w");if ($fp) {rs2csvfile ($rs, $fp); # Write to file (there was also an rs2tabfile function)Fclose ($FP);} --------------------------------------------------------------------------------Carriage-returns or newlines is converted to spaces. Field names is returned in the first line of text. Strings containing the delimiter character is quoted with Double-quotes. Double-quotes is double-quoted again. This conforms to Excel import and export guide-lines.All the above functions take as a optional last parameter, $addtitles which defaults to true. When set to false field names in the first line is suppressed.Example 10:recordset FiltersSometimes we want to pre-process all rows in a recordsets before we use it. For example, we want the Ucwords all text in Recordset.PHP:--------------------------------------------------------------------------------Include_once (' adodb/rsfilter.inc.php ');Include_once (' adodb/adodb.inc.php ');Ucwords () every element in the recordsetFunction Do_ucwords (& $arr, $rs){foreach ($arr as $k = = $v) {$arr [$k] = Ucwords ($v);}}$db = newadoconnection (' mysql ');$db->pconnect (' Server ', ' user ', ' pwd ', ' db ');$rs = $db->execute (' Select ... from table ');$rs = RsFilter ($rs, ' do_ucwords ');------------------------------------------------------------------------------- -The RsFilter function takes 2 parameters, the recordset, and the name of the filter function. IT returns the processed recordset scrolled to the first record. The filter function takes the parameters, the current row as an array, and the Recordset object. For a future compatibility, you should not use the original Recordset object.Http://www.bkjia.com/PHPjc/317529.htmlWww.bkjia.comTrueHttp://www.bkjia.com/PHPjc/317529.htmlTecharticleI want to learn pear, but on the internet to see a few posts on the evaluation of ADODB is quite high, so changed to learn this. The advantages of ADODB have these several (online said, not I said): 1, speed ratio ...