Go Back   Mike Simonds > Salesforce > Salesforce PHP Tutorials

This is a discussion on Salesforce – PHP To MySQL Database Replication Script within the Salesforce PHP Tutorials forums, part of the Salesforce category; Well honestly I have never applied the array_map to the ID field.

Reply
 
LinkBack (5) Thread Tools Rate Thread
  #11  
Old 09-07-2007, 08:43 AM
Administrator
 
Join Date: May 2007
Posts: 273
Send a message via AIM to mike Send a message via MSN to mike Send a message via Yahoo to mike Send a message via Skype™ to mike

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!!
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote

  #12  
Old 09-07-2007, 09:40 AM
Junior Member
 
Join Date: Aug 2007
Posts: 10
Send a message via MSN to slugmandrew
Question Do I need to use stripslashes()?

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.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #13  
Old 09-07-2007, 11:10 AM
Administrator
 
Join Date: May 2007
Posts: 273
Send a message via AIM to mike Send a message via MSN to mike Send a message via Yahoo to mike Send a message via Skype™ to mike

Quote:
Originally Posted by slugmandrew View Post
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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #14  
Old 09-07-2007, 11:22 AM
Administrator
 
Join Date: May 2007
Posts: 273
Send a message via AIM to mike Send a message via MSN to mike Send a message via Yahoo to mike Send a message via Skype™ to mike
Importing Leads

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($etrue) . '';
        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
Attached Files
File Type: txt mysql_leads_import.txt (3.8 KB, 113 views)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #15  
Old 09-10-2007, 09:51 AM
Junior Member
 
Join Date: Aug 2007
Posts: 10
Send a message via MSN to slugmandrew
Lightbulb Importing Objects of any type

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.
Attached Files
File Type: txt leads_import_long.txt (6.0 KB, 108 views)

Last edited by slugmandrew; 09-10-2007 at 09:53 AM.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #16  
Old 09-10-2007, 11:02 AM
Administrator
 
Join Date: May 2007
Posts: 273
Send a message via AIM to mike Send a message via MSN to mike Send a message via Yahoo to mike Send a message via Skype™ to mike

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:
  1. Sign on to Saleforce.com from your own webserver (e.g. localhost)
  2. After signing on a list of your org's objects/tables renders to the screen
  3. Choose the object/table you wish to backup and in another area the fields of that object show up
  4. You choose which fields you want to download into your local database
  5. 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

Last edited by mike; 09-10-2007 at 03:34 PM. Reason: Correct error in posting
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #17  
Old 09-10-2007, 12:49 PM
Junior Member
 
Join Date: Aug 2007
Posts: 10
Send a message via MSN to slugmandrew
Question Something like this?

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)-1i++)
$pass_this['$item[$i]'] = addslashes($r->fields->item[$i]); 
Am I hoping for too much here?

Drew.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #18  
Old 09-10-2007, 02:23 PM
Administrator
 
Join Date: May 2007
Posts: 273
Send a message via AIM to mike Send a message via MSN to mike Send a message via Yahoo to mike Send a message via Skype™ to mike

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?
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #19  
Old 09-11-2007, 07:13 AM
Junior Member
 
Join Date: Aug 2007
Posts: 10
Send a message via MSN to slugmandrew

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.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #20  
Old 09-11-2007, 08:20 AM
Administrator
 
Join Date: May 2007
Posts: 273
Send a message via AIM to mike Send a message via MSN to mike Send a message via Yahoo to mike Send a message via Skype™ to mike

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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply

Tags
mysql data replication, salesforce oracle

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


LinkBacks (?)
LinkBack to this Thread: http://www.mikesimonds.com/salesforce-php-mysql-database-replication-script-t41.html
Posted By For Type Date
Community - Re: data migration??? - Perl, PHP, Python & Ruby Development - Salesforce.com Community This thread Refback 06-29-2008 05:39 AM
Community - Re: data migration??? - Perl, PHP, Python & Ruby Development - Salesforce.com Community This thread Refback 05-30-2008 06:40 PM
rafael27619's bookmarks on del.icio.us This thread Refback 04-08-2008 02:23 PM
Community - data migration??? - Perl, PHP, Python & Ruby Development - Salesforce.com Community This thread Refback 03-27-2008 08:40 PM
API - ApexDevNet This thread Refback 07-16-2007 01:10 PM


All times are GMT -5. The time now is 07:03 PM.



Powered by vBulletin


SEO by vBSEO 3.2.0 ©2008, Crawlability, Inc.

1 2 3 4 5