Diary of A Webmaster Part 2 - Create a Content Feed

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 script's name is feed.php and it's available for download here. We'll concentrate only on the more important aspects of the script.

header("Content-Type: text/javascript");

The first thing the script must do is declare that it will send out JavaScript code, as opposed to HTML. By default, PHP identifies the type of the output with a Content-Type: text/html header. To ensure that this script's output is identified as JavaScript, however, we must set the header ourselves.

$db_server = "localhost";  
$db_db = "content";  
$db_user = "admin";  
$db_pass = "password";

To begin, our script creates four variables that will be used to log in to our MySQL server. I'm assuming that MySQL is installed on the same machine on which the feed.php script will be set up.

$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 add the "@" symbol to the beginning of the mysql_connect and mysql_select_db function calls. 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, then $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.

document.write is a JavaScript function. It takes one argument and outputs the contents of that argument directly to the browser.

Using a PHP script to output JavaScript may seem a little strange at first, but remember what I said earlier. All the user needs to do is set up a <script> tag whose src attribute references our feed.php file. As long as it returns JavaScript code, the browser will process it in the same way as it would inline JavaScript.

$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 articles most recently added to the articles table. The limit keyword makes 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 JavaScript's document.write function to build the table's rows, like this:

while($nRow = mysql_fetch_array($nResult))  
{  
  ?>  
  document.write('<tr>');  
  document.write('  <td>');  
  document.write('  <a target="_blank"  
href="http://www.jb-news.com/news.php?newsId=<?=$nRow["articleId"]?>"  
><?=addslashes($nRow["title"])?></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 result set and outputs a table row that contains a hyper-linked news headline.

The actual URL of each headline points to http://www.jb-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 code inside the while loop is that it uses PHP's addslashes function to convert any single quotes in the title to escaped single quotes by adding a backslash to them. If we were to output an un-escaped quote as part of the argument to document.write, then when that code was processed by JavaScript, it would assume that the quote marked the end of the argument, causing 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>');  
document.write(' <a target="_blank"  
href="http://www.jb-news.com/news.php?newsId=3"  
>Microsoft announces C#</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 set up the content feed and test it in our browser.

Testing Our Content Feed

To test our content feed is simple. First, make sure that the feed.php script is in a directory within your Web server's document folder structure. Next, create a new HTML file named test.html and save it in the same directory as the feed.php script (I'll assume that you've saved them both in the /php subdirectory of your Web server's 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" type="text/js"  
src="feed.php"></script>  
</body>  
</html>

Run test.html in your Web browser. Assuming you used our sample feed.php, which contains additional code to format the results, the page should look something like this:

If you have a registered domain name (such as mysite.com) that points to a Web server running PHP and MySQL, then try to create the content database on that server. Upload the feed.php script too. While keeping the test.html file on your local machine, replace its src attribute to point to the feed.php script on your Web server. Then run test.html in your browser -- it should appear exactly as before.