rss2sql.php

RSS to SQL to store in Database

    echo "<tr><th valign=\"top\" align=\"left\">updateInterval</th><td><input type=\"text\" name=\"updateInterval\" value=\"$_POST[updateInterval]\" size=\"32\"><br>How often the feed should be updated, in seconds</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">lastUpdate</th><td><input type=\"text\" name=\"lastUpdate\" value=\"$_POST[lastUpdate]\" size=\"32\"><br>The last time the feed was successfully updated</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">nextUpdate</th><td><input type=\"text\" name=\"nextUpdate\" value=\"$_POST[nextUpdate]\" size=\"32\"><br>The time the next time the feed should be updated</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">updateStatus</th><td><input type=\"text\" name=\"updateStatus\" value=\"$_POST[updateStatus]\" size=\"32\"><br>The status of the update</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">updateStatusCount</th><td><input type=\"text\" name=\"updateStatusCount\" value=\"$_POST[updateStatusCount]\" size=\"32\"><br>The number of times the same error status was repeated</td></tr>\n";

    echo "<tr><td colspan=\"2\"><hr></td></tr>\n";

    echo "<tr><th valign=\"top\" align=\"left\">ItemTable</th><td><input type=\"text\" name=\"ItemTable\" value=\"$_POST[ItemTable]\" size=\"32\"><br><b>ItemTable</b> is the  name of the table where items are stored</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">itemID</th><td><input type=\"text\" name=\"itemID\" value=\"$_POST[itemID]\" size=\"32\"><br><b>itemID</b> is database field name to store a value to uniquely identify an item</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">UniqueItemID</th><td><input type=\"text\" name=\"UniqueItemID\" value=\"$_POST[UniqueItemID]\" size=\"32\"><br><b>UniqueItemID</b> is database field name to store a value to uniquely identify an item</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemAddedTime</th><td><input type=\"text\" name=\"ItemAddedTime\" value=\"$_POST[ItemAddedTime]\" size=\"32\"><br><b>ItemAddedTime</b> is database field name to store a value to uniquely identify an item</td></tr>\n";

    echo "<tr><th valign=\"top\" align=\"left\">ItemTitle</th><td><input type=\"text\" name=\"ItemTitle\" value=\"$_POST[ItemTitle]\" size=\"32\"><br><b>ItemTitle</b> is database field name to store the title of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemDescription</th><td><input type=\"text\" name=\"ItemDescription\" value=\"$_POST[ItemDescription]\" size=\"32\"><br><b>ItemDescription</b> is database field name to store the description of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemContentEncoded</th><td><input type=\"text\" name=\"ItemContentEncoded\" value=\"$_POST[ItemContentEncoded]\" size=\"32\"><br><b>ItemContentEncoded</b> is database field name to store the content:encoded of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemLink</th><td><input type=\"text\" name=\"ItemLink\" value=\"$_POST[ItemLink]\" size=\"32\"><br><b>ItemLink</b> is database field name to store the link of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemPubDate</th><td><input type=\"text\" name=\"ItemPubDate\" value=\"$_POST[ItemPubDate]\" size=\"32\"><br><b>ItemPubDate</b> is database field name to store the pubDate of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemPubDate_t</th><td><input type=\"text\" name=\"ItemPubDate_t\" value=\"$_POST[ItemPubDate_t]\" size=\"32\"><br><b>ItemPubDate_t</b> is database field name to store the pubDate of the item it time/date format.  If there is no date the time of import will be used.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemEnclosureUrl</th><td><input type=\"text\" name=\"ItemEnclosureUrl\" value=\"$_POST[ItemEnclosureUrl]\" size=\"32\"><br><b>ItemEnclosureUrl</b> is database field name to store the enclosure URL of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemEnclosureType</th><td><input type=\"text\" name=\"ItemEnclosureType\" value=\"$_POST[ItemEnclosureType]\" size=\"32\"><br><b>ItemEnclosureType</b> is database field name to store the enclosure Type of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemEnclosureLength</th><td><input type=\"text\" name=\"ItemEnclosureLength\" value=\"$_POST[ItemEnclosureLength]\" size=\"32\"><br><b>ItemEnclosureLength</b> is database field name to store the enclosure Length of the item.<br></td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemGuid</th><td><input type=\"text\" name=\"ItemGuid\" value=\"$_POST[ItemGuid]\" size=\"32\"><br><b>ItemGuid</b> is database field name to store the GUID of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemAuthor</th><td><input type=\"text\" name=\"ItemAuthor\" value=\"$_POST[ItemAuthor]\" size=\"32\"><br><b>ItemAuthor</b> is database field name to store the Author of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemComments</th><td><input type=\"text\" name=\"ItemComments\" value=\"$_POST[ItemComments]\" size=\"32\"><br><b>ItemComments</b> is database field name to store the Comments of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemSource</th><td><input type=\"text\" name=\"ItemSource\" value=\"$_POST[ItemSource]\" size=\"32\"><br><b>ItemSource</b> is database field name to store the Source of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemSourceUrl</th><td><input type=\"text\" name=\"ItemSourceUrl\" value=\"$_POST[ItemSourceUrl]\" size=\"32\"><br><b>ItemSourceUrl</b> is database field name to store the Source URL of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemCategory</th><td><input type=\"text\" name=\"ItemCategory\" value=\"$_POST[ItemCategory]\" size=\"32\"><br><b>ItemCategory</b> is database field name to store the Category of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemCategoryDomain</th><td><input type=\"text\" name=\"ItemCategoryDomain\" value=\"$_POST[ItemCategoryDomain]\" size=\"32\"><br><b>ItemCategoryDomain</b> is database field name to store the Category URL of the item.</td></tr>\n";
    echo "<tr><th valign=\"top\" align=\"left\">ItemCreativeCommons</th><td><input type=\"text\" name=\"ItemCreativeCommons\" value=\"$_POST[ItemCreativeCommons]\" size=\"32\"><br><b>ItemCreativeCommons</b> is database field name to store the Creative Commons URL of the item.</td></tr>\n";

    if (FeedForAll_parseExtensions() === TRUE) {
      FeedForAll_parseExtensions_rss2sql_buildConfig($config, $_POST, 4);
    }

    echo "</table>\n";

    echo "<input type=\"SUBMIT\" name=\"submit\" value=\"Generate configuration data\">\n";
    echo "</form>\n";
  }
} else {
  ob_start();
  @require_once($ConfigFilename);
  @require_once($config["DBconfigFile"]);

  @require_once("FeedForAll_database.inc.php");
  ob_end_clean();

  Function rss2sql_updateOnFailure($config, $failure, $feedID) {
    GLOBAL $maxErrors;

    if (($sql = FeedForAll_database_query($config, "SELECT $config[updateStatus],$config[updateStatusCount] FROM $config[feedInfo] WHERE $config[feedID]='$feedID'")) !== FALSE) {
      if (($rv = FeedForAll_database_fetch($config, $sql)) !== FALSE) {
        if ($failure == $rv[$config["updateStatus"]]) {
          // Same failure again
          $updateStatusCount = 1 + $rv[$config["updateStatusCount"]];
        } else {
          $updateStatusCount = 1;
        }
        $nextUpdate = (600 * $updateStatusCount) + time();
        FeedForAll_database_query($config, "UPDATE $config[feedInfo] SET $config[nextUpdate]=FROM_UNIXTIME($nextUpdate),$config[updateStatus]='$failure',$config[updateStatusCount]=$updateStatusCount WHERE $config[feedID]='$feedID'");
        if ($updateStatusCount >= $maxErrors) {
          //
          // Too many failures, deactivate and notify
          FeedForAll_database_query("UPDATE $config[feedInfo] SET $config[feedActive]=0 WHERE $config[feedID]='$feedID'");
        }
      }
    }
  }

  Function rss2sql_parseFeed($config, $feedID, $useFopenURL) {
    GLOBAL $destinationEncoding;
    GLOBAL $missingEncodingDefault;

    echo "updateFeed($feedID)<br>\n";

    // Get the feed URL from the database
    $XMLfilename = "";
    if (($qry = FeedForAll_database_query($config, "SELECT * FROM $config[feedInfo] WHERE $config[feedActive]='1' AND $config[feedID]='$feedID'")) !== FALSE) {
      if (($feedInfo = FeedForAll_database_fetch($config, $qry)) !== FALSE) {
        $XMLfilename = $feedInfo[$config["feedURL"]];
      }
    }
    if ($XMLfilename == "") {
      echo "Unable to find the URL for $config[feedID]=$feedID<br>\n";
      return;
    }

    if (($XML = FeedForAll_scripts_readFile($XMLfilename, $useFopenURL, 0)) === FALSE) {
      if ($GLOBALS["ERRORSTRING"] == "") {
        echo "Unable to open RSS Feed $XMLfilename, exiting\n";
      } else {
        echo "Unable to open RSS Feed $XMLfilename with error <b>$GLOBALS[ERRORSTRING]</b>, exiting\n";
      }
      rss2sql_updateOnFailure($config, "Open Failed", $feedID);
      exit -1;
    }

    if (strstr(trim($XML), "<?xml") === FALSE) {
      $XML = "<?xml version=\"1.0\"?>\n$XML";
    }
    $XML = strstr(trim($XML), "<?xml");
    if (($convertedXML = FeedForAll_scripts_convertEncoding($XML, $missingEncodingDefault, $destinationEncoding)) === FALSE) {
      // Conversions failed, probably becasue it was wrong or the routines were missing
      $convertedXML = $XML;
      $xml_parser = xml_parser_create();
    } else {
      $xml_parser = xml_parser_create($destinationEncoding);
    }

    $rss_parser = new baseParserClass("rss2sql");
    $rss_parser->sourceFeedURL = trim($XMLfilename);
    xml_set_object($xml_parser, $rss_parser);
    xml_set_element_handler($xml_parser, "startElement", "endElement");
    xml_set_character_data_handler($xml_parser, "characterData");
    xml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING,1);
    $rss_parser->wholeString = $convertedXML;
    $parseResult = xml_parse($xml_parser, $convertedXML, TRUE);
    if ($parseResult == 0) {
      rss2sql_updateOnFailure($config, "Parsing Error", $feedID);
      $errorCode = xml_get_error_code($xml_parser);
      echo "\$errorCode = $errorCode<br>\n";
      echo "xml_error_string() = ".xml_error_string($errorCode)."<br>\n";
      echo "xml_get_current_line_number() = ".xml_get_current_line_number($xml_parser)."<br>\n";
      echo "xml_get_current_column_number() = ".xml_get_current_column_number($xml_parser)."<br>\n";
      echo "xml_get_current_byte_index() = ".xml_get_current_byte_index($xml_parser)."<br>\n";
    } else {
      xml_parser_free($xml_parser);

      //
      // Insert the new items into the database
      $timeAdded = time();
      for ($x = 0; $x < count($rss_parser->Items); $x++) {
        $okToAdd = 1;
        if (($sql = FeedForAll_database_query($config, "SELECT * FROM $config[ItemTable] WHERE $config[feedID]='$feedID' AND $config[UniqueItemID]='".rss2sql_CreateUniqueLink($rss_parser->Items[$x]->title, $rss_parser->Items[$x]->description, $rss_parser->Items[$x]->link, $rss_parser->Items[$x]->guid)."'")) !== FALSE) {
          if (FeedForAll_database_num_rows($config, $sql)) {
            // Already exists
            $okToAdd = 0;
          }
        }
        if ($okToAdd) {
          //
          // Build the insert based on the defined fields.
          $fields = "$config[feedID]";
          $values = "'$feedID'";
          $fields .= ",$config[UniqueItemID]";
          $values .= ",'".addslashes(rss2sql_CreateUniqueLink($rss_parser->Items[$x]->title, $rss_parser->Items[$x]->description, $rss_parser->Items[$x]->link, $rss_parser->Items[$x]->guid))."'";
          $fields .= ",$config[ItemAddedTime]";
          $values .= ",FROM_UNIXTIME($timeAdded)";
          if (isset($config["ItemTitle"]) && ($config["ItemTitle"] != "")) {
            $fields .= ",$config[ItemTitle]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->title)."'";
          }
          if (isset($config["ItemDescription"]) && ($config["ItemDescription"] != "")) {
            $fields .= ",$config[ItemDescription]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->description)."'";
          }
          if (isset($config["ItemContentEncoded"]) && ($config["ItemContentEncoded"] != "")) {
            $fields .= ",$config[ItemContentEncoded]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->contentEncoded)."'";
          }
          if (isset($config["ItemLink"]) && ($config["ItemLink"] != "")) {
            $fields .= ",$config[ItemLink]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->link)."'";
          }
          if (isset($config["ItemPubDate"]) && ($config["ItemPubDate"] != "")) {
            $fields .= ",$config[ItemPubDate]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->pubDate)."'";
          }
          if (isset($config["ItemPubDate_t"]) && ($config["ItemPubDate_t"] != "")) {
            $fields .= ",$config[ItemPubDate_t]";
            $values .= ",FROM_UNIXTIME('".$rss_parser->Items[$x]->pubDate_t."')";
          }
          if (isset($config["ItemEnclosureUrl"]) && ($config["ItemEnclosureUrl"] != "")) {
            $fields .= ",$config[ItemEnclosureUrl]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->enclosureURL)."'";
          }
          if (isset($config["ItemEnclosureType"]) && ($config["ItemEnclosureType"] != "")) {
            $fields .= ",$config[ItemEnclosureType]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->enclosureType)."'";
          }
          if (isset($config["ItemEnclosureLength"]) && ($config["ItemEnclosureLength"] != "")) {
            $fields .= ",$config[ItemEnclosureLength]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->enclosureLength)."'";
          }
          if (isset($config["ItemGuid"]) && ($config["ItemGuid"] != "")) {
            $fields .= ",$config[ItemGuid]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->guid)."'";
          }
          if (isset($config["ItemAuthor"]) && ($config["ItemAuthor"] != "")) {
            $fields .= ",$config[ItemAuthor]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->author)."'";
          }
          if (isset($config["ItemComments"]) && ($config["ItemComments"] != "")) {
            $fields .= ",$config[ItemComments]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->comments)."'";
          }
          if (isset($config["ItemSource"]) && ($config["ItemSource"] != "")) {
            $fields .= ",$config[ItemSource]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->source)."'";
          }
          if (isset($config["ItemSourceUrl"]) && ($config["ItemSourceUrl"] != "")) {
            $fields .= ",$config[ItemSourceUrl]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->sourceURL)."'";
          }
          if (count($rss_parser->Items[$x]->categoryArray)) {
            if (isset($config["ItemCategory"]) && ($config["ItemCategory"] != "")) {
              $fields .= ",$config[ItemCategory]";
              $values .= ",'".addslashes($rss_parser->Items[$x]->category[0]["Category"])."'";
            }
            if (isset($config["ItemCategoryDomain"]) && ($config["ItemCategoryDomain"] != "")) {
              $fields .= ",$config[ItemCategoryDomain]";
              $values .= ",'".addslashes($rss_parser->Items[$x]->category[0]["Domain"])."'";
            }
          }
          if (isset($config["ItemCreativeCommons"]) && ($config["ItemCreativeCommons"] != "")) {
            $fields .= ",$config[ItemCreativeCommons]";
            $values .= ",'".addslashes($rss_parser->Items[$x]->creativeCommons)."'";
          }
          if (FeedForAll_parseExtensions() === TRUE) {
            FeedForAll_parseExtensions_rss2sql_buildInsert($rss_parser->Items[$x], $config, $fields, $values);
          }

          FeedForAll_database_query($config, "INSERT INTO $config[ItemTable] ($fields) VALUES ($values)");
          if (FeedForAll_database_errno($config)) {
            echo FeedForAll_database_error($config)."<br>\n";
          } else {
            echo "Added ".htmlentities($rss_parser->Items[$x]->title)."<br>\n";
          }
        }

        //
        // Update the status and next update time
        $updateInterval = 86400;
        $lastUpdate = time();
        $nextUpdate = time();
        if (($sql = FeedForAll_database_query($config, "SELECT $config[updateInterval] FROM $config[feedInfo] WHERE $config[feedID]='$feedID'")) !== FALSE) {
          if (($rv = FeedForAll_database_fetch($config, $sql)) !== FALSE) {
            $updateInterval = $rv[$config["updateInterval"]];
          }
        }
        $nextUpdate += $updateInterval;
        FeedForAll_database_query($config, "UPDATE $config[feedInfo] SET $config[lastUpdate]=FROM_UNIXTIME($lastUpdate),$config[nextUpdate]=FROM_UNIXTIME($nextUpdate),$config[updateStatus]='Success',$config[updateStatusCount]='0' WHERE $config[feedID]='$feedID'");
      }
    }
  }

  //
  // Verify that there was actually a database file
  if (!isset($config["DBuser"]) || !isset($config["DBpassword"]) || !isset($config["DBmachine"]) || !isset($config["DBdatabase"])) {
    echo "Invalid database configuration\n";
    exit(2);
  }

  //
  // Verify that the requested DBtype is actually available
  if (($config["DBtype"] == "mysql") && (!function_exists("mysql_connect"))) {
    echo "Requested DB type is not available\n";
    exit(1);
  }
  else if (($config["DBtype"] == "mssql") && (!function_exists("mssql_connect"))) {
    echo "Requested DB type is not available\n";
    exit(1);
  }

  if (FeedForAll_database_connect($config)) {
    if (($config["DBtype"] == "mysql") && ($destinationEncoding == "UTF-8")) {
      // Set the communication character set
      FeedForAll_database_query($config, "SET NAMES 'utf8'");
      FeedForAll_database_query($config, "SET CHARACTER SET 'utf8'");
    }
    if (isset($_REQUEST["feedID"])) {
      rss2sql_parseFeed($config, $_REQUEST["feedID"], 0);
    } else {
      //
      // Look for feeds that need to be updated
      if (($sqlOuter = FeedForAll_database_query($config, "SELECT $config[feedID] FROM $config[feedInfo] WHERE $config[feedActive]='1' AND $config[nextUpdate] < NOW()")) !== FALSE) {
        $start = time();
        while (($rvOuter = FeedForAll_database_fetch($config, $sqlOuter)) !== FALSE) {
          rss2sql_parseFeed($config, $rvOuter[$config["feedID"]], 1);
          if (time() - $start > 60) {
            break;
          }
        }
      }
    }
  }
}
?>