Pan Fashion

Awins new Create-a-feed system is literally the bees knees, but, how do you get the data into a mysql database so you can begin building that website you’ve dreamed of building, the answer is here.
Make sure your create-a-feed interface looks like this

awins create-a-feed

Copy the code below into a php file, read the comments within the file and you should be good to go

<?
//download and unpack gzipped csv file from awin

// maximum execution time in seconds
set_time_limit(120);
//set memory limit
ini_set("memory_limit","3072M");

// insert your download url below from awins create-a-feed system
$url = "YOUR_DOWNLOAD_URL_FROM_AWIN_CREATE_A_FEED";  //make sure you've checked the csv option and the gzip option in the create-a-feed interface.
$filename = "download.csv.gz";
$newfname = $filename;

$file = fopen ($url, "rb");
if ($file) {
  $newf = fopen ($newfname, "wb");

  if ($newf)
  while(!feof($file)) {
    fwrite($newf, fread($file, 1024 ), 1024 );
  }
}

if ($file) {
  fclose($file);
}

if ($newf) {
  fclose($newf);
}

$filename =  'download.csv.gz';
$zd = gzopen($filename, "r");
$contents = gzread($zd, 10000000000); //because I have no idea how big your file will be
gzclose($zd);

$filename='awin1.csv';
$tempvar = fopen($filename,"w");
fwrite($tempvar, $contents);
fclose($tempvar);

?>

<?php
//set up dbase connection and open dbase
$dbhost = 'DBASE_HOST'; //normally localhost
$dbuser = 'DBASE_USERNAME'; //your dbase username
$dbpass = 'DBASE_PASSWORD'; //your dbase password
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
$dbname = 'DBASE_NAME'; //your dbase name
$table_name = 'test'; //change this to whatever you want your table to be called
mysql_select_db($dbname);
?>

<?
//create the table if it isn't there already.
$query = 'CREATE TABLE IF NOT EXISTS `'.$table_name.'` ('.
  '`merchant_id` double default NULL,'.
  '`merchant_name` char(255) default NULL,'.
  '`aw_product_id` double default NULL,'.
  '`merchant_product_id` char(255) default NULL,'.
  '`upc` double default NULL,'.
  '`ean` double default NULL,'.
  '`mpn` char(255) default NULL,'.
  '`isbn` double default NULL,'.
  '`model_number` char(255) default NULL,'.
  '`product_name` char(255) default NULL,'.
  '`description` longtext,'.
  '`specifications` char(255) default NULL,'.
  '`promotional_text` char(255) default NULL,'.
  '`merchant_category` char(255) default NULL,'.
  '`category_id` double default NULL,'.
  '`category_name` char(255) default NULL,'.
  '`language` char(255) default NULL,'.
  '`brand_name` char(255) default NULL,'.
  '`merchant_deep_link` char(255) default NULL,'.
  '`merchant_thumb_url` char(255) default NULL,'.
  '`merchant_image_url` char(255) default NULL,'.
  '`aw_deep_link` char(255) default NULL,'.
  '`aw_thumb_url` char(255) default NULL,'.
  '`aw_image_url` char(255) default NULL,'.
  '`delivery_time` char(255) default NULL,'.
  '`valid_from` char(255) default NULL,'.
  '`valid_to` char(255) default NULL,'.
  '`currency` char(255) default NULL,'.
  '`search_price` double default NULL,'.
  '`store_price` double default NULL,'.
  '`rrp_price` double default NULL,'.
  '`display_price` double default NULL,'.
  '`delivery_cost` double default NULL,'.
  '`web_offer` double default NULL,'.
  '`pre_order` double default NULL,'.
  '`in_stock` double default NULL,'.
  '`stock_quantity` double default NULL,'.
  '`is_for_sale` double default NULL,'.
  '`warranty` char(255) default NULL,'.
  '`condition` char(255) default NULL,'.
  '`product_type` char(255) default NULL,'.
  '`parent_product_id` double default NULL,'.
  '`commission_group` char(255) default NULL,'.
  'KEY `aw_product_id` (`aw_product_id`),'.
  'KEY `category_id` (`category_id`),'.
  'KEY `merchant_id` (`merchant_id`),'.
  'KEY `merchant_product_id` (`merchant_product_id`),'.
 'KEY `parent_product_id` (`parent_product_id`)'.
') ENGINE=MyISAM DEFAULT CHARSET=latin1;';

$result = mysql_query($query);
?>

<?
//load the data from the csv file into the database
$query = 'LOAD DATA LOCAL INFILE  \'awin1.csv\' INTO TABLE '.$table_name.' FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' IGNORE 1 LINES';
$result = mysql_query($query);
?>

<?
//close dbase connection
mysql_close($conn);
?>

You should of course have a database set up already

Hmm, thats got me thinking, how about an awin create-a-feed direct to wordpress posts…??? watch this space!!!

Coming soon, the code to turn this database into some kind of webpage, mmm, can’t wait.