Create an RSS feed from SQL, example with Wordpress

For creating an RSS feed with SQL requests, we will use the example of the widespread Wordpress format. Wordpress has, of course, a feed generator but the goal is teaching and the example will serve as a basis for the realization of a more personalized generator. It can be applied to other databases that Wordpress by adapting the name of the columns.

Defining a theoretical table

Articles contain at least the following items:

  1. Link, URL of the page.
  2. Title.
  3. Excerpt, summary of the article.
  4. Date.
  5. Text of the article.

And possibly, author's name, or an image.

With phpMyAdmin we have a view of the structure of tables in our database of articles of the blog.

What interests us is the table containing the posts tickets, if the chosen prefix is blog, the name of the table will be blog_posts.
The structure contains many data that we will not use. The post string is used as a prefix to the names of most columns, and the following are of interest to us (name followed by the type of data):

  1. post_modified datetime: the date of last modification.
  2. post_title text: the title.
  3. post_excerpt text: the summary.
  4. guid text: the unique identifier of the page, which is also the URL of the file.
  5. post_content text: the content.

The URL is built under Wordpress from the title and address of the site, but it is taken to create the identifier placed in guid, we will thus use the latter for the link on the page.

And in the absence of an excerpt, the extract will be made from first lines of text of the article.

This produces the following table:

guid post_title post_modified post_content
http://www.scriptol.com/rss_sql RSS and SQL 2008-01-18 17:24:01 Create an RSS feed ...
...      

With as many rows as there are articles in the blog. This table will serve as a starting point for defining the query.

Definition of the query by a PHP script

The script includes the following steps:

  1. Connecting to the server.
  2. Selecting the database.
  3. Reading articles according to a certain condition, such as n last posted.The list will be returned into a table.
  4. Writing an XML file in the RSS 2.0 format.
  5. And further, displaying the feed.

Connection to the server

The PHP function requires the server name, the user login and password, this data being required to install Worpress:

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

The server name is usually in the form "mysql" followed by the version number.

Selection of the base

The handler of the connection returned by the previous function will be used in each transaction with the server.

$result = mysql_select_db('mybase', $connection); 

The returned value is true if the database is found, false otherwise.

Reading articles

The SELECT ... FROM .... WHERE command of SQL allows you to return a series of articles under a certain condition.
Arguments are:

  1. The columns that you want to read. One can specify a column, a list of columns separated by a comma or the * symbol to get data for all columns. In this example we will give only the list of columns of the theoretical table previously given because we are interested in these fields.
    SELECT guid, post_title, post_modifier, post_content
  2. The name of the table is the second parameter, in this case blog_posts.
    SELECT ... FROM blog_posts
  3. The condition in our example, these are the 10 latest articles.We will not give another condition for now, the WHERE clause is omitted. Instead, articles are ordered by date in descending order, and the number is limited to 10.
  4. The ORDER BY clause allows sorting articles by date and is associated to a maximum. And the DESC parameter indicates that the order is reversed, from the most recent article rather than from the first one.
    SELECT ... FROM ... ORDER BY post_modified DESC
  5. The maximum number of articles is given by the LIMIT clause.
    SELECT ... FROM ... ORDER BY post_modified DESC LIMIT 10

The PHP code will be as follows:

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

We now have the content of articles in a two-dimensional table, at the first level are the rows of the table, and the second level the data of each column.

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

It remains to retrieve the data from the array.

Using data from the database

In fact the mysql_query function call returns a resource, an ID corresponding to the data found, it remains to retrieve them, what we will do with the PHP function mysql_fetch_assoc.

This function returns a row of the table, in which values are associated with keys that are made from name of the columns. It is used iteratively for 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 recovered the data of articles and we now have all what we need to create the XML feed file.

Create an RSS file under PHP 5

With the RSS library of the RSS ARA editor, creating a feed can be done with a few functions:

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

Creates a feed object. The title is the name of the blog, it is followed by its URL, a description and the date of the feed.

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

Adds an element to the feed with the data previously obtained.

save($nomfichier)

Saves the created feed into a file.

The complete code generation feed will be as follows:

$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 complemented by functions to access the database, what we see in the source below.

Viewing the feed

The generated feed may be viewed directly by giving its URL to a browser.

The Universal Reader library may be used also to display the feed in a Web page.

Download the source code