Blog archives for March, 2003

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.

Bob asked how hard it would be to create a comment system using MySQL, PHP, and other technologies available on our hosting service. Well, that project sounds like a great way to provide a little education and have something for me to talk about in my blog. I’m going to walk through the process I would take to develop such a service. In the process, we will likely learn things that force us to go back and redo parts — that is part of the process. I’ll also take time to explain other aspects of the tools and technology along the way.

To get started, you will need local copies of all the software that is on the hosting site. Therefore you will need to install and setup a HTTP web server (Apache), a database (MySQL), some web scripting language (PHP), and a few tools (phpMyAdmin, text editor, ftp client).

Once the software is installed and working, we will need a place store our comments. MySQL is a good, free, relational database tool that is used on most commercial hosting sites. Let me define a few database terms.

RDBMS

Relational DataBase Management System (RDBMS) is a program that manages tables of relations and allows queries and modification of that data

Relation

As the term implies, we are talking about things that are related. More specifically, a group of values (attributes) that are all related to some specific event, concept, action, person, thing, etc. For our purposes, these relations (or records), can be though of as a “row” in a “table” where we will store our comments.

Row

Another name for a relation.

Attribute

A data element in a relation. For example, you might have an ID attribute to hold the index or key value or a Name attribute to hold a person’s name.

Column

Another name for attribute.

Table

A collection of relations (rows). NOTE, all relations in a given table have exactly the same shape (i.e. same attributes in each relation). Some attributes may be NULL.

NULL

A value that means there is no value. NOTE: NULL is not the same as 0 or the empty string — it truly means no value.

SQL

Structured Query Language — The computer language that is used to access data in a RDBMS. The language has a specific syntax (like C or Java).

Query

A request to the database. These requests can be to get data, delete data, modify table structures, etc. One of the most common query is the SELECT query.

SELECT

A query that accesses (reads) data from a specific table in the database. An example SELECT in the SQL language would be: “SELECT id, name FROM comments ORDER BY id“

need to create a database using the MySQL tools for you to place your tables in. This is a one time event so see the MySQL docs for how to do that.

Before we can store any data, we need to create a table in our database. Let’s look at an example MySQL query that would create our table:

#
# Table structure for table `comments`
#
DROP TABLE IF EXISTS comments;
CREATE TABLE comments (
  ID int(11) NOT NULL auto_increment,
  PostID text NOT NULL,
  Name text NOT NULL,
  Email text,
  Comment text,
  TimeEntered datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (ID)
) TYPE=MyISAM;

This query will create a table called “comments” that has columns to store a record id, the blog post this comment is related to, the name of the person who posted the comment, that person’s email address, the comment text, and the time the comment was posted. We could also include the poster’s URL or other items but this table gives us the basic information we need for a comment system.

Next time we’ll talk about each statement and how to execute this query using a tool called phpMyAdmin.