ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools


v5:reference:connection:getupdatesql

This is an old revision of the document!


getUpdateSql

See Also

$ADODB_QUOTE_FIELDNAMES

Syntax
string getUpdateSql(
      obj $recordSet,
      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

$recordSet

The parameter $recordSet is the recordset obtained by executing a select statement to obtain the record. 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.


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
 */
 
$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.1444833530.txt.gz · Last modified: 2017/04/21 11:34 (external edit)