====== getInsertSql ======
~~NOTOC~~
== See Also ==
[[v5:reference:adodb_quote_fieldnames|$ADODB_QUOTE_FIELDNAMES]]\\
[[v5:reference:adodb_force_type|$ADODB_FORCE_TYPE]]\\
[[v5:reference:connection:autoexecute|AutoExecute()]]\\
== Syntax ==
string getInsertSql(
mixed $recordSet,
string[] $fieldArray,
optional bool $placeholder=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 [[v5:reference:connection:execute|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 [[v5:reference:adodb_force_type|$ADODB_FORCE_TYPE]] variable.
* Auto-increment fields should not be included in the field list.
$ar = array('last_name'=>"John O'Reilly");
-------------------------------------------------------------------------------
==== $placeHolder ====
This argument previosly held information about now-deprecated PHP functionality, and is now ignored
--------------------------------------------------------------------------------
This parameter was designed to provide backwards compatibility with now unsupported versions of ADOdb and may be removed in future releases.
==== $forceType ====
see [[v5:reference:adodb_force_type|$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);