Whatever you can do through the use of simple data files, you can also achieve through the use of a database system. This adds a layer of software between your script and the data. The advantage is that you get a high-level interface to extract information from your data in a variety of formats. You also gain the advantage of using carefully designed and optimized file access routines with allowances for multiuse access. The disadvantage is the additional complexity that hides many of the underlying details that should be understood by a computer scientist and the need to learn an additional set of commands for database access.
A database can be thought of as an object that represents a collection of data together with methods to access that data. One of the most common implementation of a database uses a relational model to implement the data storage and retrieval tasks. A generalized language called SQL (Structured Query Language) has also evolved to facilitate platform independent access to information.
Relational databases store raw data in tables made up of rows (entities) and columns (fields). Many tables have a column that contains a primary key that uniquely identify each row. Entities in different tables may be related to entities in other tables through a matching process that compares field entries. Table relationships can be one-to-one, many-to-one, one-to-many, and many-to-many. The decision to split data across a collection of tables (rather than use a single table) is a design decision that is further discussed in a course about database theory.
Here is a simple example to illustrate the concept. Two tables representing students and classes are shown. In the course table, the ClassID is a primary key. The Class Members table has no primary key. There are three students represented. One student is enrolled in two courses. These tables are related by the ClassID field. Using an appropriate command, it should be easy to locate all students enrolled in a course, or all courses enrolling a single student. This organization illustrates a many-to-one relationship.
| Class Members | Course | ||||||||||||||||
|
|
SQL provides commands to extract information from a collection of tables in a variety of ways. SQL is both an acronym and an initialism: you can pronounce it as 'SeQueL' (adding vowels of someone's arbitrary choice), or spell it out as esS-Queue-elL.
The class member table would be created using the following SQL command:
CREATE TABLE Class_Members(
Student_Number INTEGER,
ClassID CHAR[7])
Data can be added/updated/deleted using INSERT/UPDATE/DELETE:
INSERT INTO Class_Members (Student_Number, ClassID)
VALUES (23, '3460307')
UPDATE Course
SET CourseName = 'Data Structures and Algorithms I'
WHERE ClassID = '3460210'
DELETE from Class_Members WHERE StudentNumber = 23
Information can be extracted from a single table using SELECT. A list of matching values is returned:
SELECT ClassID FROM Class_Members WHERE Student_Number = 23
Information from multiple tables can be collected using SELECT to create a 'join'. A join is a virtual table from which selections may be made.
SELECT Student_Number, Class_Name
FROM Class_Members, Course
WHERE Student_Number.ClassID = Class.ClassID
Database access is a simple example of a client/server architecture. A client collects the requests of a user and formulates those requests in a format the database system can understand. The database acts as a server, responding to the requests of the client and supplying the client with the results of a request. This configuration is sometimes called a two-tier system. Database access via the web is usually a three-tier system. The browser communicates with the WWW server, which in turn communicates with the database system. Each of these tiers might reside on separate machines, allowing the computational load to be distributed.
Perl provides a database interface module (DBI.pm) that provides a common interface between Perl applications and any database. This in turn interfaces with a module specific to a particular database vendor through a database driver module (DBD). Porting to a different database is as simple (theoretically) as changing the DBD specification in the connect command.
PHP supports database access through an API. Each specific database system has a distinct API (collection of commands), so PHP applications are not as easily ported to a different database system. We will look at PHP access to a MySQL database.
MySQL is an open source (therefore free) database system that uses SQL as its native query language. MySQL can be accessed through a command line interface or via PHP MySQL API functions. The following PHP code specifies a connection to a MySQL database:
mysql_connect(server,username,password);
mysql_select_db("database_name");
$result = mysql_query("query_string");
$row = mysql_fetch_assoc($result);
Once a connection is made and a database selected, subsequent operations apply to that database. The connection is dropped when the script ends. Each of these commands returns false in case the command fails and may be combined with a die clause or as a condition to affect the flow of control. The fetch command is often called in a loop to extract all of the rows of the result. Its return value is a hash where database values are stored under the field name.