Use PHP's eval() to cut your Salesforce PHP scripts in half

mike · #1 10-18-2007, 11:41 AM

One item that I have always hated when I am writing scripts that deal with large data sets or records was to have to write out each line of code in the scripts for each individual field. So over the past week I was working on the php eval() statement to make my backup scripts much smaller in code, there by saving time writing each script for each object that I wanted backups for.

Here is an example of what I am talking about

Here is query from the Account Object in Salesforce:

PHP Code:


			
$query = "Select Id, Name, Type, ParentId, BillingStreet, BillingCity, 
          BillingState, BillingPostalCode, BillingCountry, ShippingStreet, ShippingCity,
          ShippingState, ShippingPostalCode, ShippingCountry, Phone, Fax, AccountNumber, 
          Website, Sic, Industry, AnnualRevenue, NumberOfEmployees, Ownership,
          TickerSymbol, Description, Rating, Site, OwnerId, CreatedDate, CreatedById, 
          LastModifiedDate, LastModifiedById, SystemModstamp, LastActivityDate, SA_ID__c, 
          FY_CY_Wireless__c, FY_CY_Total_Design__c FROM Account";

Then the way that I had to access each field was to create a local array:

PHP Code:


			
foreach ($accounts as $r)
{
$r = new SObject($r);
$pass_this['id'] = $r->Id;
$pass_this['name'] = addslashes($r->fields->Name);
$pass_this['type'] = addslashes($r->fields->Type);
$pass_this['parentid'] = addslashes($r->fields->ParentId);
$pass_this['billingstreet'] = addslashes($r->fields->BillingStreet);
......
}

And the more individual fields that I wanted to add to the SOQL statement, I had to write code for each field. So what does PHP's eval() statement do. It gives you the ability to create a dynamic array. So no matter how big your SOQL statement is the annoyance of having to write a new line of code is now forgotten and not needed.

So once you return from a function that iterates through all your Salesforce Object data and returns it in an array, let's use $records as an example, you can just use the eval() statement to dynamically create your array:
Here is the full code that inserts records into a MySQL database:

PHP Code:


			
$soql = "Select Id, Name, BillingCity, BillingState From Account";
//Processes the query to get account information from Salesforce
$records = get_records($client, $soql);

if ($records)
{
    echo '<p>There are currently ' . count($records) . ' accounts in Your Organization\'s salesforce</p>';
    $i = 0;
    //Loops through all records which come back from get_accounts function and
    //stores them into an array $pass_this
    foreach ($records as $r)
    {
        $r = new SObject($r);
        $pass_this2['id'] = $r->Id;
        foreach ($r->fields as $key=>$value)
        {
            //THIS IS WHERE EACH FIELD THAT YOU REQUEST IS 
            //BUILT NO MATTER WHAT THE SIZE OF YOUR QUERY MAY BE
            eval("\$pass_this2['$key'] = addslashes(\$r->fields->$key);");
        }
        $i++;


        $fields = implode(",", array_keys($pass_this2));
        $values = implode("','",array_values($pass_this2));

        $sql = $db->execute("INSERT INTO sforce_account (".$fields.") VALUES ('".$values."')");


        if (!$sql)
        {
            echo "Error performing query: " . $db->ErrorMsg() . "<br />";
        }


    }
    $record_count = $i;

    echo '<p>There were ' .$record_count. ' accounts inserted into you MySQL database</p>';

}

So again if your SOQL statement is as simple as querying Salesforce via the API for 4 fields or 30, this will save you lots of time.

if you have any questions please feel free to contact me

~Mike

#2 10-29-2007, 12:34 PM

This is great. I was working on my own method for outputting the values from a given query without explicitly naming the fields. However, I'd like to include values from any given reference fields as well. For example, SELECT Id, firstname, Account.Name from Contact WHERE LastName Like 'Y%'.

This is a part of what I've come up with to handle that.

PHP Code:


			
foreach ($returns as $r) {

        $output .= '<tr>';

    $r = new SObject($r);                                

    //loop through all feilds and return values,

    $field_ary['id'] = $r->Id;

    //does not work for ID, and reference feilds, ie Account Name

    foreach($r->fields as $key => $value) {

        $field_ary[strtolower($key)]=$value;

    }

    if ($r->sobjects) { //loop through related object and return fields, ie Account Name

          foreach($r->sobjects as $s) {

          $c = 0;

                foreach($r->sobjects[$c]->fields as $key => $value) {

            $field_ary[strtolower($key)]=$value;

        }

          $c += 1;

          }

    }

    foreach($field_ary as $key => $value) {

      $output .= '<td>'.$value.'</td>';

    }

        $output .= '</tr>';

}

This works, but what I'd like to do is return the values in a table where the column order is the same as the order that they appear in the query. As I have it written ID will always be in the first column and any referenced fields (ie Account.Name) are processed last. Do you have any suggestions on how this might be accomplished?

#3 10-29-2007, 02:18 PM

The second loop should read;

PHP Code:


			
if ($r->sobjects) { //loop through related object and return fields, ie Account Name

          $c = 0; //moved this variable

          foreach($r->sobjects as $s) {



          foreach($r->sobjects[$c]->fields as $key => $value) {

            //watch out for overwriting fields of same name/key, ie account.name and contact.name

       //append the sobject type to the key name

           $field_ary[strtolower($r->sobjects[$c]->type.'.'.$key)]=$value.$field_id;

        }

          $c += 1;

          }

    }

mike · #4 10-30-2007, 03:45 PM

Quote:

Originally Posted by Eric Santiago

The second loop should read;

PHP Code:


			
if ($r->sobjects) { //loop through related object and return fields, ie Account Name
          $c = 0; //moved this variable
          foreach($r->sobjects as $s) {

          foreach($r->sobjects[$c]->fields as $key => $value) {
            //watch out for overwriting fields of same name/key, ie account.name and contact.name
       //append the sobject type to the key name
           $field_ary[strtolower($r->sobjects[$c]->type.'.'.$key)]=$value.$field_id;
        }
          $c += 1;
          }
    }

Eric have you tried to run your SOQL query in the sforce explorer explorer yet? I have not done that much with relationship queries and I am not sure if you can do an ORDER BY in the SOQL statements.

BUT I have also done some changes to the flexible array to and actually took out the eval statement:

PHP Code:


			
$pass_this['id'] = $r->Id;
foreach ($r->fields as $key => $value)
{
    $pass_this[$key] = addslashes($r->fields->$key);
}

$fields = implode(",", array_keys($pass_this));
$values = implode("','", array_values($pass_this));
$i++;
$sql = $db->execute("INSERT INTO sforce_user (" . $fields . ") VALUES ('" . $values . "')");

murraybiscuit · #5 11-13-2007, 04:56 AM

so maybe you should change the title of this post...

mike · #6 11-13-2007, 06:58 AM

Why would I want to change the title of this post/thread ? The original thread title and example of using the eval() statement works. I will change it if you have a valid reason