The last tutorial, How to Create a MySQL Database with PHP, showed us how to create a database on the fly. This example will show us how to make the database usable by creating tables in it. The database we previously created was called “bozo.” Yes, as in Bozo the Clown. The first table we’re going to create will be called “famous_clowns.”
Assuming you did the last tutorial, or you have a database called “bozo,” you can run the following code to create a table in it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | <?PHP mysql_connect("localhost", "fred", "fredpw") or die (mysql_error()); mysql_select_db("bozo"); $tablename = "famous_clowns"; $maketable="CREATE TABLE $tablename ( seed INT NOT NULL AUTO_INCREMENT, name VARCHAR (30), country VARCHAR (20), rating INT (3), comment VARCHAR (100), PRIMARY KEY (seed) );"; mysql_query($maketable) or die (mysql_error()); ?> |
Line 3 is the standard line for connecting to MySQL with my fictional user named Fred and his simple password: fredpw. Line 4 selects the database we’ll be creating the table in.
Line 6 just defines a variable we’ll use to name the table. In this case, our table is going to be called “famous_clowns.”
Lines 8-15 define a variable string that we’re going to pass to MySQL. This is the actual command that will create the table. The following is a line by line explanation of this statement:
8 9 10 11 12 13 14 15 | $maketable="CREATE TABLE $tablename ( seed INT NOT NULL AUTO_INCREMENT, name VARCHAR (30), country VARCHAR (20), rating INT (3), comment VARCHAR (100), PRIMARY KEY (seed) );"; |
Line 8 is the simple MySQL command to create the table.
Line 9 defines the first column we’ll be using. Like any column name, we could call it anything but we’re going to be using it as a seed so we might as well call it that. The first parameter after defining its name is “INT” which tells MySQL that it’s a simple integer. The second argument is “NOT NULL” which just means that it can’t be zero (or empty). The “AUTO_INCREMENT” means that every time a line of data gets written to this table, this value will be incremented by one.
You certainly don’t have to have a seed in your table, and you may never use it, but it can be convenient for selecting data later or for sorting entries by the order in which they were input.
Line 10 defines the next column as “name” and we’ll use this to hold the clown’s name. The type “VARCHAR (30)” means “variable characters” with a max length of 30. If we input a name that is 10 characters long, this field will be 10 bytes. If we just set its type as CHAR with a length of 30, the value would always be 30 bytes long. Making it a variable length may save a little space in the end. Probably not in this example but if your database has tens of thousands of entries with lots of tables and columns, it would make a difference.
Line 11 defines a column called “country” which we’ll use for the clown’s country of origin.
Line 12 is another integer, three characters max, this one called “rating.” We’ll use this later to give our clowns a numeric rating from 1-100.
Line 13 just gives us a field for entering comments about our clowns.
Line 14 tells MySQL that we’ll use a PRIMARY KEY, which will be our “seed” column. This is optional, but not a bad idea to define this as it will be helpful for pulling data from this table later.
Note that there is no comma after the last line in our $maketable string. Also, these don’t have to be written as shown, with one column defined on each line, but for our purposes it makes it easier to read.
Finally, line 17 executes the MySQL query and actually makes the table. Note there is no output from this script (no print or echo statement) so if you run it through your browser, you get a blank page. But if you run it again (refresh), line 17 will produce an error and you’ll get:
Table 'famous_clowns' already exists
You can also log into MySQL (either locally, via ssh, or PHPmyAdmin) to check that your script worked and actually created the table. Just log in and run the following commands:
mysql> use bozo; Database changed mysql> show tables; +----------------+ | Tables_in_bozo | +----------------+ | famous_clowns | +----------------+ 1 row in set (0.00 sec) mysql>
You can execute the following command to check and see exactly how your columns were defined.
mysql> describe famous_clowns; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | seed | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | | country | varchar(20) | YES | | NULL | | | rating | int(3) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
There you go. You’ve now created a usable table that you can start writing data to. My next tutorial will show you how to input data into this table – directly from the command line as well as by using input from an HTML form.