[mobilebadboy]

Since there has been about 10 different threads in the last 2 days asking how to do this, I decided enough is enough. It's worth giving up my code to try and stop the constant asking.

First off, this is code is offered as is. It works for me daily, but I will not guarantee it for anyone else and will not spend all day offering support on it. Uber-coders may notice bad coding, it works, so to me it's good code.

This code will only manage ONE datafeed.

You must have FTP access for the merchant.

PHP and MySQL are required

If you don't care about the explanations, scroll down to
my 2nd post which will contain all the code by itself.


Step One

Pick a merchant, any merchant (that you're affiliated with of course). Get that merchant's ID number (ie: Nature Hills is 4742). Now create a new file named merchant_number.php, 4742.php for example. All the code given will go in this file between opening and closing PHP tags:

PHP Code:

<?

?>

Step Two

The first thing that needs to be setup are a few variables to be called throughout the script. You will need to replace everything but the first one.

PHP Code:

$ftp_server = "datafeeds.shareasale.com"; // Do not change this
$ftp_user_name = "user_name"; // Replace user_name with your SAS login name
$ftp_user_pass = "user_pass"; // Replace user_pass with your SAS password
$source_file = "/number/number.txt"; // Replace both number with the merchant ID
$destination_file = "number.txt"; // Replace number with the Merchant ID
$sasid = "sas_id"; // Replace sas_id with your SAS ID; Can be found at the top of any SAS page

// The following variables make the connection to your database

$dbuser = "db_user"; // Replace db_user with the username used to connect to your database
$dbpass = "db_pass"; // Replace db_pass with the password used to connect to your database
$database = "database"; // Replace database with the name of your database

// The following two variables will be the names of the table(s) in your database
// MAKE SURE THE NAMES YOU CHOOSE DO NOT ALREADY EXIST. THEY WILL BE DROPPED.

$temptable = "merchant_name_temp"; // Replace merchant_name (ie: naturehills_temp)
$maintable = "merchant_name"; // Replace merchant_name (ie: naturehills) 


Step Three

Now it's time to make the FTP connection and download the file to your server. I'm not going to really explain this code much, it doesn't need to be changed. Basically it just tries to connect and will either succeed or fail. If the connection is made it will then attempt to download the file, and will either succeed or fail.

PHP Code:

$conn_id = ftp_connect($ftp_server); 
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass); 

if ((!$conn_id) || (!$login_result)) { 
     echo "FTP connection has failed to $ftp_server as $ftp_user_name.<br />";
     exit; 
} else {
     echo "Connected to $ftp_server successfully as $ftp_user_name.<br />";
}

$download = ftp_get($conn_id, $destination_file, $source_file, FTP_ASCII); 

if (!$download) { 
     echo "FTP download has failed!";
} else {
     echo "Downloaded $source_file from $ftp_server as $destination_file";
}

ftp_close($conn_id); 


Step Four

Assuming all has gone well so far, it's time to get your USER ID into the feed. Again I'm not going to really bother explaining this code line by line. It just opens the file and replaces every instance of YOURUSERID.

PHP Code:

$datafeed = file_get_contents($destination_file); 
$fp = fopen($destination_file, "w"); 
$replace = str_replace("YOURUSERID",$sasid,$datafeed);
fwrite($fp, $replace); 
fclose($fp); 


Step Five

Ok, we have the file downloaded and we have our USER ID set, now we can get this thing into the database.

First let's make the connection.

PHP Code:

mysql_pconnect("localhost",$dbuser,$dbpass);
mysql_select_db($database); 


The following code does not need to be changed. All this code does is make sure the temporary table is gone, it will then create the temporary table (which you named above) and insert the feed into it. When it's all done it will DELETE your main table (that you named above) and rename the temp table to the main table name.

Create $temptable >> Insert Feed >> Delete $maintable >> Rename $temptable to $maintable

PHP Code:

$FeedFile = $destination_file; 
 
mysql_query("drop table $temptable"); 
 
mysql_query("CREATE TABLE $temptable (ProductID int(11) NOT NULL default '0',Name varchar(255) NOT NULL default '',MerchantID varchar(50) NOT NULL default '',Merchant varchar(50) NOT NULL default '',Link text NOT NULL,Thumbnail text NOT NULL,BigImage text NOT NULL,Price varchar(50) NOT NULL default '',RetailPrice varchar(50) NOT NULL default '',Category varchar(50) NOT NULL default '',SubCategory varchar(50) NOT NULL default '',Description longtext NOT NULL,Custom1 text NOT NULL,Custom2 text NOT NULL,Custom3 text NOT NULL,Custom4 text NOT NULL,Custom5 text NOT NULL,LastUpdated varchar(100) NOT NULL default '',status varchar(50) NOT NULL default '', PRIMARY KEY (ProductID))") or die(mysql_error());
 
$feed = fopen($FeedFile, 'r');
$rowNum = 0; 

while($data = fgetcsv($feed, 50000, "|")){ 
 
   $ProductID = addslashes($data[0]); 
   $Name = addslashes($data[1]); 
   $MerchantID = addslashes($data[2]); 
   $Merchant = addslashes($data[3]); 
   $Link = addslashes($data[4]); 
   $Thumbnail = addslashes($data[5]); 
   $BigImage = addslashes($data[6]); 
   $Price = addslashes($data[7]); 
   $RetailPrice = addslashes($data[8]); 
   $Category = addslashes($data[9]); 
   $SubCategory = addslashes($data[10]); 
   $Description = addslashes($data[11]); 
   $Custom1 = addslashes($data[12]); 
   $Custom2 = addslashes($data[13]); 
   $Custom3 = addslashes($data[14]); 
   $Custom4 = addslashes($data[15]); 
   $Custom5 = addslashes($data[16]); 
   $LastUpdated = addslashes($data[17]); 
   $status = addslashes($data[18]); 

   $sql = mysql_query("insert into $temptable (ProductID, Name, MerchantID, Merchant, Link, Thumbnail, BigImage, Price, RetailPrice, Category, SubCategory, Description, Custom1, Custom2, Custom3, Custom4, Custom5, LastUpdated, status) values ('$ProductID', '$Name', '$MerchantID', '$Merchant', '$Link', '$Thumbnail', '$BigImage', '$Price', '$RetailPrice', '$Category', '$SubCategory', '$Description', '$Custom1', '$Custom2', '$Custom3', '$Custom4', '$Custom5', '$LastUpdated', '$status')") or die(mysql_error()); 
   $rowNum++; 
}

mysql_query("drop table $maintable"); 
mysql_query("ALTER TABLE $temptable RENAME $maintable") or die(mysql_error()); 
fclose($feed); 


Step Six

Create a new folder/directory on your server called ftp. Upload this file there. Make sure the folder is writeable (that's up to you to figure out how to do). This script will download the feed to that folder.

Run the file from your browser, yoursite.com/ftp/merchant_number.php

[mobilebadboy]

PHP Code:

<?

$ftp_server = "datafeeds.shareasale.com"; // Do not change this
$ftp_user_name = "user_name"; // Replace user_name with your SAS login name
$ftp_user_pass = "user_pass"; // Replace user_pass with your SAS password
$source_file = "/number/number.txt"; // Replace both number with the merchant ID
$destination_file = "number.txt"; // Replace number with the Mechant ID
$sasid = "sas_id"; // Replace sas_id with your SAS ID; Can be found at the top of any SAS page

// The following variables make the connection to your database

$dbuser = "db_user"; // Replace db_user with the username used to connect to your database
$dbpass = "db_pass"; // Replace db_pass with the password used to connect to your database
$database = "database"; // Replace database with the name of your database

// The following two variables will be the names of the table(s) in your database
// MAKE SURE THE NAMES YOU CHOOSE DO NOT ALREADY EXIST. THEY WILL BE DROPPED.

$temptable = "merchant_name_temp"; // Replace merchant_name (ie: naturehills_temp)
$maintable = "merchant_name"; // Replace merchant_name (ie: naturehills)

$conn_id = ftp_connect($ftp_server); 
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass); 

if ((!$conn_id) || (!$login_result)) { 
     echo "FTP connection has failed to $ftp_server as $ftp_user_name.<br />";
     exit; 
} else {
     echo "Connected to $ftp_server successfully as $ftp_user_name.<br />";
}

$download = ftp_get($conn_id, $destination_file, $source_file, FTP_ASCII); 

if (!$download) { 
     echo "FTP download has failed!";
} else {
     echo "Downloaded $source_file from $ftp_server as $destination_file";
}

ftp_close($conn_id); 

$datafeed = file_get_contents($destination_file); 
$fp = fopen($destination_file, "w"); 
$replace = str_replace("YOURUSERID",$sasid,$datafeed);
fwrite($fp, $replace); 
fclose($fp); 

mysql_pconnect("localhost",$dbuser,$dbpass);
mysql_select_db($database);

$FeedFile = $destination_file; 
 
mysql_query("drop table $temptable"); 
 
mysql_query("CREATE TABLE $temptable (ProductID int(11) NOT NULL default '0',Name varchar(255) NOT NULL default '',MerchantID varchar(50) NOT NULL default '',Merchant varchar(50) NOT NULL default '',Link text NOT NULL,Thumbnail text NOT NULL,BigImage text NOT NULL,Price varchar(50) NOT NULL default '',RetailPrice varchar(50) NOT NULL default '',Category varchar(50) NOT NULL default '',SubCategory varchar(50) NOT NULL default '',Description longtext NOT NULL,Custom1 text NOT NULL,Custom2 text NOT NULL,Custom3 text NOT NULL,Custom4 text NOT NULL,Custom5 text NOT NULL,LastUpdated varchar(100) NOT NULL default '',status varchar(50) NOT NULL default '', PRIMARY KEY (ProductID))") or die(mysql_error());
 
$feed = fopen($FeedFile, 'r');
$rowNum = 0; 

while($data = fgetcsv($feed, 50000, "|")){ 
 
   $ProductID = addslashes($data[0]); 
   $Name = addslashes($data[1]); 
   $MerchantID = addslashes($data[2]); 
   $Merchant = addslashes($data[3]); 
   $Link = addslashes($data[4]); 
   $Thumbnail = addslashes($data[5]); 
   $BigImage = addslashes($data[6]); 
   $Price = addslashes($data[7]); 
   $RetailPrice = addslashes($data[8]); 
   $Category = addslashes($data[9]); 
   $SubCategory = addslashes($data[10]); 
   $Description = addslashes($data[11]); 
   $Custom1 = addslashes($data[12]); 
   $Custom2 = addslashes($data[13]); 
   $Custom3 = addslashes($data[14]); 
   $Custom4 = addslashes($data[15]); 
   $Custom5 = addslashes($data[16]); 
   $LastUpdated = addslashes($data[17]); 
   $status = addslashes($data[18]); 

   $sql = mysql_query("insert into $temptable (ProductID, Name, MerchantID, Merchant, Link, Thumbnail, BigImage, Price, RetailPrice, Category, SubCategory, Description, Custom1, Custom2, Custom3, Custom4, Custom5, LastUpdated, status) values ('$ProductID', '$Name', '$MerchantID', '$Merchant', '$Link', '$Thumbnail', '$BigImage', '$Price', '$RetailPrice', '$Category', '$SubCategory', '$Description', '$Custom1', '$Custom2', '$Custom3', '$Custom4', '$Custom5', '$LastUpdated', '$status')") or die(mysql_error()); 
   $rowNum++; 
}

mysql_query("drop table $maintable"); 
mysql_query("ALTER TABLE $temptable RENAME $maintable") or die(mysql_error()); 
fclose($feed);

?>

[Adrian]

This looks like great useful info. Could someone please give me an broad overview and how to implement this. I'm a bit new to mysql & php.

Perhaps a list of the tools I need to assemble and where things fit in.

I'm also not clear on the purpose of importing the data at all rather than running off of their data bases...isn't that how it is desgined.

[mobilebadboy]

This script, if setup correctly, will download the merchant feed via FTP from ShareASale. It will then import the entire thing into your database, from which you can then pull the products and build a site (although I only deal with getting the feed from SAS and putting it in the database).

Everything in my 2nd post will go in 1 file (Step One in my first post). The rest of the first post just gives a brief idea of what all the code does. All the variables that need to be set (Step Two) are commented.

I'd go back and add some extra info, unfortunately I can't....

I did this first thing when I woke up this morning, so I kind of rushed it.

[Mr. Sal]

mobilebadboy,

You went out of your way by posting a "How to FTP and Import SAS datafeeds" script for free, for anyone that wanted a free good working script.

Your script works good, I know that someone else would probably charge money for a script like that one, and even without the commented part of the code.

Three years ago, if I didn't decided to learn the hard way, I would probably had paid $100.00 for that scrip.

On behalf of all the people that probably have copied your script already (81 Views), and are probably trying it now, but have not say anything yet, I want to say thank you.

Thank you for making that script available for free to anyone that may need it, even if no one will admit it now, I know that many will use it, if not today, maybe one of this days, but eventually one day someone new will find your script as a real time saver.

[mobilebadboy]

Thanks Sal.

I hope people find it useful, even without letting me know.

I did just notice some extraneous code that really isn't necessary (I would've had to pick my original everything furniture script that I never corrected), but it still works even with it. Just a redeclared variable that wasn't needed.

[RH&A]

I was one that posted yesterday in the SaS section asking for recommendations on what script to purchase to import datafeeds into MySql. Mr. Sal replied with a link to a thread that showed how to do it with a CJ feed, and after a couple of hours, I got it to work with a SaS feed. Another poster replied and linked to your thread here and I tried it as well and it worked fine except for one small error at the end, but I wanted to see if anyone posted that error as well. If get this error Duplicate entry '0' for key 1 and it stops the last step where it drops the original table and renames the temp table. I have only tried it with one merchant feed though.

I thanked Mr. Sal and the other posters in that thread but didn't do so here as it was getting late, but no excuse.

Thank you for posting for code. Like I said, my original post was asking for recommendations on which script to purchase.

[waybar]

Thank you for posting the entire code. I am sure a lot of us will find it very useful.

[Rhia7]

I haven't tried this yet, but this certainly will help when I do.

Thank you so much

[Cheesehead]

Will this work with several different merchant's feeds imported into the same database?

[mobilebadboy]

Ric, I will give the code here a test and see if it happens to me. I know all of my current scripts do not produce that error, and I pulled the code directly from them. Although something may have happened in the process of pulling the bits out.

Cheesey, this code will only work with one feed. I mean it will work with any SAS feed but will only handle one. But as long as you change $source_file, $destination_file, $temptable and $maintable then you can use it to import all your feeds.

I currently have one setup for each merchant, although I'm working on a new one that will loop through a list of merchants and grab each and one and import them all in one process so I don't need multiple files.

Although I'm thinking that might be a bit resource intensive.

[RH&A]

I ran your script on another merchant's datafeed this afternoon and it did NOT produce the error. It worked great!

[mobilebadboy]

Good to hear.

I was just coming to grab the code and test it (been running around all day).

[Jorge - JRami]

Shawn, this is a great info for sas affiliates. I often get direct emails from a few asking details on how to do this, but with these 6 steps you have given it's great, thanks.

Would be good if this was also be posted somewhere on sas forum and linked back here perhaps with a small intro post on sas forum, might get lost here after a while. I'll be creating a similar section on our site with this info and link back to here too!

[Rhia7]

It would be great to sticky this information

[writerguy]

All right. Thanks so much, Shawn, for this great "freebie."

Unfortunately, I can't get it to work. Grrrrr.

Yesterday, I had all sorts of problems getting FTPed into the shareasale datafeed site. Today it's working fine for me using my FTP software (FileZilla).

But when I run this script, I get all sorts of errors. Here's what it's showing me, and I don't know enough PHP to understand what's wrong and how to fix it. I copied and pasted the script into a text file and saved it as ftp-bentgear.php, in a folder named "ftp" on my server. I filled in the appropriate variables for my SAS account, my database on the server, etc. Anyway, these are the errors I get. (I've put "*" to block personal info.)


Connected to datafeeds.shareasale.com successfully as ******.

Warning: ftp_get(): Error opening 7371.txt in /home/********/public_html/ftp/ftp-bentgear.php on line 32
FTP download has failed!
Warning: file_get_contents(7371.txt): failed to open stream: No such file or directory in /home/********/public_html/ftp/ftp-bentgear.php on line 42

Warning: fopen(7371.txt): failed to open stream: Permission denied in /home/********/public_html/ftp/ftp-bentgear.php on line 43

Warning: fwrite(): supplied argument is not a valid stream resource in /home/********/public_html/ftp/ftp-bentgear.php on line 45

Warning: fclose(): supplied argument is not a valid stream resource in /home/********/public_html/ftp/ftp-bentgear.php on line 46

Warning: fopen(7371.txt): failed to open stream: No such file or directory in /home/********/public_html/ftp/ftp-bentgear.php on line 57

Warning: fgetcsv(): supplied argument is not a valid stream resource in /home/********/public_html/ftp/ftp-bentgear.php on line 60

Warning: fclose(): supplied argument is not a valid stream resource in /home/********/public_html/ftp/ftp-bentgear.php on line 88

Any suggestions? Help?

Thanks so much!

Gary Speer

[RH&A]

Writerguy:

Make a simple, empty text file with the same name as the feed you are retreiving in the directory on your server where it will overwrite it with the feed it retreives from SaS, and make sure persmissions are set, ie, chmod 777

[Alan Hamilton]

Mobile, what a great gesture of sharing. I was impressed when I saw this thread. That kind of contribution is above and beyond, and I congratulate you dude. Well done!



Alan

[writerguy]

Whoopie! Making that empty text file on the server did the trick.

Now if I can just do some sort of "crash course" in templete design and PHP, I'm good to go.

Seriously, anybody know a good place to google and/or go to to learn what I need about getting data OUT of the tables onto a decent looking website?

(Yeah, I know. That question gets asked a lot around here.)

Thanks so much.

Gary Speer

[Mr. Sal]

Originally Posted by writerguy

Whoopie! Making that empty text file on the server did the trick.

Now if I can just do some sort of "crash course" in templete design and PHP, I'm good to go.

Seriously, anybody know a good place to google and/or go to to learn what I need about getting data OUT of the tables onto a decent looking website?

(Yeah, I know. That question gets asked a lot around here.)

Thanks so much.

Gary Speer

Gary, don't be so lazy!

Here are some ideas.

Join some of Geno's programs here: AM Navigator and then use the Free Dynamic Templates with his programs.

After you use those Free Dynamic Templates with his programs for a while, you may get some ideas on how to make your own templates your way.

In the mean time, you can also be making some money with some of the Geno's programs.

[Meeka]

Mobilebadboy,

So kind of you to share...Many, Many Thanks!!

Meeka

[mobilebadboy]

You're welcome.

As far as creating the empty file, that should not be necessary. I've never had to do that anyway. It could've just been a chmod/permissions thing not allowing the file to be written to the server.

With everyone using so many different options for FTP, there was no easy way for me to explain how to do that and include everyone.

I've thought about making a more advanced system for feed management, downloading, etc. through a control panel, it's just finding the time to do so.

[writerguy]

Originally Posted by mobilebadboy

You're welcome.

As far as creating the empty file, that should not be necessary. I've never had to do that anyway. It could've just been a chmod/permissions thing not allowing the file to be written to the server.

With everyone using so many different options for FTP, there was no easy way for me to explain how to do that and include everyone.

I've thought about making a more advanced system for feed management, downloading, etc. through a control panel, it's just finding the time to do so.

Hey, Shawn,

I used chmod to make the /ftp directory 777, thinking that might eliminate the need for creating an empty text file. I didn't get the error messages I pasted in earlier in this thread. But I DID get these error messages:


Connected to datafeeds.shareasale.com successfully as ******.
Downloaded /9766/9766.txt from datafeeds.shareasale.com as 9766.txt
Warning: fopen() expects at least 2 parameters, 1 given in /home/*******/public_html/ftp/ftp-mplus.php on line 43

Warning: fwrite(): supplied argument is not a valid stream resource in /home/*******/public_html/ftp/ftp-mplus.php on line 45

Warning: fclose(): supplied argument is not a valid stream resource in /home/*******/public_html/ftp/ftp-mplus.php on line 46


Any ideas??

Gary Speer

[mobilebadboy]

Have you changed anything around the "Step Four" code?

[writerguy]

Originally Posted by mobilebadboy

Have you changed anything around the "Step Four" code?

Nope, not to my knowledge. I simply copied and pasted it into a file from the forum here, saved the file as a PHP file (i.e., ftp-mplus.php is the name I gave it since it is for the Mountain Plus datafeed). I uploaded it to a directory I created on my server named "ftp," did chmod to the directory to 777.

I did the same earlier yesterday for another datafeed. When I created an empty TXT file in the ftp directory named the same as that datafeed's TXT file -- it worked without any errors.

If you would have the time and do me a great favor of looking at it, I would be happy to PM or email you the ftp-mplus.php file as an attachment. Just let me know how to contact you.

Gary Speer