ADOdb Library for PHP Manual
Prev	Generating Update and Insert SQL	Next

Generating Update and Insert SQL

Note: Since ADOdb 4.56, we support AutoExecute(), which simplifies things by providing an advanced wrapper for GetInsertSQL() and GetUpdateSQL(). For example, an INSERT can be carried out with:

    $record["firstname"] = "Bob"; 
    $record["lastname"] = "Smith"; 
    $record["created"] = time(); 
    $insertSQL = $conn->AutoExecute($rs, $record, 'INSERT');

and an UPDATE with:

    $record["firstname"] = "Caroline"; 
    $record["lastname"] = "Smith"; # Update Caroline's lastname from Miranda to Smith 
    $insertSQL = $conn->AutoExecute($rs, $record, 'UPDATE', 'id = 1');

The rest of this section is out-of-date:

ADOdb 1.31 and later supports two new recordset functions: GetUpdateSQL( ) and GetInsertSQL( ). This allow you to perform a "SELECT * FROM table query WHERE...", make a copy of the $rs->fields, modify the fields, and then generate the SQL to update or insert into the table automatically.

We show how the functions can be used when accessing a table with the following fields: (ID, FirstName, LastName, Created).

Before these functions can be called, you need to initialize the recordset by performing a select on the table. Idea and code by Jonathan Younger jyounger#unilab.com.


<?php

    #==============================================

    # SAMPLE GetUpdateSQL() and GetInsertSQL() code

    #==============================================

    include('adodb.inc.php');

    include('tohtml.inc.php');



    #==========================

    # This code tests an insert



    $sql = "SELECT * FROM ADOXYZ WHERE id = -1"; 

    # Select an empty record from the database





    $conn = &ADONewConnection("mysql"); # create a connection

    $conn->debug=1;

    $conn->PConnect("localhost", "admin", "", "test"); # connect to MySQL, testdb

    $rs = $conn->Execute($sql); # Execute the query and get the empty recordset



    $record = array(); # Initialize an array to hold the record data to insert



    # Set the values for the fields in the record

    $record["firstname"] = "Bob";

    $record["lastname"] = "Smith";

    $record["created"] = time();



    # Pass the empty recordset and the array containing the data to insert

    # into the GetInsertSQL function. The function will process the data and return

    # a fully formatted insert sql statement.

    $insertSQL = $conn->GetInsertSQL($rs, $record);



    $conn->Execute($insertSQL); # Insert the record into the database



    #==========================

    # This code tests an update

    $sql = "SELECT * FROM ADOXYZ WHERE id = 1";



    # Select a record to update

    $rs = $conn->Execute($sql); # Execute the query and get the existing record to update

    $record = array(); # Initialize an array to hold the record data to update



    # Set the values for the fields in the record

    $record["firstname"] = "Caroline";

    $record["lastname"] = "Smith"; # Update Caroline's lastname from Miranda to Smith



    # Pass the single record recordset and the array containing the data to update

    # into the GetUpdateSQL function. The function will process the data and return

    # a fully formatted update sql statement with the correct WHERE clause.

    # If the data has not changed, no recordset is returned

    $updateSQL = $conn->GetUpdateSQL($rs, $record);



    $conn->Execute($updateSQL); # Update the record in the database

    $conn->Close();

?>

$ADODB_FORCE_TYPE

The behaviour of GetUpdateSQL() and GetInsertSQL() when converting empty or null PHP variables to SQL is controlled by the global $ADODB_FORCE_TYPE variable. Set it to one of the values below. Default is ADODB_FORCE_VALUE (3):

0 = ignore empty fields. All empty fields in array are ignored.
1 = force null. All empty, php null and string 'null' fields are changed to sql NULL values.
2 = force empty. All empty, php null and string 'null' fields are changed to sql empty '' or 0 values.
3 = force value. Value is left as it is. Php null and string 'null' are set to sql NULL values and 
    empty fields '' are set to empty '' sql values.

define('ADODB_FORCE_IGNORE',0);
define('ADODB_FORCE_NULL',1);
define('ADODB_FORCE_EMPTY',2);
define('ADODB_FORCE_VALUE',3);

Thanks to Niko (nuko#mbnet.fi) for the $ADODB_FORCE_TYPE code.

Prev	Home	Next
Connecting to Multiple Databases at once	Up	Implementing Scrolling with Next and Previous