- skip to content

# Database Abstraction Layer

### User Tools

- Login

### Site Tools

- Recent changes
- Media Manager
- Sitemap

### Sidebar

##### Resources

- Reference Manual
- User Guide
- The Data Dictionary
- ADOdb-xmlschema
- ADOdb Active Record
- Performance Monitoring
- Date/Time Library
- Session Management Library
- Unsupported Addons
- Coding Standards

##### Supported Databases

- Access
- ADO
- CSV
- Firebird
- Frontbase
- IBM DB2
- Informix
- Interbase
- LDAP
- Microsoft SQL Server
- MySQL
- Netezza
- ODBC
- ODBTP
- Oracle
- PDO
- Postgresql
- SAP ADS
- SAP ASE
- SAP Maxdb
- SAP Sqlanywhere
- Sqlite
- Sybase
- Plain Text
- Visual Foxpro

##### Other

- Feature Comparison Matrix
- Connection Matrix
- Include File Guide

v5:reference:connection:getupdatesql

# getUpdateSql

##### See Also

$ADODB_QUOTE_FIELDNAMES

##### Syntax

    string getUpdateSql(
          obj $result,
          string[] $fieldArray,
          optional bool $forceUpdate=false,
          optional bool $dontFixQuotes=false,
          optional bool $forceType=null
         )

## Description

The function `getUpdateSql()` takes a set of parameters and returns an SQL statement that can be used to update a record in the database. It can automatically apply any necessary database specific quoting to character fields and exclude any unchanged variables.

## Parameters

### $result

The parameter `$result` is the handle to the result obtained by executing a select statement to obtain the record, not the recordset associated with the request. There is no need to read the row.

    $SQL = "SELECT * FROM employees WHERE emp_no=10001";
    $result = $db->execute($SQL);
    $sql = $db->getUpdateSql($result,.....

### $fieldArray

`$fieldArray` is an associative key⇒value pair of fieldnames and values. The values in the pair will replace the values in the existing record

    $ar = array('actname'=>"John O'Reilly");

### $forceUpdate

`$forceUpdate` forces the sql statement to include updates for fields that have not changed. It may be necessary to force updates when, for example, distinction needs to be made between null and zero in integer fields.

In addition, use of this parameter eliminates the need to add additional testing to determine if any fields have changed. In cases such as these, if the parameter is not set and there have been no modified fields, the getUpdateSql() method will return an empty string, causing execute to fail if passed this parameter.

Use of this parameter is tied to the now removed `magic_quotes` functionality and this parameter may be removed in future releases of ADOdb

### $dontFixQuotes

For a description of this behavior see qStr().

This parameter was designed to provide backwards compatibility with now unsupported versions of ADOdb and may be removed in future releases.

### $forceType

see $ADODB_FORCE_TYPE

## Usage

    /*
     * Sample uses the MySQL 'employees' demo database
     */

    $SQL = "SELECT * FROM employees WHERE employee_no=10001";
    $employeeRecord = $db->getAll($SQL);
    /*
     * Employee record returns:
    Array
    (
        [emp_no] => 10001
        [birth_date] => 1953-09-02
        [first_name] => Georgi
        [last_name] => Facello
        [gender] => M
        [hire_date] => 1986-06-26
    )
    */

    $SQL = 'SELECT * FROM employees WHERE emp_no=10001';
    $result = $db->Execute($SQL);

    $updateArray = array('first_name'=>'Jerry',
    		     'last_name'=>"O'Reilly",
    		     'gender'=>'M');

    $SQL = $db->getUpdateSql($result,$updateArray);

    print $SQL
    /*
     * returns: UPDATE employees
                  SET FIRST_NAME='Jerry',
                      LAST_NAME='O\'reilly'
                WHERE emp_no=10001
     */

Note:

- The `last_name` O'Reilly was automatically quoted into an appropriate formatted for insertion into a MySQL database.
- Because the value of the `gender` field was unchanged, it was discarded by the update statement.

If we want the returned statement to include all the supplied fields, we add the `$forceUpdate` option.

    $SQL = $db->getUpdateSql($result,$updateArray.true);
    /*
     * returns
     * UPDATE employees SET FIRST_NAME='Jerry',
                            LAST_NAME='O\'reilly',
                            GENDER='M'
                            WHERE emp_no=10001
     */

v5/reference/connection/getupdatesql.txt · Last modified: 2016/01/16 13:15 by mnewnham

### Page Tools

- Show pagesource
- Old revisions
- Backlinks
- Rename Page
- Back to top