COM372: Notes, Chapter 9

Creating Your Web Database
pages 219-241

Setting up: MySQL is free. You can set it up on your local machine, for purposes of development. Or, you can use the testing server that is set up for the class. Instructions for setting it up are included in appendix A.

This chapter covers the basics of creating a database, setting users and priveleges for security, and creating tables, indices, and columns. The Book-O-Rama database is given as an example (schema on page 219).

Setting up MySQL , a database server, on a local machine is covered in the appendix; we also have it on the course server. Here are some relevant links:

• MySQL—www.mysql.com
  • Downloads
  • manual (huge)
• phpMyAdmin—www.phpmyadmin.net/home_page/index.php
  • Documentation
• phpMyAdmin on course server—www.com.uri.edu/phpmyadmin/index.php

The text describes using the command-line monitor. Here, I'll do the same things using phpMyAdmin.

---

Logging in to phpMyAdmin: Connect to the course server phpMyAdmin home page (link, above). You'll be prompted for an ID and password. Use the name of your course folder / ID for both (your password is NOT your ftp password). This brings up the home page; the left side is shown here:

The left frame allows you to connect to any databases for which you are authorized. The right allows access to the database. You will be operating in a restricted environment, with access to your individual database(s). This is determined, under a master administrative root account, through MySQL priveleges. Priveleges are rights that can be set globally (adminstrators) or at a database, table, or column level. You can set these using the "privelege" link (5th from the bottom).

Here is the privelege table. For most users, I'd check all boxes on the left, giving permission to use and amend the database (SQL SELECT, UPDATE, DELETE, and INSERT commands). That is, setting priveleges establishes which SQL commands a user is allowed to execute against a database. Additional privileges can be granted under a global permissions list; these are usually reserved for administration (see table 9.2, page 224). The SQL command GRANT is used to add priveleges (this is generated by phpMyAdmin when you check boxes and submit); to take away priveleges, the opposite of GRANT is REVOKE. See examples, page 227-228.

Once the database is created, you can set up new tables. Here, I'm setting up the first of the Book-O-Rama tables, as in listing 9.1 (pages 230-231).

Listing 9.1 gives the SQL to be entered to create the five tables. We've just created the first table, and here's the rest of the listing from 9.1:
create table customers
( customerid int unsigned not null auto_increment primary key,
name char (50) not null,
address char(100) not null,
city char (30) not null
);

Here's the phpMyAdmin table to set up these 4 fields in the customers table:

When you've filled out the table and hit the "go" button, phpMyAdmin returns the SQL that set up the table, which is identical to the listing from 9.1. Surprise!

Notice that all fields are "not null," set in the "null" column. This means that the field must have a value. Also, unsigned means that the number must be zero or positive.

Setting a unique primary key: Customerid is to be the primary key for this record; there can be only one primary key. It is designated by checking the radio button (first column after the "extra" column). Also, this is to be an automatically generated unique number (auto_increment), which is set as an option in the "extra" column. This works with any integer column; if you leave the field blank, MySQL will generate a unique identifier, one more than the maximum value in the column already.

Showing databases and tables: PhpMyAdmin automates the command line instructions show and describe by automatically presenting (left frame set) a dropdown listing any databases for which you are authorized, and when you have selected a database, by displaying a list of tables underneath it.

Data Types: Data types include several variations of numeric, date and time, and string types. These are listed in Tables 9.5 (Integers, page 237), 9.6 (Floating-Point, page 237-8), 9.7 (Date and Time, page 238-239), 9.8 (Timestamp, page 239), 9.9 (Regular strings, page 240), 9.10 (Text and blob, page 2419), and 9.11 (Enum and Set, page 241). The individual types are described in the tables and are available as dropdown lists from phpMyAdmin, and won't be repeated here.