developing
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
developing [2015/07/06 05:53] – created mnewnham | developing [2015/07/18 15:09] (current) – removed mnewnham | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== Creating Applications With ADOdb ====== | ||
- | ===== Example 1: Select Statement ===== | ||
- | 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, | ||
- | |||
- | < | ||
- | |||
- | include(' | ||
- | |||
- | $conn = & | ||
- | |||
- | $conn-> | ||
- | |||
- | $recordSet = & | ||
- | |||
- | if (!$recordSet) | ||
- | |||
- | print $conn-> | ||
- | |||
- | else | ||
- | |||
- | while (!$recordSet-> | ||
- | |||
- | print $recordSet-> | ||
- | |||
- | | ||
- | |||
- | } | ||
- | |||
- | $recordSet-> | ||
- | |||
- | $conn-> | ||
- | |||
- | |||
- | |||
- | < | ||
- | The $recordSet returned stores the current row in the $recordSet-> | ||
- | |||
- | The $recordSet-> | ||
- | |||
- | | ||
- | |||
- | $rs1 = $db-> | ||
- | |||
- | | ||
- | |||
- | $rs2 = $db-> | ||
- | |||
- | | ||
- | | ||
- | To get the number of rows in the select statement, you can use $recordSet-> | ||
- | |||
- | Example 2: Advanced Select with Field Objects | ||
- | |||
- | Select a table, display the first two columns. If the second column is a date or timestamp, reformat the date to US format. | ||
- | |||
- | <? | ||
- | |||
- | include(' | ||
- | |||
- | $conn = & | ||
- | |||
- | $conn-> | ||
- | |||
- | $recordSet = & | ||
- | |||
- | if (!$recordSet) | ||
- | |||
- | print $conn-> | ||
- | |||
- | else | ||
- | |||
- | while (!$recordSet-> | ||
- | |||
- | $fld = $recordSet-> | ||
- | $type = $recordSet-> | ||
- | |||
- | |||
- | |||
- | if ( $type == ' | ||
- | |||
- | print $recordSet-> | ||
- | |||
- | $recordSet-> | ||
- | |||
- | | ||
- | print $recordSet-> | ||
- | |||
- | |||
- | |||
- | | ||
- | |||
- | } | ||
- | $recordSet-> | ||
- | |||
- | $conn-> | ||
- | |||
- | |||
- | |||
- | ?> | ||
- | 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=" | ||
- | X: TeXt, large text fields that should be shown in a < | ||
- | 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, | ||
- | 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' | ||
- | |||
- | <? | ||
- | |||
- | include(' | ||
- | |||
- | $conn = & | ||
- | |||
- | |||
- | |||
- | $conn-> | ||
- | |||
- | $shipto = $conn-> | ||
- | |||
- | |||
- | |||
- | $sql = " | ||
- | |||
- | $sql .= " | ||
- | |||
- | |||
- | |||
- | if ($conn-> | ||
- | |||
- | print 'error inserting: ' | ||
- | |||
- | } | ||
- | |||
- | ?> | ||
- | 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 John'' | ||
- | |||
- | 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(' | ||
- | |||
- | $conn = & | ||
- | |||
- | $conn-> | ||
- | |||
- | $shipto = $conn-> | ||
- | |||
- | $sql = " | ||
- | |||
- | $sql .= " | ||
- | |||
- | $conn-> | ||
- | if ($conn-> | ||
- | ?> | ||
- | 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 < | ||
- | |||
- | <? | ||
- | |||
- | include(' | ||
- | |||
- | $conn = & | ||
- | |||
- | $conn-> | ||
- | |||
- | $sql = ' | ||
- | |||
- | $rs = $conn-> | ||
- | |||
- | print $rs-> | ||
- | |||
- | ?> | ||
- | 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(' | ||
- | |||
- | $conn1 = & | ||
- | |||
- | $conn2 = & | ||
- | |||
- | |||
- | |||
- | $conn1-> | ||
- | |||
- | $conn2-> | ||
- | |||
- | |||
- | |||
- | $conn1-> | ||
- | |||
- | $conn2-> | ||
- | |||
- | ?> | ||
- | Example 7: Generating Update and Insert SQL | ||
- | |||
- | Since ADOdb 4.56, we support AutoExecute(), | ||
- | |||
- | |||
- | $record[" | ||
- | $record[" | ||
- | $record[" | ||
- | $insertSQL = $conn-> | ||
- | and an UPDATE with: | ||
- | |||
- | $record[" | ||
- | $record[" | ||
- | $insertSQL = $conn-> | ||
- | 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 " | ||
- | |||
- | 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# | ||
- | |||
- | <? | ||
- | |||
- | # | ||
- | |||
- | # SAMPLE GetUpdateSQL() and GetInsertSQL() code | ||
- | |||
- | # | ||
- | |||
- | include(' | ||
- | |||
- | include(' | ||
- | |||
- | |||
- | |||
- | # | ||
- | |||
- | # This code tests an insert | ||
- | |||
- | |||
- | |||
- | $sql = " | ||
- | |||
- | # Select an empty record from the database | ||
- | |||
- | |||
- | |||
- | $conn = & | ||
- | |||
- | $conn-> | ||
- | |||
- | $conn-> | ||
- | |||
- | $rs = $conn-> | ||
- | |||
- | |||
- | |||
- | $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[" | ||
- | |||
- | $record[" | ||
- | |||
- | $record[" | ||
- | |||
- | |||
- | |||
- | # 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-> | ||
- | |||
- | |||
- | |||
- | $conn-> | ||
- | |||
- | |||
- | |||
- | # | ||
- | |||
- | # This code tests an update | ||
- | |||
- | |||
- | |||
- | $sql = " | ||
- | |||
- | # Select a record to update | ||
- | |||
- | |||
- | |||
- | $rs = $conn-> | ||
- | |||
- | |||
- | |||
- | $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[" | ||
- | |||
- | $record[" | ||
- | |||
- | |||
- | |||
- | # 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-> | ||
- | |||
- | |||
- | |||
- | $conn-> | ||
- | |||
- | $conn-> | ||
- | |||
- | ?> | ||
- | $ADODB_FORCE_TYPE | ||
- | The behaviour of AutoExecute(), | ||
- | |||
- | 0 = ignore empty fields. All empty fields in array are ignored. | ||
- | |||
- | 1 = force null. All empty, php null and string ' | ||
- | |||
- | 2 = force empty. All empty, php null and string ' | ||
- | |||
- | 3 = force value. Value is left as it is. Php null and string ' | ||
- | |||
- | empty fields '' | ||
- | |||
- | |||
- | |||
- | define(' | ||
- | |||
- | define(' | ||
- | |||
- | define(' | ||
- | |||
- | define(' | ||
- | Thanks to Niko (nuko# | ||
- | |||
- | 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-> | ||
- | |||
- | 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(' | ||
- | |||
- | include_once(' | ||
- | |||
- | session_start(); | ||
- | |||
- | |||
- | |||
- | $db = NewADOConnection(' | ||
- | |||
- | |||
- | |||
- | $db-> | ||
- | |||
- | |||
- | |||
- | $sql = " | ||
- | |||
- | |||
- | |||
- | $pager = new ADODB_Pager($db, | ||
- | |||
- | $pager-> | ||
- | 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 = ' | ||
- | |||
- | | ||
- | |||
- | from adoxyz'; | ||
- | The above code can be found in the adodb/ | ||
- | |||
- | You can also allow display of html by setting $pager-> | ||
- | |||
- | 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('/ | ||
- | |||
- | include_once('/ | ||
- | |||
- | |||
- | |||
- | $db = & | ||
- | |||
- | $db-> | ||
- | |||
- | |||
- | |||
- | $rs = $db-> | ||
- | |||
- | |||
- | |||
- | print "< | ||
- | |||
- | print rs2csv($rs); | ||
- | print '< | ||
- | |||
- | $rs-> | ||
- | print rs2tab($rs, | ||
- | # false == suppress field names in first line | ||
- | |||
- | print '< | ||
- | |||
- | $rs-> | ||
- | |||
- | rs2tabout($rs); | ||
- | |||
- | print "</ | ||
- | |||
- | |||
- | |||
- | $rs-> | ||
- | |||
- | $fp = fopen($path, | ||
- | |||
- | if ($fp) { | ||
- | |||
- | rs2csvfile($rs, | ||
- | |||
- | 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(' | ||
- | |||
- | include_once(' | ||
- | |||
- | |||
- | |||
- | // ucwords() every element in the recordset | ||
- | |||
- | function do_ucwords(& | ||
- | |||
- | { | ||
- | |||
- | | ||
- | |||
- | | ||
- | |||
- | } | ||
- | |||
- | } | ||
- | |||
- | |||
- | |||
- | $db = NewADOConnection(' | ||
- | |||
- | $db-> | ||
- | |||
- | |||
- | |||
- | $rs = $db-> | ||
- | |||
- | $rs = RSFilter($rs,' | ||
- | 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, | ||
- | |||
- | Example 11: Smart Transactions | ||
- | |||
- | The old way of doing transactions required you to use | ||
- | $conn-> | ||
- | |||
- | $ok = $conn-> | ||
- | |||
- | if ($ok) $ok = $conn-> | ||
- | |||
- | if (!$ok) $conn-> | ||
- | |||
- | else $conn-> | ||
- | 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-> | ||
- | |||
- | $conn-> | ||
- | |||
- | $conn-> | ||
- | |||
- | $conn-> | ||
- | CompleteTrans() detects when an SQL error occurs, and will Rollback/ | ||
- | $conn-> | ||
- | |||
- | $conn-> | ||
- | |||
- | if (!CheckRecords()) $conn-> | ||
- | |||
- | $conn-> | ||
- | |||
- | $conn-> | ||
- | You can also check if a transaction has failed, using HasFailedTrans(), | ||
- | |||
- | Lastly, StartTrans/ | ||
- | |||
- | $conn-> | ||
- | |||
- | $conn-> | ||
- | |||
- | $conn-> | ||
- | if (!CheckRecords()) $conn-> | ||
- | $conn-> | ||
- | $conn-> | ||
- | $conn-> | ||
- | Note: Savepoints are currently not supported. |
developing.1436154804.txt.gz · Last modified: 2017/04/21 11:17 (external edit)