PHP/MySQL News with Comments - The Tables
(Page 2 of 7 )
OK, as I mentioned just a moment ago we will need two tables for this news system. The first is simply called news. Here is the table structure.
CREATE TABLE news (
id int(10) unsigned NOT NULL auto_increment,
postdate timestamp(14),
title varchar(50) NOT NULL,
newstext text NOT NULL,
PRIMARY KEY (id),
KEY postdate (postdate)
); |
The first column, id, is an auto increment field. What that means to us is that as every row is inserted into the database, it will increase the number stored in that field by one. This is a very easy way to get a unique id. The three other fields should be fairly self explanatory. You will notice, however, that I am using a timestamp type for the postdate field. The reason I use a timestamp field is that when inserting, you don’t need to specify a value for the field. It will automatically set it to the current date and time. Simple huh?
The second table we need is the comments table. Again the table structure.
CREATE TABLE news_comments (
id int(10) unsigned NOT NULL auto_increment,
news_id int(10) unsigned DEFAULT '0' NOT NULL,
name varchar(40) NOT NULL,
comment text NOT NULL,
PRIMARY KEY (id),
KEY news_id (news_id)
); |
We don’t see anything new here. I would like to comment on the KEY statements you see in both table structures though. Keys are a method of indexing the table. They allow for faster lookups. When you set a primary key, every value in that column must be unique. The other keys that I set, on postdate and news_id, are there to speed queries up. When we are displaying news, we are going to sort it by postdate. The key on that field will speed up the query. When we pull comments out of the database, we will look them up by the news_id they are associated with. Again, by adding a key on that column, we will speed queries up.
Next: Displaying the news >>
More Database Articles Articles
More By Matt Wade
/ 8 
