MySQL has a number of different "engines" which can handle the processing of data. Currently only (InnoDB) one can handle relationships so if you will have more than one table use InnoDB for all of them.
The first table
To create a table log in as a user. Select which database you want on the left. If you only have one database that will be the only one showing. Click on the Structure tab at the top of the page. You should see a field to "Create new table on database ..." and the name of your database. The number of fields you pick is not crucial as if you choose too few you can easily add them and any extra blank ones will be ignored. For now follow this process as practice (note that details of the interface vary depending on the version):
- set the name to firsttable and the number of fields to 5, press Go
- towards the bottom of the slightly awkward page change the Storage Engine to InnoDB
- change Collation to match your Web sites collation (these tutorial pages have taught you to use utf8 so utf8_general_ci is a good choice)
- type the field names, types and lengths into the appropriate boxes:
- cdReference, INT
- cdTitle, VARCHAR, 50
- cdArtist, VARCHAR, 50
- cdPrice, DECIMAL, 10,2
- cdLabel, VARCHAR, 25
- set the attributes of the first field to UNSIGNED (this primary key will not use negative numbers)
- set the Index of the first field to PRIMARY to make it the primary key
- tick auto-increment (A_I) on the first field so that the primary key gets a unique value for each new record
- make the cdPrice UNSIGNED as the price cannot be negative
- Click Save
You should now see something like this (you may need to click on Details to see the indexes):
The first thing to note is the weird code towards the top. This is SQL. It is the SQL which has been sent to the server to create your new table (yours will be different). You could also have typed this in yourself or written a PHP page to do it but PHPMyAdmin is more cuddly.
Underneath are the fields in the table. Check that the fields match what is shown here.
The indexes are shown (just the one).
You are now able to add fields or indexes but don't.
The second table
Instead click on the database on the left again so that you can create this new table:
- set the name to secondtable and the number of fields to 4, press Go
- change the Storage Engine to InnoDB
- change Collation to utf8_general_ci
- create the fields:
- trackID, INT, UNSIGNED, PRIMARY, Auto-increment
- cdReference, INT, UNSIGNED
- trackOrder, TINYINT, UNSIGNED
- trackTitle, INT
Press Save.
Editing tables
If you followed the instructions you have made a mistake! The trackTitle should not be INT it should be VARCHAR. To fix it you can now click on the table (secondtable) on the left and then Structure on the top. Click on the small pencil in the row which holds the detail for trackTitle. This lets you change anything about that field. Change the data type to VARCHAR and the length to 50.
You may also notice a red message about there being no index on this table. Fix it by clicking on the small key in the first field. Hover over the icon if you need to check that it is the primary key icon.
cdReference will be a foreign key as it will soon be used in a relationship. That means it needs to be indexed. Click on the Details link at the bottom of the Structure view of this table. That should reveal the indexes (1). Click the Go button to create another. Select cdReference in the drop down list and leave the rest blank. Click Save.
You should now have this:
Play with PHPMyAdmin until you are confident in creating tables but leave these two unchanged.
