PHP Introduction | Database Access Using PDO | Creating a Table in MySQL

Tags:

Now let us prepare a sample database in MySQL. In MySQL, a database has the following structure.

Database
The first thing to prepare is a database. When using MySQL, first prepare a database that organizes and stores the data you will use.

Table
A table organizes the actual data created in a database. A table defines the data structure, such as which values are stored in which items, and data is stored according to that structure.

Field, Column
These are called by several names, but they are the individual items included in a table. A table cannot store each field separately. It stores the values of all items defined in the table together in one place.

Record
This is the data actually stored in a table. One record stores one value for each field.

Originally, database creation is performed by running MySQL commands, but XAMPP includes a dedicated MySQL administration tool called phpMyAdmin, so it is convenient to use it.

http://localhost/phpmyadmin/

Access this address in a browser. The administration tool screen appears. From here, create the database and table.

1. Create a Database

First, create the database. Click the “Databases” tab among the tabs lined up at the top of the page. Under the displayed “Create database” area, set the database name and the text encoding to use. Here, enter the following and press the “Create” button to create the database.

  • Database name: mysampledata
  • Encoding: utf8_unicode_ci

2. Create a Table

After creating the database, the database name mysampledata is added to the list on the left. Click this item to select it, and the screen changes. A display for creating a new table in the mysampledata database appears. Set the name and number of columns as follows.

  • Name: sampletable
  • Number of columns: 5

Set the Table Contents

After entering the table name and number of columns and pressing the “Run” button, a screen appears for setting the table contents. Here, you set the details for each column, meaning each item that stores values in the table. Create it as follows. Settings not specifically mentioned can be left at their defaults.

Column Type Index Length Auto incremental NULL
id INT PRIMARY ON
name VARCHAR 20
mail VARCHAR 50
tel VARCHAR 20
memo VARCHAR 255 ON

With this, a table named sampletable is added to the database. This sampletable is now available for use.

The corresponding CREATE statement is as follows.

CREATE TABLE sampletable
(
    id int NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    mail varchar(50) NOT NULL,
    tel varchar(20) NOT NULL,
    memo varchar(255),
    PRIMARY KEY (id)
);

3. Add Sample Records

Add several sample data items, or records. Select “Insert” from the tabs shown above. A form for entering values for each column appears below, so write suitable values there and execute with the “Run” button. The entered content is registered as a new record. Create a few more records in the same way.

When adding records here, you do not need to write a value for the first id. It is set to automatically add a value through the auto-increment feature. Also, no error occurs even if memo is left empty.

The corresponding INSERT statements are as follows.

INSERT INTO sampletable(name, mail, tel) VALUES ("Sungjin", "sung@abc.com", "02-123-1234");
INSERT INTO sampletable(name, mail, tel, memo) VALUES ("Wonseok", "won@foo.info", "02-111-1111", "Memo");