Building a Weblog | Page 2
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
The entries Table
The comments Table
The logins Table
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
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
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".