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 www.PHP.net and www.w3schools.com/SQL 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 jason@strangerstudios.com 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`)
) TYPE=MyISAM AUTO_INCREMENT=72 ;
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.