Results 1 to 18 of 18
May 19th, 2010, 06:36 PM #1Inserting Affiliate ID into Link using PHP script
My knowledge of PHP is still pretty weak, so please, those of you who know what you're doing -- be gentle with me.
I have a script that uses FTP to download a datafeed from SAS, then inserts my SAS user ID number in the "Link" field, and finally creates a MySQL table on my server.
I then use a 3-file PHP script to display that table on a datafeed affiliate site.
My problem is this. One of the datafeeds I use (FootballFanatics) has more than 100,000 items in the feed. That means the script that downloads it and creates a table on my server has to go through 100,000+ instances of the "Link" field and replace "YOURUSERID" with my actual user ID number, line by line through the entire feed of 100K+ products.
So -- I find this using a huge amount of my VPS server resources and even timing out. I don't know whether it will exceed my VPS resources and get my account in trouble with my web host.
If I knew enough about what I was doing (), I suspect there's a way I could modify the scripts I use to display the products so that each "Link" would have "YOURUSERID" swapped for my actual user ID as and when I display the product on the website, instead of sapping all my server resources line-by-line when I'm downloading the datafeed and creating the table.
Hope that makes some sort of sense to you PHP gurus out there.
Anyone with a suggestion? Any suggestion that will avoid or overcome the huge task of the line-by-line replacement as the table is created.
FYI -- The script I use to download the SAS df and create the initial table, the script that's so resource intensive, is the one found in this old thread here at ABW:
It is essentially a slightly modified version of the script in post #2 of that thread.
Thanks to anyone who can help.Generate more fake news.
May 19th, 2010, 09:16 PM #2
It's actually the 60MB+, 100k line+ feed that's intensive. It only takes about 6 seconds to do the search/replace on your ID once the feed is imported (5.99666 seconds on a test I just ran on it).
Anyway, you could always use str_replace during display.
$Link = str_replace("YOURUSERID", "111111", $Link);
May 19th, 2010, 09:47 PM #3
May 19th, 2010, 10:10 PM #4
superCool agrees with mr. mobilebadboy. swapping your aff-id is probably the easiest thing your scripts does. what makes you think that is the slow part?
superCool has seen some recent posts about speeding up the database loading steps. that would probably be a better place to look. some scripts also take a break every so-many records or minutes. you might want to look into giving your's a rest every now and then. or breaking it into chunks that can be processed over time.
superCool is just getting into this whole datafeed thing himself, so these are just guesses....actual mileage may vary
good luck writerguy. hope you get it screaming soon
May 20th, 2010, 01:48 AM #5
I'm sure my script is probably not the most efficient either (at least those old versions of it). It's just the size of the feed. [From the same test earlier] it took about 4 minutes to import the feed itself.
You should see my FootballFanatics import script for one site. It's 1100+ lines of code (importing, deleting, spilitting out into multiple new tables, adding columns, inserting my own data, etc). Now that one is intensive.
Actually had to break it into 2 files to keep one single file from taking so long to process.
May 20th, 2010, 09:01 AM #6
Not being a coder, and having gotten to my limits of knowledge regarding modifying and using your freebie script -- I'm probably going to revamp my website and retire the FootballFanatics datafeed that's been the main feature of the site.
Probably going to put together a variety of different things on the site, still keeping some FF stuff via links, PopShops, etc.Generate more fake news.
May 20th, 2010, 09:44 AM #7
Replacing 100k lines shouldn't take down your server. As you're loading the data into the database, do the str_replace and insert into the db.
If you're timing out, add to the top of your script
I've outlined the process at
However, I didn't go the FTP route. This method allows you to upload a zip file.
May 20th, 2010, 12:34 PM #8
May 20th, 2010, 05:01 PM #9
I think I've been requesting smaller feeds that are specific to a sport for about 7 years from Football Fanatics.
If they would only join Avantlink Their datafeeds would no longer be an issue
May 20th, 2010, 06:59 PM #10
Gary, another alternative would be to use "load data". I just ran a quick test and it dropped from 4 minutes to 14 seconds (16 seconds with ID replace afterwards). My way is more precise but not as "force it in there" quick as load data can be. When you get time, try the following.
Change/add all the "change this stuff". Put this in the same folder as the datafeed file (assumed 7124.txt).
$starttime = microtime();
$startarray = explode(" ", $starttime);
$starttime = $startarray + $startarray;
// CHANGE THIS STUFF
$sasid = "000000";
$file = "7124.txt";
// STOP CHANGING STUFF
mysql_query("CREATE TABLE footballfanatics_temp
(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 '',
Manufacturer text NOT NULL,
PartNumber text NOT NULL,
MerchantCategory text NOT NULL,
MerchantSubcategory text NOT NULL,
ShortDescription text NOT NULL,
ISBN text NOT NULL,
UPC text NOT NULL,
SKU varchar(100) NOT NULL default '',
CrossSell varchar(255) NOT NULL default '',
MerchantGroup varchar(255) NOT NULL default '',
MerchantSubgroup varchar(255) NOT NULL default '',
CompatibleWith varchar(255) NOT NULL default '',
CompareTo varchar(255) NOT NULL default '',
QuantityDiscount varchar(255) NOT NULL default '',
Bestseller tinyint(1) NOT NULL default '0',
AddToCartURL varchar(255) NOT NULL default '',
ReviewsRSSURL varchar(255) NOT NULL default '',
Option1 varchar(255) NOT NULL default '',
Option2 varchar(255) NOT NULL default '',
Option3 varchar(255) NOT NULL default '',
Option4 varchar(255) NOT NULL default '',
Option5 varchar(255) NOT NULL default '',
Rsvd1 varchar(10) NOT NULL default '',
Rsvd2 varchar(10) NOT NULL default '',
Rsvd3 varchar(10) NOT NULL default '',
Rsvd4 varchar(10) NOT NULL default '',
Rsvd5 varchar(10) NOT NULL default '',
Rsvd6 varchar(10) NOT NULL default '',
Rsvd7 varchar(10) NOT NULL default '',
Rsvd8 varchar(10) NOT NULL default '',
Rsvd9 varchar(10) NOT NULL default '',
Rsvd10 varchar(10) NOT NULL default '')
") or die(mysql_error());
mysql_query("LOAD DATA LOCAL INFILE '$file' INTO TABLE `footballfanatics_temp` FIELDS TERMINATED BY '|'") or die(mysql_error());
mysql_query("update footballfanatics_temp set Link = replace(Link,'YOURUSERID',$sasid)") or die(mysql_error());
$endtime = microtime();
$endarray = explode(" ", $endtime);
$endtime = $endarray + $endarray;
$totaltime = $endtime - $starttime;
$totaltime = round($totaltime,5);
echo "This page loaded in $totaltime seconds.";
May 20th, 2010, 08:24 PM #11
Not sure I understand exactly how this works with the script of yours I've been using.
1. Do I use this instead of your script that goes to datafeeds.shareasale.com to get the file from SAS?
2. Or do I simply download the file via FTP from SAS, then upload 7124.txt to my server, then run this script from the same subdirectory as the 7124.txt file?
3. Where you're asking for $sasid, are you meaning my SAS username or the SAS ID number? I assume it's the number, but I want to be sure before I mess something up.
In short -- I think I understand this is a script which creates the necessary table on my server, but does NOT retrieve the datafeed text FIRST from SAS -- right?
Thanks for your time and help on this.
Generate more fake news.
May 20th, 2010, 09:14 PM #12
FYI -- Shawn, I sort of tried this script assuming I knew the questions I've asked above.
And it ALMOST worked.
The problem was -- it created footballfanatics_temp table, but did nothing to rename that to footballfan, which is the table name my display scripts are relying upon.
Your earlier script would have created footballfan_temp, then renamed it footballfan to replace the old "main table" with the new "temporary table."
I've tried adding pieces of your old script to the one above to make that work, and I can't get it to work, because it keeps giving me this error message:
Table 'footballfanatics_temp' already exists
Thanks for any help you can offer!Generate more fake news.
May 20th, 2010, 09:15 PM #13
This was more or less a side test, and I assumed you might already have the datafeed still on your server somewhere. If you need me to add the FTP stuff in there I can.
This does basically the same thing the other does, creates a table (footballfanatics_temp) in your database and imports the feed. The difference is it doesn't do a recursive insert line by line, it kind of "chunks" it in there, lol. Not sure how to put it. Like I said, though, dropped me from 4 minutes to 14 seconds, a slight increase <g>.
And, yeah, $sasid is your Shareasale numerical tracking ID.
 we were typing at the same time, will reply in a min
May 20th, 2010, 09:19 PM #14
How long did the script say it took to import (page loaded in....)? I'm not doing anything fancy here, just trying to improve your import speed/time. That is all. No renaming, etc. If it speeds it up, then we can worry about other stuff.
below: // STOP CHANGING STUFF
add: mysql_query("drop table if exists footballfanatics_temp");
That way it'll delete it each time you want to test it.
May 20th, 2010, 09:23 PM #15
I just fiddled with this one more time, deleting both the main table and temp table.
Then I ran the script from scratch -- and it worked!
Now I'm going to see if I can figure out how to add code from the top of your old script to this new one and have it connect to SAS and download the datafeed to my server.
Winging it here! LOL!
Generate more fake news.
May 20th, 2010, 11:03 PM #16
May 21st, 2010, 09:11 AM #17
May 21st, 2010, 10:26 AM #18
FYI -- Just sent you email, Mobilebadboy. Sent it twice because I forgot to attach the zip file the first time.Generate more fake news.
By Chris D in forum ClickBankReplies: 3Last Post: April 27th, 2009, 06:23 PM
By joyaz in forum PopShopsReplies: 26Last Post: November 14th, 2007, 11:08 PM
By trev0006 in forum Commission Junction - CJReplies: 0Last Post: October 27th, 2006, 04:58 AM
By Cursal in forum Programming / Datafeeds / ToolsReplies: 6Last Post: December 21st, 2005, 10:04 PM
By AlpineZone in forum Programming / Datafeeds / ToolsReplies: 4Last Post: June 17th, 2005, 07:02 PM