HomeMySQL Page 2 - Creating the Blog Script for a PHP/MySQL Blogging System
The Database Tables - MySQL
In this second part of a three-part series on blogging with PHP and MySQL, we will be looking at the actual blog. The blog will be simplicity itself. I have created a style sheet that will help in making the blog look clean and neat. The blog will be based purely on a open blog system.
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:
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 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.