ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools


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,.....
  • a string specifying a table name
    $table = 'employees';
    $sql = $db->getInsertSql($table,....

    Note that in this case, due to the fact that the function passes the table name by reference, the following syntax is not valid:

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

    Also note that this usage causes ADOdb to read the recordset information for itself, which may cause 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

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/03/23 19:15 by dregad