Create Your own UPSERT script to Salesforce using PHP

mike · #1 11-30-2007, 08:15 AM

For the past week or two I have been working on and testing a new feature that is being implemented in our organization that basically allows me to create an "upsert" script, but actually performing and insert and update just using IF statements.

This is how I do it, I am sure there are other ways, but this works for me and the code is sound. The example that I am going to use is the AccoutTeamMember Object in Salesforce:

Code:

Select ID, UserId, TeamMemberRole, TeamMemberId from Table

The basic SQL statement above selects the rows that are needed to either perform an update or insert.

**remember I am using ADOdb as my database abstraction layer connecting to Oralce, but the same coding can be used again MySQL or even PostgreSQL.

loop through all the fields and stick them into an array:

PHP Code:


			

$all_fields = array();
$i = 0;
while ($row = $recordSet->FetchRow())
{

    $row = array_map('htmlspecialchars', array_map('stripslashes', $row));
    $all_fields[$i]['ID'] = $row['ID'];
    $all_fields[$i]['USERID'] = $row['USERID'];
    $all_fields[$i]['TEAMMEMBERROLE'] = $row['TEAMMEMBERROLE'];
    $all_fields[$i]['TEAMMEMBERID'] = $row['TEAMMEMBERID'];

    $i++;
}

So now $all_fields has the information that is needed to perform an insert or update.

On the AccountTeamMember object an insert needs three required fields:

AccountId
UserId
TeamMemberRole

To perform an update two fields are required:

Id
TeamMemberRole

So I created two arrays:

PHP Code:


			
$sObjects_insert = array();
$sObjects_update = array();

Then the next step will perform a check to see if the ID field is Null and if it is we will perform an insert, if not, we will perform an update by using two seperate functions.

PHP Code:


			

foreach ($all_fields as $fieldset)
{
    set_time_limit(0);
    $sObject = new sObject();
    $sObject->type = 'AccountTeamMember';
    if ($fieldset['ID'] == null)
    {
        $sObject->fields = array('ACCOUNTID' => $fieldset['ACCOUNTID'], 
                                 'USERID' => $fieldset['USERID'], 
                                 'TEAMMEMBERROLE' => $fieldset['TEAMMEMBERROLE']);
        array_push($sObjects_insert, $sObject);
        echo "insert<br />";
        flush();

    }
    else
    {
        $sObject->fields = array('ID' => $fieldset['ID'], 
                                 'TEAMMEMBERROLE' => $fieldset['TEAMMEMBERROLE']);
        array_push($sObjects_update, $sObject);
        echo "update<br />";
        flush();

    }
}

Now depeding how many records you are processing, you should put this code in place to check to see if the count coming back from the while loop is greater than zero. This is so you do not pass empty records to the function that performs the update or insert, which will cause an error:

PHP Code:


			

if (count($sObjects_insert) > 0)
{
    $success_insert = account_team_insert($client, $sObjects_insert);
}
if (count($sObjects_update) > 0)
{
    $success_update = account_team_update($client, $sObjects_update);
}

That is it! Just use the standard update and insert from the PHPToolkit and you should be fine

Hope this helps someone

~Mike