Creating an RSS Feed with a SQL Database

To create an RSS feed with SQL queries, we will use the widespread Wordpress format as an example. Wordpress, of course, has a stream generator, but the goal is didactic, and an example can serve as the basis for creating a more personalized generator. It can be applied to non-Wordpress databases by configuring column names.

Definition of theoretical table

Articles contain at least the following data:

1. Link, URL of the page.
2. Title.
3. Description, summary of the article.
4. Date.
5. Article text.

Perhaps the author's name, image.

With phpMyAdmin, we have an idea of ​ ​ the structure of the tables of our blog article database.

We are interested in the message table in which the tickets are located, if the selected prefix is a blog, the name will be blog_posts.
There is a lot of data in the structure that we will not use. The post line is used as a prefix to most columns, so we are interested in the following (name followed by data type):

1. post_modified datetime is the last modified date.
2. post_title text: title.
3. post_excerpt text: (excerpt) summary.
4. guid text - Unique identifier of the page, which is also a file name.
5. post_content text: contents
.

The URL is created under WordPress from the name and address of the site, but this requires creating an identifier placed in the GUID, so we will only need to take the latter back to get a link to the page.

However, in the absence of a summary, it will be necessary to return to the first lines of the text of the article.

Therefore, we will have the following table:

With as many lines as the blog has. This table will be used as a starting point for defining the query.

Query definition by PHP script

The script contains the following steps:

1. Connecting to server.
2. Base selection.
3. Reading articles according to a given condition, for example, the last placed n. The result will be in the table.
4. Writing XML file to RSS 2.0 format.
5. The flow is then displayed.

Connecting to the server

PHP requires a server name, username and password, this data allowed Worpress to be installed:

$connexion = mysql_connect('mysql5-1', 'user', 'pass')

The server name is usually in the form mysql... This is followed by the version code.

Base selection

The connection number returned by the previous function will be used in every transaction with the server.

$result = mysql_select_db('mabase', $connexion); 

The returned value is true if the base is found, false if not.

Reading articles

SELECT command... WITH... WHERE from SQL allows you to return a set of elements according to a specified condition.
Arguments:

1. Columns to read. You can specify a column, a comma-separated list of columns, or the * character to take data from all columns. In this example, we will give a list of columns from the previous theoretical table, because we are only interested in these fields.
   SELECT guid, post_title, post_modifier, post_content
2. The table name is the second parameter in the blog_posts instance .
   SELECT... С blog_posts
3. The condition in our example is the last 10 articles. We will not name other conditions yet, the WHERE clause will be omitted. On the contrary, articles will be classified by terms down, and their number will be limited to 10.
4. The ORDER BY clause allows you to classify parts by date with a maximum number. And the DESC parameter indicates that the order is inverted, therefore from the last article, and not from the first.
   SELECT... WITH... ORDER BY post_modified DESC
5. The maximum number of elements is specified by the LIMIT clause.
   SELECT... WITH... ORDER BY post_modified DESC LIMIT 10
   

Therefore, PHP code will look like this:

$command = "SELECT guid, post_title, post_modified, post_content FROM blog_posts ORDER BY post_modified DESC LIMIT 10";
$items = mysql_query($command, $connexion);

Now we have the content of the articles in a two-dimensional table, at the first level each row of the table, and at the second - the data of each column.

[0]=> ligne 1
   [0]=> guid
   [1]=> post_title
   [2]=> post_modified
   [3]=> post_content
[1]=> ligne 2
   [0]=> guid
   [1]=> etc...

It remains to extract data from the table.

Using Basic Data

In fact, calling the mysql_query function returns a resource, the identifier corresponding to the found data remains to extract the data, which we will do with the PHP mysql_fetch_assoc function.

This function returns a table row in which values are associated with keys containing column names.
It is used iteratively to retrieve each row of the table.

while($article = mysql_fetch_assoc($items))
{
   $url= $article["guid"];
   $title= $article["post_title"];
   $date= $article["post_date"];
   $content= $article["post_content"];
}

We extracted data from articles and now have what it takes to create a stream XML file.

Create an RSS file for PHP 5

Using the RSS library of the RSS ARA editor, you can create an RSS file using several functions:

ARAFeed($title, $url, $desc, $date)

Creating flow object. Name - the name of the blog, followed by its URL, description and date of the feed.

ARAItem($title, $url, $content, $date)

Add an item to the stream with previously extracted data.

save($nomfichier)

Saves the created stream to a file.

Therefore, the complete stream generation code will look like this:

$rss = ARAItem($title, $url, $content, $date);
while($article = mysql_fetch_assoc($items))
{

   $url= $article["guid"];
   $title= $article["post_title"];
   $date= $article["post_date"];
   $content= $article["post_content"];
   $rss->ARAItem($title, $url, $content, $date)
}
$rss->save($nomfichier),

This code is supplemented by database access functions, which can be seen in the source code below.

Show Stream

The created stream can be seen directly by giving it to the URL browser.

An RSS player can be used to display a feed created on a web page.

Download source code

SQL RSS Generator.

 $rss = '<?xml version="1.0" encoding="UTF-8" ?>'."\n"[

$rss .= "<pubDate>$date"." GMT</pubDate>\n";