RSH Web Services Virtual Private Server's (VPS)

Using Databases
pg. 16

• Database Basics
• MySQL: Lightweight Database Engine for Web Servers
• Installing MySQL
• MySQL Tutorial
• Creating MySQL Databases
• Showing Database Relationships
• Creating MySQL Tables
• Adding Records To Database Tables
• Extracting Information from Database Tables
• Modifying Table Records
• Deleting Table Records
• Deleting ("Dropping") Databases
• Web Interfaces for MySQL
• PHP Interface for MySQL

One of the most powerful and useful add-ons is the ability to create databases whose content can be accessed and displayed through dynamically created Web pages.
This chapter discusses basic database concepts and includes a tutorial for creating a sample database using MySQL. For further documentation about MySQL, please refer to the MySQL Web site:
http://www.mysql.com

Database Basics
A database is a collection of information that is stored for the purpose of searching and retrieving information quickly and easily. A database consists of one or more tables, which contain one or more fields. Data stored in tables are called records. Records can be added, deleted, or otherwise modified within a table.

MySQL: Lightweight Database Engine for Web Servers
MySQL is a simple yet powerful database engine that uses a subset of ANSI SQL (Structured Query Language). For the purpose of using MySQL on a Web site, the most important elements of the package are the database engine, the monitor program, and a Web interface such as a PHP or C program. The Perl/MySQL interface is not currently supported.
The MySQL database engine runs on your Virtual Private Server and is responsible for managing databases and tables. It searches for incoming SQL commands, and then returns the requested information.
The database administration program allows you to view database statistics as well as create, copy, move, and delete databases. Commands are given at the server's command prompt.
The MySQL monitor program allows you to give simple commands, called queries, to the database engine itself. These queries are used to create, delete, or modify the information in your tables. Commands are given at the program's command prompt (MySQL >), and must include a semicolon or \g tag at the end of each command.
Here is a list of some basic SQL commands that the MySQL monitor understands:
create table: creates a new database table
insert into: inserts a new record into a table
drop table: deletes a table
delete from: deletes records from a table
update: updates records in a table
select: extracts data from a table or multiple tables
At the end of each command that you send using the MySQL monitor, you must type either a semicolon or \g to send that command to the database engine.
Here are some other commands that you can use with the monitor program:

You must have MySQL installed on your Virtual Private Server before you can use the database features and commands. You can install MySQL yourself from your command prompt, as described in the following section.

Installing MySQL
To install MySQL, Telnet or SSH to your Virtual Private Server and type the following at the command prompt:
vinstall mysql Enter
After a brief wait, a message will confirm that the installation is complete.
The vinstall mysql command creates a sample database called pets_example, which will be used to demonstrate how to create and update a database, and also creates a subdirectory of your home directory called mysql_examples.
To view the files in the mysql_examples directory, type the following at the command prompt:
cd ~/mysql_examples Enter
The mysql_examples directory contains the following files:
mysql_example.dat – A raw data dump of the pets_example database.
list-pets.php3 – A sample PHP file that demonstrates how to use MySQL from PHP to retrieve information from a database, using the sample database pets_example.
mysql_example.c – C source code example for retrieving information from a database, using the sample database pets_example.
list-pets – A compiled C program using the source code from mysql_example.c.
The above files will be explained more fully in the following sections.

MySQL Tutorial
To help illustrate the basics of database creation and manipulation, we have included a sample database as part of the MySQL package.
NOTE: If this sample database is not on your Virtual Private Server, or if you need to re-install it, type the following commands from within your home directory (the output for each command is also displayed):
tar -xvf /usr/home/contrib/mysql_examples.tar Enter
mysql_examples
mysql_examples/list-pets
mysql_examples/list-pets.php3
mysql_examples/mysql_example.c
mysql_examples/mysql_example.dat
tar: tar vol 1, 5 files, 256000 bytes read.
mysqladmin create pets_example Enter
Database "pets_example" created.
mysql pets_example < mysql_examples/mysql_example.dat Enter
The pets_example database depicts a fictional "lost pet agency" that helps reunite lost pets with their owners. Missing pets are reported to the agency, which keeps a database of information about both the pets and their owners.
An additional table, containing information about where the pet was last seen, will need to be created.
In this tutorial, you will learn how to view the relationships between a database, its tables, and fields. You will also learn how to add and extract table records.

Creating MySQL Databases
The database administration program, mysqladmin, allows you to create, copy, move, and drop (delete) databases.
Example: Create a database called ‘pets’ using the mysqladmin command:
mysqladmin create pets Enter
Once the database is created, the following output appears:
Database "pets" created.
From a technical standpoint, MySQL simply creates a subdirectory called pets within your Virtual Private Server’s ~/mysqldb directory. All tables related to the pets database, along with the data for the table, will be stored within this directory.

Showing Database Relationships
The mysqlshow command shows relationships between databases, tables, and fields.
The mysqlshow command can only be used from your Virtual Private Server's main Telnet/SSH prompt. It cannot be used from within the MySQL monitor.
To display the databases that currently exist on your Virtual Private Server, type the following at your Virtual Private Server's command prompt:
mysqlshow Enter
A list of databases should appear, similar to the following:

+--------------+
|Databases |
+--------------+
| pets |
| pets_example |
+--------------+

The pets database may or may not appear, depending on whether you created it following the instructions in the previous section.
The pets_example database was installed as part of the MySQL package, and will be used throughout the remainder of this chapter to illustrate database manipulation.
To view the tables that exist within a database, type mysqlshow database, where database is the name of the particular database whose tables you wish to view.
For example, type the following command to view the tables that exist within the pets_example database:
mysqlshow pets_example Enter
The following output appears:

+-------------+
| Tables|
+-------------+
| description |
| owner |
+--------------+

From the output displayed, you can see that the pets_example database currently contains two tables, "description" and "owner."
You can view the field structure for a particular table by typing mysqlshow database table, where database is, again, the name of the database and table is a table within that database whose records you wish to view.
For example, type the following command to view the field structure of the "description" table of the pets_example database:
mysqlshow pets_example description Enter
The following output appears, showing the list of fields within the "description" table, along with information about each field:

+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| PetID | int(11)|| PRI | 0 | |
| OwnerID | int(11)|| | 0 | |
| Species | char(30) || | | |
| Name| char(20) || | | |
| Age | int(11)| YES| | | |
| Description | char(30) || | | |
+-------------+----------+------+-----+---------+-------+

MySQL supports many different column types, which can be grouped as numeric, date and time, and string (character) types. The "description" table shown above contains two different field types, integer (INT) and character (CHAR), each of which has a specified maximum length defined within parentheses.
For a complete list of column types supported by MySQL, Click Here.

Creating MySQL Tables
Tables are managed through the MySQL monitor program. The monitor program allows you to send simple commands to the MySQL database engine in order to create, modify, or delete tables.
The general format for calling the MySQL monitor program is "mysql database," where database is the name of the database that contains the table you're working with. In this case, the database name is "pets_example," so type the following:
mysql pets_example Enter
The following message and prompt appear:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL
connection id is 9 to server version: 3.22.14b-gamma-virtual
Type 'help' for help.

mysql>

You are now working in the MySQL monitor program, which you can tell by the mysql> prompt.
You can now use the create table clause to create your tables from within the monitor. To do so, type the following lines, pressing Enter at the end of each (Note: Field names are case sensitive. It is important that you match the capitalization properly
create table last_seen (PetID int(11) PRIMARY KEY, Street char(30), City char(30), Date date);
You have just created the table that you need for the next step of this tutorial.
To view the new "last_seen" table, you must exit the MySQL monitor and return to your Virtual Private Server's command prompt:
quit Enter
Now, type the following:
mysqlshow pets_example last_seen Enter
The following table appears:

+--------+----------+------+-----+---------+-------+
| Field| Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| PetID| int(11)|| PRI | 0 | |
| Street | char(30) | YES| | | |
| City | char(30) | YES| | | |
| Date | date | YES| | | |
+--------+----------+------+-----+---------+-------+

The "last_seen" table contains three different field types: integer (INT), character (CHAR), and date. These are the field types you specified for each field using the create table clause.
Integer fields can only store numbers without decimals. Character fields can store character strings of limited size. Date fields can contain dates only, following the format YYYYMMDD.
Before you go on to the next section, be sure to use the following command to log back into the MySQL monitor:
mysql pets_example Enter

Adding Records To Database Tables
Through the MySQL monitor program, you can insert records into a table using the insert into clause. The format for the insert into clause is as follows:
mysql> insert into table values (field1, field2, field3, field4);
In the “last_seen” table, the above fields correspond to PetID, Street, City, and Date, respectively.
Now, we need to enter some information into the “last_seen” table of the pets_example database. Type the following, pressing Enter at the end of each line:
insert into last_seen values (1, ‘Safe Haven Pet Spa’, ‘Bluesville’, 19990410);
insert into last_seen values (2, ‘Golden Pond’, ‘Bluesville’, 19990327);
insert into last_seen values (3, ‘515 Elm’, ‘Patooca’, 19990412);
insert into last_seen values (4, ‘99 Churchhill Street’, ‘Happyville’, 19990313);

Extracting Information from Database Tables
You can use the select clause to extract data from a table. You can further refine your selection using keywords such as where and order by, as the following examples indicate.
While still in the MySQL monitor program, type:
select * from last_seen; Enter
This command essentially means, “get me all of the records in the “last_seen” table and show me the results.”
In response, you should see the following output:

+-------+------------------------+------------+------------+
| PetID | Street | City | Date |
+-------+------------------------+------------+------------+
| 1 | Safe Haven Pet Spa | Bluesville | 1999-05-22 |
| 2 | Golden Pond| Bluesville | 1999-03-27 |
| 3 | 515 Elm| Patooca| 1999-04-12 |
| 4 | 99 Churchhill Street | Happyville | 1999-03-13 |
+-------+------------------------+------------+------------+

You can perform more sophisticated selections by using the where keyword. The where keyword allows you to retrieve only the records that match specific criteria.
Selecting Specific Records from a Table
Example: Type the following to retrieve the records for the pets that were last seen in Bluesville:
select * from last_seen where City = ‘Bluesville’; Enter
The following output appears:

+-------+------------------------+------------+------------+
| PetID | Street | City | Date |
+-------+------------------------+------------+------------+
| 1 | Safe Haven Pet Spa | Bluesville | 1999-04-10 |
| 2 | Golden Pond| Bluesville | 1999-03-27 |
+-------+------------------------+------------+------------+

Note that only those records with “Bluesville” in the City field were returned.
Selecting Specific Fields from a Record
You can return specific fields of matching records rather than an entire record, for example:
select Street from last_seen where City = ‘Bluesville’; Enter
The above command basically says, “show me just the Street field of all records in the last_seen table for the City of Bluesville.” The output below displays the results of this command.

+------------------------+
| Street |
+------------------------+
| Safe Haven Pet Spa |
| Golden Pond|
+------------------------+

Retrieving Records in Numerical or Alphabetical Order
You can specify to display results in a specific order using the order by clause:
select * from last_seen order by Date; Enter
You should see the following output:

+-------+------------------------+------------+------------+
| PetID | Street | City | Date |
+-------+------------------------+------------+------------+
| 4 | 99 Churchhill Street | Happyville | 1999-03-13 |
| 2 | Golden Pond | Bluesville | 1999-03-27 |
| 1 | Safe Haven Pet Spa | Bluesville | 1999-04-10 |
| 3 | 515 Elm | Patooca | 1999-04-12 |
+-------+------------------------+------------+------------+

Notice that in the above output, the "Description" results are displayed in order of the date the pet was last seen.

Modifying Table Records
Through the MySQL monitor program, you can modify the information in a table record using the update clause.
Example: Referring to the pets_example database, assume that Larry Jones, who owns Goldie the Goldfish, has moved across town to 2612 Cottonwood Lane. You will need to update the information in the database using the following command from the mysql> prompt:
update owner set Street = ‘2612 Cottonwood Lane’ where OwnerID = 102; Enter
Now, use the select command to verify that the change has taken place:
select * from owner where OwnerID = 102; Enter
Larry’s Street address should now be updated as “2621 Cottonwood Lane.”

Deleting Table Records
Through the MySQL monitor program, you can delete the information in a table record using the delete from clause.
Example: Assume that Izzy the Iguana has been found. You can now delete Izzy’s “last seen” information by typing the following command at the mysql> prompt:
delete from last_seen where PetID = 4; Enter
Now, check to verify that the record was deleted:
select * from last_seen; Enter
You should see the “last seen” listing, minus PetID 4 (the information surrounding Izzy’s disappearance).
For the purpose of this tutorial, we are now finished with the MySQL monitor program. Now, exit the MySQL monitor before proceeding to the next section:
quit Enter

Deleting ("Dropping") Databases
To delete a database, you must use the database administration program from your Virtual Private Server’s regular command prompt.
mysqladmin drop pets Enter
After some cautionary information, the following prompt appears:
Do you really want to drop the "pets" database: [Y/N]
Type ‘y’ to confirm. The following message will then appear:
Database "pets" dropped
For more complete information, please refer to the documentation that accompanies the MySQL package or visit the MySQL Web site at http://www.mysql.com

Web Interfaces for MySQL
To display the contents of your databases on your Web site, you need an interface that allows your Web server to communicate with your database engine. The following sections demonstrate how to do so.

PHP Interface for MySQL
Our implementation of PHP works well with MySQL.
As an introduction, here are some of the most common commands that can be embedded inside <? ?> tags within a PHP-enabled Web page to interface it with a MySQL database.

mysql("database", "mysql query"); The mysql command is used to perform a query on a MySQL database. The results are commonly loaded into a variable so that they can be accessed later for displaying in the Web page. To display results on a Web page, the database would be replaced by the name of the database to be accessed, and mysql query would be replaced by the query that you will be making to that database. It takes the same format as if you were typing it into the MySQL monitor program from a Telnet or SSH session.
For example, to perform a query on the "pets_example" database (which was created when you installed MySQL) to select all the records from the "owner" table and load them into a variable called $result, you would place the following command in your PHP-enabled Web page:
<? $result = mysql("pets_example", "select * from owner"); ?>
As the following commands illustrate, the $result variable can be used within other queries.

mysql_numrows($result); This command returns the number of records that have been retrieved from a query and placed into a variable called $result. The result of the mysql_numrows command is commonly placed in a variable to be used in some type of loop. $result should be replaced with the name of the variable in which you stored the query results using the mysql command.
For example, to get the number of records returned from a query whose results were placed in a variable called "$result," and place this value into a variable called "$num_rows," place the following command in your Web page:
<? $num_rows = mysql_numrows($result); ?>

mysql_numfields($result); The mysql_numfields command can be used to obtain the number of fields in the results of an mysql query. This value is commonly loaded into a variable to be used in some type of loop. $result should be replaced with the variable name that you used to store the results of the query in.
For example, to get the number of fields returned from a query whose results were placed in a variable called "$result," and place this value into a variable called "$num_fields," place the following command in your Web page:
<? $num_fields = mysql_numfields($result); ?>

echo $variable; This command is used to print out the value of a variable to a Web page. $variable should be replaced with the name of the variable that you wish to print.
For example, to print the contents of the variable "$num_rows" to a Web page, place the following command in the page where you want the value to appear:
<? echo $num_rows; ?>

echo mysql_result($result, $row, $field_name); The echo mysql_result command is used to print the value of a specific record and field that has been returned from a query and stored in a variable called $result.
$result should be replaced with the variable that was used to store the query results.
$row should be a row number, the first being 0.
$field_name should be replaced with the name of the field to return.
For example, to return the value of a field called "LastName" from the first record of the results of a query that has been stored in "$result," place this command in your Web page:
<? echo mysql_result($result,0,"Description"); ?>