Showcase and discover digital art at yex

Follow Design Stacks

Subscribe to our free newsletter to get all our latest tutorials and articles delivered directly to your inbox!

Building a Forum with Dreamweaver – Part 1: Setting Up Your Database

Setting Up Your Database

The forum is completely dynamic. It stores and extracts all information from a database. A typical forum is basically a collection of messages posted by users and organized by topics. Therefore you need at least these three tables for your forum database:

  • one for user information: user_usr
  • one for messages: message_msp
  • one for topics: topic_top

Table 1 lists the structure of the table for the topics.

Table 1. Table Structure for Storing Topics
Topics topic_top
id_top Primary key: unique numeric identifier of each topic
title_top Title of the topic, displayed in the browser (for example, Economy, Business)
description_top Short description of the topic, displayed below the topic title

The SQL code that creates the topic_top table is pretty straightforward. Notice that the topic title is defined as a unique key, meaning there shouldn’t be any topics with the same title:

CREATE TABLE topic_top (
id_top int(11) NOT NULL auto_increment,
title_top varchar(100) NOT NULL default '',
description_top varchar(200) default NULL,
PRIMARY KEY (id_top),
UNIQUE KEY title_top (title_top)

Table 2 shows the structure of the table that stores messages.

Table 2. Table Structure for Storing Messages
Messages message_msg
id_msg Primary key: unique numeric identifier of each message
idtop_msg Foreign key: numeric ID of the topic to which this message is associated
idmsg_msg Self foreign key: ID of the parent message (used when posting a reply to link the current message to the message that is being replied to)
id_init_msg Self foreign key that stores the ID of the first message in the thread
idusr_msg Foreign key to the user_usr table (stores the ID of the user that posted the message)
date_msg Date when the message was posted
subject_msg Message subject
content_msg Actual message contents
subscribe_msg Flag indicating if the message author is subscribed to this thread or not (1/0)

Here is the code that creates the message_msg table:

CREATE TABLE message_msg (
id_msg int(11) NOT NULL auto_increment,
idtop_msg int(11) NOT NULL default '0',
idmsg_msg int(11) default NULL,
id_init_msg int(11) NOT NULL default '0',
idusr_msg int(11) NOT NULL default '0',
date_msg datetime NOT NULL default '0000-00-00 00:00:00',
subject_msg varchar(100) default NULL,
content_msg text NOT NULL,
subscribe_msg tinyint(4) NOT NULL default '0',
PRIMARY KEY (id_msg)

Table 3 shows the table that stores user information.

Table 3. Table Structure for Storing User Information
Users user_usr
id_usr Primary key: unique numeric identifier of each user
name_usr User name used for authentication to the forum
email_usr E-mail address of the user (for example,
photo_usr Filename of the user’s photo
password_usr User’s password in encrypted format
active_usr Flag (1/0) that indicates whether the user account has been activated or not
randomkey_usr Randomly generated code used for user account activation

The following snippet shows the SQL code for setting up the table:

CREATE TABLE user_usr (
id_usr int(11) NOT NULL auto_increment,
name_usr varchar(100) NOT NULL default '',
email_usr varchar(150) NOT NULL default '',
password_usr varchar(100) NOT NULL default '',
active_usr tinyint(2) NOT NULL default '0',
randomkey_usr varchar(100) NOT NULL default '',
PRIMARY KEY (id_usr),
UNIQUE KEY name_usr (name_usr)

Notice that because the user name must be unique, it was defined as a unique key.

If you are unfamiliar with database terminology (primary keys, foreign keys, self foreign keys, and the like) and database architecture techniques.

Figure 6 provides an overall view of the database structure.

Database structure

Figure 6. Database structure

Now that you have an idea of how your database will look, fire up the sample SQL script in your MySQL console or in your favorite database management software (such as phpMyAdmin) and create the forum database. Include the following line at the beginning if you want to create a separate database:

create database mm_forum;

The sample SQL script also contains some dummy data, which you will use when building the forum front end. Now you’re ready to connect to your newly created database.

Connecting to the Database

To connect to the database in Dreamweaver, follow these steps:

  1. Start Dreamweaver.
  2. Open the index.php page from the forum site.
  3. Choose Application panel > Databases tab, click Plus (+), and select MySQL Connection (see Figure 7).

    Creating a MySQL connection

    Figure 7. Creating a MySQL connection

  4. Configure your connection to your newly created database (see Figure 8).

    MySQL Connection dialog box

    Figure 8. MySQL Connection dialog box

If you are not sure how to connect to the database server, ask your system administrator for the proper access information. Click the Select button to locate the forum database. You can test the connection using the Test button.

In the next section you will learn how to build the forum home page, which displays a list of topic titles and descriptions.