Results 1 to 18 of 18
  1. #1
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Inserting 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:

    http://www.abestweb.com/forums/showt...t=mobilebadboy

    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.

  2. #2
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    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);

    Shawn Kerr (.com) | Disney World | SEC Football

  3. #3
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Quote Originally Posted by mobilebadboy View Post
    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);
    Thanks. I'll give that a shot.
    Generate more fake news.

  4. #4
    ABW Ambassador superCool's Avatar
    Join Date
    April 23rd, 2008
    Location
    Texas
    Posts
    1,268
    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

  5. #5
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    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.

    Shawn Kerr (.com) | Disney World | SEC Football

  6. #6
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Quote Originally Posted by mobilebadboy View Post
    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.
    I think you've hit on the major problem I'm finding right there with FootballFanatics -- the df is just too darned BIG.

    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.

  7. #7
    Member esnagel's Avatar
    Join Date
    January 1st, 2008
    Location
    Buffalo, NY
    Posts
    88
    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
    set_time_limit(0);

    I've outlined the process at
    http://www.ericnagel.com/2010/03/bui...te-step-2.html

    However, I didn't go the FTP route. This method allows you to upload a zip file.

  8. #8
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Quote Originally Posted by esnagel View Post
    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
    set_time_limit(0);

    I've outlined the process at
    http://www.ericnagel.com/2010/03/bui...te-step-2.html

    However, I didn't go the FTP route. This method allows you to upload a zip file.
    Thanks, esnagel.

    I'm going to bookmark your site and follow along on all that. It looks extremely helpful.
    Generate more fake news.

  9. #9
    Affiliate/AM Moonlighter dflsports's Avatar
    Join Date
    January 17th, 2005
    Posts
    874
    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

  10. #10
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    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).


    PHP Code:
    <?
    $starttime 
    microtime();
    $startarray explode(" "$starttime);
    $starttime $startarray[1] + $startarray[0];

    // CHANGE THIS STUFF
    $sasid "000000";
    $file "7124.txt";
    mysql_pconnect("localhost","DB_USER","DB_PASS");
    mysql_select_db("DATABASE");
    // 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[1] + $endarray[0];
    $totaltime $endtime $starttime
    $totaltime round($totaltime,5);
    echo 
    "This page loaded in $totaltime seconds.";
    ?>

    Shawn Kerr (.com) | Disney World | SEC Football

  11. #11
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Mobilebadboy,

    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.

    Quote Originally Posted by mobilebadboy View Post
    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).


    PHP Code:
    <?
    $starttime 
    microtime();
    $startarray explode(" "$starttime);
    $starttime $startarray[1] + $startarray[0];

    // CHANGE THIS STUFF
    $sasid "000000";
    $file "7124.txt";
    mysql_pconnect("localhost","DB_USER","DB_PASS");
    mysql_select_db("DATABASE");
    // 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[1] + $endarray[0];
    $totaltime $endtime $starttime
    $totaltime round($totaltime,5);
    echo 
    "This page loaded in $totaltime seconds.";
    ?>
    Generate more fake news.

  12. #12
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    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.

  13. #13
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    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.

    [edit] we were typing at the same time, will reply in a min

    Shawn Kerr (.com) | Disney World | SEC Football

  14. #14
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    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.

    Shawn Kerr (.com) | Disney World | SEC Football

  15. #15
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Okay, thanks.

    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!

    Quote Originally Posted by mobilebadboy View Post
    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.

    [edit] we were typing at the same time, will reply in a min
    Generate more fake news.

  16. #16
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    If you run into trouble let me know. And if you want to zip the script up and send it to me I'll try to get it running like it should (my first name @ the .com in my sig).


    Note: This is not an open invitation.

    Shawn Kerr (.com) | Disney World | SEC Football

  17. #17
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Quote Originally Posted by mobilebadboy View Post
    If you run into trouble let me know. And if you want to zip the script up and send it to me I'll try to get it running like it should (my first name @ the .com in my sig).


    Note: This is not an open invitation.
    I'll probably take you up on that offer later today after I've looked the script over one more time and get it zipped and ready.

    Thanks.
    Generate more fake news.

  18. #18
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    FYI -- Just sent you email, Mobilebadboy. Sent it twice because I forgot to attach the zip file the first time.
    Generate more fake news.

  19. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. clickbank affiliate link script
    By Chris D in forum ClickBank
    Replies: 3
    Last Post: April 27th, 2009, 06:23 PM
  2. Replies: 26
    Last Post: November 14th, 2007, 11:08 PM
  3. Need PHP Affiliate Jump Script for CJ
    By trev0006 in forum Commission Junction - CJ
    Replies: 0
    Last Post: October 27th, 2006, 04:58 AM
  4. inserting php in ssi
    By Cursal in forum Programming / Datafeeds / Tools
    Replies: 6
    Last Post: December 21st, 2005, 10:04 PM
  5. PHP/mySQL-based affiliate data feed parsing script?
    By AlpineZone in forum Programming / Datafeeds / Tools
    Replies: 4
    Last Post: June 17th, 2005, 07:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •