MySQL is the most popular open-source database application, and is commonly used with PHP.
A database most often contains one or more tables, used to store information categorically.
Tables contain columns and records (rows) with data.
Below is an example of a table used to store user's informations (name, email, passord):

1 Mariusfaith
2 MarPlopeace
3 I_AMlove
- This table contains three records /rows (one for each person /user) and four columns (id, name, password, email)

MySQL naming rules

Each database and each table is identified by a name. The basic MySQL naming rules for databases, tables, and columns are:

MySQL data types

When creating a table, MySQL requires that you explicitly define what sort of information each column will contain. There are three primary types: Text (strings), Numbers, Dates and times; and two secondary types: Predefined lists and Binary data.
Choosing the column types correctly dictates what information can be stored and how, also affects the database's overall performance.
The table below lists most of the available types for MySQL, with a brief descriptions and how much space they take up.

Storing text The difference between "Text data types" consists in the maximum number of characters that can be stored in an individual field, the treatment of trailing spaces, and whether you can set a default value.
CHAR(length) length bytes A fixed-length field from 0 to 255 characters long. Accepts a default value.
VARCHAR(length) length + 1 or 2 bytes A variable-length field from 0 to 65,535 characters long. Accepts a default value.
TINYTEXT length + 1 bytes A string with a maximum length of 255 characters
TEXT length + 2 bytes A string with a maximum length of 65,535 characters. Cannot define a default value.
MEDIUMTEXT length + 3 bytes A string with a maximum length of 16,777,215 characters
LONGTEXT length + 4 bytes A string with a maximum length of 4,294,967,295 characters
Storing numbers These data types represents numbers in diferent formats.
TINYINT(length) 1 byte Range of –128 to 127. Or 0 to 255 unsigned
SMALLINT(length) 2 bytes Range of –32,768 to 32,767. Or 0 to 65,535 unsigned
MEDIUMINT(length) 3 bytes Range of –8,388,608 to 8,388,607. Or 0 to 16,777,215 unsigned
INT(length) 4 bytes Range of –2,147,483,648 to 2,147,483,647. If the column is declared as UNSIGNED, the range is from 0 to 4,294,967,295
BIGINT(length) 8 bytes Range of –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Or 0 to 18,446,744,073,709,551,615 unsigned
FLOAT(length, decimals) 4 bytes A small number with a floating decimal point. Since PHP will format numbers after calculation, They recommend that you use FLOAT without the optional parameters.
DOUBLE(length, decimals) 8 bytes A large number with a floating decimal point
DECIMAL(length, decimals) length + 1 or 2 bytes A DOUBLE type, allowing for a fixed decimal point. Prior to MySQL 5.0.3, the DECIMAL data type was stored as a string, so could not be used with SQL functions, such as SUM()
Storing dates and times MySQL stores dates in one format only: YYYY-MM-DD
DATE 3 bytes A date stored as YYYY-MM-DD. The range is 1000-01-01 to 9999-12-31
DATETIME 8 bytes A combined date and time displayed in the format YYYY-MM-DD HH:MM:SS
TIMESTAMP 4 bytes A timestamp (normally generated automatically by the computer). Legal values range from the beginning of 1970 through January 2038. TIMESTAMP columns are displayed in the same format as DATETIME columns.
TIME 3 bytes In the format of HH:MM:SS
Storing predefined lists MySQL lets you store two types of predefined list that could be regarded as the database equivalents of radio button and check box states
ENUM 1 or 2 bytes This column type stores a single choice from a predefined list, each column can have one of several possible values
SET 1, 2, 3, 4, or 8 bytes This column type stores zero or more choices from a predefined list. Like ENUM except that each column can have more than one of several possible values. or more choices from a predefined list. The list can hold a maximum of 64 choices.
Storing binary data Storing binary data, such as images.
TINYBLOB   Up to 255 bytes
BLOB   Up to 64kB

- The parameters (length, decimal) are optional, but for performance purposes, you should place some restrictions on how much data can be stored in any column. Databases are normally faster when working with fixed-size columns.
- Use VARCHAR for short text items and TEXT for longer ones.
- Use DECIMAL to store currencies.
- For dates and times, it's better to use TIMESTAMP when you record in that field a fixed point in time (for example when a record was inserted into the database). And, to use a DATETIME field when the date/time can be set and changed arbitrarily.
Note that the TIMESTAMP field type is automatically set as the current date and time when an INSERT or UPDATE occurs, even if no value is specified for that particular field.
- Isn't a good idea to use binary data. It bloats your database, and you can't display images directly from a database.

• This lesson is just for a brief introduction, and you can come back here when you want to check the list of MySQL data types (you can add this page to favorites).
In the next lesson you can learn how to connect to MySQL server with PHP, create a database and tables with the object-oriented interface known as MySQLi (MySQL Improved).

Daily Test with Code Example

Which tag is a block element?
<div> <img> <span>
<div>Web Programming and Development</div>
Which CSS code displays the text underlined?
font-style: italic; text-decoration: underline; font-weight: 500;
h2 {
  text-decoration: underline;
Click on the JavaScript function that can access other function after a specified time.
insertBefore() setTimeout() querySelector()
function someFunction() { alert(""); }
setTimeout("someFunction()", 2000);
Click on the instruction that returns the number of items of a multidimensional array in PHP.
count($array) count($array, 1) strlen()
$food = array("fruits" => array("banana", "apple"), "veggie" => array("collard", "pea"));
$nr_food = count($food, 1);
echo $nr_food;       // 6
PHP MySQL Introduction, Data Types

Last accessed pages

  1. Working with HTML attributes in PHP (3793)
  2. JavaScript Course - Free lessons (11891)
  3. jQuery get() and post() (1804)
  4. Bubble Tanks 3 (20303)
  5. Working with getElementsByTagName (8420)

Top accessed pages

  1. Courses Web: PHP-MySQL JavaScript Ajax HTML CSS Flash-AS3 (50906)
  2. Read Excel file data in PHP - PhpExcelReader (39764)
  3. PHP-MySQL free course, online tutorials PHP MySQL code (37936)
  4. Get Attribute (ID, Class, Name, Title, Src) with jQuery (34200)
  5. PHP PDO - exec (INSERT, UPDATE, DELETE) MySQL (31686)