After working with Salesforce.com over the past few months, I have been able to constantly improve performing inserts, updates, and upserts using the Force.com Toolkit for PHP. With guidance from David Claiborne and Nick Tran. I have created a full update process for my organizations data. This tutorial will focus on using Oracle as the local database and pushing data to Salesforce.com. I also use AdoDB, which is a database abstraction layer.

One item that I would like to focus on is performing updates/inserts to Salesforce via the upsert command. This really gives an organization much more flexibility to keep data refreshed from an internal data system and your organizations sforce instance.

I will be using the Account Object for this tutorial. First, you must have declared an external id on a custom field either already created or one that is being created. Please understand that an external id has to be one of the following field types:

1) Text
2) Number
3) Email
4) Auto Number

As a sforce administrator, you login to Salesforce.com and go to Setup > App Setup > Accounts > Fields. Then scroll down to Account Custom Fields and Relationships. Either Create a new field that you will be utilizing or edit a existing field which is a valid field type and make it an external field.

Now that you are all set, it is time to write the php script which will perform the update!

In your PHP script set add this line to the top of your script:

ini_set("soap.wsdl_cache_enabled", "0");

Make sure that you include the PHP Tool Kit class scripts to login to Salesforce

require_once ('./includes/soapclient/SforcePartnerClient.php');
require_once ('./includes/soapclient/SforceHeaderOptions.php');

This is the Salesforce Login information that is passed when performing the upsert:


// Salesforce Login information
$wsdl = './includes/soapclient/partner.wsdl.xml';
$userName = "salesforce username";
$password = "salesforce password";

// Process of logging on and getting a salesforce.com session
$client = new SforcePartnerClient();
$client->createConnection($wsdl);
$loginResult = $client->login($userName, $password);

Then perform your loop through the Oracle database to get your records for the upsert to Salesforce.com:

while ($record_count == $number_records)
        {

            // Reset the php execution time to 20 seconds every time we get new
            // records from the database
            set_time_limit(20);


            $sql = "SELECT   id AS ownerid,
                    external_id as \"external_field__c\",
                    address as \"business address\"
                    FROM database_table
                    WHERE ROWCOUNT BETWEEN $starting_record AND ($starting_record + ($number_records-1))
                    ORDER BY ROWCOUNT";

            $recordSet = $conn->Execute($sql);

            $all_fields = array();
            $i = 0;
            while ($row = $recordSet->FetchRow())
            {
                $all_fields[$i]['OWNERID'] = $row['OWNERID'];
                $all_fields[$i]['EXTERNAL FIELD'] = htmlspecialchars(stripslashes(strip_tags($row['EXTERNAL_FIELD__C'])));
                $all_fields[$i]['BILLINGADDRESS'] = htmlspecialchars($row['BILLINGADDRESS']);
                // You can add addtional rows here if needed
                $i++;
            }

            $record_count = $i;

            $sObjects = array();

            foreach ($all_fields as $fieldset)
            {
                $sObject = new sObject();
                $sObject->type = 'Account'; // Salesforce Table or object that you will perform the upsert on
                $sObject->fields = $fieldset;
                array_push($sObjects, $sObject);
            }
            //This passes the client = the login to sales force
            // the $sObjects = data to upsert
            // $file_updated = accounts which are updated
            // $file_created = accounts which are inserted
            // $file_failed =  accounts which failed
            $success = upsert_accounts($client, $sObjects, $file_updated, $file_created, $file_failed);

            // Update the overall counts
            if (is_array($success))
            {
                $accounts_created = $accounts_created + $success[0];
                $accounts_updated = $accounts_updated + $success[1];
                $accounts_failed =  $accounts_failed + $success[2];
            }

            $starting_record = $starting_record + $number_records;

            ob_start();
            $total_record_count = $total_record_count + $record_count;
            echo $total_record_count." records processed.<br />";
            ob_end_flush();
        }

The following function does the heavy lifting which performs the upsert to sforce and updates or inserts the accounts:

function upsert_accounts($client, $sObjects, $file_updated, $file_created, $file_failed)
{
    $accounts_created = 0;
    $accounts_updated = 0;
    $accounts_failed = 0;
    try
    {
        // The upsert process
        $results = $client->upsert("external_field__c", $sObjects);

        $k = 0;

        // This loop processes $result to build the log files
        foreach ($results as $result)
        {
            // Build string from fields in $sObjects array
            // At this point, the record has already been upserted
            // We just need the data for the log file
            // The string is the same, regardless of the result
            $data2 = $sObjects[$k]->fields['NAME'] . ", " . $sObjects[$k]->fields[
                'SA_ID__C'];

            if ($result->success)
            {
                if ($result->created)
                {
                    $accounts_created++;
                    file_put_contents($file_created, $data2 . "\n", FILE_APPEND);
                }
                else
                {
                    $accounts_updated++;
                    file_put_contents($file_updated, $data2 . "\n", FILE_APPEND);
                }
            }
            else
            {
                $accounts_failed++;
                // The errors object also contains fields and status_code
                $errMessage = $result->errors->message;
                file_put_contents($file_failed, $data2 . ", " . $errMessage . "\n", FILE_APPEND);
            }
            $k++;
        }
        // Put the result counts into an array to pass back as the result.
        $success = array();
        array_push($success, $accounts_created, $accounts_updated, $accounts_failed);
        return $success;
        exit;
    }
    catch (exception $e)
    {
        // This is reached if there is a major problem in the data or with
        // the salesforce.com connection. Normal data errors are caught by
        // salesforce.com
         return false;
        exit;
    }
}

I will be writing more tutorials as I test more functionality using PHP, If you have any questions, you can email me at masimonds@gmail.com and be sure to visit My Blog and leave me feedback!

You can view the source code of this tutorial by clicking here