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.

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.

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.

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.

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).

   

   Figure 7. Creating a MySQL connection

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

   

   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.