Remember that huge list of data types that we saw when we set up the tables?
We'll go into that list in a bit more detail soon, but first let's take a quick look at a typical HTML form.
You've seen forms like this a million times. What do you think the appropriate data types are for each field?
The first name, last name and company name will all be
VARCHAR s. The length could be 100 characters long; while that is a bit overkill, it's always good to be safe!
The email address will also be a
VARCHAR with a length of 50 or 100.
The phone number could be made into an integer by cutting out the dashes (-) or whatever is breaking up the number into smaller parts. An
INT won't work because the maximum size of an unsigned
INT is 11, which translates to a maximum value of 4,294,967,295. The max value of a signed INT is 2,147,483,647. That means that a phone number like 9175555555 is too big for the type
The easiest format for telephone numbers is a
VARCHAR . Validate the format of the phone number in your code before it reaches the database. Date of birth will be a
DATE . MySQL stores the date like this:
DATETIME format (where the time is also included) saves like this:
YYYY-MM-DD HH:MI:SS .
Gender is ususally saved as
VARCHAR with a length of 1 which will accommodate an
M or an
Finally, Comments will be saved with the type
Let's examine the list of datatypes more sequentially!
VARCHAR usually correspond to an html input box. It can hold up to 255 characters.
CHAR can hold up to 30 characters, and you'll almost never use this type.
TEXT usually corresponds to an HTML
textarea box and you don't specify the limit of characters. Store your novel in this format!
For numeric data the choices are:
INT- if this is a signed integer it can hold a value between -2147483648 and 2147483647
TINYINT- an integer between -128 and 127
SMALLINT- an integer between -32768 and 32767
BIGINT- an integer between -9223372036854775808 and 9223372036854775807
DECIMAL- a format for numbers that include decimals
FLOAT- a format good for money
DOUBLE- when you are going to save values returned from sin( ), cos( ), and sqrt( ) - so you want a high degree of precision with lots of fractional decimal places.
Formatting dates is pretty straight forward in MySQL. As mentioned, this DBMS saves the DATE format like this:
DATETIME is saved like this:
TIMESTAMP is saved using the same format as DATETIME:
And year is either YYYY or YY.
NULL - the value of no value!
If a tree falls in the forest and no one is around, does it make a noise?
In the case of a database field that has been set to accept NULL, the tree fell and didn't make a noise, but it did leave a trace.
NULL means that the value has not been set. This is different from an empty string (""). The empty string means that the value of "" has been set into this field.