ADODB Getting Started Tutorial (Basic article)

1. Brief description
ADODB is a common database operations library in PHP, 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.

2. Installation
Download tgz or zip to unzip.

3. Use
Included in adodb.inc.php before use
Include ("$adodb _path/adodb.inc.php"); Includes the ADODB library

4. Functions
There is almost one global function
Newadoconnection (' DatabaseType ');
Action: Generates a ADODB object.

The possible values are: (excluding the contents in parentheses)
Access (Microsoft Access/jet)
ADO (Generic ado, the base for all of the other ADO drivers)
Ado_access (Microsoft Access/jet using ADO)
Ado_mssql (Microsoft SQL Server using ADO)
DB2 (DB2)
VFP (Microsoft Visual FoxPro)
Fbsql (Frontbase)
IBase (Interbase 6 or before)
Firebird (Firebird)
informix72 (Informix databases before Informix 7.3)
Informix (Informix)
Maxsql (MySQL with transaction support)
MSSQL (Microsoft SQL Server 7)
Mssqlpo (Portable MSSQL driver)
MySQL (MySQL without transaction support)
MYSQLT (MySQL with transaction support, identical to Maxmysql)
Oci8 (Oracle 8/9)
oci805 (Oracle 8.0.5)
Oci8po (Oracle 8/9 Portable driver)
ODBC (Generic ODBC, the base for all of the other ODBC drivers)
Odbc_mssql (MSSQL via ODBC)
Odbc_oracle (Oracle via ODBC)
Oracle (Oracle 7)
Postgres (PostgreSQL)
POSTGRES64 (PostgreSQL 6.4)
Postgres7 (PostgreSQL 7, currently identical to Postgres)
Sqlanywhere (Sybase SQL Anywhere)
Sybase (Sybase)


Rs2html turn the RecordSet object into HTML output
Include include (' tohtml.inc.php ');

5. Objects
PHP ADODB is similar to the MS ADO object structure,
Mainly uses two objects (ADOConnection object and RecordSet object)
ADOConnection is responsible for database connection, sending SQL command, etc., the recordset is mainly to get the record set of query.

6. ADOConnection Object Description
1. Establish
ADODB is using the newadoconnection () function to create a new ADODB connection object, such as:
$db = Newadoconnection (' $database _type '); A New Connection
$database _type is the database format, the possible values look at the 4th bar above.

2. Connect to the database
Using the ADOConnection Connect method, the format is as follows:
Connect ("hostname", "User name", "User password", "database Name");
return value, the connection successfully returns a ADOConnection object, error returns false, you can use ADOConnection's errormsg () function to get an error description.
$db->connect ("$host", "$user", "$password", "$database _name");

3. Execute SQL command
Execute SQL commands with the Execute function such as:
$result = $db->execute ("SELECT * FROM Employees");
if ($result = = = False) Die ("failed");
Execution successfully returns a Recordset object, otherwise FALSE

4. Affected_rows () function
The Affected_rows () function returns the number of record rows affected by the last SQL command.

5. Date format
Because ADODB need to support different database systems, these different databases may represent date/time formats in different ways. ADODB uses the DBDate () function to convert the format between different databases.

6. String format
Different databases can be formatted with an incorrect string representation, such as the ' single parenthesis ' notation. Cases:
$ID = 3
$TheDate =mktime (0,0,0,8,31,2001)
$Note = sugar Why don ' t we call it off
$sql = "INSERT into table (ID, Thedate,note) VALUES ("
. $ID. ‘,‘
. $db->dbdate ($TheDate). ', '
. $db->qstr ($Note). ")";
$db->execute ($sql);

7. Limit and top support for select command
$connection->selectlimit ($sql, $nrows, $offset);
Note: The second parameter is the number of rows that need to be returned, and the third argument starts with the first line.
Return value: Successful return of a Recordset object, failure to return FALSE; Very useful for paging.

8. Cache Caching
Cacheexecute and Cacheselectlimit functions.
The first parameter is the time-out, the number of seconds.
ADODB allows you to stage the recordset's data in your file system, and in $connection->cacheexecute ($secs 2cache, $sql) and $connection Cacheselectlimit ($secs 2cache, $sql, $nrows, $offset) wait for the set time interval to arrive, then really do the database query to save time.
You can use the $ADODB _cache_dir to specify the CACHE directory as if you were using an absolute path. However, after the timeout, PHP will not automatically delete the cache, manually deleted.

9. Business
Starttrans () Start transaction
Completetrans () end transaction ADODB automatically handle commit and rollback of transactions
Hasfailedtrans () Whether the transaction succeeds TRUE succeeds, false is failed
Most databases hold transactions directly, but the MySQL InnoDB table supports transactions, while the MyISAM table is not supported.

10, Setfetchmode
Sets the recordset index for Recordsets objects. Such as:
$db->setfetchmode (ADODB_FETCH_ASSOC); Return Associative array
Adodb_fetch_num; Index in a digital way

12, Getinsertsql, Getupdatesql
To generate an Insert SQL with two parameters, the first one is a RecordSet and the second is an array. For example:

$sql = "SELECT * from adoxyz WHERE id =-1"; # query an empty dataset from the database
$conn = &adonewconnection ("MySQL"); # Create a link
$conn->debug=1;
$conn->pconnect ("localhost", "admin", "" "," Test "); # link to MySQL, database name is test

$rs = $conn->execute ($sql); # Execute the query and get an empty data set

$record = Array (); # initialize an array to hold the recording data for new use

# set column values in a record
$record ["FirstName"] = "Bob";
$record ["LastName"] = "Smith";
$record ["created"] = time ();

# Pass in an empty dataset and column data array into the GETINSERTSQL function to perform the function
# This function will return a full-format INSERT SQL instruction according to the incoming data.

$insertSQL = $conn->getinsertsql ($rs, $record);

$conn->execute ($insertSQL); # searched records into the repository

#==========================
# The following code tests the update status

$sql = "SELECT * from adoxyz WHERE id = 1";
# Select a record to update

$rs = $conn->execute ($sql); # Execute this query and get an existing record to update

$record = Array (); # initialize an array to hold the data to be updated

# Set the value in the field
$record ["FirstName"] = "Caroline";
$record ["LastName"] = "Smith"; # update Caroline's last name changed from Miranda to Smith

# Pass in this single-record-only dataset and the array containing the data into the Getupdatesql function
# The function will return an update (update) SQL instruction with the correct WHERE condition
$updateSQL = $conn->getupdatesql ($rs, $record);

$conn->execute ($updateSQL); # Update records in the repository
$conn->close ();

13, Pageexecute paging query
such as: Pageexecute ($sql, $num _of_rows_per_page, $curr _page);
Possible attributes Atfirstpage (), Atlastpage (), AbsolutePage ()



7. Recordset Object
1. Move ($Pos)
Scrolling the current data column, ADODB support the entire database to scroll forward, some databases do not support the subsequent scrolling, this is not a problem, because you can use the staging record to the cache to simulate scrolling backwards.

2, RecordCount () or RowCount ()
Returns the number of records accessed by the SQL command, and some databases are returned as unsupported-1

3. EOF
Is the end of the record

4. Fields
Used to get the value of the recordset, usage, $rs->fields[number or field name], if $db->setfetchmode (ADODB_FETCH_ASSOC); can be accessed directly with the field name.

5, MoveNext ()
The record pointer moves down one.

6, MoveFirst
The record pointer moves at the beginning.

7, Fetchnextobject ()
Gets a row object, and the pointer moves down automatically. Such as:
$row = $rs->fetchnextobject ();
Echo ($row->username. $row->age);

8, insert_id
Gets the value that the recordset last inserted.

9, Metatypes
Gets the type of the field. Such as:
$FLD = $recordSet->fetchfield (1);
$type = $recordSet->metatype ($fld->type);
if ($type = = ' D ' | | $type = = ' T ') {...};
Possible values and meanings:
C:character field, you should use <input type= "text" > tag to take the value.
X: Text field (text), Long text field, use <textarea> tag to display data.
B:blob fields or large two-bit objects (like programs, drawings, etc.).
D: Date field
T: Time-field
L: Logical field (false value) or bit field
N: Numeric field, including auto carry, numbering, integer, floating point, real number, etc.
R: Sequence field, including sequence, auto-promotion integer, only for the selected database.

10, Fetchfield
Get a Field object such as:
$field = $rs->fetchfield ();
Field object has three attributes name, type, length respectively meaning (name, type, lengths, length may pass back-1)
The type can be converted to character format by Metatypes, for example:
$FLD = $recordSet->fetchfield (1);
$type = $recordSet->metatype ($fld->type);
if ($type = = ' D ' | | $type = = ' T ') {...};

ADODB Getting Started Tutorial (Basic article)