Thursday, October 14, 2010

Creating MySQL Tables with phpMyAdmin

After missing five days of posts, I felt bad and got up early to get today's post done. Part of the reason I got delayed posting is because I lost all the work I did when I could no longer locate one of the key files which included all my Godaddy screenshots and written notes for this post. I had to do everything over again.

The blue lights that are shining behind the oatmeal bowl is my external keyboard. It's a very well made, ultra-thin illuminated Logitech with keys that are a dream to type with. If you are a developer, you'll love it.

Ok. So where were we? We were working with Godaddy shared hosting which was taking too long to setup a database request so we had to run over to my server at another company to show you how to create a database. I got a chance to try out a few more things on their site and discovered that Bob Parsons has this habit of wanting to log you off in the middle of things if he detects inactivity. If you sip on the coffee too long, you are out. A screenshot of the many attempts at trying to kick me out, notice the inconsistency in the styling of their dialog boxes for the same message:

I understand the security worries but he's got it programmed it to an extent it's become annoying and very inconvenient. You get bumped off all the way to the top.

Let's keep plugging along to evaluate the rest of their service. We are going to create tables for our database but first I'll tell you what I know about databases.

DATABASES AND SQL

SQL is the language for dealing with relational databases. With SQL you can create tables that will contain many records of data, then insert/update/read/delete those records. You typically will have many related tables of data. There are different database systems out there, such as Oracle, IBM DB2, Microsoft SQL Server, as well open source products such as MySQL and PostgreSQL. I use MySQL, so that's what I'll be talking about in this post.

Basic SQL commands are fairly standard across various database products, but there are differences. Things like time, date, and boolean values might have a different syntax, so you have to watch for those if moving code from one database system to another. Still the basic ideas are the same, and have very similar syntax across different products.

Before I get into coding, just a little background on MySQL: I noted that MySQL is open source, however it is also sold under a commercial license that covers certain situations. There are some specific cases you need the commercial license. One of those is where you've made some changes to the MySQL source code and you do not want to release those changes as open source. Otherwise if you are just building a web site that uses standard MySQL - even if that web site is for profit - then you can use the open source license (for free).

Surprisingly MySQL is currently owned by Oracle. That is, Oracle owns the MySQL trademark and copyright. Oracle acquired MySQL when they bought Sun Microsystems in 2009 for $7.4 Billion. Sun had previously acquired MySQL for $1 Billion. So how is an open source project worth $1Billion? Well, one way MySQL makes money is through commercial licensing mentioned above, but also by selling support contracts and specialized services.

Now that Oracle owns MySQL, they can't suddenly make it proprietary software, but they could do away with the commercial license or stop making enhancements and bug fixes to MySQL. So far none of that has happened. Still, having Oracle own MySQL gives me an uneasy feeling. Do you recall when dual core processors were just coming out? Single processor with two CPU's to speed things up. Oracle charged for two licenses for a server box with a dual core processor. Same machine! It's hard to understand the rationale behind multi-core licensing.

So, what I want to do now is show how to create tables and insert some data using a tool caled phpMyAdmin on the GoDaddy shared hosting account. After the data is inserted I'll update my HTML form, turning it into a PHP program that talks to MySQL, reads data from the database, then displays that data as part of the form.

Step 1: Create the Tables
Step 2: Insert Some Data
Step 3: Connect to the Database from PHP
Step 4: Read Data from the Database using PHP
Step 5: Display the Data in the Form using PHP

As I said in the intro, I got delayed because of a missing file. In case the same thing happens to you, I will teach you a way that will ease your pain greatly in the event you need your queries as I did. I had lost my file with my queries but the tables and the records that I had inserted into them were still on Godaddy so I exported my database, which produces a database listing all of my SQL queries. Here's how you do that:

From phpMyAdmin, choose "export", select your database name, and hit "Go". That gives you all the SQL commands needed to reconstruct your database. From there, it's a straightforward cut and paste. The only thing of significance that got lost was setting up the primary and secondary keys which I will show you how to handle later. First up is the creating of the two tables.

CREATE TABLE `shoe_primary` (
`itemnum` int(11) default NULL,
`description` text,
`price` int(11) default NULL
) ;

This is a SQL query that creates a table called "shoe_primary" with 3 fields. There will be one entry in this table per type of shoe. It will hold the description and any other information that applies to the shoe regardless of size, width or color. Two of the fields (itemnum and price) are defined as int(11). That means integer values with 11 digits of precision. The description field on the other hand can hold one or more paragraphs of text, so we make that a "text" field. Text fields can hold an arbitrarily long string of character data, and are a good choice when you don't know how long the string could potentially be.

INSERT INTO `shoe_primary` VALUES(10100, 'Elegance and pain defined. Classic special-occasion heel crafted in metallic or suede leather. Cross-over straps at toe and double ankle strap with gold metal self-fastening oval buckle. Suede lining and leather sole. 3 1/2 inch self-covered high heels that are sure hell to walk in. Made in the USA. Catalog/cssbakery.com only', 275);

This is the SQL query to insert a record into the "shoe_primary" table we just created using insert into. We list the values to be stored into the fields in the same order they are defined in the table. All strings must be in quotes. Integers? No worries.

CREATE TABLE `shoe_secondary` (
`id` int(11) NOT NULL auto_increment,
`itemnum` int(11) default NULL,
`size` varchar(80) default NULL,
`width` varchar(10) default NULL,
`color` varchar(60) default NULL,
`inventory` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `itemnum` (`itemnum`)
) ;

This SQL query creates the "shoe_secondary" table. This table will hold one record per combination of shoe size, width, and color, for a particular type of shoe. Along with that data we also store the number we have in inventory, so we can tell you if they are available in your size/color choice or not. In this table we have some more fields that are integers (int(11)), and we also have some fields defined as varchar. Varchar means variable length character data, and we specify the maximum length of the string within the parens. So varchar(60) means we can store a string in that field, but with maximum size of 60 characters.

Notice the "itemnum" field repeats in both tables. In shoe_primary it's the key field that identifies the product. In shoe_secondary it's used to associate multiple secondary records to a single shoe_primary record. This is called a 1 to Many relationship between tables, where a single record in one table is related to N records in the other table. This type of relationship happens a lot in databases (One to One, and Many to Many relationships are also possible.) (The itemnum field in shoe_secondary is a "foreign key". I'll describe that concept below.)

One other thing to note in this table is that we have an 'id' field and it says auto_increment. This field will be our primary key for the shoe_secondary table. We don't really have another field that is a natural key for this table, so we just made up the 'id' field and set it to auto_increment. Auto_increment tells the database to automatically assign values to this field and to increment the assigned value as more records are added. So it's a way to have the database make up a unique key for you. Once it's assigned you can use the number to uniquely identify a record in the shoe_secondary table.

INSERT INTO `shoe_secondary` VALUES(1, 10100, '7', 'M', 'platinum', 7);
INSERT INTO `shoe_secondary` VALUES(2, 10100, '6 1/2', 'M', 'black suede', 5);
INSERT INTO `shoe_secondary` VALUES(3, 10100, '7', 'W', 'platinum', 4);
INSERT INTO `shoe_secondary` VALUES(4, 10100, '8', 'M', 'navy suede', 2);

You can group multiple SQL queries together to execute them all at once. In this case we insert four records into shoe_secondary, with a single click of the "Go" button.

PRIMARY KEYS and FOREIGN KEYS

Every table in your database should have a primary key. It's not strictly a requirement, but it's generally a good idea. The primary key might naturally be one of the fields you are already adding, such as an "item number" that uniquely identifes a single row in a table of products. One good reason for having a primary key is to use it in relationships between tables. For example in an online shopping cart application you will have multiple orders that refer to the same item number. In that case the order records would store the "item number", as a "foreign key". A "foreign key" is simply a field in one table that holds the primary key of another table. (This is also an example of a 1 to Many relationship between tables).

INDICIES

There are some fields in your tables that you know you'll be running queries against even though they are not the primary key. In that case you want to tell your database to build an index for that field to make the queries much faster. Otherwise, if you don't build an index, the database will have to examine every record in the table. Returning to the online shopping cart example, suppose you want to find all orders that include a Canon 7D camera. You'd run a query against the orders table, searching against the item_number field. That field isn't the primary key of the orders table, so you'd want to have your database build an index.