Shareasale Datafeeds

Getting the Data Feeds

Once a day, a cron job is executed which runs a PHP script to download the affilite data feeds and load the information into a MySQL database.

The script which downloads the datafeeds looks something like:

function getIDs()
 {
  echo "Getting Directory Names<p>"; flush();

  $ftp_server = "datafeeds.shareasale.com";
  $ftp_user_name = "username";
  $ftp_user_pass = "password";

  // set up basic connection
  $conn_id = ftp_connect($ftp_server);

  // login with username and password
  $login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

  $contents = ftp_nlist($conn_id, "/");

  ftp_close($conn_id);

  return $contents;
 }

function getFile($local, $remote, $file, $max_age)
 {
  $okay = false;
  echo "Getting $file<br>"; flush();

  $local_file = $local . $file;
  $server_file = $remote . $file;

  if (file_exists($local_file))
   {
    if ((time() - filectime($local_file)) < ($max_age * 24 * 60 * 60)) // less than $max_age Days old?
      {
       echo "File exists and is less than $max_age day(s) old.<p>"; flush();
       return $okay;
      }
   }

  @unlink($local_file);

  $ftp_server = "datafeeds.shareasale.com";
  $ftp_user_name = "username";
  $ftp_user_pass = "password";

  // set up basic connection
  $conn_id = ftp_connect($ftp_server);

  // login with username and password
  $login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

  // try to download $server_file and save to $local_file
  if (ftp_get($conn_id, $local_file, $server_file, FTP_BINARY)) 
     {
      echo "Successfully written to $local_file<p>"; flush();
      $okay = true;
     } 
   else 
     {
      echo "There was a problem<p>"; flush();
    }
  
  // close the connection
  ftp_close($conn_id);
  return $okay;
 }

function processFile($path, $file, $merchant)
 {
  echo "Processing Merchant $merchant<br>"; flush();
  doQuery("DELETE FROM shareasale.products WHERE MerchantID = $merchant");

  $handle = fopen($path . $file, "r"); 
  while ($handle !== false && !feof($handle))
   {
    $line = fgets($handle, 9999); 
    list ($ProductID, $Name, $MerchantID, $Merchant, $Link, $Thumbnail, 
          $BigImage, $Price, $RetailPrice, $Category, $SubCategory, 
          $Description, $Custom1, $Custom2, $Custom3, $Custom4, $Custom5, 
          $LastUpdated) = explode('|', $line);
    $ProductID = addslashes($ProductID);
    $Name = addslashes($Name);
    $MerchantID = addslashes($MerchantID);
    $Merchant = addslashes($Merchant);
    $Link = str_replace("YOURUSERID", "121021", $Link); // use your ID
    $Link = addslashes($Link);
    $Thumbnail = addslashes($Thumbnail); if ($Thumbnail == "") $Thumbnail = "images/empty.gif";
    $BigImage = addslashes($BigImage);  if ($BigImage == "") $BigImage = "images/empty.gif";
    $Price = addslashes($Price); if ($Price == "") $Price = 0.0;
    $RetailPrice = addslashes($RetailPrice); if ($RetailPrice == "") $RetailPrice = $Price;
    $Category = addslashes($Category);
    $SubCategory = addslashes($SubCategory);
    $Description = addslashes($Description);
    $Custom1 = addslashes($Custom1);
    $Custom2 = addslashes($Custom2);
    $Custom3 = addslashes($Custom3);
    $Custom4 = addslashes($Custom4);
    $Custom5 = addslashes($Custom5);
    $LastUpdated = addslashes($LastUpdated); if ($LastUpdated == "") $LastUpdated = date('Y-m-d');
    if ($Merchant <> "" && $Price > 0 && $Link <> "")
      {
       echo $Merchant . " - " . $Name . "<br>"; flush();
       doQuery("INSERT INTO shareasale.products (ProductID,Name,MerchantID,Merchant,Link,Thumbnail," .
               "BigImage,Price,RetailPrice,Category,SubCategory,Description,Custom1,Custom2,Custom3," .
               "Custom4,Custom5,LastUpdated) VALUES ('$ProductID', '$Name', '$MerchantID', " .
               "'$Merchant', '$Link', '$Thumbnail', '$BigImage', '$Price', '$RetailPrice', " .
               "'$Category', '$SubCategory', '$Description', '$Custom1', '$Custom2', '$Custom3'," .
               " '$Custom4', '$Custom5', '$LastUpdated')");
      }
   }
 }

# Open the Database
mysql_connect("dbhost", "dbusername", "dbpassword");
@mysql_select_db("shareasale") or die ("Unable to select database");
   
set_time_limit(60000);

echo "Getting data feeds from shareasale.com.<p>"; flush();

$filepath = "/path/to/download/folder/";

$ids = getIDs();

echo "IDs: ";
print_r ($ids);
echo "<br>";

foreach ($ids as $id)
 {
  if (is_numeric($id))
    {
     echo "ID is $id<br>"; flush();
     if (getFile($filepath, "/$id/", "$id.txt", 7))
       {
        processFile($filepath,  "$id.txt", $id);
       } 
    }
  }

echo "Done.<br>"; flush();

Jump down to the bottom to see that the first thing the script does is call getIDs. The getIDs method uses FTP to get the names of the directories from Shareasale's FTP server.

For each directory, the script then checks if the directory's name is numeric. The datafeed folders are labeled with the numeric ID of the merchant.

If the directory's name is numeric, the getFile methods is called. It compares the dates of the last downloaded file with the file on Shareasale's server. If the last downloaded file is too old, then the file from the server is downloaded and the processFile method is called to read the file into the database.

I used a simple table with fields matching those in the datafeed.

Here is the SQL code I used to create the products table:

CREATE TABLE `products` (
  `Product_ID` int(20) NOT NULL auto_increment,
  `ProductID` varchar(32) character set latin1 NOT NULL default '',
  `Name` varchar(255) character set latin1 NOT NULL default '',
  `MerchantID` int(20) NOT NULL default '0',
  `Merchant` varchar(255) character set latin1 NOT NULL default '',
  `Link` varchar(255) character set latin1 NOT NULL default '',
  `Thumbnail` varchar(255) character set latin1 NOT NULL default '',
  `BigImage` varchar(255) character set latin1 NOT NULL default '',
  `Price` decimal(10,2) NOT NULL default '0.00',
  `RetailPrice` decimal(10,2) NOT NULL default '0.00',
  `Category` varchar(255) character set latin1 NOT NULL default '',
  `SubCategory` varchar(255) character set latin1 NOT NULL default '',
  `Description` text character set latin1 NOT NULL,
  `Custom1` text character set latin1 NOT NULL,
  `Custom2` text character set latin1 NOT NULL,
  `Custom3` text character set latin1 NOT NULL,
  `Custom4` text character set latin1 NOT NULL,
  `Custom5` text character set latin1 NOT NULL,
  `LastUpdated` date NOT NULL default '2001-01-01',
  PRIMARY KEY  (`Product_ID`),
  KEY `ProductID` (`ProductID`),
  KEY `Category` (`Category`),
  KEY `SubCategory` (`SubCategory`),
  KEY `MerchantID` (`MerchantID`),
  KEY `Merchant` (`Merchant`),
  FULLTEXT KEY `SearchIndex` (`Name`,`Description`,`Merchant`)
);