Generating an RSS Feed from a Database with PHP

The clear answer is to use PHP to generate RSS feeds from MySQL database; 2. First understand the basic XML structure of RSS, including title, link, description, language, pubDate, lastBuildDate and multiple items in the channel; 3. Use PDO to connect to the database and query the latest 10 posts, select the id, title, content, slug and created_at fields; 4. Set the correct response header through header('Content-Type: text/xml; charset=UTF-8') and output the XML declaration; 5. Fill in the channel, encapsulate each record into an item with a loop, and use CDATA and htmlspecialchars to prevent parsing errors; 6. pubDate and lastBuildDate need to be formatted as RFC 2822 standard time with date('r'); 7. The link and guid of each item should dynamically generate URLs based on slug; 8. Optionally add a link tag to the web page head to reference rss.php to enable subscription; 9. Finally, caching can optimize performance to ensure that RSS is automatically updated after each database adds new records. The entire process dynamically generates XML content in valid RSS 2.0 format through PHP.

Generating an RSS feed from a database using PHP is a straightforward process that allows you to dynamically share content—like blog posts, news articles, or product updates—with subscribers and external services. Below is a practical guide to help you build a valid RSS feed by pulling data from a MySQL database.

1. Understand the Basic RSS Structure

RSS (Really Simple Syndication) is an XML format. A minimum valid RSS feed includes:

 <?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
  <channel>
    <title>Your Site Title</title>
    <link>https://yoursite.com</link>
    <description>What your feed is about</description>
    <language>en-us</language>
    <pubDate>Sun, 14 Apr 2024 00:00:00 GMT</pubDate>
    <lastBuildDate>Sun, 14 Apr 2024 00:00:00 GMT</lastBuildDate>

    <item>
      <title>Article Title</title>
      <link>https://yoursite.com/article/1</link>
      <description>Summary of the article</description>
      <pubDate>Sat, 13 Apr 2024 12:00:00 GMT</pubDate>
      <guid>https://yoursite.com/article/1</guid>
    </item>
  </channel>
</rss>

You'll generate this structure using PHP, with data pulled from your database.

2. Connect to the Database and Fetch Content

Assume you have a table called posts with columns: id , title , content , slug , created_at .

 <?php
// Database configuration
$host = &#39;localhost&#39;;
$db = &#39;your_database&#39;;
$user = &#39;your_username&#39;;
$pass = &#39;your_password&#39;;
$charset = &#39;utf8mb4&#39;;

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $opt);
} catch (PDOException $e) {
    die(&#39;Database connection failed: &#39; . $e->getMessage());
}

// Fetch latest 10 posts
$stmt = $pdo->query("SELECT id, title, content, slug, created_at FROM posts ORDER BY created_at DESC LIMIT 10");
$posts = $stmt->fetchAll();
?>

3. Output the RSS Feed with Proper Headers

Set the correct content type ( text/xml ) so browsers and feed readers interpret it correctly.

 <?php
header(&#39;Content-Type: text/xml; charset=UTF-8&#39;);

echo &#39;<?xml version="1.0" encoding="UTF-8"?>&#39; . "\n";
?>
<rss version="2.0">
  <channel>
    <title>My Website News</title>
    <link>https://yoursite.com</link>
    <description>Latest updates from our site</description>
    <language>en-us</language>
    <pubDate><?php echo date(&#39;r&#39;); ?></pubDate>
    <lastBuildDate><?php echo date(&#39;r&#39;, strtotime($posts[0][&#39;created_at&#39;])); ?></lastBuildDate>
    <generator>PHP RSS Generator</generator>

    <?php foreach ($posts as $post): ?>
      <item>
        <title><![CDATA[<?php echo htmlspecialchars($post[&#39;title&#39;]); ?>]]></title>
        <link>https://yoursite.com/post/<?php echo urlencode($post[&#39;slug&#39;]); ?></link>
        <description><![CDATA[<?php echo htmlspecialchars(str_tags(substr($post[&#39;content&#39;], 0, 200)) . &#39;...&#39;); ?>]]></description>
        <pubDate><?php echo date(&#39;r&#39;, strtotime($post[&#39;created_at&#39;])); ?></pubDate>
        <guid>https://yoursite.com/post/<?php echo urlencode($post[&#39;slug&#39;]); ?></guid>
      </item>
    <?php endforeach; ?>
  </channel>
</rss>

Key Points to Remember

• Use CDATA sections for titles and descriptions if they contain HTML or special characters.
• Escape output with htmlspecialchars() to avoid XML parsing errors.
• Format dates using RFC 2822 (via date('r') )—required by RSS.
• Set the correct MIME type with header('Content-Type: text/xml') .
• Use guid (globally unique identifier) ​​— usually the same as the link, unless you have internal IDs.
• Cache the feed if performance becomes an issue (eg, using file or memory caching).

Optional: Add to Your Website

Save this script as rss.php and link to it in your site's <head> :

 <link rel="alternate" type="application/rss xml" title="My RSS Feed" href="/rss.php" />

Now, anyone can subscribe to your content using an RSS reader.

Basically, it's just querying your data and wrapping it in the right XML structure. Once set up, your feed updates automatically as new rows are added to the database.

The above is the detailed content of Generating an RSS Feed from a Database with PHP. For more information, please follow other related articles on the PHP Chinese website!