Done, see if I did it right!

First, thank you for this script and the associated discussion that has followed. There was a version that truncated and then replicated, which I have happily used. However, I have some data in fields that contains line breaks in it. After replicating, the script drops those out.

Original Data:
=========
Key Points:
* Point 1
* Point 2
* Point 3

Converted Data:
==========
Key Points:* Point 1* Point 2* Point 3

I am sure there is a way to make this work, but my PHP is still quite weak and I am learning as I am going. I have appended the script with my scant few changes.

Code:

#/usr/bin/php -q
<?php

/*  NOTE: Product_2 production edition
* This script will replicate an object.  
* It Requires the following
* adodb and the PHPToolkit
* Recommended placement out of the WWW dir, I chose one dir up
* sfdc_new_inc which has the salfes force login info
* Should likely move out the MySQL login info as well
* You have run the replication script
*
* You need to tweak the following lines to insure that you get what you want
* Line: 22,23 --> Change the include pathing
* Line: 29 --> Chance the connect parameters to the MySQL DB
* Line: 33 --> Change the table that you are writing and selecting from
* Line: 47 --> Change the SQL statement to match what you are pulling
*/

ini_set("soap.wsdl_cache_enabled", "0");
error_reporting(E_ALL);
include_once ('/home7/nxtteamc/includes/adodb5/adodb.inc.php');
require_once ('/home7/nxtteamc/includes/sfdc_prod.inc');

$db = ADONewConnection("mysql");


//MySQL database connection information
$db->Connect("localhost", "nxtteamc_admin", "password", "nxtteamc_e107");
$db->debug = true;

//Truncate current database to establish a refresh
$table = "product2";
$sql = "TRUNCATE TABLE $table";
$db->Execute($sql);
if ($db)
{
    echo "table truncated\n";
}
else
{
    echo "Failed";
    var_dump($db->ErrorMsg());
    exit();
}

$soql = "Select ID,NAME,PRODUCTCODE,DESCRIPTION,ISACTIVE,CREATEDDATE,CREATEDBYID,LASTMODIFIEDDATE,LASTMODIFIEDBYID,SYSTEMMODSTAMP,FAMILY,ISDELETED,BOOK_COST__C,CATEGORY__C,COURSE_ID18__C,DESCRIPTION__C,DISPLAY_ON_WEB__C,DURATION__C,END_TIME__C,KEY_TOPICS__C,MAX_STUDENTS__C,NOTES__C,OBJECTIVES__C,ON_SITE_RATE__C,PUBLIC_FEE__C,REQUIRED_SKILLS_EXPERIENCE__C,START_TIME__C,STATUS__C,SUB_CATEGORY__C,SUGGESTED_NEXT_COURSES__C,TARGET_AUDIENCE__C,VANTIVEID__C FROM product2 where Display_On_Web__C=true";

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

exit;

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

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

    $response = $connection->query(($query), $queryOptions);
    //echo '<pre>' . print_r($response, true) . '</pre>';

    //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\n";
            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;
        }
    }
    echo "processed " . $count_records . " records\n";
    return $count_records;
}


function store_in_db($records, $db)
{
    global $table;
    $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);
            $pass_this[$key] = $r->fields->$key;
        }

        $insertSQL = $db->AutoExecute($table, $pass_this, 'INSERT');

        $rows_loaded++;

    }
    return $rows_loaded;
}

?>

are you just trying to get a mysql schema from your org? if so, have you tried my tool > Salesforce MySQL Schema Build Tool ?

I just tried it with my Org and it works perfectly

~Mike

Thanks for taking the time to reply, however, no I already use one of your scripts to get the schema over. It worked just fine.

However, I need to get at the data to display it on an external website of ours. I know I can use sites in force.com, but we already have a web based CMS that I am using and wish to display the data that I grab from our SF site.

So the schema's are already in place and are static enough that I don't need/want to change them every time. The script I posted just replicates data over to the CMS on the web site.

As I mentioned, the only problem that I seem to have is that it doesn't translate over line feeds and my data gets mashed together.

So those line breaks are in a field on a salesforce table. So is it storing them in your local database like this? before you render it to your CMS? That script that you are showing looks like the original replication script?

~Mike

It is the original replication script, other than the changes in the SQL and a authentication. I can't recall doing anything more to it.

Yes, the line breaks are indeed in the Sales Force table.

As far as I can tell they don't replicate to the MYSQL side of things. Everything else does which is amazing!

Yet any cr-lf type character seem to be dropped.

Ok I have confirmed that the replication script definitely does not process newlines. I can add them in through phpMyAdmin and my scripts work fine.

While I can't figure out why, I am trying to simply bypass the problem and convert any newline characters into <br /> for the eventual display on the web.

My php is fairly poor, however, for the life of me I can't figure out how values are getting assigned with the replication script.

This was the line I was keying on:

Code:

$pass_this[$key] = $r->fields->$key

Trying to change it too:

Code:

$pass_this[$key] = replace($r->fields->$key, '\n', '<br />')

When I made that change, I got no records passed to the DB. Any thoughts on this?

Also, what exactly does the => operator do? I know what -> and >= do, but I can't find anywhere in the PHP manual where it talks about =>.

Code:

foreach ($r->fields as $key => $value)

If I read that right, you are trying to assign the fields property of the $r object with the value of $key... but what is the => $value doing?

There maybe another way to do this. I have another type of replication script that i was working on that maybe able to store your data with the line breaks in it from a description field.

Tell me what is the data type in MySQL for this field... I will see what I can come up with my friend. We will get this worked out

~Mike

Thanks! On the MySQL side of things, I have it set up as TEXT in MySQL and TextArea in SF.

I was able to get \n characters when I used the Apex Data Loader. So what I did was download all the records, convert and \n to <br /> and then the replication works just fine. That solved the problem for the time being. Although it means that anyone who wants to enter data into a TextArea type needs to understand HTML which is not the best of solutions.

If you have ideas, I'd love to hear them. However, I am reasonably convinced that it's the way that the PHPToolkit handles things through SOAP. I modified the toolkit to include NL2BR on it's retrieval functions and didn't see any real difference.

I am going to work on this tomorrow morning when I get back to work full time. I think that I have an answer for it, but I have to run a few tests. Will you be available tomorrow to go over this once I post a fix, well a possible fix!!

~Mike