PHP MySQL Introduction, Data Types

Php-mysql Course

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):

id	name	password	email
1	Marius	faith	name@domain.net
2	MarPlo	peace	user@domain.com
3	I_AM	love	address@domain.net

- 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:

• Names can be up to 64 characters long.
• Should only contain letters, numbers, and the underscore (no spaces).
• Names can begin with a number, but cannot consist exclusively of numbers.
• A table cannot have two columns with the same name and a database cannot have two tables with the same name.
• The name of tables and columns are case-sensitive.

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.

Type	Size	Description
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
MEDIUMBLOB		Up to 16MB
LONGBLOB		Up to 4GB

- 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).

<ul>
 <li>http://coursesweb.net/html/</li>
 <li>http://coursesweb.net/css/</li>
</ul>

.some_class {
  display: list-item;
}

var obj = {
 "courses": ["php", "javascript", "ajax"]
};
var jsonstr = JSON.stringify(obj);
alert(jsonstr);    // {"courses":["php","javascript","ajax"]}

$strhtml = '<body><div id="dv1">CoursesWeb.net</div></body>';
$dochtml = new DOMDocument();
$dochtml->loadHTML($strhtml);
$elm = $dochtml->getElementById("dv1");
echo $elm->nodeValue;    // CoursesWeb.net