1. Layout of tables in MySQL
In MySQL the data is saved in tables as rows. To identify a
table all tables have a name. Additional all tables have a list
of columns with names and types. A table news can
have a column added with the type DATETIME.
Each value added to the column added must be of
type DATETIME.
| added |
|---|
2. Types for columns
MySQL supports a lot of column types. From this list you choose the
right one for your colum. A number should be saved in an INT
field and a long text should be saved in a TEXT field.
A complete list of column types can be found in the mysql manual
at Chapter 9. Data Types.
Here is a short list of common column types.
-
INT - This column type is used for integer values. Rows often got an column for identification with the name like
ID. Such columns also have attributes likePRIMAR KEY(each ID is unique) andAUTO_INCREMENT(the next ID is created automatically). -
VARCHAR(n) - This type is used for short strings. The parameter
nsets the maximum size of the string. You use this type for saving short strings like usernames or email addresses. -
DATETIME - Is used to save a time value with the full date. For a news script this type is used to save when a news entry was written.
-
TEXT - This is used for longer strings like news texts.
3. Create a MySQL table
You create a table with the sql command CREATE TABLE.
It is followed by the table name to create. You cannot use any
MySQL keywords
as table names. After that a opening parenthese followes and the list
of columns. Each column entry is seperated with a comma. Each column entry is build
with the name, the type and optional additional options like NOT NULL,
PRIMARY KEY and AUTO_INCREMENT. Each sql command must be
terminated with a semicolon. An example sql query looks like this.
CREATE TABLE news (
id INT AUTO_INCREMENT PRIMARY KEY,
author VARCHAR(30) NOT NULL,
title VARCHAR(50) NOT NULL,
content TEXT NOT NULL,
added DATETIME NOT NULL
);
Note this is a sql query. You cannot use this as php code. To send a query (like this one) to the database you must use functions like mysql_query or mysqli_query.
The NOT NULL options can be used if a value must be added to this column.
This create will create the following table.
| id | author | title | content | added |
|---|
As the table is recently created there aren't any rows yet.
Be careful of the case sensitivity of table and column names. The MySQL manual deals with problems about case sensitivity.
Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database, table, and trigger names are not case sensitive in Windows, but are case sensitive in most varieties of Unix.
For this reason the mysql manual advise to use a consistent convention throught your whole application.
[...] To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.
