+ Start a Discussion
richginparichginpa 

PHP Queries

Hi,

 

Hoping someone can assist with VERY basic question.

 

Trying to query my developer instance of Salesforce and store the data in a format so I can import into a local DB.  Having issues looping the data for all the fields as some appear to be object within an object.  For instance, I am querying OpportunityLineItems table fields Opportunity.Name, Opportunity.StageName, Pricebook.EntryName, etc...

 

 

Below is an example of the very basic code and the results.  How do I loop through the results so that all of the data is stored in an array that can then be imported into MySQL?

 

Any help is appreciated.

 

Sample Code:

try {

  $mySforceConnection = new SforceEnterpriseClient();
  $mySoapClient = $mySforceConnection->createConnection(SOAP_CLIENT_BASEDIR.'/enterprise.wsdl.xml');
  $mylogin = $mySforceConnection->login($USERNAME, $PASSWORD);

//Describe sObject
  // print_r($mySforceConnection->describeSObject('OpportunityLineItem'));

  $query = 'SELECT Opportunity.Name, Flight_Start_Date__c, Flight_End_Date__c, ListPrice, Impressions__c, UnitPrice, Quantity, Pricing_Model__c, PricebookEntry.Name, Opportunity.StageName FROM OpportunityLineItem';


  $queryResult = $mySforceConnection->query(($query));
  $records = $queryResult->records;

  foreach ($records as $record) {
  // $sObject = new sObject($record);
  // echo "Id= ".$sObject->Id;
  // echo "OpportunityName =" .$record->fields->Flight_Start_Date__c;
  // echo "Id = ".$sObject->ListPrice;

  print_r($record);
  print_r("<br>");
  }

/*
  echo "***** Login Info*****\n";
  print_r($mylogin);
*/



} catch (Exception $e) {
  echo $mySforceConnection->getLastRequest();
  echo $e->faultstring;
}

 

Results:

tdClass Object ( [Id] => [Flight_End_Date__c] => 2012-01-31 [Flight_Start_Date__c] => 2011-12-01 [Impressions__c] => 4000 [ListPrice] => 0 [Opportunity] => stdClass Object ( [Id] => [Name] => Test Opportunity 3 [StageName] => IO Live ) [PricebookEntry] => stdClass Object ( [Id] => [Name] => Test Product 1 ) [Pricing_Model__c] => CPM [Quantity] => 2 [UnitPrice] => 5 )
stdClass Object ( [Id] => [Flight_End_Date__c] => 2011-05-15 [Flight_Start_Date__c] => 2011-03-01 [Impressions__c] => 500000 [ListPrice] => 0 [Opportunity] => stdClass Object ( [Id] => [Name] => Test Opportunity 5 [StageName] => Active Interest ) [PricebookEntry] => stdClass Object ( [Id] => [Name] => Test Product 2 ) [Pricing_Model__c] => CPM [Quantity] => 1 [UnitPrice] => 15 )
stdClass Object ( [Id] => [Flight_End_Date__c] => 2011-12-31 [Flight_Start_Date__c] => 2011-02-01 [Impressions__c] => 2000000 [ListPrice] => 0 [Opportunity] => stdClass Object ( [Id] => [Name] => Test Opportunity 4 [StageName] => Active Interest ) [PricebookEntry] => stdClass Object ( [Id] => [Name] => Test Product ) [Pricing_Model__c] => CPM [Quantity] => 1 [UnitPrice] => 10 )

 

 

Park Walker (TAGL)Park Walker (TAGL)

If you really need to duplicate the data in your Salesforce database - and it's not clear why you would want to - you should query data from one object at a time and follow the references to get data for associated objects. Basically, this means you cannot include values with dots in them, like Opportunity.Name, as this causes the result to include the secondary object, Opportunity in this case. The OpportunityLineItem has an OpportunityId field which is the link to the parent Opportunity record.

 

If you really want to do it the way you are you will need to examine the type of each field being retrieved and, if it is an object, treat the content in a different way than the simple fields. I wrote up an example of this some time ago and posted it here. The results you get will not be normalized and without extracting the object Ids you will not have an easy path back to the source data.

 

I'd urge you to examine the reason for moving the data to MySQL in the first place and see if you cannot just retrieve the data from Salesforce as needed.

 

Park

jucuzoglujucuzoglu

It sounds to me that your trying to transfer the data but don't really feel the need to synchronize the data.

 

If this is the case, then perhaps rather than doing it the hard way you can do it the long way.

 

Create reports for the primary object and the child objects that your interested in, export those to CSV and then use just about any MYSQL tool such as phpMyAdmin to import the file into the table structures you have created.

 

Granted the code solution is more elegant, however if its not something you need to do very often then I think you may be better off utilizing a more straightforward means of migrating the data,

 

Also there are data migration tools in the AppExchange that will facilitate what your trying to do.