- 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:getinsertsql

# getInsertSql

##### See Also

$ADODB_QUOTE_FIELDNAMES
$ADODB_FORCE_TYPE
AutoExecute()

##### Syntax

    string getInsertSql(
          mixed $recordSet,
          string[] $fieldArray,
          optional bool $dontFixQuotes=false,
          optional bool $forceType=null
         )

## Description

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

## Parameters

### $recordSet

The parameter `$recordSet` is either a recordset obtained by executing a select statement to obtain an empty recordset. There is no need to read the row.

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

or

    $table = 'employees';
    $sql = $db->getInsertSql($table,....

Note that in the second instance, because the function passes the table name by reference, the following syntax is not valid.

    /*
     * Incorrect syntax
     */
    $sql = $db->getInsertSql('employees',....

Also note that in the second example, ADOdb must attempt to read the recordset information for itself, and there may be performance issues.

### $fieldArray

`$fieldArray` is an associative key⇒value pair of fieldnames and values. The values in the pair will be inserted into the record. Note also

- Any key in the array that does not match a field name in the table will be discarded before the SQL statement is produced.
- The value of any field in the table that does not appear in the `$fieldArray` is controlled by the $ADODB_FORCE_TYPE variable.
- Auto-increment fields should not be included in the field list.

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

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
     */

    /*
     * Get the highest employee number
     */
    $SQL = "SELECT emp_no FROM employees ORDER BY emp_no DESC";
    $topEmployee = $db->getOne($SQL);

    $topEmployee++;

    /*
     * Get an empty recordset
     */
    $SQL = "SELECT * FROM employees WHERE emp_no=-1";
    $employeeRecord = $db->execute($SQL);

    /*
     * Build new employee record
     */
    $newEmployee = array(
        'emp_no' => $topEmployee,
        'birth_date' => $db->dbDate('1995-10-01'), //creates a portable date
        'first_name' => 'Patrick',
        'last_name' => "O'Reilly",
        'gender' => 'M',
        'hire_date' => $db->dbDate('2013-01-01')
    );

    $SQL = $db->getInsertSql($employeeRecord,$newEmployee);
    print $SQL;
    /*
     * returns: INSERT INTO employees ( EMP_NO, BIRTH_DATE, FIRST_NAME, LAST_NAME, GENDER, HIRE_DATE ) VALUES ( 500000, '1995-10-01', 'Patrick', 'O\'Reilly', 'M', '2013-01-01' )
     */

    /*
     * Now execute SQL statement
     */
    $db->execute($SQL);

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

### Page Tools

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