Thread: Sample select from Salesforce and insert to MySQL script

Hy, i'm new in salesforce and php and i try to use your script
but after the execution , my sql database has empty lines can you help me ?

PHP Code:

<?php

// Start of code here
error_reporting(E_ALL & ~ E_NOTICE);

$SOAPCLIENT_DIR="./includes/soapclient/";


// Login to salesforce.com

$USERNAME = "XXXXXXXXXXXXXXXXXXX";
$PASSWORD =" XXXXXXXXXXXXXXXXXX";

//this clears out your local PHP WSDL cache incase you may have been performing
//tests against your development account or Sandbox account
ini_set("soap.wsdl_cache_enabled", "0");
//require_once ('SalesForceSettings.php');
require_once ("$SOAPCLIENT_DIR/SforceHeaderOptions.php");
require_once ("$SOAPCLIENT_DIR/SforcePartnerClient.php");

try {
  $mySforceConnection = new SforcePartnerClient();
  $mySoapClient = $mySforceConnection->createConnection($SOAPCLIENT_DIR.'/partner.wsdl.xml');
  $mylogin = $mySforceConnection->login($USERNAME, $PASSWORD);


  $query = "Select Name FROM contact_ecole__c";
  $response = $mySforceConnection->query($query);

  $con = mysql_connect("localhost","root","");
  if (!$con)
  {
    die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("salesforce", $con);

  foreach ($response->records as $record) {
    $insert_row = "Insert into contact_ecole__c VALUES ('$record->Name');";

    mysql_query($insert_row);
  }

mysql_close($con);


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

Thank you

Hi Tom,

I think you need to add the "VALUES" setting in your insert query. (unless Name is the only field, which I doubt it).

its ok
thanks for your help saariko

Tom

So Tom it's working?

Thanks Saariko for helping him out! I appreciate it

Hello Mike

I want a contact form data to be stored in database
http://www.waterfordearlylearning.org/contact_us.html

The action of the page has to to go to salesforce first, then I have to write a code to parse the data or I dont know, how it exactly works. Can you please help me witha sample code. I tried with the following code, but not even a row is being inserted in the database.

<?php

// Start of code here
error_reporting(E_ALL & ~ E_NOTICE);

$SOAPCLIENT_DIR="./includes/soapclient/";


// Login to salesforce.com

$USERNAME = "";
$PASSWORD = "";

//this clears out your local PHP WSDL cache incase you may have been performing
//tests against your development account or Sandbox account
ini_set("soap.wsdl_cache_enabled", "0");
//require_once ('SalesForceSettings.php');
require_once ("$SOAPCLIENT_DIR/SforceHeaderOptions.php");
require_once ("$SOAPCLIENT_DIR/SforcePartnerClient.php");


try {
$mySforceConnection = new SforcePartnerClient();
$mySoapClient = $mySforceConnection->createConnection($SOAPCLIENT_DIR.'/partner.wsdl.xml');
$mylogin = $mySforceConnection->login($USERNAME, $PASSWORD);


$query = "Select First Name,Last Name,Title,School,School District,Phone Number,E-mail Address,Country,State,City,Postal Code FROM contact";
$response = $mySforceConnection->query($query);

$con = mysql_connect("mysql7.e1source.com","econnon_sales ","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("econnon_salesforce", $con);

foreach ($response->records as $record) {
$insert_row = "Insert into contact VALUES ('$record->First Name','$record->Last Name','$record->Title','$record->School','$record->School District','$record->Phone Number','$record->E-mail Address','$record->Country','$record->State','$record->City','$record->Postal Code');";

mysql_query($insert_row);
}

mysql_close($con);


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

Thanks
Neha Goyal

Neha,

You want this form to insert the data into Salesforce and MySQL at the same time? is that what you are attempting to do?

I did not post the code used here in this thread, that was posted by someone else

let me await your reply and I think that I can help you out.

BUT I do not want to leave you unanswered, so I will try and help you out.

first go to > SourceForge.net: ADOdb: Files
and download the ADOdb database abstraction layer class. You will find that it is a lot more flexible and user friendly instead of using the built in MySQL functions in PHP.

I took a look at your form and sure we can get that data into Salesforce, it would not be that hard... Let me see if I Can get that data into a form that would work


~Mike

I see that on the action of that form, you are sending the data (as you stated) right to salesforce using one of their processes.

So the data is being stored in the Lead object in Salesforce? Is that a correct statement?

and then you want to put that data into a MySQL database? at the same time?

Well there is another way to do this and if you are interested, I can help you with that.

Since the form action is going straight to salesforce, I do not see how you would ever get the data into a local array, so I would send the data to that object in Salesforce via PHP instead of using their process. That way you would have the data to use and store in MySQL.


OR

You could leave it as is and create a replication script (there are plenty of examples here) and I will paste one:

PHP Code:

<?php
ini_set("soap.wsdl_cache_enabled","0");


require_once ('./soapclient_new/SforcePartnerClient.php');

/*****************************************************************************
*  This script uses ADODb, you must have ADOdb installed or in a common      *
*  folder to run this script.  The best way to do this is to go to           *
*  http://adodb.sourceforge.net/ and download the latest version and install *
*  it in your include_path in the php.ini file.                              *
******************************************************************************/
require_once ('adodb.inc.php');

$db = NewADOConnection('mysql');


//MySQL database connection information
$db->Connect("localhost","mike","mas4155","salesforce");

//$db->debug = 1;
//Truncate current database to establish a refresh
$sql = "TRUNCATE TABLE sforce_lead";
if ($db->Execute($sql)) echo "<strong>Lead Table Truncated</strong><br />";

//Salesforce Connection information
$wsdl = './soapclient_new/partner.wsdl.xml';
$userName = "me@email.com";
$password = "password";


//setup connection
$client = new SforcePartnerClient();
$client->createConnection($wsdl);
$loginResult = $client->login($userName,$password);

//SOQL query for Object in Salesforce


$soql = "Select 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,ProductInterest__c,Primary__c,CurrentGenerators__c ,NumberofLocations__c FROM Lead";

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

if ($records === false)
{
    mail("masimonds@gmail.com","PHP Lead Export Script  Error","There has been an error in the Lead replication script\nPlease Check Script");
}
echo '<p>There are currently '.$records.' Leads:</p>';

$today = date("F j, Y, g:i a");
$end = 'Lead Import script ended at '.$today.' and inserted '.$records.' into the database';


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

    //Set this to the number of records to process per batch
    //200 is the minimum
    $queryOptions = new QueryOptions(1000);
    $connection->setQueryOptions($queryOptions);

    $response = $connection->query(($query),$queryOptions);

    //for debugging


    if ($response->size > 0)
    {
        $records = $response->records;
        // Cycles through additional responses if the number of records// exceeds the batch size
        $count_records = 0;
        while (!$response->done)
        {
            $records = $response->records;
            set_time_limit(100);
            ini_set("memory_limit","512M");


            //Process curent $records
            $current_count = store_in_db($records,$db);

            if ($current_count === false)
            {
                return false;
            }
            else
            {
                $count_records += $current_count;
            }
            echo "processed ".$count_records." records<br />";
            flush();

            $response = $connection->queryMore($response->queryLocator,$queryOptions);


        }
        set_time_limit(100);
        $records = $response->records;
        //Process curent $records

        //store the last set of records into the database
        $current_count = store_in_db($records,$db);

        if ($current_count === false)
        {
            return false;
        }
        else
        {
            $count_records += $current_count;
        }
    }

    return $count_records;
}


function store_in_db($records,$db)
{
    $record_count = count($records);
    $rows_loaded = 0;

    foreach ($records as $r)
    {
        $pass_this['id'] = $r->Id;
        foreach ($r->fields as $key => $value)
        {
            $pass_this[$key] = addslashes($r->fields->$key);
            if ($value == "")
            {
                unset($pass_this[$key]);
            }
            // change from SFDC datetime format to MySQL format
            if ($key == "CreatedDate" || $key == "LastModifiedDate" || $key == "SystemModstamp")
            {
                $pass_this[$key] = strftime("%Y-%m-%d %H:%M:%S",strtotime($value));
            }
            
        }
        $fields = implode(",",array_keys($pass_this));
        $values = implode("','",array_values($pass_this));

        $query = "INSERT INTO sforce_lead (".$fields.") VALUES ('".$values."')";
        if ($db->Execute($query))
        {
            $rows_loaded++;
        }
        else
        {
            echo $db->ErrorMsg()."<br />";
            return false;
        }
    }
    return $rows_loaded;
}


?>

This will allow you to setup a CRON job and send the data from Salesforce to MySQL as much as you want a day.

Let me know how this works out or what approach you want to do


~Mike

Hello sir,

Thanks for help. I want to know, if I write the above code which you have pasted with appropriate particulars, this will store data of that contact form in MySQL. Please tell me. I am totally new to salesforce.

Neha

No Neha since you have your form going straight into Salesforce using one of their web services, this script above will replicate the data into your MySQL database from the Lead object in Salesforce.

Hope that helps!

the other way you could do it is change the action of your script to send the data to Salesforce via the PHP Tool Kit and then you could insert it into your MySQL database at the same time