Php FeedWriter Database Integration Tutorial – Generate a feed from database records

January 5th, 2012
by Daniel Soutter

In this post, the process of integrating Php FeedWriter with a MySQL database is explained as a step by step tutorial using the sample database integration script available to download from the sample scripts page.

For more information about the sample scripts used in this tutorial, please see the db-feed-sample.php Explained post, which works through the code in the feed-config.php and db-feed-sample.php scripts providing additional information about the process and configuration of the scripts.

Generate a RSS or Atom Feed from data in a MySQL database.

1. Download the latest version of the Php FeedWriter package.

2. Download the package containing the sample scripts used in this tutorial

3. Extract the contents of the Php FeedWriter package to your web server to your ‘includes’ directory. (eg. ‘root_dir/includes/’ )

4. Extract the contents of the database sample scripts to your web server (for the purpose of this example, the sample files are extracted to the root directory).

5. Open the feed-config.php file in your text editor.

5.1 Change the values of the parameters containing the database connection details (host, port, user, password, database name). – Lines 26 to 30

$feed_db_host = 'dbhost';
$feed_db_port = '3306'; //default MySQL port
$feed_db_user = 'dbuser';
$feed_db_pass = 'dbpassword';
$feed_db_dbname = '';

5.2 Change the name of the database table to the table containing records to populate feed items with data ($feed_items_table).

$feed_items_table = 'articles';

5.3 Update the limit if required ($feed_item_limit), which is used to limit the number of records obtained from the database and also limits the number of items added to the feed.

$feed_item_limit = 100;

5.4 Update the values in the $feed_item_table_cols array to the names of the relevant columns in your MYSQL database table. Eg. ‘item_title_column’ => ‘item_title’, ‘where item_title’ is the column name. (These values are used to generate the database select query and also to map the database columns to elements in each feed item).

$feed_item_table_cols = Array(
   'item_id_column' =>         'item_id',            //DB Record ID column
   'item_title_column' =>         'item_title',
   'item_content_column' =>     'item_body',
   'item_date_column' =>         'date_modified',     //To be set as last updated and used as order by column.  Assumed column type "TIMESAMP"
   'item_link_column' =>         null,                //In this example, the link is generated using a base url and the record ID so NULL is set for this column.  If a column name is entered instead of NULL, the value from this column will be used to set the feed link.
   'item_copyright_column' =>     null,                 //example NULL value indicating that there is no column containing copyright details for each item.
   'item_author_name_column' => 'item_author_name',
   'item_category_column' =>     'category'
);

5.5 Set the item link base url ($item_link_base_url), which is used to generate the link to the page on your website for each feed item, if the ‘item_link_column’ element in the $feed_item_table_cols array is set to null.

$item_link_base_url = "http://phpfeedwriter.webmasterhub.net/?id=";

5.6 Update the values of parameters used to populate the feed Channel data. The first three have been included below as an example.

//Feed Title
$feed_title = 'Php FeedWriter Sample Feed from Database';

//Feed Link
$feed_url = 'http://phpfeedwriter.webmasterhub.net/';

//Feed Description
$feed_description = 'Generate an Atom 1.0, RSS 2.0 and other feeds from website or database content';

5.7 (Optional) set the select query to a custom query if required, which will be used instead of generating the query dynamically from the column mappings set in 5.3.  To allow the query to be generated dynamically, the $select_query parameter should be set to null (default).

$select_query = null;

5.8 Save the feed-config.php file.

6. Open the db-feed-sample.php script in your text editor.

6.1 If required, adjust the location of the line containing require_once(“FeedWriter.php”); to the correct location of the FeedWriter.php file. (eg. require_once(“includes/FeedWriter.php”);

require_once("includes/FeedWriter.php");

6.2 Save the db-feed-sample.php file.

7 Open the db-feed-sample.php file in your browser to test the feed. (eg. http://www.your-domain.com/db-feed-sample.php)

Additional Notes:

If your database table is set up in a way that is not directly compatible with the configuration of this sample, you may need to include additional field mappings in the $feed_item_table_cols array.

If you are using a custom select query that contains joins or column aliases, you will still need to include the column/field mappings in the $feed_item_table_cols array to allow the feed items to be populated with data.

If you have customised the field mappings to cater for your MySQL database, you may need to adjust the code in the db-feed-sample.php file where items are added to the feed to use the different column mappings.

 

 

 

 

 

Tags: , ,
Posted in FeedWriter Class | Comments (0)

  • Php FeedWriter (c) Copyright 2011 Daniel Soutter. Php FeedWriter and the information on this site has been designed to benefit website developers, owners and users, but may contain errors and is provided as is, with no warranty. Terms.