Salesforce – PHP To MySQL Database Replication Script - Page 2

mike · #11 09-07-2007, 08:43 AM

Well honestly I have never applied the array_map to the ID field. In the example that I have given, that was for a CREATE in salesforce. So if your change works then go for it!!

slugmandrew · #12 09-07-2007, 09:40 AM

Yeah, I think it's a little long-winded, but it'll do for now. Can I also just ask why you used stripslashes in your example? Is it run before htmlspecialchars so that the slashes aren't converted into special characters? I wasn't sure if i should apply it to the account fields as well before updating.

I read that on $_POST variables addslashes() is run automatically if magic_quotes_gpc is on so maybe I should? Ok it seems to work fine. Sorry this is turning into a monologue.

Coming back to the topic of this post, I've now used your SQL replication script to create a copy of my own salesforce instance (developer's account) which has 85 tables, and another created from your complete schema (90 tables). I have also used the account update script to update both SQL databases with all current accounts and it works great!

We mainly use salesforce to manage leads and accounts at the moment, so i'm going to see if i can modify your script to create a local copy of all leads. I actually feel like I am getting closer to having our own copy of our salesforce data!

You have an absolute goldmine here. I reckon this site will be heaving in a year's time. Thanks again.

mike · #13 09-07-2007, 11:10 AM

Quote:

Originally Posted by slugmandrew

Yeah, I think it's a little long-winded, but it'll do for now. Can I also just ask why you used stripslashes in your example? Is it run before htmlspecialchars so that the slashes aren't converted into special characters? I wasn't sure if i should apply it to the account fields as well before updating.

I read that on $_POST variables addslashes() is run automatically if magic_quotes_gpc is on so maybe I should? Ok it seems to work fine. Sorry this is turning into a monologue.

Coming back to the topic of this post, I've now used your SQL replication script to create a copy of my own salesforce instance (developer's account) which has 85 tables, and another created from your complete schema (90 tables). I have also used the account update script to update both SQL databases with all current accounts and it works great!

We mainly use salesforce to manage leads and accounts at the moment, so i'm going to see if i can modify your script to create a local copy of all leads. I actually feel like I am getting closer to having our own copy of our salesforce data!

You have an absolute goldmine here. I reckon this site will be heaving in a year's time. Thanks again.

I used stripslashes on some of the fields coming out of Oracle because they were large text fields, some CLOB's that had lots of stuff that needed to be cleaned. I think that is why I used it, also I applied to the array_map so it would handle all the $rows

So the MySQL tool to create your local schema worked for you okay? Did you see or have any problems with it? If so, please do let me know

mike · #14 09-07-2007, 11:22 AM

You stated that you wanted to get your leads imported, well that is too easy

On my localhost, I have a table which was created by the MySQL toolkit that I released and it created a table called sforce_lead

So check out the this leads import example

I made a few small changes

I changed the function to get_records() and then added a new variable called $query to it:

PHP Code:


			
function get_records($connection, $query)
{
    try {


        $queryOptions = new QueryOptions(500);
        $response = $connection->query(($query), $queryOptions);
        // check count to see if we are done
        if ($response->size > 0) {
            $accounts = $response->records;
            // Cycles through additional responses if the number of records
            // exceeds the batch size
            while (!$response->done) {
                set_time_limit(100);
                $response = $connection->queryMore($response->queryLocator);
                $accounts = array_merge($accounts, $response->records);
            }
        }

        return $accounts;
    }
    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
        echo '' . print_r($e, true) . '';
        return false;
        exit;
    }
}

this is done so you can pass it any SOQL (look at $soql) and can process any object from salesforce

You just have to change the MySQL table information and add any fields to the $pass_this array

I just tested this with my developer account and imported 8 records into my local dB.

Try it out and also look at a nifty thing that I was taught

You can add as many fields as you wish, but if you processes the $fields and $values, you can add as many fields as you wish to the $pass_this array and the following code will insert them:

PHP Code:


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

Test it and let me know what you think

slugmandrew · #15 09-10-2007, 09:51 AM

Mike,

Thanks for this. I had already modified your account import script to deal with leads and it seems to work well. I'll post the modified code to show you how I changed it.

It was quite laborious to create though, as the group of fields had to be modified in 3 different places. Your script seems to have a nice solution to having to write the select query and the insert query separately, but i was wondering if we could make it even more modular and so it could be used to update other salesforce elements as well.

Basically I was wondering if we can use a single array of the field names to:

a) build the select query ($soql)
b) collect the field values from salesforce (populate $pass_this)
c) build the insert query ($sql)?

If this could be accomplished we could pass any array of fields into a function that could perform an update for any object. That function could then call get_records with the $soql query it has constructed. Then it just means creating an array for each object instead of an entire script.

I'm aware that the SOQL query must use UpperCase names but as sql queries are not case sensitive we could use the same array for both.

Let me know what you think. Maybe i'm way out and this isn't even possible.

mike · #16 09-10-2007, 11:02 AM

Quote:

Basically I was wondering if we can use a single array of the field names to:

a) build the select query ($soql)
b) collect the field values from salesforce (populate $pass_this)
c) build the insert query ($sql)?

If this could be accomplished we could pass any array of fields into a function that could perform an update for any object. That function could then call get_records with the $soql query it has constructed. Then it just means creating an array for each object instead of an entire script.

I'm aware that the SOQL query must use UpperCase names but as sql queries are not case sensitive we could use the same array for both.

Let me know what you think. Maybe i'm way out and this isn't even possible.

Well good I am glad that you were able to modify one of my examples and get it to work, lol

to me before I made the change in the SQL insert statement, it really seemed to be drawn out and repetitive by adding the fields here and there. With the change:

PHP Code:


			

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

that I mentioned before, it really cuts down since array_keys and array_fields does all the work for you on the exact fields that you have added to the foreach() loop

PHP Code:


			
foreach ($leads as $r) 
{
    $r = new SObject($r);
    //Add fields here that you want to pass into the database, I only added two or three
    $pass_this['id'] = $r->Id;
    
    //don't forget to clean fields addslashes prior to inserting data into MySQL DB
    $pass_this['lastname'] = addslashes($r->fields->LastName);
    $pass_this['firstname'] = addslashes($r->fields->FirstName);
    $pass_this['email'] = addslashes($r->fields->Email);
    
    ........
}

I am working on this, and tell me if it is what you are asking for:

Sign on to Saleforce.com from your own webserver (e.g. localhost)
After signing on a list of your org's objects/tables renders to the screen
Choose the object/table you wish to backup and in another area the fields of that object show up
You choose which fields you want to download into your local database
Click on "submit" and the data replication is performed

That sounds like what you are looking for, or maybe not that detailed

let me know man

~Mike

slugmandrew · #17 09-10-2007, 12:49 PM

Yeah, that's the kind of end product I was imagining. Sounds like the perfect solution.

Basically at the moment i want to pass an array of values and an object type to a function instead of hand-coding the queries and the $pass_this array.

I've rewritten the first chunk of your revised script so that it can create the $soql query using all items in the $item array then the $type variable:

PHP Code:


			
$item = array("Id", "IsDeleted", "MasterRecordId", "LastName", "FirstName", "Salutation", "Name", "Title", "Company", 
         "Street", "City", "State", "PostalCode", "Country", "Phone", "MobilePhone", "Fax", "Email", "Website", "Description", 
         "LeadSource", "Status", "Industry", "Rating", "AnnualRevenue", "NumberOfEmployees", "OwnerId", "IsConverted", 
         "ConvertedDate", "ConvertedAccountId", "ConvertedContactId", "ConvertedOpportunityId", "IsUnreadByOwner",      
         "CreatedDate", "CreatedById", "LastModifiedDate", "LastModifiedById", "SystemModstamp", "LastActivityDate", 
         "SICCode__c", "Primary__c", "NumberofLocations__c", "ProductInterest__c", "CurrentGenerators__c");

$type = "Lead";

//creates your Salesforce SOQL statement

$arrlen = count($item);
$soql = "Select ";
for($i=0; $i<=$arrlen-1; $i++)
{
    $soql .= $item[$i];
    if($i<=$arrlen-2)
    {
        $soql .= ", ";
    }
}

$soql .= " FROM " . $type;

//echo $soql;

//Processes the query to get account information from Salesforce
$leads = get_records($client, $soql);

This could easily be a function of it's own.

I have stored the type of the object in a separate variable so it can be added to the end of $soql

The array_keys and array_values are great like you say, but could we also add all of the items to $pass_this using values stored in an array and looping through rather than hand-coding each line?

Maybe make a loop something like this inside the foreach loop to create the $pass_this array:

PHP Code:


			
for($i=0; $i<=count($item)-1; i++)
$pass_this['$item[$i]'] = addslashes($r->fields->item[$i]);

Am I hoping for too much here?

Drew.

mike · #18 09-10-2007, 02:23 PM

have you ever used describeglobal and/or describeSobject? You could get the fields that way

Ur stuff looks good, maybe we could work on this together

Also are you going to dreamforce?

slugmandrew · #19 09-11-2007, 07:13 AM

I was playing with describeglobal() the other day but I've only used it to retrieve and display data. I have just been looking at your FieldInfo() function as well, which looks very useful with describeglobal().

Yeah working together sounds great. We may have slightly different long-term goals but i'm sure we can help each other out a lot on the way. Maybe we can both explain a bit more about what we have done and where we are going with this?

I don't think I can make it to Dreamforce. I live in London so it's a long way to go! Maybe if they do something here i'll go. I went to a CRM success one a while back but that was when we were just starting out with salesforce. We know a lot more about salesforce now so it would be useful. Doesn't look like they're coming to the UK for a while though.

mike · #20 09-11-2007, 08:20 AM

Well to give you a short synopsis of what I am trying to achieve here is rather simple my friend and that is to help people with what I have learned and what works when working with Salesforce.com and PHP over the API.

See one thing that I noticed is when I first started working with the API that there was not enough information or examples in PHP besides the PHP toolkit and I just wanted to pass on my knowledge. I am still learning PHP as I go, using new functions and new ideas on how to do stuff. I am a firm believer in not "reinventing the wheel" when it comes to code. There are so many PHP developers on the net that share their ideas and like to help, I just wanted to join that crowd, that is all.

My goals? None really, just trying to help that is all.... and do my job (which pays the bills)

Anyway that is what I am about

~Mike