How to get Webgains Vouchers into a MySql database

[scriptmonkey]

Tweet

Two parts to this;

Here's the SQL to create the table called vouchers, add it to a database in phpmyadmin:

CREATE TABLE IF NOT EXISTS `vouchers` (
  `PRID` varchar(11) NOT NULL,
  `Merchant` varchar(255) NOT NULL,
  `Code` varchar(255) NOT NULL,
  `Expiry` varchar(26) NOT NULL,
  `Offer` varchar(255) NOT NULL,
  `Notes` varchar(255) NOT NULL,
  `Signup Link` varchar(255) NOT NULL,
  PRIMARY KEY  (`Code`),
  KEY `PRID` (`PRID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here's the php code to download and insert the data into the table:

<?php
    set_time_limit(0);
    ini_set("memory_limit","3072M");
    ini_set('max_upload_filesize', 3338388608);
    $url = 'http://www.webgains.com/newsletter/images/DiscountCodes.csv';
    $filename = 'codes.csv';
    $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);
    }

    $hostname_wg = "hostname";  // Change this
    $database_wg = "databasename"; // Change this
    $username_wg = "username"; // Change this
    $password_wg = "password"; // Change this
    $wg = mysql_connect($hostname_wg, $username_wg, $password_wg) or trigger_error(mysql_error(),E_USER_ERROR);
    mysql_select_db($database_wg, $wg);
    $sql = "LOAD DATA LOCAL INFILE 'codes.csv' INTO TABLE vouchers FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\'" ;
    mysql_query($sql, $wg) or die(mysql_error());
    $sql = "delete FROM `vouchers` WHERE `PRID` = 'PRID'";
    mysql_query($sql, $wg) or die(mysql_error());
    $sql = "update vouchers set `Offer` = replace(`Offer`,'?','&pound;')";
    mysql_query($sql, $wg) or die(mysql_error());
    $sql = "update vouchers set `Offer` = replace(`Offer`,'&amp;pound;','&pound;')";
    mysql_query($sql, $wg) or die(mysql_error());
    $sql = "update vouchers set `Signup Link` = replace(`Signup Link`,'http://www.webgains.com/signup.html?programid=','http://track.webgains.com/click.html?wgcampaignid=YOURCAMPAIGNID&wgprogramid=')"; // Change YOURCAMPAIGNID to your campaign ID
    mysql_query($sql, $wg) or die(mysql_error());
    $sql = "OPTIMIZE TABLE `vouchers`";
    mysql_query($sql, $wg) or die(mysql_error());
    mysql_close($wg);
    ?>

Change the obvious values to suit.

This won't duplicate anything that's there already, nor will it delete expired codes.

You'll then have a mysql table with the following fields:

PRID
Merchant
Code
Expiry
Offer
Notes
Signup Link

which you can access from a php page.

Tony

[srubin]

Hi Tony,

Thanks for your post and for sharing this information with the other readers.

Should anyone require any assistance implementing this, or anything else at all, please feel free to contact us at support@webgains.com.

Thanks again,