Ten Adodb instances (clear version) _ PHP Tutorial

10 instances of Adodb (clear version ). I wanted to learn pear, but I saw several posts on the Internet that spoke a lot about adodb, so I learned this. The advantages of ADODB are as follows: 1. the speed is faster than learning pear. However, I have seen several posts on the Internet that have high Comments on adodb, so I learned this again.
The advantages of ADODB include ):
1. the speed is twice faster than pear;
2. more database types are supported than pear, and even ACCESS is supported;
3. no installation or server support required (this is important for new users)
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 has translated the full text of README or knows where there is a translation, please reply to me. thank you.
Tutorial
Example 1: Select Statement
Task: connect to an Access database named Northwind and display the first two fields of each record.

In this instance, we create an ADOC connection object and use it to connect to a database. this connection uses the PConnect method, which is a persistent connection. when we want to query the database, we can call the Execute () function of this connection at any time. it returns an ADORecordSet object which is actually a cursor that holds the current row in the array fields []. we use MoveNext () to switch from a record to the next record.

NB: there is a very practical function SelectLimit which is not used in this example. it can control the number of records displayed (for example, only the first 10 records can be displayed on pages ).


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

Include ('adodb. inc. php'); # load adodb
$ Conn = & ADONewConnection ('access'); # Create a connection
$ Conn-> PConnect ('northwind '); # connect to a MS-Access database named northwind
$ RecordSet = & $ conn-> Execute ('select * from Products'); # Search all data from the products data table
If (! $ RecordSet)
Print $ conn-> ErrorMsg (); // if an error occurs during data search, the error message is displayed.
Else
While (! $ RecordSet-> EOF ){
Print $ recordSet-> fields [0]. ''. $ recordSet-> fields [1].'
';
$ RecordSet-> MoveNext (); // point to the next record
} // Display data in the list

$ RecordSet-> Close (); // optional
$ Conn-> Close (); // optional
?>
--------------------------------------------------------------------------------

$ RecordSet returns the current array in $ recordSet-> fields and performs a numerical index on the field (starting from 0 ). we use the MoveNext () function to move to the next record. when the database finds the end, the EOF property is set to true. if an error occurs in Execute (), recordset returns flase.

$ RecordSet-> fields [] arrays are generated by PHP database extensions. Some database extensions can only be indexed by number, but not by field name. if you insist on using the field name index, you should use the SetFetchMode function. regardless of the format of the index, recordset can be created by Execute () or SelectLimit.


PHP :--------------------------------------------------------------------------------
$ Db-> SetFetchMode (ADODB_FETCH_NUM );
$ Rs1 = $ db-> Execute ('select * from table'); // use a digital index
$ Db-> SetFetchMode (ADODB_FETCH_ASSOC );
$ Rs2 = $ db-> Execute ('select * from table'); // index by field name
Print_r ($ rs1-> fields); # shows array ([0] => 'v0', [1] => 'v1 ')
Print_r ($ rs2-> fields); # shows array (['col1'] => 'v0', ['col2'] => 'v1 ') then ')--------------------------------------------------------------------------------


To obtain the record number, you can use $ recordSet-> RecordCount (). -1 is returned if no current record exists.

Example 2: Advanced Select with Field Objects
Search the table to display the first two fields. if the second field is in the time or date format, change it to the US standard time format.

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

Include ('adodb. inc. php'); // load adodb
$ Conn = & ADONewConnection ('access'); // create a connection
$ Conn-> PConnect ('northwind '); // connect to the MS-Access database named northwind
$ RecordSet = & $ conn-> Execute ('select mermerid, OrderDate from Orders '); // search for CustomerID and OrderDate fields from the Orders table
If (! $ RecordSet)
Print $ conn-> ErrorMsg (); // if a database search error occurs, the error message is displayed.
Else
While (! $ RecordSet-> EOF ){
$ Fields = $ recordSet-> FetchField (1); // assign the second field to $ fields
$ Type = $ recordSet-> Variable Ype ($ variable-> type); // format of the field value

If ($ type = 'd' | $ type ='t ')
Print $ recordSet-> fields [0]. ''.
$ RecordSet-> UserDate ($ recordSet-> fields [1], 'M/d/Y ').'
'; // If the field format is date or time, make it output in American standard format
Else
Print $ recordSet-> fields [0]. ''. $ recordSet-> fields [1].'
'; // Otherwise, output as is

$ RecordSet-> MoveNext (); // point to the next record
}
$ RecordSet-> Close (); // optional
$ Conn-> Close (); // optional

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

In this example, we use the FetchField () function to check the format of the second field. it returns an object containing three variables.

Name: Field name
Type: The Real format of the field in its database
Max_length: the maximum length of a field. some databases do not return this value, for example, MYSQL. in this case, the max_length value is equal to-1.
We use plain Ype () to convert the field database format to the standard field format.

C: struct field, which can be found in Label.
X: text-type field, which stores large texts. Tag
B: Block, large binary objects, slices
D: date field
T: Time field
L: logical field (Boolean logic or bit-field)
I: integer field
N: number field, including autoincrement, numeric, floating point, real, and integer ).
R: continuous field. includes serial and autoincrement integers. it can only work in the specified database.
If your YPE is of the date or timestamp type, we use the user-defined date format UserDate () function for output. UserDate () is used to convert the php SQL date string format to the user-defined format, another use of MetaType () is to confirm the data validity before insertion and replacement.

Instance 3: Inserting
Insert a record that contains date and period data in the order data table. before insertion, the record must be converted. for example: the single-quote in the word John's.

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

Include ('adodb. inc. php'); // load adodb
$ Conn = & ADONewConnection ('access'); // create a connection

$ Conn-> PConnect ('northwind '); // connect to the ACCESS database northwind
$ Shipto = $ conn-> qstr ("John's Old Shoppe ");

$ SQL = "insert into orders (customerID, EmployeeID, OrderDate, ShipName )";
$ SQL. = "values ('anact', 2,". $ conn-> DBDate (time (). ", $ shipto )";

If ($ conn-> Execute ($ SQL) === false ){
Print 'error inserting: '. $ conn-> ErrorMsg ().'
';
} // Output error message if insertion fails
?>
--------------------------------------------------------------------------------

In this example, we can see that ADOdb can easily handle some advanced database operations. the unix timestamp (a long integer) is converted to the correct Access format by DBDate (), and the right escape character is used for quoting the John's Old Shoppe, which is John's Old Shoppe and not PHP's default John's Old Shoppe with qstr ().

Observe the error handling of execution statements. If an error occurs in Execute (), the ErrorMsg () function returns the last error prompt. Note: php_track_errors might have to be enabled for error messages to be saved.

Instance 4: Debugging
Include ('adodb. inc. php'); // load adodb
$ Conn = & ADONewConnection ('access'); // create a connection
$ Conn-> PConnect ('northwind '); // connect to the ACCESS database northwind
$ Shipto = $ conn-> qstr ("John's Old Shoppe ");
$ SQL = "insert into orders (customerID, EmployeeID, OrderDate, ShipName )";
$ SQL. = "values ('anact', 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 displays all SQL information before execution and all error messages. in this example, we no longer need to call ErrorMsg (). to display a recordset, refer to the rs2html () instance.

You can also refer to the section of Custom Error Handlers.

Instance 5: MySQL and Menus
Connect to the MySQL database agora and generate Drop-down menuThe option is displayed as the first field, and the returned 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, database name: agora$ SQL = 'SELECT mermername, CustomerID from MERS mers '; // The name of the search field is used for display and the id is used for return values.$ Rs = $ conn-> Execute ($ SQL );Print $ rs-> GetMenu ('getcust ', 'Mary rosli'); // display the menu?>--------------------------------------------------------------------------------Here we define a menu named GetCust, where 'Mary rosli' is selected. see GetMenu (). we also have a function that returns record values to an array: GetArray (), and as an associative array with the key being the first column: GetAssoc ().Instance 6: Connecting to 2 Databases At OncePHP :-------------------------------------------------------------------------------- Include ('adodb. inc. php'); # load code common to adodb$ Conn1 = & ADONewConnection ('mysql'); # create a mysql connection$ Conn2 = & ADONewConnection ('Oracle '); # create a oracle connection$ Conn1-> PConnect ($ server, $ userid, $ password, $ database );$ Conn2-> PConnect (false, $ ora_userid, $ ora_pwd, $ oraname );$ Conn1-> Execute ('Insert ...');$ Conn2-> Execute ('Update ...');?> // Connect two databases at the same time--------------------------------------------------------------------------------7: Generating Update and Insert SQLADOdb 1.31 and later versions support two new functions: GetUpdateSQL () and GetInsertSQL (). this allow you to perform a "SELECT * FROM table query WHERE... ", make a copy of the $ rs-> fields, modify the fields, and then generate the SQL to update or insert into the table automatically.Let's take a look at how these two functions are executed in this worksheet: (ID, FirstName, LastName, Created ).Before these functions can be called, you need to initialize the recordset by grouping 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. does this sentence seem to be the same? could anyone tell me the reason?#======================================# This code tests an insert$ SQL = "SELECT * FROM ADOXYZ WHERE id =-1"; # find an empty record $ conn = & ADONewConnection ("mysql"); # create a connection$ Conn-> debug = 1;$ Conn-> PConnect ("localhost", "admin", "", "test"); # connect to MySQL, testdb$ Rs = $ conn-> Execute ($ SQL); # obtain an empty record$ Record = array (); # create an array to be inserted# Set the insert 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 will process the data and return# A fully formatted insert SQL statement. # variables are formatted before insertion.$ InsertSQL = $ conn-> GetInsertSQL ($ rs, $ record );$ Conn-> Execute ($ insertSQL); # insert data into the database#======================================# The following program demonstrates modifying data, which is 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 will 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 ();?>--------------------------------------------------------------------------------Instance 8 Implementing Scrolling with Next and PreviousThe following example shows a small paging 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 Created36 Alan Turing Sat 06, Oct 200137 Serena Williams Sat 06, Oct 200138 Yat Sun Sat 06, Oct 200139 Wai Hun See Sat 06, Oct 200140 Steven Oey S06, Oct 2001Page 8/10Call the Render ($ rows) method to display data by page. if you do not enter a value for Render (), the default value of ADODB_Pager is 10 records per page.You can select to display any field in SQL and define the 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. in php, the ADODB_Pager object is in the adodb/adodb-pager.inc.php. you can add images and color changes to the ADODB_Pager code. you can set $ pager-> htmlSpecialChars = false to display the HTML code.Some of the code used here was 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. 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""; Print 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 is also an rs2csvout function)Print"";$ Rs-> MoveFirst (); $ fp = fopen ($ path, "w ");If ($ fp) {rs2csvfile ($ rs, $ fp); # write to file (there is also an rs2tabfile function)Fclose ($ fp );}--------------------------------------------------------------------------------Carriage-returns or newlines are converted to spaces. field names are returned in the first line of text. strings containing the delimiter character are quoted with double-quotes. double-quotes are double-quoted again. this conforms to Excel import and export guide-lines.All the above functions take as an optional last parameter, $ addtitles which defaults to true. When set to false field names in the first line are suppressed.Example 10: Recordset FiltersSometimes we want to pre-process all rows in a recordset before we use it. For example, we want to 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 _ ucword ');--------------------------------------------------------------------------------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 two parameters, the current row as an array, and the recordset object. for future compatibility, you shocould not use the original recordset object.Bytes. The advantages of ADODB include: 1. speed ratio...