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.
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(); } /* * Recordsets and connections are automatically closed at the end of the script */ $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().
selectLimit, which allows us to limit the number of rows shown.
$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.
/* * Load the common code */ include 'adodb.inc.php'; /* * Load the Microsoft Access driver' */ $conn = adoNewConnection('access'); /* * Create a persistent connection to the northwind database, using * the Northwind ODBC DSN */ $conn->pConnect('northwind'); $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 adofieldobject 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.
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 = adoONewConnection('access'); # create a connection $conn->pConnect('northwind'); # connect to MS-Access, northwind dsn /* * Inserting a string containing quote characters * may cause a statement to fail if not pre-processed */ $shipto = $conn->qstr("John's Old Shoppe"); $orderDate = $conn->dbDate(time()); $sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) "; $sql .= "values ('ANATR',2,".$orderDate.",$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 formatted for Access with dBDate(). Note that the date field should not be quoted. If required by the database, the field will be automatically quoted.
The correct 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 by using qStr().
Observe the error-handling of the Execute statement. False is returned by Execute() if an error occurred. 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.
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.
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().
In this example we maintain connections to 2 databases at the same time. The transactional status of each database is managed independantly, but we can ensure that both databases remain synchronized by checking the transactional status of both connections before committing changes to both.
include 'adodb.inc.php'; # load code common to ADOdb $conn1 = adoNewConnection('mysql'); # create a mysql connection $conn2 = adoNewConnection('oracle'); # create a oracle connection $conn1->connect($server, $userid, $password, $database); $conn2->connect(false, $ora_userid, $ora_pwd, $oraname); $conn1->Execute('insert ...'); $conn2->Execute('update ...');
The method autoExecute() simplifies record handling by providing an advanced wrapper for the methods 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');
These 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. You can also pass a table name (by reference) instead of a recordset into getInsertSQL (in $rs), and it will generate an insert statement for that table.
include 'adodb/adodb.inc.php'; include 'adodb/tohtml.inc.php'; /* * This code tests an insert */ $sql = "SELECT * FROM ADOXYZ WHERE id = -1"; /* * Select an empty record from the database */ $conn = adoNewConnection("mysqli"); # create a connection /* * This enables debugging of connections */ $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();
The behaviour of AutoExecute(), GetUpdateSQL() and GetInsertSQL() when converting empty or null PHP variables to SQL is controlled by the global adodb_force_type|$ADODB_FORCE_TYPE variable.
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.
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('mysqli'); $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.
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 doUcwords(&$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,'doUcwords');
The rsFilter() method takes 2 parameters, the recordset, and the name of the filter function. It returns the processed recordset scrolled to the first record.
See the Transaction Handling Guide
ADOdb drivers are easily extendable to provide support for a new database or to extend functionality for an existing database. More information is available here.