Quakenet/#php Tutorial

Note: If you opened this page from an external URL pay attention that all chapters are linked together. Be sure you also read all prior chapters of this tutorial, otherwise you will miss relevant content explained before.

MySQL / Tables

  1. Layout of tables in MySQL
  2. Types for columns
  3. Create a MySQL table

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.

Table news
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.

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.

Table news
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.

Questions about the chapter

No questions

Back to Next to
Copyright © to the OPs of #php/QuakeNet Valid XHTML 1.0 Strict Valid CSS!