How To Create A MySQL Database Table

In this previous section I showed you how to create an empty MySQL Database, called Blog, for the purpose of a Wordpress Blog. Since then I have created another empty mysql database called members that was created in exactly the same way as the Blog database. The reason for creating the members database is because in this section I will teach you how to create a Database Table for that members database.

Once you have created yourself an empty mysql database, called members for this example, continue by clicking on the PHPMYADMIN link to take you to the PHP/MySQL Admin (Administration) control panel (Fig 1.1). You will need to be logged into your cpanel control panel of course before clicking on the PHPMYADMIN link.

Fig 1.0 Login to your cpanel control panel, if need be, and then click on the PHPMYADMIN link to continue.

Fig 1.1 Click on the MEMBERS database link to view its content

When the PHP/MySQL Admin (Administration) control panel window opens look to the left-side of it and you should see your empty, members, mysql database there. In this example I have two databases. One called blog with 10 Tables inside it and one called members with 0 Tables inside it. There may also be another database in the list called information_schema with 17 Tables inside it but if so this belongs to the system and not you. So do not touch it. Instead. Click on the members database link (above) to view its content (below). Do not worry about the content on the PHP/MySQL Admin (Administration) control panel window. It looks quite scarey but is in fact really scarey!!

Fig 1.2 Click on the SQL tab (window), at the top of the control panel, to continue.

The first thing you will see after clicking on the link of an empty database is the request to create a Table for that empty database (above). This is because the database itself is just the empty structure or foundation upon which the tables sit. The PHP/MySQL Admin (Administration) control panel allows you to communicate with the database - Create/delete tables, Add/edit/delete table data, Import/export table data, Etc. So if you only have one mysql database with your web hosting package you could still install a blog, forum and so on using the same database but with different tables. A lot of PHP Scripts that require a MySQL Database use their own prefix (i.e. wp_, phpbb_, etc).

Getting back to table creation. There are two main methods for creating a table. With method one you click on the SQL tab (Fig 1.2 above) and then paste or type your sql code (commands/instructions) into the console window (edit box) that appears (Fig 1.4 below). And with method two you click on the IMPORT tab and then browse for a .sql file to import. That .sql file should contain exactly the same sql code as the sql code you paste or type into the sql console window. I find it easier to open a .sql file, using Notepad for example, with all my sql codes inside it; Ready to be copied and pasted into the console window. So in this example click on the SQL tab (above) and then copy and paste (or type) the following code into the console window.

Fig 1.3 EDIT > SELECT ALL and then EDIT > COPY the sql code from the create_table.sql file and then.....

Fig 1.4 .....Right Click > PASTE that sql code into the console window before clicking on the GO button.

When you have pasted the sql code from the create_table.sql file into the console window click on the GO button to run (execute) that code and therefore create a table called clients. The first line of the create table code is the instruction that actually creates the table; a table called clients in this example.

The next line sets up an INT field (number data entry) called ID, that is needed in all database tables. It is an indexing number that is automatically increased when you add another record (row of data) to the table. The record (i.e. Mr, John, White, etc) will inherit that index number as its ID. So if you insert three records they will have IDs of 1, 2 and 3 respectively for example and the index number will automatically be increased to 4. If you then delete all three records, or even keep them, the next new record will have an id of 4 regardless. And if you were to delete record 2, and so keep records 1 and 3, the next new record would still have an id of 4. So the index number is totally separate from the id - The id is only formed from the current index number.

The third line onwards, except the last two lines, create TEXT fields (text data entries) called title, firstname, lastname and so on. These lines you can delete or add as you wish. In other words. You could type `middlename` text NOT NULL, in between the firstname and lastname lines to add a Middle Name field (text data entry). You can even change the titles of those fields if you wish. You could change area into town or landline into telephone. The point is, they are text fields that can be added, deleted or modified. If you want to do the same for number fields you can do. You could add `membership_number` int(8) NOT NULL default'0', in between firstname and lastname if you wish. The number in brackets in the length of the number (i.e. 8 numbers long) and the default (starting id/number) is 0 but could be 24 if you wish.

The last line but one (Primary Key) tells the database that you want the ID field to be the primary (master) column or primary (master) identifier. For example. If you made firstname the primary key it would useless because there would probably be more than one person called John in your table data (records). The primary key is set up to select the table column whose data uniquely identifies each record. Hence why you set up an automatically increasing index number for each record, so that each record can be uniquely identified by its unique ID number. Primary Key is also used in MySQL Programming but do not concern yourself with that! The last line just completes the sql instruction.

Fig 1.5 Click on the CLIENTS table link to view the structure of the table

After clicking on the GO button (Fig 1.4 above) to run (execute) the create table code you should receive a success message (Fig 1.5 above). If so, look to the left-side of the control panel and you should see a link to a table called CLIENTS. Click on it to view the structure of the table.

Fig 1.6 The structure of the CLIENTS table - Click on the DROP tab to delete the table and its records

Fig 1.7 Click on the YES button to confirm deletion of the table

Fig 1.6 above shows the table structure for the CLIENTS table. As you can see, the structure has the fields inside it that came from the create table code. From here you can carry out certain operations. For example. If you want to insert some data into the table (create a record) click on the INSERT tab. And if you want to delete a table, including its records, click on the DROP tab. Doing so will bring up a YES / NO message requester asking you to confirm deletion of the table (and its records), as shown in Figures 1.6 and 1.7 above.

Fig 1.8 The CLIENTS table has been dropped (deleted) successfully

Regardless of what operation you carry out the information message will always keep you informed about the last operation's failure and/or success. In this example (below) the information message states that the table has been successfully dropped (deleted). Underneath that information message is the sql code (command/instruction) used to carry out the operation. In this case DROP TABLE `clients`. What this means is, now you know the code for dropping a table, you could of clicked on the SQL tab and entered DROP TABLE `clients` into the console window before clicking on the GO button (just as you did with the create table code). So instead of using the above method to drop the table you could of used the DROP code. And this is the beauty of SQL commands and SQL in general. The code can be run (executed) from a PHP Script or from this PHPMYADMIN console window. With a PHP Script though you need to use the CONNECT commands to connect to the database whereas this PHPMYADMIN control panel does all the connection stuff for you.

Going back to data insertion (record creation). Clicking on the INSERT tab will allow you to insert data into the fields set up by the create table code. Simply fill in the edit boxes with the appropriate information before clicking on the GO button. In this example I filled in the title field, the firstname field and last three fields at the bottom of the record before clicking on the GO button. Although I could of filled in the ID field with an index number of my own (i.e. 24) I did not, to avoid the Duplicate Entry error. Remember. The ID has to be unique and once you forget which ID number you gave to which record it can become confusing. So leave IDs to the automatic increment system.

Fig 1.9 Fill in the record's text boxes before clicking on the GO button to continue

Above I mentioned the two main methods for creating tables. Method one (Fig 1.2 onwards) involves copying and pasting sql code from a .sql file into the sql console window and method two, which I will show you now, uses the IMPORT tab. The import example below demonstrates how it is possible to create two tables at the same time using just the one create_tables.sql file. It is possible because you just duplicate your CREATE TABLE commands with different table names, either with the same fields in them or with different fields in them. The same can be achieved using method one of course because you would just be copying and pasting the same code.

Begin by clicking on the IMPORT tab and then click on the BROWSE button (not exampled here). It will bring up the Choose File To Upload file requester that requires a text file to upload (import). In this example I navigated that file requester to the create_tables.sql file, which is a text file with the file extension .sql. After that I clicked on the GO button to continue.

Fig 1.10 BROWSE for a .sql file to import and then click on the GO button to continue

Fig 1.11 The FAMILY and FRIENDS tables have been created, together.

Although the two tables, FAMILY and FRIENDS, have been created from the same .sql file they are independent of each. You can DROP (delete) one for example. And that is the beauty of both methods. They allow you to use only the code you want. For example. If I drop the FRIENDS table I could replace it again by using only its portion of code from the .sql file.

Fig 1.12 Copy only the portion of table code you need when replacing a table

So far this section has concentrated on the table creation side of things as opposed to filling a table with data, even though I touched on this with the INSERT tab above. And this was intentional because, in my opinion, the PHPMYADMIN control panel is only really of use to an absolute beginner in terms of creating tables. For filling a table with data you are better off with a Database Viewer/Editor (Manager) such as VTY. VTY is a free, one file only, PHP Script that enables you to manage your MySQL Databases online. There is nothing to install. Simply Download VTY, upload it to your public_html folder and then type www.domain.com/vty.php into your web browser. Therefore giving you, and your secretary for example, worldwide access to your mysql databases.

Once you have unzipped the downloaded vty.zip file, copy and paste the vty.php file from the vty-1-6 folder into your public_html (website) folder. After that type www.domain.com/vty.php into your web browser to execute (run) the vty.php php script. The first page to appear asks you for your database User Name & Password. It does not need the name of the database at this time because it finds the databases associated with the logged in user, as opposed to giving a database name and then looking for users. The LANGAUGE, SERVER TYPE and HOST NAME details are chosen by default and should not need changing. When you have supplied the needed details click on the SEND button to continue.

Fig 2.0 Enter a database User Name & Password and then click on the SEND button to continue

The next page gives you a DATABASE drop-down menu where you can select the database you wish to work on, if you have more than one database of course. If not, obviously there will only be one database name available. Once you have selected your database from the drop-down menu the page will change again, this time asking you to select a table to work on (Fig 2.2). The same rules apply. If you have more than one table you can choose which one to work on, otherwise you will only have one to work on of course. I say of course but in reality you might not have a database or table set up. In which case VTY offers the appropriate link in order for you to set one up (database or table).

Fig 2.1 Select the database you want to work on

Fig 2.2 Select the table you want to work on

After selecting your database and table the next thing to do is fill in its fields (records) by clicking on the ADD NEW ROW link. Remember. Record and Row Of Data is the same thing. So in some other script you may see ADD NEW RECORD for example. In this example I am adding an empty record (row of empty data) to the family table which belongs to the members database. Again, different wording. You are not just adding a new record or new row of data here, you are specifically adding an EMPTY record or EMPTY row of data. In other words. The record (row of data) is not automatically filled with default data; you have to fill those empty records with data yourself.

Fig 2.3 Click on the ADD NEW ROW link to continue

The ADD NEW ROW page allows you to fill in the empty record (rows of empty data/edit boxes) with your own data. In this example I have only filled in certain parts of the first record (first row of data), belonging to the family table, before clicking on the SAVE button. Clicking on the RESET button will clear the record (edit boxes), which is good in those occasions where you have made a mistake with the data (i.e. you have filled in the wrong person's details).

Fig 2.4 Scroll through the edit boxes (fields) and fill them with data, if need be, before clicking on the SAVE button

With the record filled with data, that has now been saved, you can then create another record by clicking on the ADD NEW ROW button again. Therefore, building up records for the table. Looking at the VTY options available (below) you can EMPTY THIS TABLE of records, DROP THIS TABLE (delete the table), EDIT or DELETE one or more records (by first putting a tick next to each record) and so on. SQL QUERY is also an option - Its is the VTY console window that allows you to paste sql commands into it and execute those sql commands. VTY is not the best database manager around but it is free and it does allow you to manage your databases.

Fig 2.5 Remember to ALWAYS LOGOUT when you have finished your database(s) management session

What the above has tried to show you is that although you might need to use your cpanel control panel to create an empty mysql database with user(s), and the phpmyadmin control panel to create your table(s) and then VTY to fill in the record data it is possible for the absolute beginner to set up a database, even if it takes three main steps to do so. In fact, with VTY it would only take two main steps. After creating a mysql user name and password, without an associated mysql database, with your cpanel control panel you could then use VTY for all other tasks (including the creation of an empty database for that user name and password).

If you need to know more about SQL and its commands/instructions the official MySQL website (http://www.mysql.com) has a Developer Zone and more precisely a Documentation / Reference web page that explains the syntax, data types, commands/instructions/statements, etc of mysql. Look on the right side of that web page for the chapters section. There are plenty of mysql websites on the internet as well; some of which make mysql easier to learn, through better interpretation, than the chapters on the official mysql website. Even though mysql can be complex, do not let that put you off learning it because in truth (like many programming languages) only a handful of commands are ever used - No one uses all of them.