Task: Connect to the Access Northwind DSN, display the first 2 columns of each row.

In this example, we create a ADOConnection object, which represents the connection to the database. The connection is initiated with PConnect, which is a persistent connection. Whenever we want to query the database, we call the ADOConnection.Execute() function. This returns an ADORecordSet object which is actually a cursor that holds the current row in the array fields[]. We use MoveNext() to move from row to row.

NB: A useful function that is not used in this example is SelectLimit, which allows us to limit the number of rows shown.

include('adodb.inc.php');    # load code common to ADOdb

$conn = &ADONewConnection('access');        # create a connection

$conn->PConnect('northwind');   # connect to MS-Access, northwind DSN

$recordSet = &$conn->Execute('select * from products');
 
if (!$recordSet) 
 
         print $conn->ErrorMsg();
 
else
 
while (!$recordSet->EOF) {
 
         print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>';
 
         $recordSet->MoveNext();
 
}
 
$recordSet->Close(); # optional

$conn->Close(); # optional

The $recordSet returned stores the current row in the $recordSet→fields array, indexed by column number (starting from zero). We use the MoveNext() function to move to the next row. The EOF property is set to true when end-of-file is reached. If an error occurs in Execute(), we return false instead of a recordset.

The $recordSet→fields[] array is generated by the PHP database extension. Some database extensions only index by number and do not index the array by field name. To force indexing by name - that is associative arrays - use the SetFetchMode function. Each recordset saves and uses whatever fetch mode was set when the recordset was created in Execute() or SelectLimit().

         $db->SetFetchMode(ADODB_FETCH_NUM);
 
         $rs1 = $db->Execute('select * from table');
 
         $db->SetFetchMode(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')

To get the number of rows in the select statement, you can use $recordSet→RecordCount(). Note that it can return -1 if the number of rows returned cannot be determined.

Select a table, display the first two columns. If the second column is a date or timestamp, reformat the date to US format.

include('adodb.inc.php');    # load code common to ADOdb

$conn = &ADONewConnection('access');        # create a connection

$conn->PConnect('northwind');   # connect to MS-Access, northwind dsn

$recordSet = &$conn->Execute('select CustomerID,OrderDate from Orders');
 
if (!$recordSet) 
 
         print $conn->ErrorMsg();
 
else
 
while (!$recordSet->EOF) {
 
         $fld = $recordSet->FetchField(1);
         $type = $recordSet->MetaType($fld->type);
 
 
 
         if ( $type == 'D' || $type == 'T') 
 
                 print $recordSet->fields[0].' '.
 
                          $recordSet->UserDate($recordSet->fields[1],'m/d/Y').'<BR>';
 
         else 
                 print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>';
 
 
 
         $recordSet->MoveNext();
 
}
$recordSet->Close(); # optional

$conn->Close(); # optional

In this example, we check the field type of the second column using FetchField(). This returns an object with at least 3 fields.

name: name of column type: native field type of column max_length: maximum length of field. Some databases such as MySQL do not return the maximum length of the field correctly. In these cases max_length will be set to -1. We then use MetaType() to translate the native type to a generic type. Currently the following generic types are defined:

C: character fields that should be shown in a <input type=“text”> tag. X: TeXt, large text fields that should be shown in a <textarea> B: Blobs, or Binary Large Objects. Typically images. D: Date field T: Timestamp field L: Logical field (boolean or bit-field) I: Integer field N: Numeric field. Includes autoincrement, numeric, floating point, real and integer. R: Serial field. Includes serial, autoincrement integers. This works for selected databases. If the metatype is of type date or timestamp, then we print it using the user defined date format with UserDate(), which converts the PHP SQL date string format to a user defined one. Another use for MetaType() is data validation before doing an SQL insert or update.

Example 3: Inserting

Insert a row to the Orders table containing dates and strings that need to be quoted before they can be accepted by the database, eg: the single-quote in the word John's.

<?

include('adodb.inc.php'); # load code common to ADOdb

$conn = &ADONewConnection('access'); # create a connection

$conn→PConnect('northwind'); # connect to MS-Access, northwind dsn

$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().'<BR>';

}

?> In this example, we see the advanced date and quote handling facilities of ADOdb. The unix timestamp (which is a long integer) is appropriately formated for Access with DBDate(), and the right escape character is used for quoting the John's Old Shoppe, which is Johns Old Shoppe and not PHP's default John's Old Shoppe with qstr(). Observe the error-handling of the Execute statement. False is returned by Execute() if an error occured. The error message for the last error that occurred is displayed in ErrorMsg(). Note: php_track_errors might have to be enabled for error messages to be saved. Example 4: Debugging <? include('adodb.inc.php'); # load code common to ADOdb $conn = &ADONewConnection('access'); # create a connection $conn→PConnect('northwind'); # connect to MS-Access, northwind dsn $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 have turned on debugging by setting debug = true. This will display the SQL statement before execution, and also show any error messages. There is no need to call ErrorMsg() in this case. For displaying the recordset, see the rs2html() example. Also see the section on Custom Error Handlers. Example 5: MySQL and Menus Connect to MySQL database agora, and generate a <select> menu from an SQL statement where the <option> captions are in the 1st column, and the value to send back to the server is in the 2nd column. <? include('adodb.inc.php'); # load code common to ADOdb $conn = &ADONewConnection('mysql'); # create a connection $conn→PConnect('localhost','userid',,'agora');# connect to MySQL, agora db

$sql = 'select CustomerName, CustomerID from customers';

$rs = $conn→Execute($sql);

print $rs→GetMenu('GetCust','Mary Rosli');

?> Here we define a menu named GetCust, with the menu option 'Mary Rosli' selected. See GetMenu(). We also have functions that return the recordset as an array: GetArray(), and as an associative array with the key being the first column: GetAssoc().

Example 6: Connecting to 2 Databases At Once

<?

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 …');

?> Example 7: Generating Update and Insert SQL

Since ADOdb 4.56, we support AutoExecute(), which simplifies things by providing an advanced wrapper for GetInsertSQL() and GetUpdateSQL(). For example, an INSERT can be carried out with:

$record[“firstname”] = “Bob”;

  $record["lastname"] = "Smith"; 
  $record["created"] = time(); 
  $insertSQL = $conn->AutoExecute($rs, $record, 'INSERT'); 

and an UPDATE with:

$record[“firstname”] = “Caroline”;

  $record["lastname"] = "Smith"; # Update Caroline's lastname from Miranda to Smith 
  $insertSQL = $conn->AutoExecute($rs, $record, 'UPDATE', 'id = 1'); 

The rest of this section is out-of-date:

ADOdb 1.31 and later supports two new recordset 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.

We show how the functions can be used when accessing a table with the following fields: (ID, FirstName, LastName, Created).

Before these functions can be called, you need to initialize the recordset by performing a select on the table. Idea and code by Jonathan Younger jyounger#unilab.com. Since ADOdb 2.42, you can pass a table name instead of a recordset into GetInsertSQL (in $rs), and it will generate an insert statement for that table.

<?

#

# SAMPLE GetUpdateSQL() and GetInsertSQL() code

#

include('adodb.inc.php');

include('tohtml.inc.php');

#

# This code tests an insert

$sql = “SELECT * FROM ADOXYZ WHERE id = -1”;

# Select an empty record from the database

$conn = &ADONewConnection(“mysql”); # create a connection

$conn→debug=1;

$conn→PConnect(“localhost”, “admin”, “”, “test”); # connect to MySQL, testdb

$rs = $conn→Execute($sql); # Execute the query and get the empty recordset

$record = array(); # Initialize an array to hold the record data to insert

# Set the values for the fields in the record

# Note that field names are case-insensitive

$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.

$insertSQL = $conn→GetInsertSQL($rs, $record);

$conn→Execute($insertSQL); # Insert the record into the database

#

# This code tests an update

$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

# Note that field names are case-insensitive

$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();

?> $ADODB_FORCE_TYPE The behaviour of AutoExecute(), GetUpdateSQL() and GetInsertSQL() when converting empty or null PHP variables to SQL is controlled by the global $ADODB_FORCE_TYPE variable. Set it to one of the values below. Default is ADODB_FORCE_VALUE (3):

0 = ignore empty fields. All empty fields in array are ignored.

1 = force null. All empty, php null and string 'null' fields are changed to sql NULL values.

2 = force empty. All empty, php null and string 'null' fields are changed to sql empty or 0 values. 3 = force value. Value is left as it is. Php null and string 'null' are set to sql NULL values and empty fields are set to empty sql values. define('ADODB_FORCE_IGNORE',0); define('ADODB_FORCE_NULL',1); define('ADODB_FORCE_EMPTY',2); define('ADODB_FORCE_VALUE',3); Thanks to Niko (nuko#mbnet.fi) for the $ADODB_FORCE_TYPE code. Note: the constant ADODB_FORCE_NULLS is obsolete since 4.52 and is ignored. Set $ADODB_FORCE_TYPE = ADODB_FORCE_NULL for equivalent behaviour. Since 4.62, the table name to be used can be overridden by setting $rs→tableName before AutoExecute(), GetInsertSQL() or GetUpdateSQL() is called. Example 8: Implementing Scrolling with Next and Previous The following code creates a very simple recordset pager, where you can scroll from page to page of a recordset. 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); This will create a basic record pager that looks like this:

ID First Name Last Name Date Created 36 Alan Turing Sat 06, Oct 2001 37 Serena Williams Sat 06, Oct 2001 38 Yat Sun Sun Sat 06, Oct 2001 39 Wai Hun See Sat 06, Oct 2001 40 Steven Oey Sat 06, Oct 2001 Page 8/10 The number of rows to display at one time is controled by the Render($rows) method. If you do not pass any value to Render(), ADODB_Pager will default to 10 records per page.

You can control the column titles by modifying your SQL (supported by most databases):

$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 the adodb/tests/testpaging.php example included with this release, and the class ADODB_Pager in adodb/adodb-pager.inc.php. The ADODB_Pager code can be adapted by a programmer so that the text links can be replaced by images, and the dull white background be replaced with more interesting colors.

You can also allow display of html by setting $pager→htmlSpecialChars = false.

Some of the code used here was contributed by Iván Oliva and Cornel G.

Example 9: Exporting in CSV or Tab-Delimited Format

We provide some helper functions to export in comma-separated-value (CSV) and tab-delimited formats:

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 “<pre>”;

print rs2csv($rs); # return a string, CSV format print '<hr>';

$rs→MoveFirst(); # note, some databases do not support MoveFirst print rs2tab($rs,false); # return a string, tab-delimited

                                                   # false == suppress field names in first line

print '<hr>';

$rs→MoveFirst();

rs2tabout($rs); # send to stdout directly (there is also an rs2csvout function)

print “</pre>”;

$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 Filters

Sometimes we want to pre-process all rows in a recordset before we use it. For example, we want to ucwords all text in recordset.

include_once('adodb/rsfilter.inc.php');

include_once('adodb/adodb.inc.php');

ucwords() every element in the recordset function 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 two parameters, the current row as an array, and the recordset object. For future compatibility, you should not use the original recordset object. Example 11: Smart Transactions The old way of doing transactions required you to use $conn→BeginTrans(); $ok = $conn→Execute($sql); if ($ok) $ok = $conn→Execute($sql2); if (!$ok) $conn→RollbackTrans(); else $conn→CommitTrans(); This is very complicated for large projects because you have to track the error status. Smart Transactions is much simpler. You start a smart transaction by calling StartTrans(): $conn→StartTrans(); $conn→Execute($sql); $conn→Execute($Sql2); $conn→CompleteTrans(); CompleteTrans() detects when an SQL error occurs, and will Rollback/Commit as appropriate. To specificly force a rollback even if no error occured, use FailTrans(). Note that the rollback is done in CompleteTrans(), and not in FailTrans(). $conn→StartTrans(); $conn→Execute($sql); if (!CheckRecords()) $conn→FailTrans(); $conn→Execute($Sql2); $conn→CompleteTrans(); You can also check if a transaction has failed, using HasFailedTrans(), which returns true if FailTrans() was called, or there was an error in the SQL execution. Make sure you call HasFailedTrans() before you call CompleteTrans(), as it is only works between StartTrans/CompleteTrans. Lastly, StartTrans/CompleteTrans is nestable, and only the outermost block is executed. In contrast, BeginTrans/CommitTrans/RollbackTrans is NOT nestable. $conn→StartTrans(); $conn→Execute($sql); $conn→StartTrans(); # ignored if (!CheckRecords()) $conn→FailTrans(); $conn→CompleteTrans(); # ignored $conn→Execute($Sql2); $conn→CompleteTrans(); Note: Savepoints are currently not supported.