Creating Tables with SQL and PHP

 

You can create tables using SQL (and whole databases), and specify the fields you want to go in the table. However, doing it this way is not recommended: you tend to forget which fields are in the table, their data types, which field is the primary keys, and which ones are set to NULL values. If you can get to grips with visual tools like phpMyAdmin then so much the better.

To create a table then, you use the CREATE keyword (known as a clause, in database speak). Here's the SQL to create the simple address book we've been using. This assumes that the database itself already exists, and that the PHP code to open a connection has already been written (you saw how to do this in a previous section):

$SQL="CREATE TABLE AddressBook
(
ID int(7) NOT NULL auto_increment,
First_Name varchar(50) NOT NULL,
Surname varchar(50) NOT NULL,
email varchar(50),
PRIMARY KEY (ID),
UNIQUE id (ID)
)";

mysql_query($SQL);

So you start with the Clause CREATE TABLE. Then you type the name of the table you want to create. In between round brackets, you type the name of your table Columns, followed by some formatting. In the code above, the first field being set up is this:

ID int(7) NOT NULL auto_increment,

The column name will be ID. The data type is an integer that is no longer that 7 digits. NOT NULL means you want something in this field, and that it can't be left blank. The ID number will be auto incremented, whenever a new record is added. Notice that there is only one comma in the line. The comma separates each field you want to create in your table.

We're also setting up three other columns here: First_Name, Surname, and email. First_Name and Surname can't be left blank ( NOT NULL), but email can be.

At the end, we have these two lines:

PRIMARY KEY (ID),
UNIQUE id (ID)

The primary key is used for things like joining data from one table to the data from another. We've set this to our ID field. Primary keys don't have duplicate values, so we've set this to be a UNIQUE field.

Once you've written your SQL statement, you can go ahead and execute it:

mysql_query($SQL);

Creating tables like this means a lot of extra, tricky work for you as a PHP programmer. If you can use a tool to do the job for you, then your coding life gets easier!

In the next part, you'll see how to the Update command works.