sql2rss.php

for news RSS to HTML as ASP

  } else {
    //
    // With an external config file with smarts, there might be multiple
    // queries from the same config file
    if (isset($SQLQuery)) {
      $outputCacheName = "$localCacheFolder/".md5($SQLQuery).".cache.xml";
    } else {
      $useOutputCaching = 0;
    }
  }

  if ($useOutputCaching && !isset($_REQUEST["FORCECACHEREFRESH"])) {
    if (file_exists($outputCacheName) === TRUE) {
      // File exists, so lets check the age
      if ((time() - filemtime($outputCacheName)) <= $outputCacheTTL) {
        if (($fd = @fopen($outputCacheName, "rb")) !== FALSE) {
          if (flock($fd, LOCK_EX)) {
            $finalResult = fread($fd, filesize($outputCacheName));
            flock($fd, LOCK_UN);
            if (strlen($finalResult) == filesize($outputCacheName)) {
              $gotDataFromCache = 1;
            }
          }
          fclose($fd);
        }
      }
    }
  }

  if ($gotDataFromCache == 0) {
    //
    // This is a local file, so use fopen
    $ChannelTemplateData = "";
    if (($fd = @fopen($ChannelTemplate, "rb")) === FALSE) {
      echo "Unable to open the ChannelTemplate, $ChannelTemplate<br>\n";
      exit;
    }
    while (($data = fread($fd, 4096)) != "") {
      $ChannelTemplateData .= $data;
    }
    fclose($fd);
    
    //
    // Do substitutions from the URL into the channel template
    $ChannelTemplateData = sql2rss_subsutition($_REQUEST, $ChannelTemplateData);
    
    //
    // Find the spedified encoding of the feed
    $results = NULL;
    if (preg_match("/<\?xml(.*)\?>/", $ChannelTemplateData, $results) !== FALSE) {
      if (count($results) != 0) {
        $results[0] = str_replace("'", "\"", str_replace(" ", "", $results[0]));

        if (($location = stristr($results[0], "encoding=")) !== FALSE) {
          $parts = split("\"", $location);
          $destinationEncoding = $parts[1];
        }
      }
    }

    $ItemTemplateData = "";
    if (($fd = @fopen($ItemTemplate, "rb")) === FALSE) {
      echo "Unable to open the ITEMTemplate, $ItemTemplate<br>\n";
      exit;
    }
    while (($data = fread($fd, 4096)) != "") {
      $ItemTemplateData .= $data;
    }
    fclose($fd);

    
    //
    // Do substitutions from the URL into the SQL Query
    $SQLQuery = sql2rss_subsutition($_REQUEST, $SQLQuery);

    //
    // Build the <items>s
    $result = "";
    if (FeedForAll_database_connect($config) !== FALSE) {
      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 (($sql = FeedForAll_database_query($config, $SQLQuery)) !== FALSE) {
        if (isset($debugLevel) && ($debugLevel >= 3)) {
          echo "SUCCESS: FeedForAll_database_query(\$config, $SQLQuery)<br>\n";
        }
        while (($rv = FeedForAll_database_fetch($config, $sql)) !== FALSE) {
          $result .= sql2rss_subsutition($rv, $ItemTemplateData);
        }
      } else {
        if (isset($debugLevel) && ($debugLevel >= 1)) {
          echo "ERROR: FeedForAll_database_query(\$config, $SQLQuery) => ".FeedForAll_database_error($config)."<br>\n";
        }
      }
    }

    // Setup the channel
    // Remove the existing <generator> and replace it with ours
    $ChannelTemplateData = preg_replace("/<generator.*generator>/", "", $ChannelTemplateData);

    // Remove the existing <lastBuildDate> and replace it with ours
    $ChannelTemplateData = preg_replace("/<lastBuildDate.*lastBuildDate>/", "", $ChannelTemplateData);

    // Remove the existing <pubDate> and replace it with ours
    $ChannelTemplateData = preg_replace("/<pubDate.*pubDate>/", "", $ChannelTemplateData);

    // Put the items in before the close of </channel>
    $finalResult = str_replace("</channel>", "<generator>FeedForAll-scripts (sql2rss.php), http://www.FeedForAll.com/, Copyright 2006-2007, NotePage, Inc.</generator>\n<lastBuildDate>".date("r")."</lastBuildDate>\n<pubDate>".date("r")."</pubDate>\n".$result."</channel>", $ChannelTemplateData);

    //
    // Write to cache
    if ($useOutputCaching) {
      if (($fd = @fopen($outputCacheName, "wb")) !== FALSE) {
        if (@flock($fd, LOCK_EX)) {
          fwrite($fd, $finalResult);
          flock($fd, LOCK_UN);
        }
        fclose($fd);
      }
    }
  }

  if (!headers_sent()) {
    // Send the Content-Type to force UTF-8
    header("Content-Type: application/xml; charset=UTF-8");
  }
  
  if (isset($debugLevel) && ($debugLevel >= 1)) {
    $finalResult = str_replace("&", "&amp;", $finalResult);
    $finalResult = str_replace("<", "&lt;", $finalResult);
    $finalResult = str_replace(">", "&gt;", $finalResult);
    echo "<pre>$finalResult</pre>\n";
  } else {
    echo $finalResult."\n";
  }
} else {
  // Assist building a configuration file
  ob_start();
  @include_once($ConfigFilename);
  ob_end_clean();

  // If this is not a post, then use values from the file
  if ($_SERVER["REQUEST_METHOD"] != "POST") {
    $_POST["useOutputCaching"] = $useOutputCaching;
    $_POST["outputCacheTTL"] = $outputCacheTTL;
    $_POST["ChannelTemplate"] = $ChannelTemplate;
    $_POST["ItemTemplate"] = $ItemTemplate;
    $_POST["DBconfigFile"] = $DBconfigFile;
    $_POST["SQLQuery"] = $SQLQuery;
    $_POST["DBtype"] = $DBtype;
  }

  if (!headers_sent()) {
    // Send the Content-Type to force UTF-8
    header("Content-Type: text/html; charset=UTF-8");
  }
  echo "<html><head><title>sql2rss.php Configuration tool</title></head><body bgcolor=\"#EEEEFF\">\n";

  if ($_SERVER["REQUEST_METHOD"] == "POST") {
    echo "Copy between the dividers below and save as the configuration file<br>\n";
    echo "<br>================= Start of file =================\n";
    echo "<pre>\n";
    echo "&lt;?PHP\n\n";
    echo "//\n";
    echo "// Enable the caching for the resulting XML file.  Since it may take time to \n";
    echo "\$useOutputCaching = $_POST[useOutputCaching];\n\n";
    echo "//\n";
    echo "// The variable \$outputCacheTTL controls how many seconds a cached copy of\n";
    echo "// resulting RSS feed can be used before it must be recreated.\n";
    echo "\$outputCacheTTL = $_POST[outputCacheTTL];\n\n";
    echo "//\n";
    echo "// \$ChannelTemplate is the XML file that contains the outer, <channel>, items\n";
    echo "// for the produced feed.  This MUST be a valid RSS 2.0 feed without any\n";
    echo "// <item>'s.  It must also specify \"UTF-8\" encoding, since all the produced\n";
    echo "// items will be in UTF-8.\n";
    echo "\$ChannelTemplate = \"$_POST[ChannelTemplate]\";\n\n";
    echo "//\n";
    echo "// \$ItemTemplate is the ITEM file that contains the the template for <item>.\n";
    echo "// This MUST be a valid RSS 2.0 feed <item>.  It must also specify \"UTF-8\" \n";
    echo "// encoding, since all the produced items will be in UTF-8.\n";
    echo "\$ItemTemplate = \"$_POST[ItemTemplate]\";\n\n";
    echo "//\n";
    echo "// \$configFile is the configuration file with the database username and\n";
    echo "// password\n";
    echo "\$DBconfigFile = \"$_POST[DBconfigFile]\";\n\n";
    echo "//\n";
    echo "// \$SQLQuery is a valid database query for your database\n";
    echo "\$SQLQuery = \"$_POST[SQLQuery]\";\n\n";
    echo "//\n";
    echo "// Since we are calling sql2rss.php from here, we don't want\n";
    echo "// to include a separate config file.\n";
    echo "\$ConfigFilename = \"DO_NOT_INCLUDE_CONFIG\";\n";
    echo "if (!isset(\$_REQUEST[\"buildConfig\"])) {\n";
    echo "  @include(\"sql2rss.php\");\n";
    echo "}\n\n";
    echo "?&gt;\n";
    echo "</pre>\n";
    echo "================== End of file ==================<br><br>\n";
  }

  echo "<form method=\"POST\">\n";
  echo "<table border=\"0\">\n";
  echo "<tr><th valign=\"top\">useOutputCaching</th><td>Enable the caching for the created RSS feed.  Since it may take time to process multiple RSS feeds and may be noticed by visitors to your website this is highly recommended.  <u>During development and testing set this to &quot;No&quot; so you can see the results of your changes.</u><br>";
  echo "<select name=\"useOutputCaching\" size=\"1\">\n";
  echo "<OPTION VALUE=\"0\"";
  if ($_POST["useOutputCaching"] == 0) echo " SELECTED";
  echo ">No\n";
  echo "<OPTION VALUE=\"1\"";
  if ($_POST["useOutputCaching"] == 1) echo " SELECTED";
  echo ">Yes\n";
  echo "</SELECT>\n";
  echo "</td></tr>\n";

  echo "<tr><th valign=\"top\">outputCacheTTL</th><td>The variable <b>outputCacheTTL</b> controls how many seconds a cached copy of resulting RSS feed can be used before it must be recreated.<br><input type=\"text\" name=\"outputCacheTTL\" value=\"$_POST[outputCacheTTL]\" size=\"8\"></td></tr>\n";

  echo "<tr><th valign=\"top\">ChannelTemplate</th><td><b>ChannelTemplate</b> is the XML file that contains the &lt;channel&gt; elements for the created feed.  This MUST be a valid RSS 2.0 feed without any &lt;item&gt;'s.  It must also specify &quot;UTF-8&quot; encoding, since all the created items will be in UTF-8.<br><input type=\"text\" name=\"ChannelTemplate\" value=\"$_POST[ChannelTemplate]\" size=\"64\"></td></tr>\n";

  echo "<tr><th valign=\"top\">ItemTemplate</th><td><b>ItemTemplate</b> is the XML file that contains the &lt;item&gt; template for new elements for the created feed.  This MUST be a valid RSS 2.0 feed with only &lt;item&gt;'s.  It must also specify &quot;UTF-8&quot; encoding, since all the created items will be in UTF-8.<br><input type=\"text\" name=\"ItemTemplate\" value=\"$_POST[ItemTemplate]\" size=\"64\"></td></tr>\n";

  echo "<tr><th valign=\"top\">DBconfigFile</th><td><b>DBConfig</b> is the name of the file created during install with the information needed to access the database.<br><input type=\"text\" name=\"DBconfigFile\" value=\"$_POST[DBconfigFile]\" size=\"64\"></td></tr>\n";

  echo "<tr><th valign=\"top\">SQLQuery</th><td><b>SQLQuery</b> is the actual SQL query or select statement to retrieve your data from the database.<br><textarea name=\"SQLQuery\" rows=\"4\" cols=\"64\" wrap=\"soft\">".stripslashes($_POST["SQLQuery"])."</textarea></td></tr>\n";

  echo "</table>\n";

  echo "<input type=\"SUBMIT\" name=\"submit\" value=\"Generate configuration data\">\n";
  echo "</form>\n";
}
?>