Developer

Building a dynamic Web site, part 5: Putting it together

Vincent Danen rounds out the news subsystem of your dynamic Web site in part five of this Daily Drill Down series. Read on to find out how to create a page that will retrieve information from your database and display it for your users.

Welcome to part five of Vincent Danen’s Building a dynamic Web site series! Previously, Vincent decided to use Apache, MySQL, PHP4, and Perl as the building blocks of the project. He explained how to install and configure these applications, fine-tuned PHP4, and built a few simple debug pages to help with site development. In part four, he showed you how to design the database for your Web site news and built the administrative page to add entries to the database via the Web site. If you’ve missed any of the previous parts to this series, check them out:


In this part of our Daily Drill Down series, I’ll explain how to create the page that will actually retrieve the information from the database and display it for users to view. I’ll also show you some interesting variations, like how to create a short list of news titles with a link to another dynamic page that displays the actual news contents.

Create the front page
In the last part of this series, we wrote the code to handle the back end of the site, which allowed you to easily add the news items to be displayed on the Web site. As the administrator or maintainer of the site, you are now able to add news items at will. The only task remaining is to create a page to display these news items to the visitors coming to your Web site.

This page is easy to create and is quite similar to what you did with the administrative page; you just do it backwards, to some degree. Most likely, your site will have an index page that on a portion of which you will display your news items. Most sites do not employ the entire page to display site news with good reason: You want the news items to be an added feature to your site, not become your entire site.

To split the page into separate components, you’ll need to have an understanding of tables or frames. Personally, I prefer tables to frames as they provide a much cleaner look and greater flexibility. At any rate, any designer who wants to split his or her pages into components is best off doing so with tables. You can achieve a consistent look with properly designed and implemented tables, and they provide better compatibility for older browsers (and text-based browsers like Lynx), as well.

The first thing you’ll want to do is decide how to display the news. Since we’re storing the date in the database with a specific format, namely YYYY_MM_DD, we can sort the dates quite easily if we want to.

Next, you’ll need to write the code to retrieve the information from the database. This simple piece of code looks like this:
<?
 mysql_pconnect("localhost","root","$r_pw")
 or die("Unable to connect to SQL server");
 mysql_select_db("Website") or die("Unable to select database");
 $query = "SELECT * FROM news ORDER BY date DESC";
 $news = mysql_query($query) or die("Select failed!");
?>


The code above is a very simple piece of retrieval code that can be reused anywhere in your Web site. The first line establishes a persistent connection to the MySQL server on localhost as the user root with the password stored in the variable $r_pw . (See part four for a discussion of this variable and how to secure important passwords.)

The second line either selects the Website database or prints an error message in the HTML page indicating that there was an error. The next line assigns the value of the variable $query. In this case, we’re using SQL syntax for the operation we wish to perform, like so:
SELECT * FROM news ORDER BY date DESC

The code above also tells the MySQL server to select all entries in the news table and sort them by the date field and to give us the information in descending order, or the newest entries first and the oldest entries (those first in the database) last. If at all possible, it is much more efficient to do your sorting within the SQL syntax. The PHP code to perform a reverse sort, although not entirely too complex, is more code than you need to write since MySQL can provide this information in the format you prefer.

Finally, perform the query specified by $query and assign the output to the $news variable. From this point forward, we’ll be using $news to access our information.

With the code above, you’ve told MySQL to give you the news items in reverse, sorted by date. This will allow you to display the information from the most current news items back to the oldest news items. Initially, you won’t have many entries in your news database. But as your site grows and you have more items to make your viewers aware of, your database will grow. Over time, it may become quite large.

The last thing you’d want to do is generate a page with more news items than anyone would realistically be interested in. For this reason, you should choose the number of news items you wish to display on the page. This number could be five or 10 news items to be displayed at any given time, depending on the placement and size of your index page. By placing a cap on the number of items to display, you’ll ensure that your page doesn’t get saturated by news items, creating an unbalanced page.

To create a restriction on the number of items to display, you’ll need to create a loop and iterate it each time you process a news item (until you reach the maximum number you want to display). The rest of the code to display the news items should look something like what follows. (In this example, I’ve looped six times, which gives me six news items to display.)
<?
 $i = 0;
 // This will loop up to six times:
 while ($newsx = mysql_fetch_array($news)) {
 if ($i == 6) {
 // We have displayed all six so now we exit the loop
 break;
 }
 // This increases the value of $i by one each time
 $i++;
?>

<b><? echo $newsx['title']; ?><br />
<i><b><? echo $newsx['date']; ?>:</b></i>
Posted by: <? echo $newsx['author']; ?><br />
<? echo $newsx['body']; ?>
<hr />
<? } ?>


Now, how do you use all of this code? Well, the first thing you do is assign the variable $i to a value of 0. This variable is your counter. Once it reaches a count of six, you want to exit the loop since you want to display only six items. The next thing you want to do is fetch one entry from the $news variable. (Remember, you assigned the results of your query to this variable.) Save the array to a new variable called $newsx. Then, test to see whether or not $i is equal to the number 6. If it is, break out of the loop. If it isn't, increase the value of $i by one (which is represented by the code $i++).

Next, break out of PHP and write the HTML code to format and display the news items. The HTML code isn't overly creative, but it's functional. Within the code that determines how to display the output (feel free to spice it up), break in and out of PHP to echo the contents of your array. By using the variable $newsx['author'], you display the value of the author of the news item for each item you store, with the exception of the id field. Since the id field is reserved for making your life easier, there’s no need to display it to users.

Finally, you must end the while() loop, so you have a solitary ending curly brace in the PHP markers (written as <? } ?>).

The output of the first entry in the database will look something like this:
Testing news items
2000_07_30: Posted by: Vincent Danen
This is a test of the new news server on this dynamic website!


Displaying single news items
Suppose your news items are a little longer and it isn’t feasible to display them entirely on the front page. You can still use the above code to display the date, author, and title of the item without displaying the body of the item. You can also turn the title into a hyperlink pointing to a special page that will display the item. This technique is a little different but not remarkably so. You’d still use the same code to retrieve the information out of the database and the same basic code to display the information. The only thing that you’d need to change would be the HTML code you use to display the news item. Let's change the above code to look like this:
<?
 $i = 0;
 // This will loop up to six times:
 while ($newsx = mysql_fetch_array($news)) {
 if ($i == 6) {
 // We have displayed all six so now we exit the loop
 break;
 }
 // This increases the value of $i by one each time
 $i++;
?>

<b><a href="/news.php?id=<? echo $newsx['id']; ?>">
<? echo $newsx['title']; ?></a><br />
<i><b><? echo $newsx['date']; ?>:</b></i>
Posted by: <? echo $newsx['author']; ?><br />
<hr />
<? } ?>


Notice the change? You’ve turned the title text into a hyperlink to a special page called news.php and you’ve also passed it an argument consisting of the id value of the news item being displayed. For example, the URL to the new page may end up being
/news.php?id=0000023

if the id number associated with this news item was the string 0000023 or the number 23. However the database stores the number (whether padded with zeros or not) is how it will be inserted into the URL.

Now that you have a dynamic link, you need to create the news.php page. This page will be unique in that it will serve all the news items from a single page. What I mean by this is that for every news item in your database, news.php will be the single page to display that item. The dependent factor here is the id that is passed to the page in the URL. The following code will be the basis for the display page:
<?
 mysql_pconnect("localhost","root","$r_pw")
 or die("Unable to connect to SQL server");
 mysql_select_db("Website") or die("Unable to select database");
 $query = "SELECT * FROM news WHERE id = \"$id\"";
 $news = mysql_query($query) or die("Select failed!");
?>
This code is a little different from what we've seen previously.
The only thing that changed is the value of $query, or the SQL
syntax we are using. The SQL syntax is
SELECT * FROM news WHERE id = "$id"


Notice that you have to escape the quotation marks with the backslash ( \ ) character. The reason for this is that you need to provide the value of $id to MySQL in quotation marks. You can't use literal quotation marks in the string you assign to $query, so you must escape them first.

Basically, you’re telling the MySQL server that you want the news item associated with the id represented by the $id variable. Using the above example, you’d pass the following to MySQL:
SELECT * FROM news WHERE id = "0000023"

In this instance, the entry associated with that id would be assigned to the value of $news. You still need to turn the results of the query into an array for easy referencing, so you’d then add the following command to your PHP code:
<? $newsx = mysql_fetch_array($news); ?>

You can now reference all of the elements of the database entry via the $newsx array, so you might use the following code to display the news item:
<b><? echo $newsx['title']; ?></b><br />
<i><? echo $newsx['date']; ?>:</i><br />
By <? echo $newsx['author']; ?><br />
<hr />
<p><? echo $newsx['body']; ?></p>


And that's it! Obviously, a little more creative energy is in order to make it look good, but that's the basis of what needs to be done.

At this point, you have a fully functional dynamic news system for your Web site. You have the basic components necessary to deal with everything from inserting new news items to retrieving and displaying the news items stored in the database. The administrative interface, along with the code to display a few entries on your main news (or index) page and the code necessary to display each item separately, give you the ability to manage a nice news subsystem on your site.

There are many ways you can tailor this technique to fit your needs other than those I've discussed. Beyond changing the number of entries to display on your main page and deciding whether to display full news items or simply their titles and dates, you can take the above code and change it to your heart's content to provide other ways of accessing the data. For example, you can easily make a page that displays older news items sorted by year or month and show news items for that time period by doing some simple regular expression matching. You can fancy up the HTML code surrounding the PHP code to make the displays unique to your site and to get them to blend in with your other content.

The only thing missing from all of these tools is an easy method of modifying your entries. For example, if you were to insert a news item and you spelled something wrong or you got the date wrong, you’d have to go into the MySQL command-line interface to modify or delete the entry. If you delete the entry, you’ll then have to reenter all of the information, and this can be quite cumbersome for long news entries. So, to round out the news subsystem, let’s create three final administrative pages that you can use to modify news items in your database.

Create pages to edit news items
The first page is a simple list of all the items in your database with a link to the page that will be used to edit the information. Here’s the code you need to make that list of news items:
<?
 mysql_pconnect("localhost","root","$r_pw")
 or die("Unable to connect to SQL server");
 mysql_select_db("Website") or die("Unable to select database");
 $query = "SELECT * FROM news ORDER BY date DESC";
 $news = mysql_query($query) or die("Select failed!");
 $newsx = mysql_fetch_array($news);

 while ($newsx = mysql_fetch_array($news)) {
?>

<a href="/admin/editnews.php?id=<? echo $newsx['id']; ?>">
<? echo $newsx['title']; ?></a>,
<i><b><? echo $newsx['date']; ?></b></i>

<? } ?>
This will make a list of news items in the format
Check out this new thing!, 2000_08_10
Testing news items, 2000_07_30


The title of the news item serves as the hyperlink to the page you’ll be using to edit the news, which in this case is editnews.php in your administrative directory. Remember to secure it with .htaccess! The same basic principle applies. The link uses the unique id number for each entry to create a reference for the editnews.php page. That page will use the id number to retrieve the information from the database and allow you to manipulate it and resubmit the changes to the database.

The editnews.php page should look something like this:
<?
 mysql_pconnect("localhost","root","$r_pw")
 or die("Unable to connect to SQL server");
 mysql_select_db("Website") or die("Unable to select database");
 $query = "SELECT * FROM news WHERE id = \"$id\"";
 $news = mysql_query($query) or die("Select failed!");
 $newsx = mysql_fetch_array($news);
?>

<head>
<body>
...
<form method="GET" action="editdone.php3">
<table>
<tr>
 <td>
 Author Name:
 </td>
 <td>
 <input type="text" name="author" value="<? echo $newsx['author']; ?>">
 </td>
 </tr>
 <tr>
 <td>
 Title:
 </td>
 <td>
 <input type="text" name="title" value="<? echo $newsx['title']; ?>">
 </td>
 </tr>
 <tr>
 <td>
 News Body:
 </td>
 <td>
<textarea name="body" rows="5" cols="50"><? echo $newsx['body']; ?></textarea>
 </td>
</tr>
</table>
<input type="hidden" name="id" value="<? echo $id; ?>">
<center><input type="submit" value="Modify"></center>
</form>
...
</body>
</html>


This creates a form that inserts the information retrieved from the database into the various fields. By doing this, you can directly modify the text as it is stored in the database. After you’ve finished and you press the Modify button, you’ll be taken to the final page, aptly called editdone.php.

The editdone.php page will actually take the values you’ve modified and change the entry in the database. Notice in editnews.php that you have a hidden entry containing the id number. This is important: Without the id number, you cannot accurately update the database.

Your editdone.php page should merely present a link pointing back to your main administration index page, if you use one, or a link to somewhere else on your site or an automatic referrer URL to another part of your site. This is easily accomplished in PHP as well, using this code:
<?
 mysql_pconnect("localhost","root","$r_pw")
 or die("Unable to connect to SQL server");
 mysql_select_db("Website") or die("Unable to select database");
 $query = "UPDATE news SET ";
 $query .= "author=\"$author\", ";
 $query .= "title=\"$title\", ";
 $query .= "date=\"$date\", ";
 $query .= "body=\"$body\" ";
 $query .= "WHERE (id=$id) "
 $result = mysql_db_query(news, "$query");
 if (!$result) {
 echo "There was an SQL error!<br />";
 echo mysql_errno().": ".mysql_error()."<br />";
 } else {
 Header("Location: http://www.mysite.com/index.php");
 }
?>


In fact, the above code can be your entire page. What it does is relatively simple. It connects to the MySQL server, and then it executes a different kind of query. This is an update query; the SQL syntax, as displayed above, will insert the new values of each field into the database. You continue the query by appending new strings to the query by using the .= operator. Finally, you process the query, and the result of the query is returned to the variable $result. If $result doesn't exist, there was an error, and the page displayed will indicate the MySQL error number and error message. If $result does exist, it means that the operation completed successfully and you’ll be redirected to the page www.mysite.com/index.php.

Conclusion
Building a dynamic Web site is by no means an easy task. It is easier to maintain, however, and also easier to use. It is a little more difficult to develop than standard static sites, but this extra work really pays off. Understanding the benefits of a dynamic Web site will usually encourage people to begin moving portions of their Web sites into SQL-driven databases using scripting languages like PHP or Perl to handle the transactions with the database.

We've finally rounded out the news subsystem of the Web site. It took a little bit of work, but we accomplished it. The code illustrated in this series of Daily Drill Downs is generic enough to be easily adaptable to any environment and almost any need. The same basic principles apply whether you’re building a news system or a catalog of the music CDs you own for your personal intranet. In fact, using MySQL and PHP, you can create efficient Web-driven interfaces to the database to store all kinds of information, whether it be a personal inventory or an e-commerce Web site.

Vincent Danen, a native Canadian in Edmonton, Alberta, is an avid Linux "revolutionary" and a firm believer in the Open Source philosophy. He attempts to contribute to the Linux cause in as many ways as possible, from his Freezer Burn Web site to local advocacy in his hometown. Owner of a Linux consulting firm, Vincent is also the security updates manager for MandrakeSoft, creators of the Linux-Mandrake operating system. Vincent is a certified Linux Administrator by Tekmetrics.com.

The authors and editors have taken care in preparation of the content contained herein, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

About Vincent Danen

Vincent Danen works on the Red Hat Security Response Team and lives in Canada. He has been writing about and developing on Linux for over 10 years and is a veteran Mac user.

Editor's Picks