Setting Up the Database | Dreamweaver Mx: Advanced Php Web Development

Chapter 5 - Beginning Site Development

byGareth Downes-Powellet al.
Wrox Press 2003

Now that we have established exactly what information we need to store in the site's database, and what information we require from people who visit our site, we can implement a simple site database that will hold all of the information that we require.

In the last chapter we looked at the structure of our database, but did not explain how we were going to achieve it. We've previously introduced a number of tools that allow us to manipulate the content and structure of our MySQL database, using Structured Query Language (SQL). Now all we need is the relevant sequence of SQL Statements to create that structure for us.

The bookings Table

When a web site visitor makes their reservation online, all of the reservation information is stored in the bookings table. This table is created with the SQL statement below, and forms the backbone of the online reservation system.

     #     # Table structure for table 'bookings'     #     CREATE TABLE bookings (       ID tinyint(11) NOT NULL auto_increment,       roomID tinyint(11) NOT NULL default '0',       clientID tinyint(11) NOT NULL default '0',       startDate date NOT NULL default '0000-00-00',       endDate date NOT NULL default '0000-00-00',       adults int(11) NOT NULL default '1',       children int(11) NOT NULL default '0',       roomType varchar(30) NOT NULL default '',       roomOptions int(8) NOT NULL default '0',       networkConnection int(8) NOT NULL default '0',       PC int(8) NOT NULL default '0',       requirements varchar(250) NOT NULL default '',       PRIMARY KEY (ID)     ) TYPE=MyISAM COMMENT='Room Booking Table';

The bookings table is related to two other tables, the room table, and the client table which is indicated by the columns roomID and clientID respectively. During the registration process, the web site visitor's details are stored within a separate table, the clients table, which we will cover shortly.

Information about a particular booking, client and room is retrieved with the creation of a specific SQL Query statement, similar to this:

     SELECT CONCAT (clients.firstName, ' ', clients.lastName) AS CustomerName,     bookings.startDate AS ArrivalDate, bookings.endDate AS DepartureDate     FROM clients, bookings     WHERE clients.ID = bookings.clientID AND     clients.firstName = 'John' AND clients.lastName = 'Smith';

Assuming the table contained some data, this SQL Query would return all arrival and departure dates, for all bookings made under the name "John Smith" in a format similar to the table below.

CustomerName	ArrivalDate	DepartureDate
John Smith	2002-02-12	2002-02-14
John Smith	2002-05-15	2002-05-15

The clients Table

This table stores all of the customer's information when the customer places an online reservation. It should be noted that more information about the customer can be requested, but it is always recommended that you ask only for the information that you absolutely need.

The SQL used to create the clients table looks something like this:

     #     # Table structure for table 'clients'     #     CREATE TABLE clients (       ID tinyint(11) NOT NULL auto_increment,       title varchar(10) default NULL,       firstName varchar(30) NOT NULL default '',       lastName varchar(30) NOT NULL default '',       address1 varchar(100) NOT NULL default '',       address2 varchar(100) default NULL,       town varchar(100) NOT NULL default '',       province varchar(100) NOT NULL default '',       country varchar(40) NOT NULL default '',       postCode varchar(20) NOT NULL default '',       tel varchar(20) NOT NULL default '',       email varchar(100) NOT NULL default '',       cardNo varchar(16) NOT NULL default '',       expiryDate date NOT NULL default '0000-00-00',       PRIMARY KEY (ID)     ) TYPE=MyISAM COMMENT='Customer Table';

As you can see from this table, most of the information stored here is quite pertinent to that of a hotel booking. The data contained in this table is retrieved from the reservation booking form, during the online reservation process.

The room Table

This table contains all of the information about every room in the hotel, its price, type etc. Yet again, the structure of this table is rather simple, consisting of only five columns. The statement used to create this table is this:

     #     # Table structure for table 'room'     #     CREATE TABLE room (       ID int(11) NOT NULL auto_increment,       price int(11) NOT NULL default '0',       bed tinyint(4) NOT NULL default '0',       description text,       number tinyint(5) NOT NULL default '0',       PRIMARY KEY (ID),       INDEX bed (bed),       INDEX number (number)     ) TYPE=MyISAM COMMENT='Room description';

The creation of this table leads us to the last table in this database.

The users Table

This table is used exclusively for administration and staff access to the 'secret' parts of the web site. In this table you will find information about each authorized user, their names, usernames and encoded passwords:

     #     # Table structure for table 'users'     #     CREATE TABLE users (       ID int(8) NOT NULL auto_increment,       username varchar(20) NOT NULL default '',       password varchar(20) default NULL,       firstName varchar(30) NOT NULL default '',       lastName varchar(30) NOT NULL default '',       status varchar(10) NOT NULL default '',       PRIMARY KEY (ID)     ) TYPE=MyISAM;

Creating the Tables

Now that we have established the SQL required to create our tables in the database, we need to use the tools, previously introduced in Chapter 2, to create these tables.

Using phpMyAdmin

As we have seen before, phpMyAdmin is a very powerful web-based tool, and is useful for managing a database structure when it is stored on a remote server.

To begin, visit your phpMyAdmin site, and click on the entry in the left frame that represents your Hotel database. In our example site this database is called dwHotel. If you haven't yet created the database, you can do so by entering the name in the field provided and clicking "Create". Once you've selected your database, you should be able to see a section of the page in the main frame similar to the image below:

click to expand

This section allows us to directly enter the SQL that we have already given, to create the table and all its columns. To use this section, simply enter the SQL as it is given earlier and when finished, click on the 'Go' button. It is best to do this a table at a time, so as to lessen the chances of an error.

click to expand

Once done, you should see a page confirming the SQL statement that you have just entered, similar to the above screenshot which details the results of creating the clients table. To confirm that the table has been created successfully, and to your specifications, click on the name of the new table in the left-hand frame to take you to the table structure page.

In our example above we have created the clients table using the following SQL:

    CREATE TABLE clients (      ID tinyint(11) NOT NULL auto_increment,      firstName varchar(30) NOT NULL default '',      lastName varchar(30) NOT NULL default '',      address1 varchar(100) NOT NULL default '',      address2 varchar(100) default NULL,      town varchar(100) NOT NULL default '',      province varchar(100) NOT NULL default '',      country varchar(40) NOT NULL default '',      postCode varchar(20) NOT NULL default '',      tel varchar(20) NOT NULL default ",      email varchar(100) NOT NULL default '',      cardNo varchar(16) NOT NULL default '',      expiryDate date NOT NULL default '0000-00-00',      PRIMARY KEY (ID)    ) TYPE=MyISAM COMMENT='Customer Table';

This SQL statement produces a table structure resembling the screenshot below. You will note that immediately you can compare the SQL statement overleaf, to the corresponding entries in this table.

click to expand

Of course this is a quick and painless method for creating tables when you have the relevant SQL statements already. If you don't have the relevant SQL already prepared, then you can visually create your table using phpMyAdmin's Table Wizard.

The Table Wizard

To begin using the table wizard, you must ensure that you are working with the correct database. To do this, visit your phpMyAdmin page, and click on the relevant database name in the left-hand frame. You will see a section in the main frame that resembles the image below.

click to expand

To begin using the table wizard enter the name of the table you wish to create, and the number of fields, or columns, that the table will contain. In the screenshot above, you will see that this time we are creating the bookings table, which contains a total of twelve fields. Once this has been done, click on the 'Go' button, to proceed to the next step.

click to expand

We now proceed to enter the information for each column, as seen in the above screenshot. When creating a database table like this, you are presented with a number of options to help you determine the precise nature of the columns within that table. We have previously discussed some of these options in Chapter 2; if you want to know more about the available data types, the documentation links will take you directly to the MySQL web site.

You will notice from the screenshot that there are a couple of discrepancies, between what is entered onto the page, and our SQL Statement for the creation of the bookings table. The missing elements are the default values, because in our example above no default values have been entered.

This has been purposely done to illustrate a feature of phpMyAdmin. When creating a column that requires a zero default value that is NOT NULL, if no value is entered then the software will assume that the value is zero. This is an essential point to remember, as if you require the default value to be anything else, you must explicitly state the value you wish to be used.

When you have completed this form, and are happy that you have chosen the data types, lengths, attributes, and values that you require for each field in your database, click on the 'Save' button to create the table. You are then taken to another page that confirms the SQL statement that phpMyAdmin created to generate the table.

click to expand

Once again, when the table has been successfully created, it will appear in the left frame, where you should click it, and verify that the table is indeed constructed the way you wished it to be.

The Table Wizard in phpMyAdmin is not a wizard in the strictest sense, but it does allow you to easily and visually create your table and columns, which can be more appealing than dealing with raw SQL Statements.

Continuing with the theme of table creation, we will now explore how to achieve similar results using a database management tool that is installed with MySQL itself, the MySQL Monitor.

Using the MySQL Monitor

As mentioned in Chapter 2, the MySQL Monitor is an integral part of MySQL, and is installed automatically when you install MySQL onto your local system. Even if you have no plans to run a MySQL Server locally, it is useful to install MySQL anyway, and prevent the service from starting (by removing it from the Start Up folder in the Start Menu, or by disabling the MySQL Service in the Administration Tools of Windows NT, 2000, and XP). I recommend doing this, as the tools that are installed are useful even if you are connecting to a MySQL Server on another machine.

We can modify our use of the MySQL monitor using some extra settings, called switches, when we start the program. The syntax of this command can be seen below, all the highlighted sections of this command should be replaced with the actual settings for your MySQL server.

    mysql -u username -p -h mysqladdress databasename

To access the MySQL Monitor using the above syntax, in either Windows, Linux, or OSX, we can enter a command similar to the one below.

     mysql - u dwmxphp -p -h 192.168.0.223 dwHotel

There are various command-line switches that can be used, and it may be necessary for you to use additional switches that are peculiar to your MySQL configuration. The most commonly used switches and their syntax are included in the table below for your reference.

MySQL Monitor Syntax

Usage: mysql [OPTIONS] [database]

Name	Command	Description
Compress	-C, --compress	Uses a compression algorithm when communicating with the server. Occasionally used to speed up communication with the server. This option will only work if the server supports compression.
Database	-D, --database= [databaseName]	Specifies the name of the database to use.
Execute	-e, --execute= [SQLCommand]	Connects to the server, executes the given command, and then immediately exits the MySQL Monitor program.
Force	-f, --force	Continues, even if an error is encountered.
Host	-h, --host=[Host Address]	Connects to specified MySQL Server. This is useful for connecting to a remote web hosting machine.
HTML	-H, --html	Produces output in HTML.
MySQL Monitor Help	--help	Displays a full list of all the command-line switches that can be used with the MySQL Monitor.
Password	-p [password], -- password [= [Password]]	Password to use when connecting to server. If password is not given it will be asked for.
Port	-P, --port=...	Port number to be used for connection.
Start Logging	--tee=[filename]	Stores all commands and results that appear on screen into a separate file.
Stop Logging	--no-tee	Stops logging commands and results to the file.
Username	-u, --user=[UserName]	User for login if not current user.
Version	-v, --version	Output sversion information and exits.

The switches that we have used in the command line are highlighted in the above table for clarity. There are many more command-line switches available, for more information on these you should type mysql -help at the command prompt.

When you have typed the command line that you need to use to connect to your MySQL server, press Enter, and the MySQL Monitor will attempt to connect to your database. On a successful connection you will receive the MySQL command prompt from which we can enter SQL Query commands, and directly communicate with the server.

If you haven't already done so, you can create the dwHotel database using the command:

     CREATE DATABASE dwHotel

Then select this database with the command:

     USE dwHotel

As in the previous examples for phpMyAdmin we will proceed to create the clients table. To do this, we simply type out the SQL as it has previously been given, ensuring that we follow the SQL to the letter, as mistakes here will produce an error, and we will have to retype our work.

     mysql> CREATE TABLE clients (         ->   ID tinyint(11) NOT NULL auto_increment,         ->   title varchar(10) default NULL,         ->   firstName varchar(30) NOT NULL default '',         ->   lastName varchar(30) NOT NULL default '',         ->   address1 varchar(100) NOT NULL default '',         ->   address2 varchar(100) default NULL,         ->   town varchar(100) NOT NULL default '',         ->   province varchar(100) NOT NULL default '',         ->   country varchar(40) NOT NULL default '',         ->   postCode varchar(20) NOT NULL default '',         ->   tel varchar(20) NOT NULL default '',         ->   email varchar(100) NOT NULL default '',         ->   cardNo varchar(16) NOT NULL default '',         ->   expiryDate date NOT NULL default '0000-00-00',         ->   PRIMARY KEY (ID)         -> ) TYPE=MyISAM COMMENT='Customer Table';

Here we can see what the SQL looks like when we type it out in the MySQL Monitor. As you can see, each line of the SQL statement is prefixed with ->. The MySQL Monitor does this automatically whenever you enter a command without ending the line in either a semi-colon, or \g, and it signifies a multi-line SQL Statement. The moment that the MySQL Monitor encounters either a semi-colon, or a \g entry, it will assume that the command is complete and it will automatically execute it.

You should bear this in mind if you are wishing to delete tables, or columns in your database, as the MySQL Monitor will not check first if you are sure, it will just action the statement. So here I would urge a real sense of caution if you ever make any amendments or changes using this software.

Query OK, 0 rows affected (0.01 sec)

When you reach the semi-colon at the end of the SQL, press enter, and you should receive the above message. Now that we have created the table, we need to check that it is built according to our specifications. To check the structure of a table we use the following SQL Statement:

     mysql> SHOW COLUMNS FROM clients;

This will work for any table. All you need to do is to supplement clients for the name of the table that you wish to view the structure of. Using this command will return an output similar to the one below.

     +------------+--------------+------+-----+------------+----------------+     | Field      | Type         | Null | Key | Default    | Extra          |     +------------+--------------+------+-----+------------+----------------+     | ID         | tinyint(11)  |      | PRI | NULL       | auto_increment |     | title      | varchar(10)  |      |     |            |                |     | firstName  | varchar(30)  |      |     |            |                |     | lastName   | varchar(30)  |      |     |            |                |     | address1   | varchar(100) |      |     |            |                |     | address2   | varchar(100) | YES  |     | NULL       |                |     | town       | varchar(100) |      |     |            |                |     | province   | varchar(100) |      |     |            |                |     | country    | varchar(40)  |      |     |            |                |     | postCode   | varchar(20)  |      |     |            |                |     | tel        | varchar(20)  |      |     |            |                |     | email      | varchar(100) |      |     |            |                |     | cardNo     | varchar(16)  |      |     |            |                |     | expiryDate | date         |      |     | 0000-00-00 |                |     +------------+--------------+------+-----+------------+----------------+     13 rows in set (0.00 sec)

As you can see, like phpMyAdmin, it is very easy to compare this layout with the SQL Statement itself, to ensure that the table has been created in exactly the way that we wanted. If during this process something has gone wrong, don't worry. We will look at how to change your database tables in Chapter 7, where we also discuss Advanced SQL usage.

Once you have finished creating your tables, you should close down this tool. To do this, simply type EXIT, and you are returned to your command prompt.

Now that we have looked at how to create our database tables, we need to put them to use. Dreamweaver MX includes an enhanced template system, which allows us to standardize our page layout, so that all we need to be concerned about is creating our template, and then entering all of our content.