Sometimes you require to create your own RSS feeds to enable user to subscribe to them. In my case the client wanted to display job details on his website. Everyday, someone from his office, types in new job vacancies and these get displayed on his home page. The list has following columns:
- Date
- Company
- Designation
This client desired RSS connectivity with this list so that his website can automatically send new tweets to his Twitter account as soon as new vacancy details are saved in the database.
First, I am showing you how to connect to a MySQL table and thereby creating RSS feed page with the result-set.
Assume that we want to show only two fields on the RSS page viz. Designation and Company. To achieve this, below is the code that we will use for fetching values from the database and creating XML tags from within PHP.
Create a new PHP file with the name say, rsscode.php and paste the below given code into it.
<?php
header("Content-type: text/xml");
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
// Set RSS version.
echo "
<rss version=\"2.0\">";
// Start the XML.
echo "
<channel>
<title>My Website RSS</title>
<description>Here you can subscribe to the RSS Feeds</description>
<link>http://www.example.com</link>";
mysql_connect('localhost:3306', 'my_user_name', 'my_password') or die('ERROR--Connection the database failed');
mysql_select_db('my_database') or die('ERROR--Connection to the database failed');
// Fetch record from the database.
$data = mysql_query("SELECT * FROM listing ORDER BY recordid DESC LIMIT 10");
while($row = mysql_fetch_array($data))
{
// Make complete links
$row[designation] = str_replace("listing/", "http://www.example.com/listing/",$row[designation]);
echo "
<item>
<link>http://www.example.com/listing/details.php?id=".$row[recordid]."</link>
<guid isPermaLink=\"true\">http://www.example.com/listing/details.php?id=".$row[recordid]."</guid>
<title>".$row[designation]."</title>
<description><![CDATA[".$row[company]."]]></description>
<comments>http://www.example.com/listing/details.php?id=".$row[recordid]."#Comments</comments>
</item>";
}
echo "
</channel>
</rss>";
?>
Just change the database user credentials according to your settings and change the column names according to your table.
Here, details.php is the file that will be opened when the user clicks on any feed. The row column mentioned above as row[recordid] is actually the primary key value through which we are identifying the record in the MySQL table that we want to show on the page details.php.
The URL might look something like this when you click any of the feed:
http://www.example.com/listing/details.php?61
You can use the $_GET function of PHP to retrieve the value from the query-string. Here, $_GET will pick the value, 61, from the URL and you can use a query like:
SELECT company, designation FROM listing WHERE recordid = 61;
in your file details.php to display the results in a desired format.
Now, when we have finished with our RSS, we want it to connect to Twitter. For this, the simplest approach I follow is to create an account at TwitterFeed. It is a great tool when you want to send updates simultaneously to Twitter & FaceBook.
When you have finished creating the account, log in to TwitterFeed and click on Create Feed button. A screen as given below will open:
Type in the Feed Name, say, My Site Feed, and in the RSS Feed URL type in the link of your newly created RSS page. Here, it may look something like:
http://www.example.com/listing/rsscode.php
That's it. Save the new Feed and don't forget to enter your Twitter login credentials within TwitterFeed. You can also set the duration when you want TwitterFeed to check updates in your RSS feed page.
Now, each time your job vacancies list gets updated, a new tweet will be automatically sent to Twitter and it will be visible to all your followers.
Through TwitterFeed, you can also connect to FaceBook, just in case you want to send the updates to Twitter & FaceBook simultaneously.
Comments