the science bit

Using the MySQL Administrator application to create a table and MySQL Query Browser to view entries in tables.

In order to use PHP to store information in a database, you will need to create a table within that database that can receive that information. This method uses applications in the free MySQL Tools pack to do so.

When you first open MySQL Administrator, you will see a Connection Editor panel. Click on the + sign in the bottom left to create a new connection and enter the following:

Connection Name: anything
Username: Ist initial + Surname
Password: 5 letter code

Then connect. You should be able to see the info page. The only part of this that you should be working with is the Catalogs area, so click on the appropriate icon.

Your database has already been created for you and should appear on the left as your first initial and surname (e.g. mnortham). This is the only one you will be able to administrate. The database doesn’t currently have any tables in, so to create a table, click on Table Actions and select Create Table..

Before adding columns, give the table an appropriate name (in our case the table will store information about uploaded images, so ‘images’ works nicely). Then you can add your first column by clicking on the + icon near the middle-left. Each column will store a different bit of information in the table (and one full piece of information is then called a row), so we need columns for the image name, an image description and the all important id.

The first one we’ll create is ‘id’. Practically every table you ever create should begin with an ‘id’ column as the number which will end up in that column will be unique to that specific row. This makes it much easier to extract the correct information we need later on because we can reference that unique id (instead of potentially worrying about trying to discern between things that have the same description for instance).

So, create a column called id and leave the data type as INT (integer). Then tick Primary Key and Auto Increment in the lower half of the panel. This ensures we can use the id to index this table as well as making sure that each new entry automatically assigns itself a new number.

Then add 2 more columns called ‘imgfile’ and ‘des’, setting the data type as VARCHAR(50) for both of them. This data type will allow the column to contain a range of characters which is useful for us because we will be storing the path to an image file and a textual description. The number 50 in brackets means that it can only contain up to 50 characters which is ample for our needs. When you’re designing a database table structure, it’s important to make sure it’s set up for its purpose.

Hit ‘apply’ and then ‘execute’ to run the SQL which will create your table.

NOTE: When you press apply, if you have a line like:

PRIMARY KEY(`newcolumn`, `id`)

then this is incorrect. For some reason, MySQL Administrator sometimes throws this in when it shouldn’t. Just edit the line to read:

PRIMARY KEY(`id`)

before hitting execute.