Salesforce – PHP To MySQL Database Replication Script

mike · #1 07-16-2007, 08:34 AM

Over the past few weeks I have been writing tutorials, giving out code snippets, and data base schema's trying to help PHP developers, that work with Salesforce, some idea on how to build a local database replication system so that an organization can use their salesforce data with their own systems. Today I am releasing the first of a series of scripts that can be used as is or modified that will create a copy of your data in a MySQL database. I am starting this series on the Salesforce Account Object / Table.

I have successfully created PHP scripts which export my organizations data from salesforce into Oracle 10g. I know that many organizations use MySQL (since it is open source) and very stable. I have not seen any examples of exporting data from Salesforce into MySQL so I thought I would try and help out a little.

Attached is a schema for the account object / table for MySQL, also attached is complete working export script that will export the account object from salesforce and store it in MySQL, given the schema that I am providing. To run this script you have to have the following:

PHP 5
ADOdb database abstraction layer / class
Your salesforce username and password
MySQL connection strings (password, user, and database name)

In this example you have to change line 14 of the script with your MySQL account settings:

PHP Code:


$db->Connect("localhost", "Username", "password", "TargetDatabaseName");

I also added a new part to the script that allows for the truncation of the local MySQL table. I use this because the data coming in is really a data dump and this will also eliminate duplicates in your data. Someone asked me why I also do not set a Primary key on this or other tables when working with salesforce. Well if you try and set such a key, let's say on the Id field and you try and run this script on a weekly basis without a truncate, you will not be able to insert data into the table because it will error on inserting on a primary key. I have tested this and it will error, I assure you.

You can comment out the code if you wish, look around line 19-21:

PHP Code:


$sql = "TRUNCATE TABLE sforce_account";
if ($db->Execute($sql))
echo "Salesforce Account Table Truncated
";

In order to get this account table replication working, I suggest this:

Open the account SQL file and import it into your database or test database using phpmyadmin or some other data base tool.

Open and edit the attached PHP script and change the MySQL database settings and your salesforce connection (user and pass)

Check the paths in the PHP script (see line 6 - 8):

PHP Code:


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

Then just run the script and once it is complete, compare the number of records you have in your instance of Salesforce.com to the record count of MySQL:

Code:

SELECT count( * ) FROM sforce_account

The script already performs a count of your accounts (see line 37):

PHP Code:


echo 'There are currently ' . count($accounts) . ' accounts:';

I hope this works for you and if you have any questions, please feel free to contact me

Thanks,
Mike

Please remember that while this and other scripts on my site work in my environment, they are just examples. Use of these scripts and tutorials are at the risk of whomever downloads and uses them as is I assume no risk what so ever.

slugmandrew · #2 08-24-2007, 09:28 AM

Hi,

I stumbled across your site yesterday while researching the salesforce.com API. Some of the stuff you have here is really excellent and definitely much needed as there is so little on this subject on the web so far. (from what I've found)

I'm currently in the process of learning php and MySQL and have built a simple database for use on our site. Then I started to think about exchanging data between salesforce and our own db so that customer leads, accounts and user information could be shared seamlessly.

One problem I have is that I have never come across ADOdb before so this is somewhat of a stumbling block for me as I am only just getting to grips with MySQL. Is there any chance you will do a replication script using plain MySQL statements? Even just some basic examples would be great because I'm finding it difficult to understand what parts require ADOdb and which parts don't.

Keep up the good work.

Slugmandrew

mike · #3 08-24-2007, 03:40 PM

Originally Posted by slugmandrew

Hi,

I stumbled across your site yesterday while researching the salesforce.com API. Some of the stuff you have here is really excellent and definitely much needed as there is so little on this subject on the web so far. (from what I've found)

I'm currently in the process of learning php and MySQL and have built a simple database for use on our site. Then I started to think about exchanging data between salesforce and our own db so that customer leads, accounts and user information could be shared seamlessly.

One problem I have is that I have never come across ADOdb before so this is somewhat of a stumbling block for me as I am only just getting to grips with MySQL. Is there any chance you will do a replication script using plain MySQL statements? Even just some basic examples would be great because I'm finding it difficult to understand what parts require ADOdb and which parts don't.

Keep up the good work.

Slugmandrew

You should really start using ADOdb for your connections, but if you do not want to do it that way I can understand. I will try and get some sort of example on what you are looking for in the next day or so, but cannot promise anything.

If you download some of my examples and ADOdb and try it, it should work out of the box

Sorry I have been in Hawaii on vacation and have not had access to the Internet what so ever

~Mike

slugmandrew · #4 08-29-2007, 11:30 AM

Mike,

Thanks for your advice. Since posting the last message i've had a bit of a read about ADOdb and it doesn't seem that hard to learn and may come in useful in the future so i will give it a go. What the hell, i'm neck-deep in new code anyway

I have now got my installs of everything working ok and I have tested with the sample login script with the php toolkit, but i'm getting an error when i try to update an account that has an ampersand in it's name. I posted on the ADN board here. If anyone more experienced than me can help i'd appreciate it.

Thanks,

Slugmandrew

mike · #5 08-30-2007, 10:48 AM

Are you having problems with the Ampersands in the samples from the PHP Toolkit? Let me know, I may have a solution

If you open the edit.php from the samples and search for this line (around line number 56):

PHP Code:


$name = $_POST['AccountName'];

change it to:

PHP Code:


$name = htmlspecialchars($_POST['AccountName']);

that will convert the & to & so it can be used over SOAP(XML format)

Try that and let me know if that works

~Mike

slugmandrew · #6 08-31-2007, 04:20 AM

Your change works great! Thanks so much.

Yes, it was the samples in the toolkit. Strange that I found a post about it on the ADN but there was no solution there. The guy who had the problem before said he used a php function to replace any & with & but that seemed a bit like re-inventing the wheel to me.

Just one question, I'm a bit lost as to why SF would give out developer accounts with demo account names full of ampersands when the php toolkit doesn't convert them properly in the code samples?!? Any ideas why, or is it something i've done? This doesn't seem to be a common problem.

Thanks again.

mike · #7 08-31-2007, 08:16 AM

I have found that you will run across simple errors as this from time to time. I also came across some errors in the Toolkit when I started to use it. Believe me it is not done intentional or is not something that you have done. Nick Tran is the only one at Salesforce that works on the toolkit and is always looking for people to help correct items such as this. What I would do is post your problem on the forums or maybe even the solution and then he will make the change, that is what I do.

You should also add htmlspecialchars to any field, except the Id field, that you will be sending to SForce in the future. I did not know this at one time but started to receive the same errors that you did.

One way to accomplish this to your whole array at one time is to use a php function called <a href="http://us3.php.net/array_map">array_map</a>

Look at this example:

PHP Code:


while ($row = $recordSet->FetchRow())
{
                $row = array_map('htmlspecialchars', array_map('stripslashes', $row));

                $all_fields[$i]['ISACTIVE']  =  $row['ISACTIVE'];
                $all_fields[$i]['PRICEBOOK2ID']  =  $row['PRICEBOOK2ID'];
                $all_fields[$i]['PRODUCT2ID']  =  $row['PRODUCT2ID'];
                $all_fields[$i]['UNITPRICE']  =  $row['UNITPRICE'];
                $all_fields[$i]['USESTANDARDPRICE']  =  $row['USESTANDARDPRICE'];
                $i++;
}

The first line inside the While loop:

PHP Code:


$row = array_map('htmlspecialchars', array_map('stripslashes', $row));

applies this to all rows, all fields. so if you have to remove, change, or add any fields to the array, you still apply the htmlspecialchars to the affected rows

Hope this helps

~Mike

dfa327 · #8 08-31-2007, 09:37 PM

Hi Mike,

I like your site. Well done

Now, can you kindly tell me how I can get the mysql equivalent table generation for the Account and Case tables? I saw your account sql, but I wanted to know how you got it. I'm looking to do db backups of those to mysql. I'd appreciate whatever you can to help educate me

Thanks,
David

mike · #9 09-01-2007, 09:50 AM

David

To get those tables and all your other objects to tables, Have you tried this?

Salesforce PHP MySQL Database Replication Tool

slugmandrew · #10 09-07-2007, 07:02 AM

Mike,

Thanks for your help yet again. This looks like a useful function. At first I couldn't really work out how to use it but I think I have it now.

So in your previous example, you are taking each row as an array, $row, using array_map to stripslashes and add htmlspecialchars, and then passing the 'cleaned' array back into $row itself?

It took me a while to work out how to change this code:

PHP Code:


if (isset($_POST['doUpdate'])) 
  {
    
    $sfid = $_POST['sfid'];
    $ct = htmlspecialchars($_POST['City']);
    $st = htmlspecialchars($_POST['State']);
    $phone = htmlspecialchars($_POST['Phone']);
    $fax = htmlspecialchars($_POST['Fax']);
    $name = htmlspecialchars($_POST['AccountName']);  // added 'specialchars' to remove &
    
    $fieldsToUpdate = array('Id' => $sfid,
                            'Name'=>$name,
                            'BillingCity'=>$ct,
                            'BillingState'=>$st,
                            'Phone'=>$phone,
                            'Fax'=>$fax);  // create array from posted variables
    
    $sObject = new SObject();  // declare new sObject
    $sObject->fields = $fieldsToUpdate;  // set fields of new sObject
    $sObject->type = 'Account';  // set type to account
    $acct = $mySforceConnection->update(array ($sObject));  // update sObject

    header('Location: welcome.php');   // redirect to welcome.php
  }

into something that used array_map on the last 5 values but not on the id.
I saw some examples that declared an array and then used array_map to pass the values into another array, so I came up with this:

PHP Code:



  if (isset($_POST['doUpdate'])) 
  {
    $sfid = $_POST['sfid'];
    $ct = $_POST['City'];
    $st = $_POST['State'];
    $phone = $_POST['Phone'];
    $fax = $_POST['Fax'];
    $name = $_POST['AccountName'];  
    
    $fieldsToClean = array('Id'=>'',
                            'Name'=>$name,
                            'BillingCity'=>$ct,
                            'BillingState'=>$st,
                            'Phone'=>$phone,
                            'Fax'=>$fax);  // create array to clean from posted variables
                            
    $fieldsToUpdate = array_map('htmlspecialchars', $fieldsToClean);   // map the first array using htmlspecialchars, with empty id                    
    $fieldsToUpdate['Id'] = $sfid;  // add the id field
        
    $sObject = new SObject();  // declare new sObject
    $sObject->fields = $fieldsToUpdate;  // set fields of new sObject
    $sObject->type = 'Account';  // set type to account
    $acct = $mySforceConnection->update(array ($sObject));  // update sObject

    header('Location: welcome.php');   // redirect to welcome.php
  }

Declaring the id field before the array_map function was the only way I could get this to work. I tried using array_map on the main 5 first and then adding the id field to the array afterwards, but it didn't update the records correctly. Is this due to the order of items in the array? I thought being an associative array it wouldn't matter that the Id was at the end.

Any feedback is appreciated.