This is an old revision of the document!
This page is under reconstruction
User Guide
List Of Tutorials
Basic Tasks
Database Abstraction | Using basic ADOdb commands to read data from and write back to the database |
Data Dictionary Basics | How to obtain information about database fields, columns and tables |
Keys,Fields & Field Objects | Understanding how to work with the most detailed level of data provided by ADOdb |
Advanced Tasks
Transactions | Understanding Transaction scope, and how to start, stop and roll back transactions |
Creating & Extending Drivers | Adding new functionality and changing the behaviour of existing drivers |
Additional Documentation
Reference Guide
Include File Guide
Writing Portable SQL
Migrating MySQL applications to ADOdb
Customizing Error Handling
Stored Procedure Example
Deprecated Functionality
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 = 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.
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) 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.
For more detailed information on error handling, see the section on debugging
Example 6: Connecting to 2 Databases At Once
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 ...');
$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.
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.