How To Create A Table For A MySQL Database

Keep Track Of Products, Members And So On With A MySQL Database

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 so that I can show you how to create a Table Of Data (Database Table) for it - A database that you can fill with members details.

A Database Table is made up of one or more rows (horizontal lines) of data, also known as records. For example. Row 1: Mr, John, White. Row 2: Ms, Helen, Smith. Row 3: Mrs, Lisa, Jones. Within each row, each separate entry has a column name (known as a Title or Heading) as the diagram below shows.

An example of 3 Rows Of Data (3 Records) within a MySQL Database

When filling in a row of data you think in rows and not columns. In other words, you do not think of filling in all the Last Names first. You think in rows and therefore fill in the Title of a row first, then the First Name of a row and so on until a row has been completed - This in turn means one record has been filled in (one row of data).

GETTING STARTED

Once you have created yourself an empty mysql database, called members, 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  Click on the PHPMYADMIN link to continue




Fig 1.1  Click on a Database Name to continue

When the PHP/MySQL Admin (Administration) control panel opens (above), 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 and one called members. Both with 0 Tables inside them. There may also be another database in the list called information_schema, with 17 Tables inside it, that belongs to the system and not you. If so, do not touch it. Continue regardless by clicking on the members database link (above).

And do not worry about the content on the PHP/MySQL Admin (Administration) control panel. It looks quite scarey but is in fact really scarey!!

Fig 1.2  Click on the SQL Tab (window) to continue

After clicking on the members database link (Fig 1.1 above) click on the SQL tab (window), at the top of the control panel, to continue. It allows you to enter SQL Code (commands/instructions) into its console window (edit box). SQL stands for Structured Query Language - A programming language for database purposes. In this example the sql code to create a database table.

The first thing you will see after clicking on the link of an empty database (i.e. members) 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 a database table sits.

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  The sql code to create a database table called goldmembers




Fig 1.4  The sql code pasted into the console window - Click on the GO button to continue

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 goldmembers.

The SQL Code

The first line of the create table code is the instruction that actually creates the table; a table called goldmembers in this example.

The second line sets up an INT field (a Number data entry) called ID, that is needed in all database tables. It is an indexing number that is automatically increased whenever 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.

This means if you insert three records for example they will have IDs of 1, 2 and 3 respectively 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 therefore 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 for a particular record is only formed from the current index number.

Adding TEXT Records

The next twelve 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 Town into Area or Telephone into Landline. The point is, they are text fields that can be added, deleted or modified.

Adding DATE Records

The next two lines create DATE fields (date data entries) in the format of YYYY-MM-DD (Year Month Day) - E.g: 2010-04-31. The fields are called Subscribed Date and Renewal Date, which you would use to record a member's subscription date and membership renewal date.

Adding NUMBER Records

The next line creates an INT (Integar/Number) field called MembershipNumber. The number in brackets is the length of the number (i.e. 10 numbers long) and the default value is 0, but could be 24, 6 or whatever you want.

NOT NULL

NULL in computer programming usually represents a value of 0 (zero) or "" (an empty string of text). This means when a piece of code (such as a Variable) has not be given a specific value (i.e. 24 or "John") a default value of 0 or "" will be used instead. However. In sql code NULL means void (no value. Not even 0 or "", because they are classed as values). So when you use sql code such as NOT NULL you are stating that the field should not be void, it must have a value of some sort.

When you use NOT NULL without a default value of your own (i.e. default '0') sql will give that field a default value of 0 or "".....because you have specified NOT NULL. In other words; Even though you have not specified your own default value, NOT NULL has specified it for you as either 0 or "".

The PRIMARY KEY

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, that uniquely identifies each record. Hence why the ID column was set up - It will store a unique index number, that is automatically increased when a new record has been added to the database, that the record being added to the database will inherit (as explained above).

So basically ID is a column with 1, 2, 3, etc values stored inside to represent records 1, 2, 3, etc. Whenever a Blog or whatever wants to add a new user to the database it must first look at the record numbers (IDs) to see where it can insert that new user's details (i.e. insert user "John" into record 4).

The Example Table

In the above example table I have inserted TEXT fields, DATE fields and an INT (Number) field, in that order. This does not mean you have to though. You could insert a TEXT field, called MiddleName for example, in between the FirstName field and LastName field or insert it in between the DATE fields. You could add more fields, or delete fields, rename the table's name, etc. It is up to you. That table is only an example.

Fig 1.5  The goldmembers table has been created successfully

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 phpMyAdmin control panel and you should see a link to the table. Click on it to view the structure of the table.

Fig 1.6  The structure of the goldmembers table

If you want to delete some fields, for whatever reason(s), simply put a tick next to each field you want deleting and then click on the DROP (Delete) button.

Fig 1.7  Select the fields you want to delete and then click on the DROP button

If you want to delete the whole table click on the DROP (Delete) Tab above the table structure. In both delete scenarios you will be asked to confirm the delete action.

Fig 1.8  Click on the DROP Tab to delete a whole table




Fig 1.9  Confirm your delete action for this table

Command-wise the deleting of a table could of been done via the SQL Tab, by entering DROP TABLE `goldmembers` into its console window. And to delete one or more fields could of been done with this command ALTER TABLE `goldmembers` DROP `SubscribedDate`, DROP `RenewalDate`;.

Fig 1.10  Confirm your delete action for these fields

You should begin to realize by now that each action you do has an associated sql command, which is normally shown in the sql console window and/or in a message requester. This means you can use those sql command yourself to build up a collection of sql commands, for example, inside a text editor; ready for importing. You could DROP (delete) many tables at once for example.

INSERT Data Into A Table

To insert a row of data into a table simply select the table, click on the INSERT Tab, Type in your entries and then click on the GO button. It's that easy! You do not have to fill in every entry, just those you want to fill in.

Fig 1.11  Enter your data into the necessary fields and then click on the GO button

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.

Use VTY To Manage Your Table Data

VTY is a free, one file only, PHP Script that enables you to manage your MySQL Databases online. Simply download the vty.php file, upload it to your public_html folder and then type www.???.com/vty.php into your web browser in order to execute (run) the vty.php php script. This will then allow you, and your secretary for example, worldwide access to your mysql databases.

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 1.12  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 1.14).

Fig 1.13  Select the database you want to work on

The same rules apply for the table. 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 1.14  Select the table you want to work on

After selecting your database and table you are then ready to manage that database and table. From here you can edit a record, delete a record, add a new record and so on.

Fig 1.15  Click on the ADD NEW ROW link to continue




Fig 1.16  Click on the SAVE button to save this row of table data

Remember. Record and Row Of Data is the same thing. So in some other script you may see ADD NEW RECORD for example. In the above example I am adding an empty record (row of empty data) to the goldmembers table which belongs to the members database. Again, different wording (empty). I am not just adding a new record or new row of data here, I am specifically adding an EMPTY record or EMPTY row of data. In other words. The record (row of data) is not automatically filled with personal data; I have to fill those empty records with data yourself.

Remember to ALWAYS LOGOUT when you have finished your database(s) management session, especially if you are on a public computer such as an Internet Cafe's computer.

What these mysql lessons have 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).