//rename the old database
$query5="drop table if exists temp2;
rename table something to temp2;
rename table temp to something;
create table temp like something";
//drop old database but not till testing is complete
/*"drop table temp2";*/
$result5 = mysql_query($query5);

$con = mysql_connect($server, $user, $pass, $db)
$res = mysql_query($query, $con)

#!/usr/bin/php
<?php
function getad($url) {
if (ini_get('allow_url_fopen') == '1') {
  $content = file_get_contents($url);
  if ($content !== false) {
    // do something with the content
    //my host doesn't allow this, so can't be bothered
    } 
  else {
    $content = getcurlad($url);
    }
  } 
else {
  $content = getcurlad($url);
  }
echo "$url: $content<br />";
return $content;
}
 
function getcurlad($url) {
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.5) Gecko/20041107 Firefox/1.0');
$content = curl_exec($ch);
curl_close($ch);
return $content;
}
 
function GetRemoteLastModified($url) {
$headers = get_headers($url,1);
return $headers['Last-Modified'];
}
 
$monthnames = array(1 => "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
function LongDateToU($thisdate) {
/* Takes a date in the format Wed, 08 Jan 2003 23:11:55 GMT (RFC2822 format)
and gets seconds since Unix Epoch 
**NOTE**: ignores timezone as I only use it for files on same server */
$pieces = explode(" ",$thisdate);
global $monthnames;
$d = $pieces[1]+0;
$m = $pieces[2];
for ($q=1;$q<=12;$q++) {
  if ($m == $monthnames[$q]) {
    $m = $q;
    }
  }
$y = $pieces[3]+0;
$fulltime = $pieces[4];
unset($pieces);
$pieces = explode(":",$fulltime);
$hr = $pieces[0]+0;
$min = $pieces[1]+0;
$sec = $pieces[2]+0;
return date("U",mktime($hr, $min, $sec, $m, $d, $y));
}
 
function savead($adname, $content) {
$wfile = "./ads/".$adname;
echo "creating $wfile<br />\n";
$handle = fopen($wfile,'w');
if (fwrite($handle,$content) === FALSE) {
	//notify me
    mail($adminEmail,"Failure creating ad file on DIP","On DIP record $couponId, the ad file could not be created\n\nRobot35 with love");
	}
fclose($handle);
}
 
$script_root = "./";
$adminEmail = "nowone@nowhere.com";
$allcoupons = array();
$dbServer='localhost';
$dbUser='ausername';
$dbPass='apassword';
//pass 0 - us site
mysql_connect("$dbServer", "$dbUser", "$dbPass") or die("Could not connect");
echo "Connected successfully<br />";
//pick up and store the data
$srchstr = array("1234567","3456789","1425852","u=4044","qrABC11IRks","0gAFgh2vvLQ","gnid=72311");
$replstr = array("7654321","9876543","1420158","u=16578","edip0q*M7qs","eLem0q*M7qs","gnid=78241");
for ($p=0;$p<2;$p++) {
  if ($p == 0) {
    mysql_select_db("user_ussite") or die("Could not select database ARN");
    }
  else {
    mysql_select_db("user_uksite") or die("Could not select database SMM");
    }
  echo "<br /><br />Database $p selected successfully<br />";
  $query="SELECT * FROM `coupons` where sector='handheld' and ExpiryDate>=CURRENT_DATE";
  $result=mysql_query($query);
  if ($result != FALSE) {
    $numrows=mysql_num_rows($result);
    }
  else {
    $numrows=0;
    }
  echo "$numrows records in database $p<br />";
  for ($j1=0;$j1<$numrows;$j1++) {
    $allcoupons[$j1]=mysql_fetch_assoc($result);
    $allcoupons[$j1]['CouponId'] = $allcoupons[$j1]['CouponId']+9000;
    $allcoupons[$j1]['CouponName'] = mysql_real_escape_string($allcoupons[$j1]['CouponName']);
    $allcoupons[$j1]['ShortName'] = mysql_real_escape_string($allcoupons[$j1]['ShortName']);
    $allcoupons[$j1]['CouponCode'] = mysql_real_escape_string($allcoupons[$j1]['CouponCode']);
    echo "<br />original url: ";
    echo $allcoupons[$j1]['CouponUrl'];
    $allcoupons[$j1]['CouponUrl'] = mysql_real_escape_string(str_replace($srchstr,$replstr,$allcoupons[$j1]['CouponUrl']));
    echo "<br />now: ";
    echo $allcoupons[$j1]['CouponUrl'];
    $allcoupons[$j1]['ImageUrl'] = mysql_real_escape_string(str_replace($srchstr,$replstr,$allcoupons[$j1]['ImageUrl']));
    $allcoupons[$j1]['ImageAlt'] = mysql_real_escape_string($allcoupons[$j1]['ImageAlt']);
    $allcoupons[$j1]['metaDesc'] = mysql_real_escape_string($allcoupons[$j1]['metaDesc']);
    $allcoupons[$j1]['tags'] = mysql_real_escape_string($allcoupons[$j1]['tags']);
    $allcoupons[$j1]['Live'] = 'no';
    $allcoupons[$j1]['TrafficBugged'] = 'no';
    $allcoupons[$j1]['ImageOptim'] = '';
    $allcoupons[$j1]['HandPicked'] = '0000-00-00';
    $allcoupons[$j1]['LastTweet'] = '0000-00-00';
    echo "$j1 read, ";
    #print_r ($allcoupons[$j1]);
    }
 mysql_select_db("user_handheld") or die("Could not select database DIP (1st)");
  echo "<br />Database DIP selected<br />";
//make a temp database
  if ($p == 0) {
    $query2="CREATE TABLE temp LIKE coupons";
    $result2=mysql_query($query2);
    echo "<br />TEMP DATABASE CREATED<br />";
    }
  //add all the stuff to it
  for ($j=0;$j<$numrows;$j++) {
    if (isset($allcoupons[$j]['CouponId'])) {
      //record may have been deleted because DIP is not approved for this campaign
      $query3 = "insert into temp (`CouponId`, `CouponName`, `ShortName`, `CouponCode`, `CouponUrl`, `ImageUrl`, `ImageAlt`, `CouponAd`, `metaDesc`, `tags`, `sector`, `business`, `country`, `StartDate`, `StartTime`, `ExpiryDate`, `ExpiryTime`, `SomeDaysOnly`, `isAdult`, `Live`, `lastTweet`, `HandPicked`, `real_expiry`, `TrafficBugged`, `ImageOptim`) VALUES ({$allcoupons[$j]['CouponId']}, '{$allcoupons[$j]['CouponName']}', '{$allcoupons[$j]['ShortName']}', '{$allcoupons[$j]['CouponCode']}', '{$allcoupons[$j]['CouponUrl']}', '{$allcoupons[$j]['ImageUrl']}', '{$allcoupons[$j]['ImageAlt']}', '{$allcoupons[$j]['CouponAd']}', '{$allcoupons[$j]['metaDesc']}', '{$allcoupons[$j]['tags']}', '{$allcoupons[$j]['sector']}', '{$allcoupons[$j]['business']}', '{$allcoupons[$j]['country']}', '{$allcoupons[$j]['StartDate']}', '{$allcoupons[$j]['StartTime']}', '{$allcoupons[$j]['ExpiryDate']}', '{$allcoupons[$j]['ExpiryTime']}', '{$allcoupons[$j]['SomeDaysOnly']}', '{$allcoupons[$j]['isAdult']}', '{$allcoupons[$j]['Live']}', '{$allcoupons[$j]['lastTweet']}', '{$allcoupons[$j]['HandPicked']}', '{$allcoupons[$j]['real_expiry']}', '{$allcoupons[$j]['TrafficBugged']}', '{$allcoupons[$j]['ImageOptim']}')";
      $result3=mysql_query($query3);
      #echo "$j written: $query3,<br />";
      }
    }
  //clear data from previous pass
  unset($allcoupons);
  }
$query1 = "select * from temp order by CouponAd";
$result1 = mysql_query($query1);
$numrows1 = mysql_num_rows($result1);
echo "<br />$numrows1 rows in combined table<br />";
//grab ads and modify content AND main links for cj/trd etc (where sites have individual ids)
//pickup stuff we just stored to sort out site ids
for ($j0=0;$j0<$numrows1;$j0++) {
  $row = mysql_fetch_array($result1);
  $couponId = $row['CouponId'];
  $adname = $row['CouponAd'];
  $country = $row['country'];
  if ($adname == '') {
    //notify me
    echo "{$couponId} has no ad!<br />";
    mail($adminEmail,"Missing ad file on DIP","On DIP record $couponId, the ad file name is  missing\n\nRobot35 with love");
    }
  else {
    echo "Looking at ad $adname: ";
    $adsource = ($country == "UK") ? "http://www.somesite.co.uk/ads/" : "http://www.asomesite.com/ads/";
    $remotemoddate = LongDateToU(GetRemoteLastModified($adsource.$adname));
    echo "remote: ".$remotemoddate.", ";
    if (file_exists("./ads/".$adname)) {
      $localmoddate = filemtime("./ads/".$adname);
      }
    else {
      //dummy date
      $localmoddate = date("U",mktime(0,0,0,1,1,2000));
      }
    echo "local: ".$localmoddate.", ";
    if ($remotemoddate>$localmoddate) {
      echo "New ad needed<br />";
      mail($adminEmail,"Adcheck required on DIP","On DIP, the ad $adname is required/needs an update\n\nRobot35 with love");
      }
    else {
      echo "Existing ad ok<br />";
      }
    }
  }
//rename the old database
$query5="drop table if exists temp2;
rename table coupons to temp2;
rename table temp to coupons;
create table temp like coupons";
//drop old database but not till testing is complete
/*"drop table temp2";*/
$result5 = mysql_query($query5);
mail($adminEmail,"DIP Update Complete","I have completed the daily update on DIP\n\nRobot35 with love");
include "./coupon-update.php";
echo "<br />\nUpdate complete!";
?>