Building a Weblog | Page 2

[previous] [next]

Building a Weblog

Building the Database

The first step in the Blogtastic project is to build the database. To begin, create a new database in phpMyAdmin called blogtastic. Within this database, create four tables:

Figure 4-1 shows how the four tables relate to each other. The first relationship is between the blog category (the categories table) and the blog entry (the entries table). To reference the correct category, the same id from the categories table is stored in the cat_id field of the entries table. In database parlance, the id field is known as the primary key, and the cat_id field is known as the foreign key. If these two values match, an explicit connection exists between the tables. In other words, if you know the id of the category (stored in cat_id), you can run a query to pull out the other category information (such as the name of the category) using that id. The second relationship—between the entries table and the comments table—works in exactly the same way; the id field from the entries table is connected to the blog_id in the comments table. This connects each comment with the correct blog entry.

The final table is the logins table, which has no relationship to the others; the table is used simply for logging each user into the application.

Implementing the Database

With the database design laid out, you can create the database tables. In phpMyAdmin, follow the steps discussed in Chapter 2 to add new tables, using these details:

The categories Table

id. Make this a TINYINT (this type is used because there will not be many categories) and turn on auto_increment in the Extras column. Set this field as a primary key. cat. Make this a VARCHAR. Because a category title longer than 20 letters is unlikely, set the size to 20.

The entries Table

id. Make this an INT (several blog entries are possible) and turn on auto_increment. Set this field as a primary key. cat_id. Make this a TINYINT (the same type as the primary key it references—id in the categories table). dateposted. Use the DATETIME type. This data type stores the current date and time in the international ISO standard format, which is pretty clunky, but you can format the date later in PHP. subject. Make this a VARCHAR. Unless your blog title is extremely long, set the length of this field to 100. body. Make this a TEXT field. If you ever want to store very large areas of text, the TEXT type is a good choice. You don't need to specify a length.

The comments Table

id. Make this an INT (several comments are likely). Turn on auto_increment and set this field as a primary key. blog_id. Make this an INT (the same type as the id field in the entries table, to which it is related). dateposted. Use the DATETIME type. name. Make this a VARCHAR. Because comment titles longer than 50 characters is unlikely, set the length to 50. comment. This is the main body of the comment. Set to the TEXT type.

The logins Table

id. Make this a TINYINT (there will be very few logins; possibly only one). Turn on auto_increment and set this field as a primary key. username. Make this a VARCHAR and give it a length of 10. (Enforcing a maximum length for the username is a common practice.) password. Make this a VARCHAR and give it a length of 10. (As with usernames, enforcing a maximum length for a password is a common practice.)

Inserting Sample Data

With the tables created, insert some initial data into them so that you can test the code as you write it (again using phpMyAdmin). Remember that when you are adding data to any of these tables, do not fill in a number in the id column; this value will be handled by auto_increment.

Because this is the first project in the book, sample data has been added to the tables for you to ensure that everything connects as expected. As you work through the book and understand the database concepts in better detail, you can add additional sample data.

Sample Data for the categories Table
Add the following categories in this order: Life, Work, Music, and Food.

Sample Data for the entries Table
Add the information in Table 4-1 to the entries table.

Both entries reference the Life entry in the categories table, via the cat_id. In the dateposted field, use the Function combo box to select the NOW option, which fills the field with the date and time you add the entry to the table.

Sample Data for the comments Table

Add the information in Table 4-2 to the comments table.

In this table, reference the first blog entry (the one with the 'Welcome to my blog!' subject) by supplying a value of 1 to the blog_id field.

Sample Data for the logins Table
In this table, add a single entry with a username and password of your choice. This example includes a username of "jono" and a password of "bacon".

[previous] [next]

URL: