Relational Databases

One of the most powerful tools computers give us is the ability to store and search data. Early applications stored data for programs in files and used indexes to search the files for particular bits of data. These programs didn't fare very well on networks because there are problems with more than one computer trying to update data in a file simultaneously. This fact and the lack of standard database structure and command syntax encouraged the creation of the networked relational database.

When you write a program and want to be able to manipulate and search data you often have to construct the data files and the code to perform the manipulation and searches. This is a time consuming task and if you are developing many different applications you have to reinvent the data handling routines for every job. By removing the database functions such as file management, indexing, searches and simultaneous data access, you speed up development of applications and allow for these specialized database programs to become highly optimized and scalable.

What is a database

A database is simply organized data. A database contains tables which are basically descriptions of types of data. Tables in turn contain records which is the actual data.

By using a common identifier between tables it is possible to "relate" one table to another. For instance, imagine you had a table that contained data about items sold in a store today. You also have tables that give you the details about the items and the vendors that sell them to you.

If you want to get a list of how much of each vendors items your sold today you will have to relate the sales table, the items table and the vendors table by a common field they all share. This aspect of relating data is what gives a relational database its power.

How it works

A relational database can be seen as the data handling part of another application. The application instructs the database to perform searches, as well as add, delete and modify data via the Structured Query Language or SQL. The SQL standard is supported by all major database vendors, but the implementation of the full standard in all cases is not a guarentee. The common workhorse functions are the same in most cases.

An easy way to understand how this works is to imagine you need to instruct someone to list the vendors your company uses. You have to write the instructions to perform this action. Before relational databases you would have to write the instructions to cover walking down to accounting, looking for the right file cabinet, opening it, finding the correct files, opening each of them and copying all the names down. If someone else was using a file you needed, you simply had to wait until they finished. Since relational databases came into existance those steps have been reduced to a single phone call and the uttered phrase, "SELECT * FROM VENDORS;"

SQL is a very human readable language. It does have syntax rules, but it is not as hard as learning a programming language. The most basic types of queries are SELECT, INSERT and UPDATE. Select searches for data, INSERT adds data and UPDATE changes existing data. DELETE is also fairly common, but I end up using it to clean up bad records by hand rather than in a program.

Security

Relational databases also have excellent security. In most database programs there is a special database that contains access permissions for users and databases. This allows a database administrator the ability to tune permissions to needs. The basic set of permissions in MySQL include the following:

• Select Priviledge - Ability to search data in tables
• Insert Priviledge - Ability to add data in tables
• Update Priviledge - Ability to modify data in tables
• Delete Priviledge - Ability to delete data in tables
• Index Priviledge - Ability to index tables
• Alter Priviledge - Ability to alter tables
• Create Priviledge - Ability to create databases
• Drop Priviledge - Ability to delete databases
• Grant Priviledge - Ability to delete databases
• Reload Priviledge - Ability to reload database privileges
• Shutdown Priviledge - Ability to shutdown the database program
• Process Priviledge - Ability to change the individual threads running in the database program
• File Priviledge - Ability to import / export data from / to files

The administrator has a special account that has all the privileges to start with and can be used to create custom accounts. If you intend to run a database yourself you will have to create databases and assign permissions yourself. While not especially complicated, it is tricky and will be detailed step by step for MySQL in Chapter 3.

MySQL Tools

MySQL is a freely available yet full featured relational database. It has a number of tools to manage the databases it operates, but there are only three I use regularly and only one of those is necessary if you aren't managing the database yourself. The command is "mysql" and it is a shell that allows an operator to enter SQL commands directly to the database. This command requires a few command line arguments to get it connected properly. A typical command line I use is as follows:

mysql -uuser -ppassword -hhost.domain.com database

• -u specifies the database user account to use
• -p specifies the database user password to use
• -h specifies the database server to connect to
• database specifies the database to act on

Once you are connected you will see this:

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.21.33b

Reading history-file /home/aawtrey/.mysql_history
Type 'help' for help.

mysql>

That mysql> prompt will allow you to enter any valid SQL statement and it will be executed by the database. I use this to test queries and manage the tables of data in the database.

SQL statments can be entered all on one line or broken into smaller pieces since the "mysql" program ignores extra white space which includes tabs, spaces, or carriage returns. All SQL statements must contain the correct syntax and be terminated with a ";" (semicolon).

Creating Tables

I will now take you through setting up a simple table. We want to store messages our site visitors leave. We want to track who they are, their email address, the time they visited and their message. Additionally, we need to define a primary key to uniquely identify the record. Since we may get more than one "Tom" we can't use any of the data fields for the key. Here is the SQL statement to create a table to store the data in.

CREATE TABLE guests (
  guest_id int(4)
  	unsigned
	zerofill
	DEFAULT '0000'
	NOT NULL
	auto_increment,
  guest_name varchar(50),
  guest_email varchar(50),
  guest_time timestamp(14),
  guest_message text,
  PRIMARY KEY (guest_id)
);

We have defined 5 types of data to track for our guestbook. The first field is named "guest_id". The "int(4)" means it is a 4 digit number. The "unsigned" means that it can only be a positive number. The "zerofill" is something I add to force the number to take up all the digits by displaying leading zeros. Using that parameter will make the numbers "0001", "0002", etc. Issuing the DEFAULT statement makes the counting start at "0000".

"NOT NULL" requires a little explanation. When a field has never had any data posted into it, it is considered NULL. This is not the same as containing "0" since "0" is a character. A field that is created as "NOT NULL" must have data in it. Lastly, "auto_increment" means that the number in subsequent records will be generated by adding "1" to the previous record.

The next 2 fields "guest_name" and "guest_email" are defined as "varchar(50)". This type of field is a variable length character string. This means the name can be anywhere from 0 to 50 characters long.

The "guest_time" data type is "timestamp(14)". The timestamp datatype is automatically updated by the database when the record is updated. The number of characters defines the format of the data. 14 characters includes a 4 digit year and a 2 digit month, day, hour, minute and second.

The "guest_message" type is "text". This is a non-indexed data type that allows for 65,535 characters to be stored in it. By non-indexed it means that the data is not readily searchable by the database.

The last line defines the "guest_id" as the primary key. Every table should have a "PRIMARY KEY" defined. This is the unique identifier for each record in the table.

The command to look at all the tables in a database is:

SHOW TABLES;

You can look at the properties of the fields in a table by issuing this statement:

DESCRIBE TABLES;

There is also ways to alter table and field properties using the "ALTER TABLE" function, but I leave that to you for your homework. Feel free to pursue the online documentation at the MySQL website.

Deleting a table is as simple as issuing the following command.

DROP TABLE guests;

Adding Data

Now that we have the table created we will have to add data to it. I suggest putting a record in directly to test the sql statement you will use in the program. Here is the SQL query to add a record to the database:

INSERT INTO guests (
guest_id, guest_name, guest_email, guest_time, guest_message
) values(
0000,'Tony','tony@awtrey.com',NULL,'This is how it works!'
);

The statement starts by telling the database we want to insert data into the "guests" table. Next we list the fields we are going to update and finally, the data we want inserted in each field. You have to match the order of the listed fields and the data.

The "guest_id" field is autoincrementing, so it doesn't really matter what you insert into it. I put 0000 just because I wanted to. Strings like the ones for "guest_name", "guest_email" and "guest_message" must be quoted. To put a quote character in the field you must put a "" (backslash) in front of it. The "guest_time" field is set automatically, but I insert the special NULL character, which is the manual trigger, because that's the kind of guy I am.

If that is added to the database correctly you will see:

Query OK, 1 row affected (0.00 sec)

Searching The Table

Getting the data back from the database is even easier. This statement will return all the data in the "guests" table:

SELECT * FROM guests;

In the "mysql" program the returned data will appear in a text form that resembles this:

Sometimes you only want certain records. The statement to limit a search to particular criteria is like this:

SELECT * FROM guests WHERE guest_name='Tony';

This will only return the records where the "guest_name" is "Tony". You can also limit the fields that are returned in this way:

SELECT guest_name,guest_email FROM guests;

This should return something that looks like this:

Updating Existing Data

Changing data is a little trickier. To change the email address of the previously entered data use this statement:

UPDATE guests
SET guest_email='info@awtrey.com'
WHERE guest_email='tony@awtrey.com';

The successful execution of the query will result in:

Query OK, 1 row affected (0.00 sec)

The resulting data when selected should look like this:

Deleting Data

To delete all the data from a table only needs the following statement:

DELETE FROM guests;

To delete specific records the query would look like this:

DELETE FROM guests WHERE guest_name='Tony';

The successful execution of the query will result in:

Query OK, 1 row affected (0.00 sec)

Saving A Database

One other valuable feature most databases possess is the ability to output SQL statements that define the structure and content of the tables in a database. MySQL is no exception and includes the "mysqldump" utility for this purpose. The command is issued from the command line, not within the "mysql" program.

mysqldump -uuser -ppassword -hhost.domain.com database > filename.sql

• -u specifies the database user account to use
• -p specifies the database user password to use
• -h specifies the database server to connect to
• database specifies the database to act on
• > filename.sql directs the output to a file. Normally output is directed to the screen.

The output of a mysqldump can be directed back into a blank database to recreate a complete set of tables and data. This is especially handy if you develop a structure on a staging system and need to quickly and easily move the whole contents of a database to a new system. Here is the command to do that using the "mysql" utility.

mysql -uuser -ppassword -hhost.domain.com database < filename.sql

• -u specifies the database user account to use
• -p specifies the database user password to use
• -h specifies the database server to connect to
• database specifies the database to act on
• < filename.sql directs the mysql program to read instructions from a file instead of the command line.

Now lets find out how to do this kind of stuff with PHP.