Storing Content in a Database PHP

Information stored in a database is not limited to short strings, like the 32-character item name. You can create 64K blobs, which are enough to store a goodsized Web page. The advantage here is that pages exist in a very structured environment. They can be identified by 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 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 zero, in which case we understand the message to be the beginning of a thread. The body doesn't have to be plain text. It can contain HTML. In this way it allows users to create their own Web pages using their browsers.

The script in Listing has two modes: listings message titles and viewing a single message. If the messageID variable is empty, then a list of every message in the system is shown organized by thread. This is accomplished by the showMessages function.

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. UL tags are used to display the message titles. The indention aids the user in understanding the hierarchy.
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)
);

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 ree of messages in memory, but I'll leave that as 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, but the core functionality exists. 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.


Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

PHP Topics