Syndicate your content using PHP and MySQL
Setting Up An Automated Content Feed From Your Web Site
7th February 2002
Introduction
So, you’ve spent the best part of a year creating your PHP/MySQL driven web site. You’ve got a fair amount of decent, flexible content, and you’re buddies with a couple of other webmasters, so they’ve given you a free banner ad on their site in exchange for one on yours.
Promoting your site can be even harder than developing it. I’ve found that one of the best ways to attract more visitors to your site is by sharing your content with whoever wants it. That’s right; let them publish your articles on their site (as long as they add a link back to your site near the end), write articles specifically for other web sites and eZines and setup a content feed from your site.
Just last week I setup a content feed similar to the one I’m going to describe in this article. I want to share with you exactly how I went about doing so.
What’s that I hear you say? «How do I setup a content feed? I only know PHP and MySQL!». Never fear I say, for in this article I will show you how easy it is to set one up. But first, let’s discuss why you would want to setup a content feed for your site.
Why a content feed?
If your site’s going to succeed on the net, it needs to contain useful, free content targeted directly at a niche market. Take a look at some of the most popular new sites on the ‘net: ZDNet, Slashdot, Cnet, MoreOver, etc. All of these sites provide dynamic content feeds that anyone can use on their site, meaning that if I wanted to display their recent news headlines on my site, I would just have to fill in a form and paste some HTML tags on my site and I’d be done.
If Joe Bloggs posts the HTML code for ZDNet’s content feed on his site, then it’s a win-win situation: Joe is providing valuable news to his visitors that keeps them coming back on a daily basis, and ZDNet get the traffic from Joes visitors actually clicking on the news headlines to see the full story on their site.
Here’s a list of reasons why you should consider creating your own content feed like ZDNet’s:
It’s one of the best forms of free advertising that you can get.
Once other people post your content feed on their site, each headline actually links back to the full article your site. As many of you will know, the more links you have back to your site from other sites, the higher search engines such as Google will position you in their rankings.
Obviously, having others post your content feed on their site will boost your traffic. By how much though depends on the quality of your content and how you name your headlines.
I’ve recently syndicated this sites articles into an XML-based content feed and it truly is working wonders for our visitor count. If you’re familiar with XML, then you can see the XML file.
Hopefully by now I’ve convinced you to setup a content feed for your site. Let’s take a look at creating a sample content feed using some PHP and a MySQL database.
Creating a sample content feed
One of the reasons why content feeds are so popular is that because 99% of the time, all you need to do to add someone elses content feed to your site is add a reference to an external JavaScript file using a HTML <script> tag, like this:
<script language=»JavaScript» src=»http://www.joe-bloggs-news.com/feed.php»>
Let’s assume that I run a small web site and want to publish Joe Blogg’s content feed on my site using the code above. In it, I’ve set the src attribute to a file named feed.php on Joe’s web site (and not a typical JavaScript file that ends with .js).
This isn’t a problem, however, because both IE and Netscape don’t care what the file extension is, just as long as when the file is grabbed, it actually contains valid JavaScript code. Let’s create a sample content feed now. To start with, load the MySQL console application and create a new database named content:
create database content;
use content;
Next, create a new table named articles. This table will contain all of our news items:
create table articles
(
articleId int auto_increment not null,
title varchar(100),
content text,
primary key(articleId),
unique id(articleId)
);
The table we’ve just created should be pretty self-explanatory. It contains three fields: articleId, an integer which is also a primary key and unique identifier. Title, which will contain the headline of each news item, and content, which is a text field that will contain the body of our news item.
Let’s add three news items to our articles table using the following MySQL commands:
insert into articles values(0, ‘PHP scripting language takes world by storm’, ‘It was announced today that at least two million web sites around the world are using the PHP and MySQL language/databasing technique to create sites that are flexible’);
insert into articles values(0, ‘Linux includes G++ compiler’, ‘Linux, one of the worlds most popular operating systems also includes a copy of both the GCC and G++ compilers, which allow you to compile C/C++ program easily from the command line’);
insert into articles values(0, ‘Microsoft announces C#’, ‘Microsoft have today announced that its new .NET framework will be based around a language named C# (pronounced C-Sharp). This language is much like C++ but designed for moder n prorgammers’);
Next, we want to create the PHP script that will retrieve the news from our MySQL database and output it to the browser so that it appears as a bunch of JavaScript code. The PHP scripts name is feed.php and it is available for download here. We will concentrate only on the more important aspects of the script.
$db_server = «localhost»;
$db_db = «content»;
$db_user = «admin»;
$db_pass = «password»;
Our script starts of by creating four variables which will be used to login to our MySQL server. I’m assuming that MySQL is installed on the same machine as where the feed.php script will be setup.
@$sConn = mysql_connect($db_server, $db_user, $db_pass);
@$dConn = mysql_select_db($db_db, $sConn);
if(!$sConn || !$dConn)
{
?>
document.write(«Couldn’t load news»);
<?php
}
Next, we use these four variables to connect to our MySQL server and select the content database. We prepend the mysql_connect and mysql_select_db function calls with the «@» symbol and save the return values to variables. This tells PHP not to write any connection errors to the browser. This is important in our script, because we only want it to return valid JavaScript code.
If either the mysql_connect or mysql_select_db commands fail, they $sConn or $dConn will contain no data. We use an if control to check for this, and if either one of them failed, we output document.write(«Couldn’t load news»); to the browser.
[Note] document.write is a JavaScript function. It takes one argument and outputs the contents of that argument directly to the browser. [End Note]
Using a PHP script to output JavaScript may seem a little strange at first, but remember what I said earlier. All anyone that wants to use our content feed needs to do is setup a <script> tag with its src attribute referencing our feed.php file. As long as our script returns JavaScript code, then it will be processed in just the same way as inline JavaScript by the browser.
$nResult = mysql_query(«select articleId, title from articles order by articleId desc limit 10»);
Using the mysql_query function with a select statement returns the articleId and title fields for the ten most recent articles added to the articles table. The limit keyword takes care of making sure that only ten are returned.
To actually output the news headlines as part of a HTML table, we use a PHP while loop and output several calls to JavaScripts document.write function to build the tables rows, like this:
while($nRow = mysql_fetch_array($nResult))
{
?>
document.write(«<tr>»);
document.write(» <td bgcolor=’#FFEFCE’ height=’25’>»);
document.write(» <p style=’margin-left:5; margin-right:5’>»);
document.write(» <a target=’_blank’ href=’http://www.joe-bloggs-news.com/news.php?newsId=<?php echo $nRow[«articleId»]; ?>’><font face=’verdana’ size=’2′ color=’black’><?php echo str_replace(«\»», «\\\»», $nRow[«title»]); ?></font></a>»);
document.write(» </td>»);
document.write(«<tr>»);
<?php
}
There’s nothing special about this while loop. It uses PHP’s mysql_fetch_array function to retrieve the next record from the $nResult resource and outputs a table row containing a hyper-linked news headline.
The actual URL of each headline is pointing to joe-bloggs-news.com/news.php and includes the id of the article tacked onto the end. You can change that URL to a script on your PHP server and display the news based on the value of the newsId query string value. That’s outside the scope of this article, however.
One important thing to note about the while loop is that it uses PHP’s str_replace function to convert any double quotes in the title to escaped double quotes. If we output an un-escaped double quote as part of the argument to document.write, then when that code is processed by JavaScript, it will assume that the double quote marks the end of the argument. This will cause a JavaScript syntax error.
Sample output for one of the news items using the while loop looks like this:
document.write(«<tr>»);
document.write(» <td bgcolor=’#FFEFCE’ height=’25’>»);
document.write(» <p style=’margin-left:5; margin-right:5’>»);
document.write(» <a target=’_blank’ href=’http://www.joe-bloggs-news.com/news.php?newsId=3’><font face=’verdana’ size=’2′ color=’black’>Microsoft announces C#</font></a>»);
document.write(» </td>»);
document.write(«<tr>»);
That’s actually all we need to do to create a content feed from our sample MySQL database. Let’s now look at setting up the content feed and testing it in our browser.
Testing Our Content Feed
Testing our content feed is simple. Firstly, make sure that the feed.php script is in an Apache aware directory on your web server. Next, create a new HTML file named test.html and save it in the same directory as the feed.php script (I will assume that you have saved it in the /php directory of your web servers root directory). Enter the following HTML into test.html:
<html>
<head>
<title> Joe Bloggs News Test </title>
</head>
<body bgcolor=»#FFFFFF»>
<h1>The Latest News</h1>
<script language=»JavaScript» src=»http://localhost/php/feed.php»></script>
</body>
</html>
Run test.html in your web browser.
If you have a registered domain name (such as mysite.com) that points to a web server running PHP and MySQL, then try creating the content database on that server. Upload the feed.php script too. While still keeping the test.html file on your local machine, replace its src attribute to point to the feed.php script on your web server. Run test.html in your browser and it should appear exactly as before.
What makes our content feed dynamic?
Many things. The fact that we have complete control over the feed.php script on our server means that we could change anything we liked in it (maybe to include a sponsors ad) and any sites that were already using our content feed wouldn’t have to do a thing to get the updated version of our content feed because it’s retrieved from our server every time it’s displayed on their site.
Also, whenever we add a new item to our articles table, it will appear at the top of the content feed. Let’s try adding one now. Using the MySQL console application, enter the following query:
insert into articles values(0, ‘MySQL Version 4 Released’, ‘Today, MysQL AB have released MySQL version 4. It comes packed with a number of new features including transactions and faster query execution plans’);
Return to your browser and hit the refresh button. Notice the change ?
It’s just as easy to remove items from our content feed using the MySQL delete command:
delete from articles where articleId < 3;
This would remove those articles whose articeId is less than three, thus leaving two news headlines in our feed.
So, as you can see, it doesn’t matter whose using our content feed on their site: if they’re using the <script src=»…»> method to include the JavaScript generated by our feed.php script it in their pages, then any new records we add will automatically be visible in the content feed when it’s refreshed in their browser.
Conclusion
Take the sample content feed (which is available as part of the support material for this article) described throughout this article and modify it to match the look and feel of your site. You’ll also need to modify the MySQL queries to extract the right fields from your database.
Once you’ve customised the script for your liking, tell everyone about it: your eZine subscribers, friends, other webmasters, etc. You’ll be amazed by how many of them will agree to post it on their site because it’s so simple to do.
Remember, all that anyone who wants to have your content feed on their site needs to do is include the following HTML code in any page of their site:
<script language=»JavaScript» src=»http://www.joe-bloggs-news.com/feed.php»>
Obviously, replacing the src attribute with your fully qualified domain name and the path to your feed.php script.