Creating a database

Starting the jokes project

Where are we?

You know how to create a database. Now learn how to add a table to a database.

This lesson’s goals

Learn:

• All data is in tables. Each row is about a single thing, like a dog. Each column is an attribute, like a name or a weight. Each row has the same columns.

• Each field has a data type. MySQL has lots of data types. We talked about INT, DECIMAL, CHAR, TEXT, and DATE.

• Tables have primary keys. A primary key is a field whose values are different for every row.

• Usually you create an INT field to be a primary key. Make it unsigned and auto_increment. MySQL will fill in a unique value.

• Learn how to create a table with phpMyAdmin, and add data to it.

• Learn how to export a table from one database (e.g., one on your local machine), and import it into another (e.g., one on your hosting account).

All data is in tables

All MySQL data is in tables. Here’s an example:

Figure 1. dogs table

The data is in rows and columns.

Each row is data about a single dog. So all the data on the first row is about Francis. A row is also called a record.

Each column is a characteristic of a dog, like the dog’s name, breed, or weight. Columns are also called fields. Every row has the same fields.

Here’s the articles table from the DogRock content management system (CMS):

Figure 2. articles table

Each row is data about one article. Each column in an attribute of articles, like the title.

Here’s the products table from the DogToys product catalog:

Figure 3. products table

Each row is data about one product. Each column in an attribute of products, like the price.

Data types

Each column contains a specific type of data. Here are the data types for the dogs fields.

Figure 4. dogs data types

When you create a table, you tell MySQL the data type of each column. Don’t worry about how you do that just yet; we’ll talk about it soon. For now, let’s focus on the data types.

There are many different data types. The core types are in three categories:

• Characters.

• Numbers.

• Date/time.

Character types

Characters are text, as in “I like the number 96.” Character fields are also called string fields.

You can tell MySQL how long character fields should be. For example…

CHAR(10)

... tells MySQL that a field can have up to 10 characters. Try to put more in the field, and the extra characters will be cut off.

Another character data type is:

TEXT

A TEXT field can have up to about 65,000 characters.

Number types

We’ll only talk about two number types:

• Integers (whole numbers)

• Decimals (numbers with a fixed number of decimal places)

Integer fields can contain whole numbers only, like 7. Try to put 7.32 into an integer, and the .32 will be cut off.

Make integer fields with the INT data type.

Decimals are declared like this:

DECIMAL(10,2)

This is a number that is 10 digits long, with 2 decimal places.

Date/time

MySQL has several date/time types. You can store times down to a fraction of a second, if you want.

We’ll just use the simplest date/time type: DATE. It stores just a date, and not a time.

But how to format a date? In the US, it’s month/day/year, so June 3, 2013 would be 6/3/13. In Australia, you would use day/month/year, as in 3/6/13.

Just as there’s a standard for HTTP, and another one for HTML, and another one for CSS, there’s an international standard for dates. It’s YYYY-MM-DD. So June 3, 2013 would be 2013-06-03.

Most Webers use the standard format when storing dates in a database. That’s what we’ll do. But when we show the dates to people, we’ll use the US format. You can adjust the format, if you like.

Other types

There are many other data types. Other types of strings, other types of numbers, and things that aren’t either. For example, coordinates of a place on the earth, used in mapping programs.

We’ll stick with CHAR, TEXT, INT, DECIMAL, and DATE for now. I’ll explain others if we need them.

Primary key

Here’s the products table again:

Figure 3 (again). products table

Most tables have a column that is the table’s primary key. This uniquely identifies each row in the table.

For products, only one product will have a product_id of 1. Only one product will have a product_id of 2. There will never be any duplicates.

Could the name field be a primary key? No. There might be two products from different manufacturers that have the same name. Maybe two manufacturers make a product they call “Giant chew rope.” They will have different product_ids, so the database can tell them apart.

Here’s the articles table again:

Figure 2 (again). articles table

Two articles could have the same name. For example, Wendy could write an article called “Old Time Howl.” In three years, Bounder could write an article called “Old Time Howl.”

The field article_id takes care of it. Each row has a different value for article_id. It’s the table’s primary key.

Here’s the dogs:

Figure 1 (again). dogs table

What is the primary key? Is it name? That’s the best field we have, but it isn’t very good. Two dogs could have the same name. There might be two Ralphs, for example.

This table isn’t designed well. It would be better if it had a primary key:

Figure 5. dogs table with primary key

Integer primary key

It’s common practice to add an integer field (whole number, remember) to a table, and make it the primary key. That’s what I did with the dogs table.

The first row has a value of 1 for this field. The next row has 2, and so on. Actually, it doesn’t matter what the values are, as long as each one is different. I could make the dog_id of the first row 432, the second row 89, the third row 299, etc. But making them 1, 2, 3, etc., is usual.

When we add a new record to the table, we want dog_id to have a new unique value. We can tell MySQL to do this for us. When we create the table, we can mark an integer primary key field as auto_increment.

When our PHP program adds a new record to dogs, we send the values for each field to MySQL. Like this:

insert into dogs (name, breed, weight) values ('Jamie', 'Scottie', 9);

Notice that we didn’t give a value for dog_id. Since dog_id is an auto_increment field, MySQL will set the value for us. It will take the next value in sequence. So if the last record we added have a value of 3,409, then MySQL will fill in the value 3,410.

Creating a table with phpMyAdmin

Let’s see how you actually add a table to the a database.

I added a humans table to the dog database. It has fields for:

• Primary key.

• Name, e.g., Sarah.

• Weight in pounds.

First, I opened the database in phpMyAdmin. Do this by selecting the database in the drop-down:

Figure 5. Open a database

Then I typed the name of the new table and the number of fields into the Create table form:

Figure 6. Create a new table

Then I clicked the Go button.

Here are the settings for the primary key, human_id:

Figure 7. The human_id field

The data type is INT. I didn’t need to give a length; INT has a standard length, and MySQL knows what it is.

Primary keys are not negative; they could be, but it would be strange. So I made the field UNSIGNED. It’s also set to AUTO_INCREMENT. You should make your primary keys INT, UNSIGNED, and AUTO_INCREMENT.

I marked as human_id the table’s primary key, using the radio button next to the picture of a table with a key on it. Don’t forget to mark your primary keys!

Here are the settings for name:

Figure 8. The name field

It’s set to CHAR and given a length of 20. You always need to give a length to CHAR fields.

Here are the settings for weight:

Figure 9. The weight field

It’s set to INT.

I clicked the Save button to make the table. phpMyAdmin created an SQL statement, and told MySQL to run it. Here’s the feedback I got.

Figure 10. Create table feedback

Adding data with phpMyAdmin

Now I have some tables. How about adding data?

Let’s say I want to add data to the dogs table. First, I open the table.

Figure 11. Opening the dogs table

The I click the Insert tab.

Figure 12. Insert tab

I see a form that lets me add data.

Figure 13. Add record form

I fill in the form, but not the auto_increment field!

Figure 14. Adding data

dog_id, the primary key, is an auto_increment field. MySQL will fill in that value for me.

I click the Save button, and get this feedback:

Figure 15. Feedback on adding data

You can see the SQL INSERT statement that phpMyAdmin created. You can also see the message “Data truncated for column ‘breed’ at…” Why? Because I declared breed as CHAR(10). The value I typed in – Whale hound – is 11 characters long. So the last character was lost.

Here’s what the table looks like with the new record:

Figure 16. New data

You can see the truncated breed.

You can also see that the dog_id is 6. Remember that I didn’t type in that value. Because it’s an auto_increment field, MySQL filled in the value itself.

Exporting to remote

I created the dogs table on my local PC, and added some data. How do I get it on my Hostgator server? I could retype everything, but there’s an easier way:

• On localhost: Export the table to a file.

• On hosting server: Import the file to make a table.

When you export a table in phpMyAdmin (on your local machine), phpMyAdmin recreates the SQL statements that made the table and added the data. Then you can tell phpMyAdmin (the one on your hosting account) to execute those statements.

Exporting

Open the table you want to export:

Figure 17. Open table

Click the Export tab:

Figure 18. Export tab

Make sure SQL is the export format:

Figure 19. Export format

Check “Save as file”:

Figure 20. Save as file

Click the Go button. Your browser will save the file.

Open the file in Notepad++, Netbeans, or some other editor. You’ll see the SQL that phpMyAdmin exported.

Importing

Start phpMyAdmin on your hosting account. If you forget how, here’s a reminder.

Open the database you want to put the table into.

Click the import tab:

Figure 21. Import tab

Click the Browse button:

Figure 22. Browse button

Select the file to import. Click the Go button. phpMyAdmin will grab the file and run the SQL statements inside it.

<?php
//DB connection data.
$host = 'localhost';
$db = 'dogtoys';
$user_name = 'dogtoys';
$password = 'dogtoys';
?>

Summary

• All data is in tables. Each row is about a single thing, like a dog. Each column is an attribute, like a name or a weight. Each row has the same columns.

• Each field has a data type. MySQL has lots of data types. We talked about INT, DECIMAL, CHAR, TEXT, and DATE.

• Tables have primary keys. A primary key is a field whose values are different for every row.

• Usually you create an INT field to be a primary key. Make it unsigned and auto_increment. MySQL will fill in a unique value.

• You learned how to create a table with phpMyAdmin, and add data to it.

• You learned how to export a table from one database (e.g., one on your local machine), and import it into another (e.g., one on your hosting account).

What now?

You’ll need a way to apply your database skills. In the next lesson, you’ll set up the Jokes application. You’ll add to it as we work through the rest of the chapter.

Creating a database

Starting the jokes project