Making the Blog Part 2

The first MtB post was merely an introduction of the idea for this series. In this, the second installment, I will describe the MySQL schema used to store post information. Future MtB posts will go into the coding behind the various public and tools-oriented pages used to display and update the posts. So let’s get started.

My blog is written in PHP and uses a MySQL database to store and retrieve the posts and other information needed to make the site function. If you haven’t used PHP or MySQL before, you might want to checkout and to learn more about these great technologies.

If you are already familiar with PHP and MySQL or just want to jump into things “balls-out”, then let me start by describing the schema or structure of my MySQL tables.

Currently, the DB has just one table, call it post. The post table has the following fields:

Field Type Description
id int(11) The Primary Key for each post.
poster varcar(32) Who is posting the post. This is usually me, but could theoretically be the Pope or something. This field is also marked as an index.
title varchar(128) This is the title of the post, displayed in the colored header.
type char(1) This type is a letter referring to which category the post should be entered into. This allows posts to be filtered by type. Currently there are 5 types: News, Games, Writing, Art, and Other.
text longtext This is the body of the post.
date datetime The date of posting.
keywords varchar(128) This is an extra indexed field where I enter various keywords for each post. For instance a keyword for this post could be PHP or MySQL or Fuddy Duddy. These keywords aren’t currently displayed on the post, but I plan on later adding a page to search posts by keyword.

Now how do we setup this table? Well, I used PHP MyAdmin. It should be pretty self explanatary how to create the above table with this tool. If you haven’t already tried, browse around PHP MyAdmin, find the new table button, and take a stab.

If you’d like to (or need to) use SQL to create the table, here is an SQL dump of the table:

CREATE TABLE `jasonposts` (
  `id` int(11) NOT NULL auto_increment,
  `poster` varchar(32) NOT NULL default ‘jason’,
  `title` varchar(128) NOT NULL default ”,
  `type` char(1) NOT NULL default ”,
  `text` longtext NOT NULL,
  `date` datetime NOT NULL default ‘0000-00-00 00:00:00’,
  `keywords` varchar(128) NOT NULL default ”,
  PRIMARY KEY  (`id`),
  KEY `poster` (`poster`,`title`,`type`,`keywords`),
  KEY `keywords` (`keywords`)

I’ll let you all ruminate on this information. The next installment in this series will cover the front page and how posts are displayed and filtered using PHP and SQL queries of this database.

Look forward.