| [ Team LiB ] |
|
23.3 Storing Content in a DatabaseInformation stored in a database is not limited to short strings, like the 32-character item name from Listing 23.3. You can create 64K blobs, which are enough to store a good-sized Web page. The advantage here is that pages exist in a very structured environment. You can identify them with a number, and relationships can be drawn between them using only these numbers. The disadvantage is that since the information is now in a database, you can't just load the file into your favorite editor. You have to balance the costs and benefits; most Web sites don't need every piece of content stored in a database. A situation where it makes a lot of sense to put the content in a database is a Bulletin Board System, or BBS. The system stores messages, which are more than just Web pages. Each message has its own title, creation time, and author. This structure can be conveniently wrapped up into a database table. Furthermore, since each message can be given a unique identifier, we can associate messages in a parent-child tree. A user can create a new thread of discussion that spawns many other messages. Messages can be displayed in this hierarchical structure to facilitate browsing. As with all database-related systems, the first step is to create a table. Listing 23.7 creates a table for storing messages. Each message has a title, the name of the person who posted the message, when the message was posted, a parent message, and the body of text. The parent ID might be NULL, in which case we understand the message to be the beginning of a thread. The body doesn't have to be plaintext. It can contain HTML. In this way it allows users to create their own Web pages using their browsers. Listing 23.7 Create message table
CREATE TABLE Message
(
ID INT NOT NULL AUTO_INCREMENT,
Title VARCHAR(64),
Poster VARCHAR(64),
Created DATETIME,
Parent INT,
Body BLOB,
PRIMARY KEY(ID)
);
The script in Listing 23.8 has two modes: listing message titles and viewing a single message. If the messageID variable is empty, the script shows a list of every message in the system organized by thread. It accomplishes this with the showMessages function. You might want to turn back to Chapter 4, specifically the section on recursion. The showMessages function uses recursion to travel to every branch of the tree of messages. It starts by getting a list of all the messages that have no parent. These are the root-level messages, or beginnings of threads. After showing each root-level message, showMessages is called for the thread. This process continues until a message is found with no children. Unordered-list tags display the message titles. The indention aids the user in understanding the hierarchy. Listing 23.8 A simple BBS
<html>
<head>
<title>Listing 23-8</title>
</head>
<body>
<?php
print("<h1>Leon's BBS</h1>\n");
//connect to server, then test for failure
if(!($dbLink = mysql_connect("localhost", "httpd", "")))
{
print("Failed to connect to database!<br>\n");
print("Aborting!<br\n");
exit();
}
//select database, then test for failure
if(!($dbResult = mysql_query("USE test", $dbLink)))
{
print("Can't use the test database!<BR>\n");
print("Aborting!<BR>\n");
exit();
}
/*
** recursive function that spits out all
** descendent messages
*/
function showMessages($parentID)
{
global $dbLink;
$dateToUse = Date("U");
print("<ul>\n");
$Query = "SELECT ID, Title, Created " .
"FROM bbsMessage " .
"WHERE Parent=$parentID " .
"ORDER BY Created ";
if(!($dbResult = mysql_query($Query, $dbLink)))
{
//can't execute query
print("Couldn't query bbsMessage table!<br>\n");
print("MySQL Reports: " . mysql_error() . "<br>\n");
exit();
}
while($row = mysql_fetch_assoc($dbResult))
{
//show message title as a link to view the body
print("<li>({$row['Created']}) " .
"<a href=\"" .
"{$_SERVER['PHP_SELF']}?messageID={$row['ID']}" .
"\">" .
"{$row['Title']}</a></li>\n");
//show children of this message
showMessages($row['ID']);
}
print("</ul>\n");
}
/*
** print out a form for adding a message with
** parent id given
*/
function postForm($parentID, $useTitle)
{
print("<form action=\"{$_SERVER['PHP_SELF']}\" " .
"method=\"post\">\n" .
"<input type=\"hidden\" name=\"inputParent\" " .
"value=\"$parentID\">\n" .
"<input type=\"hidden\" name=\"ACTION\" " .
"value=\"POST\">\n" .
"<table border=\"1\" cellspacing=\"0\" " .
"cellpadding=\"5\" width=\"400\">\n" .
"<tr>\n" .
"<td width=\"100\"><b>Title</b></td>\n" .
"<td width=\"300\">" .
"<input type=\"text\" name=\"inputTitle\" " .
"size=\"35\" maxlength=\"64\" value=\"$useTitle\">" .
"</td>\n" .
"</tr>\n" .
"<tr>\n" .
"<td width=\"100\"><b>Poster</b></td>\n" .
"<td width=\"300\">" .
"<input type=\"text\" name=\"inputPoster\" " .
"size=\"35\" maxlength=\"64\">" .
"</td>\n" .
"</tr>\n" .
"<tr>\n" .
"<td colspan=\"2\" width=\"400\">" .
"<textarea name=\"inputBody\" " .
"cols=\"45\" rows=\"5\"></textarea>" .
"</td>\n" .
"</tr>\n" .
"<tr>\n" .
"<td colspan=\"2\" width=\"400\" align=\"middle\">" .
"<input type=\"submit\" value=\"Post\">" .
"</td>\n" .
"</tr>\n" .
"</table>\n" .
"</form>\n");
}
/*
** perform actions
*/
if(isset($_REQUEST['ACTION']))
{
if($_REQUEST['ACTION'] == "POST")
{
$Query = "INSERT INTO bbsMessage " .
"(Title, Poster, Created, Parent, Body)" .
"VALUES(" .
"'" . addslashes($_REQUEST['inputTitle']) . "', " .
"'" . addslashes($_REQUEST['inputPoster']) . "', " .
"NOW(), {$_REQUEST['inputParent']}, " .
"'" . addslashes($_REQUEST['inputBody']) . "')";
if(!($dbResult = mysql_query($Query, $dbLink)))
{
//can't execute query
print("Couldn't insert into bbsMessage " .
"table!<br>\n");
print("MySQL Reports: " . mysql_error() .
"<br>\n");
exit();
}
}
}
/*
** Show Message or show list of messages
*/
if(isset($_REQUEST['messageID']) AND
($_REQUEST['messageID'] > 0))
{
$Query = "SELECT ID, Title, Poster, Created, " .
"Parent, Body " .
"FROM bbsMessage " .
"WHERE ID={$_REQUEST['messageID']} ";
if(!($dbResult = mysql_query($Query, $dbLink)))
{
//can't execute query
print("Couldn't query bbsMessage table!<br>\n");
print("MySQL Reports: " . mysql_error() . "<br>\n");
exit();
}
if($row = mysql_fetch_assoc($dbResult))
{
print("<table border=\"1\" cellspacing=\"0\" " .
"cellpadding=\"5\" width=\"400\">\n" .
"<tr>" .
"<td width=\"100\"><b>Title</b></td>" .
"<td width=\"300\">{$row['Title']}</td>" .
"</tr>\n" .
"<tr>" .
"<td width=\"100\"><b>Poster</b></td>" .
"<td width=\"300\">{$row['Poster']}</td>" .
"</tr>\n" .
"<tr>" .
"<td width=\"100\"><b>Posted</b></td>" .
"<td width=\"300\">{$row['Created']}</td>" .
"</tr>\n" .
"<tr>" .
"<td colspan=\"2\" width=\"400\">" .
"{$row['Body']}" .
"</td>" .
"</tr>\n" .
"</table>\n");
postForm($row['ID'], "RE: {$row['Title']}");
}
print("<a href=\"{$_SERVER['PHP_SELF']}\">" .
"List of Messages</a><br>\n");
}
else
{
print("<h2>List of Messages</h2>\n");
// get entire list
showMessages(0);
postForm(0, "");
}
?>
</body>
</html>
For the efficiency-minded, this use of recursion is not optimal. Each thread will cause another call to showMessages, which causes another query to the database. There is a way to query the database once and traverse the tree of messages in memory, but I'll leave that as an exercise for you. If a message title is clicked on, the page is reloaded with messageID set. This causes the script to switch over into the mode where a message is displayed. The fields of the message are displayed in a table. If the message contains any HTML, it will be rendered by the browser, because no attempt is made to filter it out. This restriction is best applied as part of the code that adds a new message. Regardless of the two modes, a form is shown for adding a message. If a message is added while the list of messages is shown, the message will be added to the root level. If a message is added while the user is viewing a message, then it will be considered a reply. The new message will be made a child of the viewed message. This BBS is simple. A more sophisticated solution might involve allowing only authenticated users to add messages or keeping messages private until approved by a moderator. You can use this same structure to build any application that manages user-submitted data, such as a guest book. If you are searching for a sophisticated BBS solution, I suggest checking out Brian Moon's Phorum project <http://www.phorum.org/>. |
| [ Team LiB ] |
|