phpHoo, Part I | 4

MySQL - Creating our database

The first thing we'll need to do with MySQL is create our database. The easiest way to do this is to first connect to the MySQL server:

mysql -u username -p
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 387 to server version: 3.21.32a

Type 'help' for help.

mysql>


You should now be at the MySQL prompt. If you receive an error at this point, it may be that you entered your database username or password incorrectly. Check with your server administrator to resolve this problem.

To create our database, issue the create command:

mysql> create database phpHoo;
Query OK, 1 row affected (0.00 sec)


If you do not receive the "Query OK" response, check the syntax of your create command. If you've entered it correctly but still receive errors, it may be that you have not been granted "Create" permissions in the database permissions table. If this is the case, you'll need to discuss the problem with your server administrator. Teaching you SQL administrative tasks is beyond the scope of this tutorial, so we'll assume your database is created.

Now if you log off (quit) the MySQL server and want to access this database directly, the command would be:

mysql -u username -p phpHoo

For now, let's use the same session. To tell MySQL to use the new database, issue the 'use' command:

mysql> use phpHoo;
Database changed

MySQL will use the phpHoo database for all future queries and operations. We are now ready to start creating our tables. In relational databases, data is stored in a table->column->row->element format. It can best be illustrated as a grid:

TABLE    -----------------------------------------
COLUMN   -   CatID  -  CatName   -   CatParent   -
ROW 0    -     1    -    Art     -      NULL     -
ROW 1    -     2    -  Computers -      NULL     -
ROW 2    -     3    -  Religion  -      NULL     -
etc..    -----------------------------------------

This is a graphic (albeit poor) representation of the Table->Column->Row->Element data structure. In this case, it's the Categories table that we're about to create. The category name of row 1 of this structure would look like this:

    Table       ->    Column    ->    Row #    ->    Element
    Categories  ->    CatName   ->    Row 1    ->    Computers

Now that you have a clearer idea of how our data is going to be represented, we need to design and create the structure of our Categories table. Here is the CREATE statement we'll use:

    mysql> create table Categories (
           CatID bigint(21) NOT NULL auto_increment,
           CatName varchar(32) NOT NULL,
           CatParent bigint(21),
           PRIMARY KEY(CatID),
           UNIQUE (CatName)
           );
    Query OK, 0 rows affected (0.00 sec)

Since the Category listing is going to be hierarchical, there are going to be Categories with sub-categories. Those sub-categories are quite possibly going to have more sub-categories. Each category needs to have it's own unique identifier AND sub-categories need to keep track of their parent categories.

This defines a column named CatID. The element for this column will be a big integer up to 21 bytes (digits) in length. 'NOT NULL' tells MySQL that this element can never be empty. 'auto_increment' tells MySQL to automatically increment this ID number by 1 every time we add a new category. So the first category we add is going to be CatID #1, the second will automatically be CatID #2 etc.

Each category needs to have it's own name. 'varchar(32)' tells MySQL that this element will be a variable length string, with a maximum of 32 characters. If you feel this is too short for your Category names, feel free to make it a larger size. Once again, we use 'NOT NULL' to tell MySQL that this element can never be empty. (This is a bit of a misnomer, since MySQL secretly ignores the "NOT NULL" directive on string columns, but it's still a good practice to specify it as NOT NULL since not all SQL servers handle string columns in this fashion.. now back to our regularly scheduled program...)

This is where we store this Categories "Parent" category ID. In our working example, the "Computers" category has several sub-categories. (Dynamic HTML, E-Commerce, etc..) When we enter these sub-categories, the database needs to know that 'E-Commerce' is a "child" to 'Computers', so we use the 'CatParent' column to keep track of these category associations. Notice with this entry, we've omitted the 'NOT NULL'? This is because in our hierarchy, we're going to have several Categories that are at the "Top" level, and won't have a CatParent. For these categories, we'll assign a NULL value to the CatParent column to show that these are Top Level categories.

Although database indexing is beyond the scope of this tutorial, it's a good idea to always assign a PRIMARY KEY to your tables. This tells MySQL what element in the table is the item that other tables and associations will key off of. The element that you assign to be the Primary Key must always be unique, so we'll assign it to the CatID, since that's automatically incremented by MySQL and will never have more than one entry for each Category.

The last item in our table tells MySQL that we never want to duplicate the 'CatName' column. You may or may not want to do this in your own table, but having two Categories with the same name is going to lead to problems. Let's avoid that by making it UNIQUE. Now if you try to add more than one category with the same name, MySQL will refuse to enter it. This completes the structure of our 'Categories' table, let's move on to the second table in our database.

Produced by Jonathan Eisenzopf and
Created: July 20, 1999
Revised: July 20, 1999

URL: https://www.webreference.com/perl/xhoo/php1/