Creating the Blog Script for a PHP/MySQL Blogging System - The Database Tables
(Page 2 of 4 )
Here are the tables that will hold our messages:
Articles tbl:
CREATE TABLE `article` (
`artid` int(5) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`comments` text NOT NULL,
`date_posted` date NOT NULL default '0000-00-00',
`categoryID` int(4) NOT NULL default '0',
`artchild` int(5) NOT NULL default '0',
PRIMARY KEY (`artid`)
) TYPE=MyISAM AUTO_INCREMENT=30 ;
Most of the fields in the table should be self explanatory, except perhaps the "artchild" column. The artchild column will hold the message ID of a message that users reply to. This is how it works: the first time a article is written, its artchild value will be “0” and it will have an automatically created number. When a response is created to this article the new response article’s artchild will have the auto number value added to its artchild column.
So for example if we wanted to retrieve all the articles related to a certain topic, say topic number eight, then all we need to do is retrieve all articles that have an "artchild" value of eight. The categoryID is the foreign key. It represents the ID of the category name in the category table, which we have not yet talked about. That is presented in the code below:
Categories tbl:
CREATE TABLE `categories` (
`catid` int(5) NOT NULL auto_increment,
`category` varchar(255) NOT NULL default '',
PRIMARY KEY (`catid`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
This table will hold the category names of the topics that will be used in the blog. The categories will be managed by the administrator in the admin section of the blog.
The Blog
The blog itself will really have only two pages. The index.php page will show a list of all the main topics in the database. The comments.php page will enable you to view comments made to a particular message, and also give you the chance to comment on the article. There will be an additional page, functions.php, which will hold all the functions used in the blog.
The blog will only allow the administrator to introduce new discussions. The actual users will only be able to comment on those discussions. This is because a blog by its very nature is like a web based personal diary, and as with any other diary you don’t let other people write in it unless you personally want them to.
Next: index.php >>
More MySQL Articles
More By Jacques Noah
