March 9th, 2003 2:53 pm
Lets look at each of the SQL query lines in detail:
DROP TABLE IF EXISTS comments;
This line drops, or delete, any existing table with the name “comments”. Even though this is an extra step that does nothing the first time we create the table, future queries will make sure any old table is delete before we create our table. Of course, we want to follow this query with queries to add back in the data so we don’t loose our existing comments. We can also ALTER the table later (adding fields or removing them) but that is for a later post.
CREATE TABLE comments (
This statement begins a block to create a table. I’ve used the convention of using all UPPER CASE for keywords. SQL is case sensitive so Comments is different that comments.
ID int(11) NOT NULL auto_increment,
This is our first field and will be used as a key or index value in the table. Since we don’t care about the exact value, I’m using the auto_increment field option so that each time you specify NULL as the value for this field, the next unique value will be stored instead. We’ll talk more about this when we look at accessing the records.
PostID text NOT NULL,
This field is used to hold the posting identifier. Since I don’t know if it will be numeric (posts might be identified by a filename), I’m using a text field to store this value. Later we will use this to collect all the comments associated with a given posting. NOTE: unless we make sure that post IDs are unique in our blogging software, the comments will not be correctly associated with posts.
Name text NOT NULL,
This is a text field to hold the name of the person making the comment. The NOT NULL clause means we can’t create a record without having a value for this field (and any other fields that are NOT NULL). I picked this because I want to know who makes comments.
Email text,
This is a text field to hold the e-mail address of the person making the comment. This field can be left NULL (purely my design decision — you could require this).
Comment text,
This field is for the comment.
TimeEntered datetime NOT NULL default '0000-00-00 00:00:00',
This is the date and time that the comment was made. I will use this to sort the comments in order.
PRIMARY KEY (ID)
This statement simply lets the database software build some special access data to speed up a lookup where the ID is specified.
) TYPE=MyISAM;
This statement finished the table statement and states that we will be using the Indexed Sequential Access Method to get to the data (technical detail that does not matter to us)
There is a tool called phpMyAdmin which is a set of PHP pages that provide a simple and powerful method of working with databases. After you put phpMyAdmin under the document root of your web server and do some simple configuration, you can then create tables, add/remove records, update fields, alter tables, etc. all with a nice GUI.
To create our table, simply go to the phpMyAdmin main page and select the proper database (if you have more that one). Then pick the SQL query function and either paste the text of your query into the text field and click “go” or browse to a file with your query and then click “go”. After creating the table, you can look at the table by selecting the structure page. From here you can browse the table, select a subset of the table, insert new records, view the metadata (properties of the fields and tables), and much more.
Another nice feature allows you select a table, and generate a text version of the table’s structure as well as statements containing the table. The output of this function will create a file that can be read by phpMyAdmin to create an exact copy of the table on a different system or to restore a table after it has been deleted. I use this feature to prototype changes on my system and then upload the database changes to my server.
Before we can read our table, we need records in it. Let’s create some dummy records so we have some data to play with when we begin using PHP to access the comments. To add a record, go to the Structure page and select Insert for our comments table. You will see a form that lets you enter data for field in the record. Leave the ID field alone and fill in any other field you wish. NOTE: If you enter data for a field that allows NULL, make sure to uncheck the NULL checkbox or it will ignore you value. Click “go” and you will have a record. Create several records and use the browse function to view, edit, etc. your records.
Now that we have a table and some sample data, we will cover in our next session how PHP works and some simple code that can read our table.