Track daily database changes via RSS feeds

2005-04-13
2013-05-28
  • refbase v0.8 offers to convert almost[1] any query into a RSS feed which can be subscribed to using your favorite news reader. Creating a RSS feed that contains all records that were added (or edited) today may seem tricky, though. This is because, obviously, the date in the query would change every day. Here's how to do it:

    The MySQL query language offers a 'CURDATE()' function that will insert the current date (like '2005-04-13') and which can be used within the WHERE clause of a SQL query. If you display the SQL search page of your refbase database and paste the following query into the SQL Query form:

    SELECT author, title, year, publication, volume, pages FROM refs WHERE modified_date = CURDATE() ORDER BY author, year DESC

    the database will display all records that were edited today.

    Here's an appropriate news feed for the refbase Demo Database (http://demo.refbase.net/):

    http://demo.refbase.net/rss.php?where=modified_date%20%3D%20CURDATE%28%29&showRows=10

    The resulting RSS feed will always display all those records of the refbase Demo Database that were edited during the current day. (If you don't see any records for this feed, chances are high that there were no changes to any records during this day -- of course, _you_ could login and make some changes yourself... :)

    To adopt this feed URL to your own database, simply replace 'http://demo.refbase.net' with the URL to your own refbase base directory. You may also want to adopt the value of the 'showRows' parameter which specifies how many records will be displayed. You can then use the resulting URL as feed URL when subscribing to this feed in your news reader.

    Btw, the same could be done for records that were *added* to the database using the 'created_date' field instead:

    http://demo.refbase.net/rss.php?where=created_date%20%3D%20CURDATE%28%29&showRows=10

    Matthias

    ---
    [1] (regarding RSS support in refbase-0.8.0: you can't
         subscribe to RSS feeds whose queries include user-specific
         fields. This will hopefully be added in a future release.)