Creating a MySQL database and table_MySQL

How do you create a table to insert your form data into? In this tutorial I will explain how to do this with MySQL.

Creating a MySQL database and table

In the previous tutorials we have created a form to collect data and (if you did not already had this) set up a local development environment (with PHP, MySQL and Apache).

We now want to be able to insert form data into a database on the web server. That way we can retrieve those data any time we want later on.

A database is a collection of data. It’s organized in such a way that it can be quickly searched to retrieve specific data. MySQL is probably the most popular database management system. A MySQL database contains one or more tables. The data in a table are organized in records (= rows). Within each records there a several fields (= columns). We can use so-called queries to create databases, tables, retrieve data, etc. The SQL in MySQL stands for Structured Query Language. In this tutorial you will learn the basics of this language.

This tutorial supposes that you have already setup a development environment with PHP and MySQL on your local machine or know how to do so. If not I advice you to first read thetutorial how to set up a local development environment

Steps to create a database

To create a new database you need to:

1. Log into phpMyAdmin (as the “root” user)
2. Click on the tab “Databases” (at the top)
3. Insert the name of the database into the field below “Create Database” (in the previous and next tutorials I’ll use the name ‘all_sports’ for the database).
4. From the dropdown menu “Collation” choose the option “utf8_general_ci”.
5. Then click on the button “Create”.

You should now see the name of the new database appear both in the table below Create Database and in the panel to the left.

Steps to create the table

To create a table inside a database you:

1. First need to select that database by clicking on its name in the panel on the left
2. Then click on the “SQL” tab (at the top)
3. Insert the code shown below into the query box (and then click on Go)

SQL - query to create table

If everything went as it should you should see the name of the table (“members” in my code above) listed below the name of the database in the panel on the left.

You can see/check the structure of the table you just created by clicking on the tab “Structure”; then click on the name of the table (e.g. “members”); then click again on the tab “Structure” (which is now displayed to the right of the tab “Browse”).

Primary key column

The first column/field has nothing to do with the form we created earlier but contains the variable “id”. With this field we can make sure that every record in the table is unique.

The column named “id” has the following characteristics:

INT UNSIGNEDThe INT data type makes the column take an integer. Unsigned means that no negative values are allowed.

NOT NULLWith NOT NULL we ensure that every column has a value.

AUTO_INCREMENTCauses MySQL to set a unique value for this column in every row. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. This way we are guaranteed that each record has a unique value.

KEYAn auto-increment column is useful as a key, because you will tend to search for rows based on this column. When you look at the structure information you will notice that the key symbol for this field has a different color.

Each entry in the column “id” will now have a unique number, with the first starting at 1 and the others counting upward from there. And whenever a new row is inserted, its “id” column will automatically be given the next number in sequence.

Data types in SQL

We have defined the fields in the table with the use of various SQL data types:

VARCHAR (n)The VARCHAR data type accepts text strings (the term VARCHAR stands for VARiable length CHARacter string). With VARCHAR data type there is a limit on the size of the field in the table. This limit is set with (n). As you can see I have sometimes set this limit to 255 or 128.

For this table I did not use the data type CHAR. But it’s good to be aware of this data type and how it differs from the VARCHAR type. Both CHAR and VARCHAR accept text strings and impose a limit on the size of the field. The difference between these 2 data types is that with CHAR every string has the specified size. If I had used CHAR(6) for Gender than the string “male” would be padded with (two) spaces. A VARCHAR field does not pad the text; it lets the size of the field vary to fit the text that is inserted. But VARCHAR requires a small amount of overhead to keep track of the size of each value. So CHAR is slightly more efficient if the sizes are similar in all records, whereas VARCHAR is more efficient if sizes can vary a lot and get large. In addition, the overhead causes access to VARCHAR data to be slightly slower than to CHAR data.

Besides VARCHAR and CHAR we could also use the TEXT data type when creating a table. With First Name etc. you might be tempted to use the data type TEXT. However with the TEXT data type you will never search more than a certain number of leading characters in a field. Because a user will also want to be able to search for part of a name I used the VARCHAR type.

DATEWith the data type DATE the input will be formatted like this “yyyy-mm-dd”. You’ll notice that this is slightly different from the format in the form (mm/dd/yyyy). When you insert data with the form into the database and check the result you will see that this doesn’t pose a problem and the form date will be translated correctly into the SQL format.

INTINT stands for Integer. When your data consist of numbers (e.g. phone number) you might be tempted to use the INT data type. My advice is to only use the INT data type for the primary key and for data that you want to do mathematics on (e.g. add, multiply). A phone number for instance is not something that you’re going to multiply with another number. The same goes for a ZIP code. That’s why we defined them as VARCHAR data types.

How to add a field to the table?

You might have noticed that we “forgot” one field (“region”) when we created the table. This allows me to explain how we can add a field/column to an existing table.

Go to the tab that shows the structure of the table. Below the structure you will see the option to add one (or more) column(s) to the table.

1. Choose the option “After”
2. From the dropdown menu choose “zipcode”
3. Click on Go

You then get a window asking you to define the field/column:

1. For Name insert “region” (without the quotation marks)
2. For Type choose VARCHAR
3. For Length/values insert 128
4. For Collation choose “utf8_general_ci” (without the quotation marks)
5. Then click on Save

Now that our database/table is ready we can start writing the PHP script that will send the data from the form to the MySQL database.