#!/web/cgi-bin/php5
<?php

// database's name
define ("DB_NAME", "midb_articles");

// database's username
define ("DB_USER", "vcnewsdb");

// database's password
define ("DB_PASS", "BDim0605");

// database server
define ("DB_SERVER", "localhost:3306");

try
{
    // message in case this order came from VC News Daily
    $extras = "";

    // connect to database server
    if (($connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS)) === FALSE)
    {
        // there was an error connecting to the DB server
        $str = sprintf ("Unable to connect to database server: %s", DB_SERVER);
        throw new Exception ($str);
    }

    // select database
    if (mysql_select_db(DB_NAME, $connection) === FALSE)
    {
        // there was an error selecting the DB name
        $str = sprintf ("Unable to select database name: %s", DB_NAME);
        throw new Exception ($str);
    }

    //$sql = sprintf ("SELECT * FROM articles where aid='pvmppbqrnt'");
    //$sql = sprintf ("SELECT * FROM articles order by pubdate asc limit 27");
    $sql = sprintf ("SELECT * FROM articles order by pubdate");
    echo $sql . "\n";
    $results = mysql_query ($sql);
    if (mysql_num_rows($results) == 0)
    {
        // there was an incorrect number of stored invoice numbers 
        $str = sprintf ("Unable to find articles");
        throw new Exception ($str);
    }

    while ($row = mysql_fetch_array($results, MYSQL_ASSOC))
    {
      $txt = $row["body"];
      $numOccurrances = substr_count ($txt, " www.");
      if ($numOccurrances == 0)
        continue;
      echo $row["aid"] . " " . $numOccurrances . " \n";
      $hrefOccurrances = substr_count ($txt, "href=");
      if ($hrefOccurrances > 0)
      {
        echo "found href\n";
        continue;
      }
      $offset = 0;
      $stpos = strpos ($txt, " www.", $offset);
      $endpos = strpos ($txt, ".com", $stpos);
      $site = substr ($txt, $stpos + 1, $endpos + 4 - $stpos - 1); 
      //echo $stpos . " " . $endpos . "\n";
      echo $site . "\n";
      $tofind = "/" . $site . "/";
      $toreplace = '<a target="_blank" href="http://'. $site . '">'.$site.'</a>';
      echo $toreplace;
      $updatedTxt = preg_replace ($tofind, $toreplace, $txt);
      $sql = "UPDATE articles set body=\"".mysql_real_escape_string($updatedTxt)."\" where aid='" . $row["aid"] . "'";
      //echo $sql . "\n";
      //echo $updatedTxt . "\n";
      mysql_query ($sql);
      echo mysql_error();
    }

    mysql_close ($connection);
}
catch (Exception $e)
{
    // catch the exception and display the error message
    echo "<p>The following error has occurred: " . $e->getMessage() . "<br />";
}
?>