autoExecute

Syntax

  ADORecordSet_empty|false autoExecute(
     string $tables,
     string[] $arrFields,
     string $mode,
     optional mixed $where=false
     optional bool $forceUpdate=false
     )

Description

The autoExecute() function can automatically generate and execute INSERT and UPDATE statements on a given table. It is a wrapper for getInsertSQL() and getUpdateSQL()

autoExecute() inserts or updates $table given an array of $arrFields, where the keys are the field names and the array values are the field values to store. An INSERT or UPDATE is generated based on $mode (see below).

Until 5.22, the function returned a boolean value, with true indicating success and false that an error occurred. Starting with 5.23.0¹⁾, success returns an ADORecordSet_empty object, to be consistent with other ADOdb insert / update functions. Backwards-compatibility is guaranteed, because ADORecordSet_empty == true.

autoExecute() is designed to provide a simple and robust interface to record updating and insertion. As such, it contains multiple layers of table, column and data validation to ensure data integrity; in particular, the table is first queried to extract key information before the SQL is generated. This overhead causes a noticeable performance degradation when used for large numbers of record updates. Consider using execute() for that purpose instead.

$table

The name of the table to update

$arrFields

An associative array of field⇒value pairs

$mode

Legal values for ` $mode ` are

String	Integer	Constant
INSERT	1	DB_AUTOQUERY_INSERT
UPDATE	2	DB_AUTOQUERY_UPDATE

$where

The $where clause is required if $mode == 'UPDATE'.

$forceUpdate

Value	Description	Overhead
false	(default). Only fields that have been modified are updated	The database record is re-read before update
true	All fields provided are updated	The constructed statement may be considerably larger

Usage

autoExecute($table, $arrFields, $mode, $where=false, $forceUpdate=true)

Insert example

$table               = 'people'
$record["first_name"] = "Carol";
$record["last_name"]  = "Smith"; 
 
$conn->autoExecute($table,$record,'INSERT');
 
/*
 * result executes "INSERT INTO people (first_name,last_name) values ('Carol',Smith')";
 */

Update example

$table               = 'people'
$record["first_name"] = 'Carol';
$record["last_name"]  = 'Jones'; 
$where               = "last_name like 'Sm%'";
 
$conn->autoExecute($table,$record,'UPDATE', $where);
 
/*
 * result executes "UPDATE people SET first_name='Carol',last_name='Jones' WHERE last_name like 'Sm%'";
 */

In situations where the table names contain certain special characters, such as dashes, the $ADODB_QUOTE_FIELDNAMES setting can be used to wrap the table and field names.

$ADODB_QUOTE_FIELDNAMES = 'NATIVE';
$table               = 'people'
$record["first-name"] = 'Carol';
$record["last-name"]  = 'Jones'; 
$where               = "last_name like 'Sm%'";
 
$conn->autoExecute($table,$record,'UPDATE', $where);
 
/*
 * result executes "UPDATE `people` SET `first-name`='Carol',`last-name`='Jones' WHERE `last-name` like 'Sm%'";
 */

¹⁾

https://github.com/ADOdb/ADOdb/issues/1213