Title: MySQL Basic Data Types and CREATE TABLE ($$$)
Course: PHP/MySQL Course

Numeric Data Types

Read about the numeric data types on page 305 to 307.

Note:
- The integer types all hold only whole numbers, make sure that you do not choose one that may not hold the future possible range of numbers you will need.
- Do not use integer types for anything that may have a fractional part.
- The most common integer type to use is just INT
- Unless you have a specific reason, you should always use DOUBLE for any number that has a fractional part.
- For the best compatibility you should leave off the M and D specifiers and just use DOUBLE by it self for all floating point numbers.
- DOUBLE(10,2) would specify a number up to 10 places and show 2 decimal places. 10 is the display length and 2 is the number of decimals displayed. Neither of these affect the actual precision and storage of the number.
- DECIMAL is a special type that you can specify the exact display length of up to 64 digits and the number of decimals. This will allow you to store larger precision than even DOUBLE. But it is slower and takes more storage, so only use it for very special circumstances.

Recommend:
- Only use INT and DOUBLE, without display or decimal specifiers.

Question?

for storing the number of children in a family, which is the best data type?

- int|INT|TINYINT|tinyint

Date and Time types

Read about the date and time datatypes on page 307 to 208.

Notes:
- The most used types are DATE and DATETIME
- Use DATE for date only
- Use DATETIME for time only or a data and a time
- TIMESTAMP is designed to be a special date/time field that gets set to the current time any time you modify the record. So it keeps track of when the record was modified.
- Remember that DATE are stored as a string in the form YYYY-MM-DD (year, month, day), and not in the normal US MM-DD-YYYY
- To convet a date or time from a database DATE or DATETIME field to a universal unix timestamp:

$ts = strtotime($db_data); // where $db_data holds the db data

and to convert from a timestamp like the current time to a mysql compatible DATE data you can use:

$db_date = date("Y:m:d",time()); // current time

or for a compatible DATETIME data you can use:

$db_date = date("Y-m-d H:i:s",time());

Question?

How are dates stored in side a DATE field?

- As MM-DD-YY such as 03-13-09 for March 13, 2009
- As YYYY-MM-DD such as 2009-03-13 for March 13, 2009
- As YY-MM-DD such as 09-03-13 for March 13, 2009
- As YYYY-DD-MM such as 2009-13-03 for March 13, 2009

String types

Read about string types on page 308 and 309.

Notes:
- CHAR(M) is more efficient, but you should usually use the VARCHAR(M) for most text data, it is more space efficient but just a little slower
- The (M) part in each is the maximum number of characters
- VARCHAR(255) is the largest for VARCHAR
- TEXT should be used for when you expect larger than 255 characters could be input.
- BLOB types are normally used for storing binary data like a actual picture or excel file inside of the database, pick the BLOB type for the size of data possible
- If you need to store a really long text (like a book) use LONGTEXT (up to over 4 billion characters)
- ENUM is a specialized type that is vary useful for having just a small list of options available for a database field. Here are some examples:

for a field named gender: enum( "M","F")
for a field named color: enum("blue","red","white","green")

Question?

What type of field would you define to store the text for a research paper that is always less than 40 pages long with 2000 characters or less per page.

- BLOB
- MEDIUMTEXT
- VARTYPE(80000)
- TEXT

CREATE TABLE

Read about creating a table on page 309 to 310.

Notes:
- The SQL command to create a table shown on page 310 is typed in and the semi-colon always ends a command. The actual command that you type is:

CREATE TABLE grocery_inventory (
id INT NOT NULL PRIMARY KEY AUTO_INDENT,
item_name VARCHAR(50) NOT NULL,
item_desc TEXT,
item_price FLOAT NOT NULL DEFAULT 0.0,
curr_qty INT NOT NULL DEFAULT 0
);

Each field has a field name and the data type. You can then follow the data type with several options

The most common is NOT NULL, this will not allow a Null data value to be entered, the null value means there is not data

PRIMARY KEY most tables should have one field that uniquely identifies each recored, this is how your tell mySQL which field that is.

AUTO_INDENT, this will tell a int field to automatically fill in new values with 1 greater than the biggest int stored in a record so far. This will automatically create unique primary keys.

DEFAULT value, this will set an initial value if one is not supplied.

There are many more options for create table. At some point you should get a good book on just MySQL and check out the MySQL documentation at

http://dev.mysql.com/doc/refman/5.0/en/index.html

Question?

How many fields for the table employees does this CREATE TABLE generate:

CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY AUTO_INDENT,
last_name VARCHAR(25) NOT NULL,
first_name VARCHAR(25) NOT NULL,
salary DOUBLE NOT NULL,
gender ENUM( "Male","Female")
);

- 5|five|FIVE|Five