MySQL

MySQL is the most popular open-source database management system. It runs Content Management Systems (CMS) such as WordPress, Drupal, and Joomla! It is also used by high-traffic websites like Facebook and YouTube. MySQL is currently owned by Oracle and offers the Community Edition of MySQL for free. The functionality of the Community Edition is identical to the for-purchase Enterprise version. The only difference is there is no official support with the free version. However Oracle maintains a MySQL website.

Note: Dreamweaver can only connect to MySQL databases. Some hosting sites only allow connecting to the databases they host from their domain. So in Dreamweaver I can connect to MySQL databases only on the local host. My remote site is running MySQL 5.1 and locally I am running MySQL 5.5.24.

phpMyAdmin MySQL Administration via Web Browser

MySQL is a command line program. It does not have a graphical user interface. Direct access to MySQL is through a windows command prompt. To make the administration of MySQL easier, most people access MySQL through a third-party user interface. These third-party user interfaces include: phpMyAdmin, MySQL Workbench, Navicat, and SQLWave.

phpMyAdmin is a free open-source software designed to handle the administration of MySQL over the internet. The software was written in PHP and provides a graphical user interface for the most frequently used database management functions. Most web host provide phpMyAdmin as the standard MySQL interface. The phpMyAdmin home page provides documentation and a help forum.

  1. On my PC I am using version 3.5.3 of phpMyAdmin which runs in a browser from http://localhost/phpmyadmin/.

    • Requires PHP 5.2.0 or newer.
    • Requires MySQL 5.0 or newer.

  2. My host server is running version 2.6.4 of phpMyAdmin which runs from http://cp1.your-hosting.net/phpMyAdmin.

    • Requires PHP 4.1.0 or newer.
    • Requires MySQL 3.23.32 or newer.

The name of the phpMyAdmin configuration file is config.inc.php.

phpmyadmin

MySQL Storage Engines MyISAM, InnoDB

MySQL supports several storage engines. Choosing a storage engine determines how the data is stored and retrieved. MyISAM has been the default storage engine since MySQL 3.23. Starting with MySQL 4.1.5, InnoDB became the default storage engine for MySQL on Windows. The main difference between the two storage engines is that InnoDB has support for transactions and foreign key constraints, where MyISAM does not.

The InnoDB Storage Engine has commit, rollback, and crash-recovery capabilities. InnoDB also support foreign key referential-integrity constraints. You can mix InnoDB tables with tables from other MySQL storage engines. To determine which storage engines your server supports, use the "show engines" command in the MySQL console:


mysql console


Database Design Theory and Suggested Rules,

MySQL is a relational database. The relations between the tables are managed with primary and foreign keys. Each row in a database table should have a unique identifier know as a primary key. The primary key is usually a unsigned integer which is automatically incremented by one for each new row. However you can use any combination of numbers and letters as the primary key, as long as they are unique for each row.

For example, the "customers" table would have a primary key called "customer_id" defined as an auto-incrementing unsigned number. The "states" table would use the unique two character abbreviation for the "state_id" primary key. You could link the customer table with the states table by using the "state_id" in the customer table. When the primary key of the states table is used in the customers table, it is known as a foreign key in the customers table. Although a primary key can only be used once in its own table, it can be used many times a foreign key.

Since there can be many customer in one state, this type of relationship is known as one-to-many. The relationship between two tables can also be many-to-many, in which case an intermediate table is used to cross-reference the primary keys.

Suggested Design Rules

Below are some suggested design rules for MySQL databases:

  1. The ideal table should represent a single subject.
  2. If values are repetitive, consider moving them to their own table.
  3. Columns should store only one piece of information.
  4. Cross-reference tables by storing the primary key as a foreign key in related tables.
  5. Handle many-to-many relationships with a cross-reference table.

Suggested Naming Rules

Below are some suggested naming rules for MySQL databases:

  1. Use lower-case only. Use all lower-case when defining names for databases, tables, and columns. Use only an underscore to create hybrid names such as last_name.
  2. Use plural nouns for table names, such as customers, cities, or products.
  3. Use singular nouns for column names, such as city or product.
  4. Do not use any of the MySQL reserved words for names.
Note: If you encounter names that use a hyphen or is a MySQL keyword, you need to surround the name with back ticks (`last-name`), usually located above the tab key.


MySQL Data Types Most Commonly Used Data Types

MySQL has 36 different data types. However, in most cases, just a few data types are commonly used. They are listed below.

  1. INT - integers.
  2. FLOAT - floating-point numbers (approximate value).
  3. DECIMAL - fixed-point numbers. (use for currency, no rounding errors, must set maximum number of digits before and after the decimal point.
  4. CHAR - fixed-width text, 255 characters maximum, accepts default value.
  5. VARCHAR - variable-width text, 65,535 character maximum, accepts default value.
  6. DATE - YYYY-MM-DD format.
  7. TIME - HOURS:MINUTES:SECONDS format.
  8. TIMESTAMP - combined date and time value. Range from 1970 to 2038.
Creating a MySQL Database Collation, Location of DB Files

A collation is a set of rules for comparing characters in a character set. Setting the collation in MySQL affects the sorting order of data and can have minor performance implications. To display the available collation and character sets in the MySQL console enter the "show character set;" command. (Wamp MySQL default user is "root" with no password).

collation
The ending characters indicate: _ci = Case-insensitive, _cs=Case-sensitive, and _bin=Binary. Two collations commonly used for English languages are unicode:

  1. utf8_general_ci (My choice)
  2. utf8_unicode_ci
utf8_general_ci is somewhat faster than utf8_unicode_ci, but less accurate (for sorting).

The MySQL database files are located in the MySQL data directory (C:\wamp\bin\mysql\mysql5.5.24\data\). Below are the files for the "1218369_language" database which has three tables: "categories", "languages", and "lines". The *.frm files hold the table form (definition). The *.MYD files hold the table data. The *.MYI files hold the table indices. The *.opt file contains the database configurations. These files are created for both the MyISAM and InnoDB storage engines.

MySQL Database Files

phpMyAdmin Export/Import Export/Importing Small Databases

phpMyAdmin can be used for exporting and importing small databases. However phpMyAdmin uses a time-consuming process that transmits the data across a network to the user's computer, keeping the database locked the entire time. For larger databases it is better to perform the export locally on the server using the command line and native MySQL commands.

Export DB Data and Database Structure from Server

This process exports both the data and the database structure:

  1. Log in to phpMyAdmin on the server for the database to export.

  2. Select Export from the MySQL menu.

  3. Select the export type of SQL.

  4. Checked the "Save as file" check box and press the Go button.

    phpmyadmin
    phpMyAdmin 2.6.4


  5. I saved the file (pdb11.your-hosting.net.sql) to my desktop.

    DB SQL
Export DB Data Only for Table from Server

This process exports only the data from the database in a CSV format:

  1. Log in to phpMyAdmin on the server and Select the table to export.

  2. Select Export from the MySQL menu.

  3. Select the export type of CSV and change the CSV option: "Fields terminated by" to be a comma and remove the value from the "Fields enclosed by" option.

  4. Checked the "Save as file" check box and press the Go button.

    phpMyAdmin
    phpMyAdmin 2.6.4

  5. I saved the file (pdb11.your-hosting.net.csv) to my desktop.

    csv file


Import Database Data and Structure to PC

This process imports both the data and the database structure:

  1. Log in to phpMyAdmin on the server.

  2. Select Import from the MySQL menu.

  3. Browse to find the *.sql file to import (pdb11.your-hosting.net.sql).

  4. Press the Go button.

    php myadmin

    phpMyAdmin 3.5.3


  5. Wait for message concerning the import status.

    myphpadmin



Top | PHP API