View source for Archive:PHP and MySQL Programming/Creating a Table

== Introduction ==
{{?}}
Before creating a table, please read the previous section on [[Programming:PHP and MySQL:Creating a Database|Creating a Database]].

A Table resides inside a database. Tables contain rows, which are made up of a collection of common fields or columns. Here is a sample output for a SELECT * query :

 mysql> SELECT * FROM `books`;
 +------------+--------------+------------------------------+------+
 | ISBN       | Author       | Title                        | Year |
 +------------+--------------+------------------------------+------+
 | 1234567890 | Poisson, R.W | Programming PHP and MySQL    | 2006 |
 | 5946253158 | Wilson, M    | Java Secrets                 | 2005 |
 | 8529637410 | Moritz, R    | C from Beginners to Advanced | 2001 |
 +------------+--------------+------------------------------+------+

As you can see, We have rows (horizontal collection of fields), as well as columns (the vertical attributes and values).

== Creating a Table ==

The SQL code for creating a table is as follows :

 mysql> CREATE TABLE `table_name` (
        `field1` type NOT NULL|NULL default 'default_value',
        `field2` type NOT NULL|NULL default 'default_value',
        ...
        );

== Example ==

Here is an example of creating a table called `books` :

 mysql> CREATE TABLE `books` (
        `ISBN` varchar(35) NOT NULL default '',
        `Author` varchar(50) NOT NULL default '',
        `Title` varchar(255) NOT NULL default '',
        `Year` int(11) NOT NULL default '2000'
        );

== Getting Information about Tables ==

To get a list of tables :

 mysql> SHOW TABLES;

Which produces the following output :

 +-------------------+
 | Tables_in_library |
 +-------------------+
 | books             |
 +-------------------+
 1 row in set (0.19 sec)

To show the CREATE query used to create the table :

 mysql> SHOW CREATE TABLE `books`;

Which produces the following output : 

 +-------+--------------------------------------------+ 
 | Table | Create Table 
 +-------+--------------------------------------------+
 | books | CREATE TABLE `books` (
   `ISBN` varchar(35) NOT NULL default '',
   `Author` varchar(50) NOT NULL default '',
   `Title` varchar(255) NOT NULL default '',
   `Year` int(11) NOT NULL default '2000'
 ) TYPE=MyISAM |
 +-------+--------------------------------------------+
 1 row in set (0.05 sec)

And then to show the same information, in a tabulated format :

 mysql> DESCRIBE `books`;

Which produces the following output : 

 +--------+--------------+------+-----+---------+-------+
 | Field  | Type         | Null | Key | Default | Extra |
 +--------+--------------+------+-----+---------+-------+
 | ISBN   | varchar(35)  |      |     |         |       |
 | Author | varchar(50)  |      |     |         |       |
 | Title  | varchar(255) |      |     |         |       |
 | Year   | int(11)      |      |     | 2000    |       |
 +--------+--------------+------+-----+---------+-------+
 4 rows in set (0.18 sec)

[[../Creating a Database/|Previous: Creating a Database]] | [[../|TOC]] | [[../Basic Queries/|Next: Basic Queries]]

[[Category:PHP and MySQL Programming|{{SUBPAGENAME}}]]