Clickbank Xml Feed Mysql Cron
I need a php script that will get a 3.5MB .ZIP file from an outside url at Clickbank.com containing a 16 MB XML feed with approximately 10,000 unique items, unzip the file contents (1 .xml file and 1 .dtd file) and convert the data to and create/populate 2 MySQl tables.
This script will be run daily in CRON so no user intervention is allowed. It has to be fully automatic.
Please make sure to have error-checking in the script, so if something goes wrong in a preceeding step, NO database are TABLES emptied, but an e-mail is sent warning something went wrong.
ALL TABLES(2) in the database will be repopulated entirely each time the script is run.
For Each Record of the table “t_cb_raw” where “commission” is less than “40” THEN do NOT include that record in the newly created new table named “t_cb_noduplicates”, “t_cb_raw” is the Local CBMarketFeed DB after download of the CBMarketFeed.
t_cb_noduplicates-ftitle = tcbraw-Title
t_cb_noduplicates-fdesc = tcbraw-Description
t_cb_noduplicates-fvendorid = tcbraw-Id
t_cb_noduplicates-flink = http://”tcbraw-Id”.”tcbraw-Id”.hop.clickbank.net/
( Table t_cb_noduplicates-flink value needs to be UNIQUE, once first unique “tcbraw-Id” is encountered, no further duplicates allowed )
t_cb_noduplicates-popularityrank = tcbraw-PopularityRank
t_cb_noduplicates-recurring = tcbraw-HasRecurringProducts
t_cb_noduplicates-gravity = tcbraw-Gravity
t_cb_noduplicates-earnedpersale = tcbraw-EarnedPerSale
t_cb_noduplicates-percentpersale = tcbraw-PercentPerSale
t_cb_noduplicates-totalearningspersale = tcbraw-TotalEarningsPerSale
t_cb_noduplicates-totalrebillamount = tcbraw-TotalRebillAmt
t_cb_noduplicates-referred = tcbraw-Referred
t_cb_noduplicates-commission = tcbraw-Commission
t_cb_noduplicates-added = tcbraw-ActiveDate
t_cb_noduplicates-fcat = tcbraw-Name
t_cb_noduplicates-ftime = time() ; The time that the data was retrieved (seconds since 1970)
“CREATE TABLE t_cb_noduplicates (“.
” fid bigint(20) NOT NULL auto_increment,”.
” ftitle varchar(255) NOT NULL,”.
” fdesc longtext NOT NULL,”.
” fvendorID varchar(32) NOT NULL,”.
” flink varchar(255) NOT NULL,”.
” fpopularityrank INT NOT NULL,”.
” frecurring BOOL NOT NULL,”.
” fgravity FLOAT NOT NULL DEFAULT ‘0’,”.
” fearnedpersale FLOAT NOT NULL DEFAULT ‘0’,”.
” fpercentpersale FLOAT NOT NULL DEFAULT ‘0’,”.
” ftotalearningspersale FLOAT NOT NULL DEFAULT ‘0’,”.
” ftotalrebillamount FLOAT NOT NULL DEFAULT ‘0’,”.
” freferred FLOAT NOT NULL DEFAULT ‘0’,”.
” fcommission INT NOT NULL,”.
” fadded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,”.
” fcat varchar(32) NOT NULL,”.
” ftime int(11) DEFAULT ‘0’ NOT NULL,”.
” PRIMARY KEY (fid),”.
” UNIQUE fid (fid),”.
” KEY fhtime (fhtime),”.
” KEY fcat (fcat)”.
” );” ;
On successful completion of each step the BEGIN & END EVENT will be logged.
On JOB Completion -OR- if there is an ERROR an e-mail is sent containing the results of the BEGIN & END EVENT LOG showing Steps Completed, and in case of ERROR, the ERROR.send an email showing the start and end times for the process (from downloading the new feed, to when the table was finished updating with the new data) as follows;
Example LogFile/Process Email:
2010-03-27 10:04pm started download clickbank feed
2010-03-27 10:09pm finished download clickbank feed
2010-03-27 10:10pm started parse clickbank feed
2010-03-27 10:14pm finished parse clickbank feed
2010-03-27 10:14pm started replace of tcbraw table
2010-03-27 10:29pm finished replace of tcbraw table
2010-03-27 10:29pm started query-replace of t_cb_noduplicates table
2010-03-27 10:35pm finished query-replace of t_cb_noduplicates table
Find the clickbank xml feed zip file at http://www.clickbank.com/feeds/marketplace_feed_v1.xml.zip
Platform: Linux-based web server
The PHP script must run on a Linux-based web server CRON using the following:
Apache version 2.2.15
PHP version 5.2.13
MySQL version 5.0.90-community
cPanel for CRON Version 11.25.0-RELEASE
I will be running the script with a cron job, but it must also be able to be fired off by hitting it with a browser.
Deliverables:
1) Complete and fully-functional working scripts(s) program(s) in executable form as well as complete source code of all work done.
2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables):
a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer’s environment–Deliverables must be installed by the Seller in ready-to-run condition in the Buyer’s environment.
b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request.
3) All deliverables will be considered “work made for hire” under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder’s Seller Legal Agreement).