MySQL Databases Part II
Okay I was probably a bit brief before with databases and so I thought I’d expand a little before I go any further.
A database is the main object. This holds it all together. You can have many databases in MySQL quite easily, for different projects, or even different databases for use on the same site.
A database contains tables. Tables hold records. When I first learnt databases I would have called a table a database, so anyone who’s old school may think the same way! But now they’re called tables. You can also have many tables in a database. Tables contain records. Each record contains information relative to the table. A Record is main up of fields/columns. Each field is given a name, a type such as character, variable character, integer, a length if not preconfigured and a few other options such as whether it should automatically increase for each record (eg. an integer that counts up from 1).
It’s good practice to always have a row id field in each table and set this to be an integer and to auto increment. This can then be set to be the Primary key, which means that it must be unique and is the main reference for that table. By having a table index you can often perform searches much quicker and reference the rows easier.
To look at a database from a real world example take a filing cabinet. The database is the filing cabinet, holds everything inside. Each table is a draw in the filing cabinet, and each record is the file within the drawer.
So to take the example I started in phpMyAdmin. If you create a database on the front page of phpMyAdmin called ‘mydb’ and click Create. This creates the database which will appear in the left bar. Now to create a table. On the next screen it will ask for you to give a table name and number of fields. Here give the table a name of ‘content’ and fields ‘5’ and click go. A page will now display with 5 lines of text boxes. To explain each box/column
Field: This is where you give it a name. I tend to stick to using underscores in the names instead of hyphens or spaces. Make the field understandable but becareful what you use as some words cannot be used such as ‘desc’ for description say, as ‘desc’ means descending and is used in sql statements.
Type: This is the character type of the field. The most commonly used ones are INT for Integers (used for calculations and counting), VARCHAR for variable characters (alphanumeric words), TINYTEXT for longer text fields containing up to 255 characters, TEXT for storing up to 65,000+ characters and FLOAT for decimal numbers (I use this for the money columns).
Length/Values: This is where you specify the length if it’s required. TINYTEXT and TEXT do not need this field filling in as they are pre-specified. FLOAT should be used as X,Y where X is how many numbers before the decimal point and Y is how many decimal places.
Attributes: Ignore for now
Null: You can set a field to Null or Not Null. When Not Null the field must always have a value in it for every record.
Extras: Here you can set your field to be autoincrement. This as already explained, is where you set the field to automatically increase by 1 if you do not enter a value. Can only be used on numerical fields.
At the end of the row you have some radio buttons. The first is Primary Key. You can only have one primary key per table. The others (Index, Unique, Fulltext) you can safely ignore for now.
So fill out the table as per the previous post’s instructions:
rowid – INT – 5 – autoincrement – Primary key
firstname – VARCHAR – 25
surname – VARCHAR – 35
address – TINYTEXT
postcode – VARCHAR – 10
and then click Save. So now you have a table in your database.
In phpMyAdmin you can use the tabs across the top of the table pages to perform various operations
BROWSE lets you see the content of each table.
INSERT allows you to add a row to the table via a simple form.
SEARCH lets you search on each field through the table
SQL lets you apply MySQL commands directly by writing the raw code in a text box. This is a handy box for testing with too.
EMPTY as it says, empties the table
DROP will delete the entire table and it’s content
EXPORT lets you dump the content of the database either into the next screen or you can save it as a file. This is the best method for backing up.
OPERATIONS gives you the options of various operations such as duplicating the table, resetting various fields etc.
All of this can be learnt mainly by playing, that’s how I learnt really and I think it’s the best way. Nothing in there can do any damage besides completely deleting the table. If you create a test table then play with that is the best way.
So that’s a database. You now have a database and a table, all we have to do now is the PHP connections and learn how to use PHP to add and retrieve data from the database.