The tool we are using to administer, or manage, the class MySQL database is phpMyAdmin, a free tool that can be installed on most web servers. Before we outline the way we use this tool, it’s important to bear in mind the structure of MySQL databases: database servers can house multiple databases. Each database can contain multiple tables. Each table can have multiple rows. And each row contains multiple fields, or columns, of data.
How you use phpMyAdmin depends on what you’re trying to achieve. If you want to do something related to the server itself, you will want to be in the “server view”. If you want to do actions that relate to a particular database as a whole, you will need to go to the “database view”. If you want to perform actions that relate to a specific table, you will need to go to the individual “table view” for that table. And if you want to do actions that relate to data held in a particular row, you will need to go to the “browse rows view” that shows the data held in rows in the table.
Server view
To begin, when you first load up phpMyAdmin, you are in what I am calling the “server view”. It is unlikely you will want to do much here. So you need to select the database you want to administer:
Selecting a database in the "server view"
Database view
Once you have selected a database from the drop-down menu, you will be taken to the “database view”, where you can do actions that relate to this particular database. For example, if you wanted to add or delete a table from the database, this is the view that you would want to use. You could also export the entire database from here, or import tables into the database.
Overview of the "database view"
At the top of the “database view”, you will see breadcrumbs. These show you in which level view you are currently located in the hierarchy of the database server. You can click the server breadcrumb to go up one level to the “server view”.
On the left side of this view are links to individual “table views” for all the tables contained within this database.
Note: hidden at the very bottom of this page, below the details of all the tables, you will find a form you can use to create a new table within this database. Using this form will take you to the “create table view” outlined below.
form for creating a new table
Table view
Once you select a table from list of links in the left of the “database view”, you come to the “table view”, where you can administer aspects of the selected table.
Main components of the "table view"
By default this view shows you the structure of the selected table, meaning what fields have been defined for this table. To edit any particular field, perhaps to rename it or change its type, you can click the pencil icon in the “Action” section to the right of any field name.
Below the list of fields contained within this table, you will find a form you can fill to create a new field in this table. This is useful in case you find that the original fields you created for this table are no longer sufficient to store the data you intend to use it for.
form for adding a field to the table
Along the top of the view are tabs for exporting this table individually (as opposed to exporting the entire database, which can be done from the “database view”), running SQL commands directly on the table, and browsing the rows of data stored within this table.
You will most likely use this view for viewing the structure, or browsing the rows of data contained within this table.
Browse rows view
If you clicked the “Browse” tab from the “table view”, or if you ran a query directly on the table using the “SQL” tab, you will come to the “browse rows view”. This view shows you rows of data that match the SQL query you specified. From here, you can sort, read, update, or delete rows of data from this table.
the "browse rows view"
The pencil icon next to any row will allow you to update the data in that row. The red “x” icon will delete the specified row.
Create table view
If you created a new table using the form at the bottom of the “database view”, you would be taken to the “create table view”, where you can specify the fields and attributes of the new table.
the "create table view"
Here is the convention I always use when creating tables. I recommend you do the same:
- always use lowercase field names, table names, and database names
- always create the first field called “id”, with type “INT”, “auto_increment” selected, and set it as the “primary key” for the table
- always create the last field called “created” with type “TIMESTAMP”, and make sure the “CURRENT TIMESTAMP” checkbox is checked
- if you want a field that will hold text with less than 255 characters, use the “VARCHAR” type of field, with the length set to “255″
- if you want a field that will hold more than 255 characters, use the “TEXT” type of field