MySQL is a command line application. The free download phpMyAdmin provides a web frontend that enables developers to perform a large number of common SQL tasks from creating tables to populating them and creating backups.
Creating the Database
The creation of a MySQL database has be done on a MySQL supporting server. Many hosting companies allow for the creation of a MySQL database through their administrator control panel, or they will have already created one for you.
Once a MySQL database has been created then the phpMyadmin frontend can be used. Essentially phpMyadmin is a collection of PHP files that contain all the necessary SQL queries for the manipulation of a MySQL database.
With a hosting company the rights to your MySQL database should already have been applied. When you connect to your phpMyadmin pages you will see an empty database listed.
Your phpMyadmin directory should be password protected. Again, this can often be done through the hosting company’s administrator control panel if has not already been done for you.
Creating the Tables
The tables you require in your database can now be created. This means firstly defining the table structure ie the field names and data types.
Tip: Give your tables sensible names. Try to use a consistent naming convention for table and fieldnames. Avoid whitespacing and other special characters in field names. Use of underscores is permitted.
- New tables can be created in phpMyAdmin from the default homepage.
Table fields can easily be added or removed once the table has been created.
Defining the Table
Once you have created a new table phpMyAdmin will prompt you to enter the details for each field. As with your table name try to use consistent field names and avoid the use of special characters (except perhaps underscores).
Having named your field you will need to decide the data type for the field. Take particular care to assign appropriate data types to the fields you create in your table.
Tip: If you have not done so already at this stage in an application development you should invest time in understanding the data, viewing sample data and understanding how it is structured. Use good database design techniques and ensure that your database is normalized.
We will consider all the options for field declaration in turn. Perhaps the most important of the options (after the name) is the data type. In MySQL the following are some of the core data types you are likely to use.
Text Data Types
CHAR [length]– Fixed length 255 characters (1 byte)
VARCHAR[length] – Variable length 255 characters (1byte)
TINYTEXT – String with maximum 255 characters
TEXT – 2 bytes 65,535
MEDIUMTEXT – 3 bytes 16 million
LONGTEXT – 4 bytes 4,294 million (phew)
Note: The BLOB data types are used for storing binary data. That is not something that you would generally do with a web application. For example if you want to have image data in your database it makes better sense to reference the image file rather than physically add the file to your database.
Numeric Data Types
TINYINT[length] – range -128 to 127 or 0 to 255 if UNSIGNED
SMALLINT[length] – range -32,768 to 32,767 or 0 to 65,535 if UNSIGNED
MEDIUMINT[length] – range -8 mill to 8 mill or 0 to 16 mill if UNSIGNED
INT[length] – range – 2000 mill to 2000 mill or 0 to 4000 mill if UNSIGNED
BIGINT[length] – massive!
FLOAT – floating decimal point
DOUBLE[length, decimals] – larger number with floating decimal point
DECIMAL[length, decimals] – a double stored as a string, allowing for a fixed decimal point
Dates and Times
DATE – in format YYYY-MM-DD
DATETIME – in format YYYY-MM-DD HH:MM:SS
TIMESTAMP – in format YYYYMMDDHHMMSS (range ends 2037)
TIME – in format HH:MM:SS
Note: We shall consider the manipulation of Dates and Times later in the course.
Some text and numeric data types require you to declare the field length. Try and set the field length to the smallest possible for any given task as it will then use less space in the database and should speed up your application.
For text fields the collation type of the field needs to be set. Collation refers to the character set used to store the data and is used to provide support for different languages. For example the default is ‘latin1_swedish_ci’, Swedish been based on Latin.
Note: The default ‘latin1_swedish_ci’ collation works fine for English because English contains no special characters such as accents.
Use Attribute to set ‘SIGNED’ or ‘UNSIGNED’ for numeric values. That is ‘unsigned’ for non-negative integers. There is also the option to set ‘ON UPDATE CURRENT_TIMESTAMP’ if a date/time data type is used. This will as the name suggests add a current timestamp to the field when it is updated.
A field can be assigned as ‘null’ or ‘not null’ ie be empty or not. When set to ‘not null’ this ensures that a row must have a value for the specific field. Thus a new row cannot be added without a value for any ‘not null’ fields.
The ‘default’ modifier allows the database designer to add a ‘default’ value for a field.
Tip: The ‘not null’ and ‘default’ can be used in combination to tidy up SQL transactions such as INSERT and UPDATE.
The ‘extra’ modifier can be used to enable ‘auto_increment’. This will be familiar to Microsoft Access users as ‘AutoNumber’ and will automatically increment the field’s value for each row inserted.
Tip: When allowing users to insert new records it is often useful to enable ‘auto_increment’ on the field identified as the primary key as this ensure the primary key is unique..
Primary keys and Indexes
The next set of radio buttons relate to primary keys and indexes. There are three options from left to right – Primary Key, Index, Unique (with an additional option to use ‘Fulltext’).
The primary key is likely to be the most familiar. A primary key is used to uniquely identify a row in a table. Primary keys are an essential database design tool and every table should have a primary key.
Indexes are used in MySQL to speed up data access. (Primary keys are a type of index ie they are used to access data quickly.) Unlike primary keys ‘normal’ indexes are not unique and can be created against specific field to speed up query processing. Which fields to index? Identify what queries are most common ie which fields are most often used for searches.
Care needs to be taken in defining indexes as they do take up space and require updating. Dynamic data that changes regularly would thus need to be indexed regularly. And don’t index every column.
Unique indexes are the same as ‘normal’ indexes but with one difference. All values of the unique indexed field must only occur once.
Finally the ‘Fulltext’ option can be used to set up a special type of index that allows for full-text searches. It is supported on tables of type MyISAM for VARCHAR and TEXT fields.
Warning: You may only have one primary key per table.
Tip: A good naming convention is to prefix or suffix all your primary keys with the ‘ID’ to make them easily identifiable.
Table Data Storage Types
The table you create can be of a one of a number of different storage types. MySQL has a number of database engines, but you’re most likely to encounter MyISAM and InnoDB. The default table type is MyISAM. Which should you use?
Unless you’re creating a very simple or experimental database, it’s almost certainly the wrong choice! MyISAM doesn’t support foreign key constraints or transactions, which are essential for data integrity. In addition, the whole table is locked whenever a record is inserted or updated; this causes a detrimental effect on performance as usage grows.
In most cases therefore it is recommended you change from the default of MyISAM to InnoDB.
A normalized database application is likely to consist of multiple tables. With PHP/MySQL the use of table joins can be managed two ways.
Table joins can be performed via SQL embedded in PHP pages. This requires you to hardcode table joins in your SQL queries. For example:
SELECT products.cert_id, cert_desc, product_name FROM products, certificate_info WHERE products.cert_id = certificate_info.cert_id AND products.cert_id = '12'
The same could be achieved by using the SQL INNER JOIN command.
SELECT p.cert_id, c.cert_desc, p.product_name FROM products AS p INNER JOIN certificate_info AS c ON p.cert_id = c.cert_id WHERE p.cert_id = '12'
To create table joins in phpMyAdmin you will need to create primary keys and indexes in your tables. Then use the Relation View icon found in the ‘Structure’ section to add joins.
Note: When adding foreign keys and relationships the tables must be of type InnoDB. You must also ensure that the value of the foreign key column is set to null or that both tables to be joined are empty of values. It is therefore best to make your joins prior to populating your tables with data.
Populating Tables with Data
Data can be added row by row in phpMyadmin via the ‘Insert’ tab.
Tip: If you use the join technique outlined above a drop-down of options is made available for the foreign key field.
Alternatively you may wish to upload a delimited text file to populate a table. Chose the Import Tab to browse for the text file you wish to upload. The text file will need to be a delimited CSV file.
Choose CSV using LOAD DATA and enter parameters for the type of delimited file you are using.
- Fields terminated by
- Fields enclosed by
- Fields escaped by
- Lines terminated by
- Column Names
- Use Local keyword
Likely value to add include ; “ \ and \n for new line \t for tabs.
Backing Up Table Structure and Data
Use the Operations tab in phpMyAdmin to take copies of your table structure or both the table structure and data. These SQL files can be loaded back into phpMyAdmin if required.