Accessing Database Information Using Perl

Creating and Working with a MySQL Database

The key to using MySQL is an understanding of the SQL syntax itself. If you've used Oracle or another SQL-based system, you'll be right at home interacting with MySQL. For beginners, this introduction should be enough to get started, but we recommend a more complete text such as MySQL, 2nd Edition (ISBN: 0-7357-1212-3).

To start MySQL, invoke the client (mysql) using mysql -u<username> -p<password>. To start, only the root account is available. If you didn't set the password for root, no password is required.

TIP

The MySQL tools (including the client you're using now) can be used to access MySQL servers across a network using the -h <hostname/ip> switch. You could, for example, connect to a server named my.serverwithmysql.com running MySQL with the username kitten and password fuzzy4 using mysql -ukitten -pfuzzy4 -hmy.serverwithmysql.com.

Creating and Deleting Databases

The first step when working with MySQL is to create the database itself. If you've worked with FileMaker Pro or AppleWorks, this is a very different concept. In MySQL, a database is a container that holds a collection of tables. These tables, in turn, hold actual information. The FileMaker database model has a single table in a single database. To create relationships between different collections of data requires multiple databases. In MySQL, a single database can contain multiple tables each with unique data.

To create a database, make sure that you've started MySQL and are at a command prompt:

 brezup:jray jray $ /usr/local/mysql/bin/mysql -uroot -pjohn Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.7-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> 

Next, use create database <database name> to set up an empty database. Finally, type use <database name> to start working with the new database.

NOTE

The MySQL client requires that all commands end with a semicolon (;). Input can span multiple lines, as long as a semicolon appears at the end.

For example, let's start with an employee database:

 mysql> create database employee; Query OK, 1 row affected (0.07 sec) mysql> use employee; Database changed mysql> 

If you want to delete the database that you've defined, you can use the drop command, which works just like the create command:

 drop database <database name> 

CAUTION

A MySQL database can contain multiple tables, each with its own data. Deleting a database removes all information that has been stored in any of the tables.

Creating Tables

After a database has been created, you need to set up the internal tables that actually hold the data you want to store.

When making a table, use another create command to tell the system what type of data you want to store.

 create table <tablename> (<columns...>) 

For example, let's create some tables for our employee database:

 create table tblemployee (   employeeID int not null,   firstname  varchar(50),   lastname  varchar(50),   titleID   int,   salary   float,   primary key (employeeID) ); create table tbljobclassification (   titleID   int not null,   title    text,   minsalary  float,   maxsalary  float,   primary key (titleID) ); 

The first table, tblemployee, holds information about each person in the database, such as his name and salary. The second table, tbljobclassification, contains job classification data a general position description, and the minimum and maximum salary ranges for that position.

TIP

The MySQL commands show databases, show tables, and describe <table name> can be used to display the available MySQL database, show the tables within the current database, and provide a detailed description of a named table.

Fields are defined within a table creation statement by using the syntax <fieldname> <datatype> <options>. Two common options are employed to force certain conditions on a table field:

• not null Forces the field to contain a value. If a user attempts to insert data into the database and a not null field is left blank, an error occurs.

• auto_increment When used with an integer field, the value for the field will be determined automatically by MySQL and be incremented with each subsequent record.

The final line of a table creation command should define a primary key (or keys) for the table: primary key (<fieldname 1,fieldname 2,...>). Defining keys is a necessary part of creating a normalized database structure. For more information on normalization, see http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization. We highly recommend reading through this tutorial, at the very least, before designing large-scale database models.

To remove a table that has been defined, type drop table <table name>.

MySQL Data Types

When defining a database table, numerous data types are used to build the collection of information that can be stored. Table 19.1 contains a description of the common available data types. This is a summarized version of the documentation supplied at http://dev.mysql.com/.

Table 19.1. Database Tables Are Built with MySQL Data Types

Data Type	Description
TINYINT [UNSIGNED]	A very small integer. The signed range is 128 to 127. The unsigned range is 0 to 255.
SMALLINT [UNSIGNED]	A small integer. The signed range is 32768 to 32767. The unsigned range is 0 to 65535.
MEDIUMINT [UNSIGNED]	A medium-size integer. The signed range is 8388608 to 8388607. The unsigned range is 0 to 16777215.
INT [UNSIGNED]	A normal-size integer. The signed range is 2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
INTEGER [UNSIGNED]	The same as INT.
BIGINT [UNSIGNED]	A large integer. The signed range is 9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.
FLOAT	A small (single precision) floating-point number. Cannot be unsigned. Allowable values are 3.402823466E+38 to 1.175494351E 38, 0 and 1.175494351E 38 to 3.402823466E+38.
DOUBLE	A normal-size (double-precision) floating-point number. Cannot be unsigned. Allowable values are 1.7976931348623157E+308 to 2.2250738585072014E 308, 0 and 2.2250738585072014E 308 to 1.7976931348623157E+308.
DECIMAL	An unpacked floating-point number. Cannot be unsigned. Behaves like a CHAR column: unpacked means that the number is stored as a string, using one character for each digit of the value.
DATETIME	A date and time combination. The supported range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59. MySQL displays DATETIME values in YYYY-MM-DD HH:MM:SS format but enables you to assign values to DATETIME columns using either strings or numbers.
TIMESTAMP	A timestamp. The range is 1970-01-01 00:00:00 to some time in the year 2037.
YEAR	A year in two- or four-digit format (the default is four-digit). The allowable values are 1901 to 2155, and 0000 in the four-digit format and 1970 2069 if you use the two-digit format (70 69).
CHAR(<M>) [BINARY]	A fixed-length string that is always right-padded with spaces to the specified length when stored. The range of M is 1 to 255 characters. Trailing spaces are removed when the value is retrieved. CHAR values are sorted and compared in case- insensitive fashion according to the default character set unless the BINARY keyword is given.
VARCHAR(<M>) [BINARY]	A variable-length string. Note: Trailing spaces are removed when the value is stored. The range of M is 1 to 255 characters. VARCHAR values are sorted and compared in case- insensitive fashion unless the BINARY keyword is given.
TINYBLOB / TINYTEXT	A BLOB or TEXT column with a maximum length of 255 (2^8 1) characters.
BLOB / TEXT	A column with a maximum length of 65535 (2^16 1) characters.
MEDIUMBLOB / MEDIUMTEXT	A BLOB or TEXT column with a maximum length of 16777215 (2^24 1) characters.
LONGBLOB / LONGTEXT	A BLOB or TEXT column with a maximum length of 4294967295 (2^32 1) characters.

Inserting Records into a Table

There are two ways to insert data into a table; both use the insert command with this structure:

 insert into <table name> [(<field1,field2,...>)]      values (<'value1','value2',...>) 

The difference between the methods comes from the optional field listing. If you want to insert into only a few fields of a table, and want to manually specify the order, you would include the field names, as in this example using the tblemployee table created earlier:

 insert into tblemployee (lastname,firstname,employeeID)     values ('Ray','John','1'); 

In this example, only the lastname, firstname, and employeeID fields are given in the record, and they don't occur in the same order in which they were defined in the original table.

The second way you can use insert is to provide all the field values at once, in the table definition order. This method doesn't require the field names to be listed:

 insert into tblemployee values ('1','John','Ray','1','35000.00'); 

It is important to note that you must obey the not null clause for a table definition at all times. In these examples, we had to include a value for the employeeID field; otherwise, the insert would have caused an error message to be generated.

To demonstrate the rest of the MySQL syntax, you'll need some data to work with. Go ahead and insert some information into the tables:

 insert into tbljobclassification values ('1','Programmer/Analyst','20000','80000'); insert into tbljobclassification values ('2','Web Developer','20000','50000'); insert into tbljobclassification values ('3','CEO/President','40000','5000000000'); insert into tblemployee values ('1','John','Ray','1','25300.65'); insert into tblemployee values ('2','Will','Ray','1','32100.25'); insert into tblemployee values ('3','Joan','Ray','1','55300.75'); insert into tblemployee values ('4','Robyn','Ness','2','35000.20'); insert into tblemployee values ('5','Anne','Groves','2','35000.65'); insert into tblemployee values ('6','Julie','Vujevich','2','30300.01'); insert into tblemployee values ('7','Jack','Derifaj','1','12000.00'); insert into tblemployee values ('8','Russ','Schelby','1','24372.12'); insert into tblemployee values ('9','Bill','Gates','3','50000.01'); insert into tblemployee values ('10','Steve','Jobs','3','380000000.00'); 

These statements add three different job classifications (Programmer/Analyst, Web Developer, and CEO/President) to the system, as well as 10 employees who fall under these classifications.

After your database has been populated, you can update or delete individual records using the commands update, delete, and replace into.

TIP

MySQL data can be backed up quickly using mysqldump -a -A -u<username> -p <password> > <backup filename>. The resulting file contains all the database, table, and record creation statements.

You can reload an empty MySQL database with this data using the mysql client and mysql -u<username> -p<password> < <backup filename>.

Modifying Records in a Table

Obviously, data in a database must be able to change; otherwise, it would be useful for only a short period of time or for limited applications. There are three types of modification that can be made: updating data already in a table, deleting a record outright, or completely replacing a record with new data.

Updating Existing Records

To change existing data, use the update command:

 update <table name> SET <field name 1>=<expression 1>,     <field name 2>=<expression 2>,<field name n>=<expression n>     [WHERE <search expression>] 

To use update, you must supply a table name as well as the names of the fields that need to be updated and the new values that they should take on. This leaves one important part of the equation missing: the search expression. If you don't tell update which fields to modify, it modifies all the tables. For example, issuing the command

 update tblemployee set salary='3000'; 

modifies every listed employee so that the salary field contains '3000'. If this is the desired action, great! If not, you're likely to be smacking your forehead when you discover what you've done.

To be a bit more selective about the update, you must define the WHERE search expression. This selects only the records that you want to update. For example, assume that we want to set the salary for employeeID 1 to equal 30000.99. The update statement would look like this:

 update tblemployee set salary='30000.99' where employeeID='1'; 

This update statement searches the table for a field where employeeID is equal to 1 and then updates the value in that record's salary field.

In addition to =, there are a number of common ways to select a record based on comparing a field to a value; that is, you can select records by creating an expression that evaluates to true or false. Table 19.2 shows some of the most common expression operators and syntax.

Table 19.2. Some Common Expression Operators and Syntax

Expression Syntax	Description
<fieldname> = <value>	Selects records based on a direct comparison to a value.
<fieldname> > <value>	Selects records where the value of a field is greater than a given value.
<fieldname> < <value>	Selects records where the value of a field is less than a given value.
<fieldname> >= <value>	Selects records where the value of a field is greater than or equal to a given value.
<fieldname> <= <value>	Selects records where the value of a field is less than or equal to a given value.
<fieldname> LIKE <value>	Selects records based on a simple SQL pattern-matching scheme. The character % matches any number of characters, whereas _ matches a single character.

These basic expressions can be combined to form more complex searches:

NOT <expression> Evaluates to true if the expression evaluates to false.

<expression> OR <expression> Evaluates to true if either of the expressions is true.

<expression> AND <expression> Evaluates to true if both of the expressions are true.

(<expression>) Use parentheses to combine expressions to force an order of evaluation.

Check the MySQL documentation for further information on available mathematical expressions, string comparisons, and other operators that can be used in expression syntax.

Deleting Records from a Table

To delete data from a MySQL system, you use a command similar to update but without supplying new field values:

 delete from <table name> [WHERE <search expression>] 

As with the update command, you can leave out the WHERE portion of the statement entirely. Unfortunately, the result would be the elimination of all data from the named table. Again, if this is your intention, by all means use it! For example, to delete employees who make more than $50,000 from the database, you would enter

 delete from tblemployee where salary>'50000'; 

Replacing Existing Records

There is one final way to conveniently replace existing records with new data. Using the INSERT command to try to save a record more than once when one already exists will result in an error. This happens because only one record with a given primary key can exist at a time. For example, assuming that we've filled the database with the following employee record:

 insert into tblemployee values ('1','John','Ray','1','25300.65'); 

attempting to insert another record using the same employee ID (1) causes an error:

 mysql> insert into tblemployee values ('1','Maddy','Green','1','41000.00'); ERROR 1062: Duplicate entry '1' for key 1 

To circumvent this, you could update the existing record; or delete the record and then rerun the insert; or use the replace into command.

replace replaces an existing record with new data or, if no record exists, simply inserts a record. Think of replace as a more powerful version of the basic insert command. It can be used to add new records to a table or replace existing records with new data. The syntax is identical to insert. For example, let's retry the insert into the tblemployee table this time using replace:

 mysql> replace into tblemployee values ('1','Maddy','Green','1','41000.00'); Query OK, 2 rows affected (0.00 sec) 

Success!

CAUTION

replace is a useful command unique to the MySQL instruction set. Although convenient, it is best to stick to the basic insert statement for most database operations to avoid inadvertently deleting data.

Querying a Database

After you've added data to the tables in a database, you would obviously want to display it. Querying a MySQL database is performed with the select statement. The power of relational databases comes from the capability to relate data in one table to that of another, and select can do just that:

 select <field name1>,<field name2>,.. from <table name 1>,<table name 2>,..     [where <search expression>] [ORDER BY <expression> ASC|DESC] 

If this isn't confusing for you, fantastic. If you're like the rest of us, however, some explanation is necessary.

The simplest query that select can perform is to pull all the data out of a single table (select * from <table name>). For example:

 mysql> select * from tbljobclassification; +---------+--------------------+-----------+-----------+ | titleID | title              | minsalary | maxsalary | +---------+--------------------+-----------+-----------+ |    1    | Programmer/Analyst |   20000   |   80000   | |    2    | Web Developer      |   20000   |   50000   | |    3    | CEO/President      |   40000   |   5e+09   | +---------+--------------------+-----------+-----------+ 3 rows in set (0.00 sec) 

Changing Result Order

To sort the information based on one of the fields, use order by with an expression (often one or more comma-separated field names), and asc for ascending order or desc for descending order:

 mysql> select * from tbljobclassification order by maxsalary desc; +---------+--------------------+-----------+-----------+ | titleID | title              | minsalary | maxsalary | +---------+--------------------+-----------+-----------+ |    3    | CEO/President      |   40000   |   5e+09   | |    1    | Programmer/Analyst |   20000   |   80000   | |    2    | Web Developer      |   20000   |   50000   | +---------+--------------------+-----------+-----------+ 

In this example, the tbljobclassification table is displayed, and the records are sorted by the maximum salary in descending order (most to least). Obviously, this is great for getting data out of a single table and manipulating its order, but it still doesn't draw on the relational power of MySQL.

Joining Multiple Tables

To fully exploit MySQL's capabilities, relationships must be created and used. A relationship links two or more tables based on a common attribute. For example, the tblemployee and tbljobclassification tables share a titleID field. Each employee record has a titleID field that can be used to relate to the tbljobclassification table. The process of relating tables together is called a join.

To see a join in action, let's take a look at how you would display a list of each employee's name, along with his or her job title. The select statement looks like this:

 select firstname,lastname,title from tblemployee,tbljobclassification     WHERE tblemployee.titleID=tbljobclassification.titleID; 

Translating this query into English is simple: Select the firstname, lastname, and title fields (select firstname,lastname,title) from the tblemployee and tblj obclassification database tables (from tblemployee,tbljobclassification). Relate the two tables by matching the titleID field in tblemployee to the titleID field in tbljobclassification (WHERE tblemployee.titleID=tbljobclassification.titleID).

The result is a neat display of the employees and their corresponding job titles:

 mysql> select firstname,lastname,title from tblemployee,tbljobclassification    WHERE tblemployee.titleID=tbljobclassification.titleID; +-----------+----------+------------------- + | firstname | lastname | title              | +-----------+----------+------------------- + | Maddy     | Green    | Programmer/Analyst | | Will      | Ray      | Programmer/Analyst | | Joan      | Ray      | Programmer/Analyst | | Jack      | Derifaj  | Programmer/Analyst | | Russ      | Schelby  | Programmer/Analyst | | Robyn     | Ness     | Web Developer      | | Anne      | Groves   | Web Developer      | | Julie     | Vujevich | Web Developer      | | Bill      | Gates    | CEO/President      | | Steve     | Jobs     | CEO/President      | +-----------+----------+--------------------+ 10 rows in set (0.03 sec) 

TIP

In this example, the two titleID fields are referenced by an extended version of their name <table name>.<field name>.

By using this syntax, you remove ambiguity in the SQL statements that would result from multiple tables containing the same names. You can use this when referring to any field, and even use it to refer to a database, table, and fieldname: <database name>.<table name>.<field name>. In large database projects, with dozens of tables, it helps document the relationships that are being used and is suggested as the standard query format.

A select statement can be combined with the WHERE search expressions that you've already seen in this chapter. For example, the last query can be modified to show only the employees who earn more than $50,000:

 select firstname,lastname,title,salary from tblemployee,tbljobclassification     WHERE tblemployee.titleID=tbljobclassification.titleID     AND tblemployee.salary>'50000'; 

For example:

 mysql> select firstname,lastname,title,salary from     tblemployee,tbljobclassification WHERE     tblemployee.titleID=tbljobclassification.titleID     AND tblemployee.salary>'50000'; +-----------+----------+--------------------+---------+ | firstname | lastname | title              | salary   | +-----------+----------+--------------------+---------+ | Joan      | Ray      | Programmer/Analyst | 55300.8  | | Bill      | Gates    | CEO/President      |  50000   | | Steve     | Jobs     | CEO/President      | 3.8e+08  | +-----------+----------+--------------------+--------- + 3 rows in set (0.00 sec) 

Of course, expressions can be combined with other expressions to create truly complex queries.

Performing Calculations Within Results

Using built-in MySQL functions, you can create virtual fields that contain data calculated as the query is performed. The syntax for an inline calculation is

 <expression> as '<variable name>' 

For example, the expression required to calculate the percentage of the maximum salary that each person makes could be represented by

 tblemployee.salary/tbljobclassification.maxsalary*100 as 'percent' 

Adding this code into a query of all the employee names and salaries results in

 mysql> select firstname,lastname,salary,tblemployee.     salary/tbljobclassification.maxsalary*100 as 'percent'     from tblemployee,tbljobclassification where     tblemployee.titleID=tbljobclassification.titleID; +-----------+----------+---------+-------------------+ | firstname | lastname | salary  | percent           | +-----------+----------+---------+-------------------+ | Maddy     | Green    |  41000  |       51.25       | | Will      | Ray      | 32100.2 |    40.1253125     | | Joan      | Ray      | 55300.8 |    69.1259375     | | Jack      | Derifaj  |  12000  |        15         | | Russ      | Schelby  | 24372.1 |  30.465148925781  | | Robyn     | Ness     | 35000.2 |   70.0003984375   | | Anne      | Groves   | 35000.6 |   70.001296875    | | Julie     | Vujevich |  30300  |  60.60001953125   | | Bill      | Gates    |  50000  | 0.001000000234375 | | Steve     | Jobs     | 3.8e+08 |        7.6        | +-----------+----------+---------+-------------------+ 10 rows in set (0.01 sec) 

Suddenly, the database has provided information that didn't even exist previously! Using these methods, you can use the MySQL database engine to perform much of the mathematical work of database applications, leaving the logic to other programming languages.

NOTE

Just because a database system can be used for calculations doesn't mean that it should be. Database systems are optimized for I/O, not necessarily for numerics. This is, of course, highly dependent on your project and implementation details.

Using Summarization and Grouping Features

Summarizing data is another useful part of any query. Using the summarization functions, you can easily find totals for numeric columns, or count the number of records of a particular type. Here are a few summarization functions that can be used in a query:

• max() The maximum of a given field. Used to match the highest value. For example, if you use max on the salary field of the employee table, it should return the highest salary in the group.

• min() The minimum of a given field. Performs the exact opposite of the max function.

• sum() The sum of the values in a given field. For example, you could use sum to find the total amount paid in salaries.

• count() Provides a count of the number of occurrences of a given field.

For example, you could find the minimum salary of all the employees by typing

 mysql> select min(salary) from tblemployee; +-------------+ | min(salary) | +-------------+ |    12000    | +-------------+ 1 row in set (0.01 sec) 

Or a count of the occurrences of the titleID field:

 mysql> select count(titleID) from tblemployee; +----------------+ | count(titleID) | +----------------+ |       10       | +----------------+ 1 row in set (0.00 sec) 

This second example obviously isn't very useful all it did was return the number of times the titleID field was used that is, 10 times, once in each record. Displaying the count of each of the types of titleIDs would make more sense. This can be accomplished with one last construct the group by clause.

group by organizes the data based on a field name and then makes it available to the summarization function. For example, the previous query could be modified like this:

 select titleID,count(titleID) from tblemployee group by (titleID); 

Instead of simply counting the field occurrences and reporting a result, the query groups the records by the titleID field and then counts the occurrences within each group. The output looks like this:

 mysql> select titleID,count(titleID) from tblemployee group by (titleID); +---------+----------------+ | titleID | count(titleID) | +---------+----------------+ |    1    |       5        | |    2    |       3        | |    3    |       2        | +---------+----------------+ 3 rows in set (0.00 sec) 

As with all queries, this could be turned into a join to provide information from more than one table. To show the actual job titles rather than just ID numbers, you could modify the query like this:

 mysql> select title,count(tblemployee.titleID) from     tblemployee,tbljobclassification where     tblemployee.titleID=tbljobclassification.titleID     group by (tblemployee.titleID); +--------------------+----------------------------+ | title              | count(tblemployee.titleID) | +--------------------+----------------------------+ | Programmer/Analyst |             5              | | Web Developer      |             3              | | CEO/President      |             2              | +--------------------+----------------------------+ 

This output should be a bit more presentable. Note that in the modified query, the extended name (table name and field name) was used to refer to the titleID field. Failure to do this would result in an ambiguity error.

TIP

We highly recommend looking through the official MySQL documentation to get an idea of the full capabilities of the product. This chapter should not be seen as a complete reference to the capabilities of this wonderful application.

Alternative Database Servers

Besides MySQL, Tiger boasts several industrial-strength commercial SQL database solutions. If you're interested in pointing and clicking, take a look at these products:

• FrontBase A Mac OS X native SQL database system that features a fully graphical administration and RealBASIC integration. http://www.frontbase.com/.

• OpenBase Another commercial SQL system for Mac OS X. OpenBase features GUI tools for designing database schema, as well as application development using either RealBASIC or RADStudio. http://www.openbase.com.

• FileMaker Pro Apple's "own" database package, FileMaker Pro is available for Mac OS X, Windows, and handheld devices. FileMaker Pro is useful for creating desktop database applications and is available in a Server version for developing web-based applications. http://www.filemaker.com/.

• 4D A high-end database development tool for creating desktop and web applications. 4D provides a far richer development environment than FileMaker Pro but, as a result, has a much higher learning curve. Available on both Windows and Macintosh platforms, 4D is an excellent choice for creating complex data-driven desktop applications. http://www.4d.com/.

• PostgreSQL The PostgreSQL platform is a powerful alternative to MySQL that supports many features that are not yet available in MySQL. PostgreSQL is available from http://www.postgresql.org/.

• SQLite SQLite is an embeddable SQL database engine. It does not require a server, supports a large subset of SQL (including views and subselects!), writes to a flat file on your system, and is very fast for common operations; http://www.sqlite.org/.

Accessing MySQL Through a GUI

If you find yourself managing MySQL databases frequently, you might find that the command-line client is a less-than-appealing solution for controlling your database server. GUI clients can provide simpler access to the data and easy editing of MySQL user permissions and database schema.

Two of the best clients for working with MySQL are the native Cocoa application CocoaMySQL and the PHP-based phpMyAdmin. We will take a short look at how you can install and configure these applications to start working with your MySQL server immediately.

CocoaMySQL

CocoaMySQL is a fast and easy way to work with databases directly from a native Tiger GUI. You can download the application for free from http://cocoamysql.sourceforge.net.

When starting CocoaMySQL, it will prompt you for connection information for your database server, as seen in Figure 19.1. Enter the hostname (or localhost/127.0.0.1 for the local machine) and the username and password you created when installing the MySQL Server. If you didn't change anything, the default username is root with no password.

Figure 19.1. Enter the information needed to connect to your MySQL server.

You can save the information you've entered by choosing Save To Favorites from the Favorites pop-up menu. Click Connect to connect to the MySQL Server.

The CocoaMySQL interface is divided into three primary components: databases, tables, and information, as shown in Figure 19.2.

Figure 19.2. The CocoaMySQL Windows provides quick access to all the data stored in MySQL.

Use the pop-up menu in the Databases area to select a database to work with. The Tables list will refresh to display all the tables in the chosen database. Selecting a table will display information about the choice in the main viewing area to the right. The Structure, Content, and Custom Query buttons can be used to edit and display the field definitions for the table, the data stored in the table, and create custom SQL queries, respectively.

The buttons below each of the areas can be used to refresh the display, duplicate the selected resources, and delete (drop) or add a new database, table, or record.

Most anything you would commonly need to do from the MySQL command line can be managed within CocoaMySQL. If you find that you would like additional control, however, (such as editing user accounts), a better solution might be phpMyAdmin.

phpMyAdmin

The phpMyAdmin application works through your web browser to provide access to just about every feature MySQL has to offer. To use phpMyAdmin, you must have access to a PHP server or have enabled PHP on your Tiger computer. The instructions to do this are located in Chapter 23, "Creating a Web Server."

Start by downloading the latest phpMyAdmin distribution from http://www.phpmyadmin.net/home_page/. Unarchive the distribution in your PHP-enabled server, or the ~/Sites folder if you've enabled PHP on your desktop. To keep things simple, you may want to rename the distribution folder or create an Apache alias to the distribution folder; the default installation folder includes a version number and patch-level string.

 [www:~/public_html] jray% mv phpMyAdmin-2.6.0-pl3 phpMyAdmin 

Next, you'll need to set up a user account in MySQL that can read the MySQL user table this will enable phpMyAdmin's ability to authenticate users against known MySQL users. To do this, invoke the mysql client with the username and password you used to set it up, and then enter the following command. You should replace <a new password> with a password of your choice.

 GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY '<a new password>'; GRANT SELECT (     Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,     Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,     File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,     Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,     Execute_priv, Repl_slave_priv, Repl_client_priv     ) ON mysql.user TO 'pma'@'localhost'; GRANT SELECT ON mysql.db TO 'pma'@'localhost'; GRANT SELECT ON mysql.host TO 'pma'@'localhost'; GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)     ON mysql.tables_priv TO 'pma'@'localhost'; 

This creates a new MySQL user, pma, with the password you've chosen. Finally, you'll need to edit the phpMyAdmin file config.inc.php to make a few configuration changes.

Search for the following lines:

 $cfg['Servers'][$i]['host']          = 'localhost'; // MySQL hostname or IP address $cfg['Servers'][$i]['port']          = ''; // MySQL port - leave blank for default port $cfg['Servers'][$i]['socket']        = ''; // Path to the socket - leave blank for default socket $cfg['Servers'][$i]['connect_type']  = 'tcp'; // How to connect to MySQL server ('tcp' or 'socket') $cfg['Servers'][$i]['extension']     = 'mysql'; // The php MySQL extension to use ('mysql' or 'mysqli') $cfg['Servers'][$i]['compress']      = FALSE; // Use compressed protocol for the MySQL connection // (requires PHP >= 4.3.0) $cfg['Servers'][$i]['controluser']   = 'pma'; // MySQL control user settings // (this user must have read-only $cfg['Servers'][$i]['controlpass']   = '<pma password>'; // access to the "mysql/user" // and "mysql/db" tables). // The controluser is also // used for all relational // features (pmadb) $cfg['Servers'][$i]['auth_type']     = 'http'; // Authentication method (config, http or cookie based)? 

Edit the lines to reflect the host you are connecting to, the username and password you just set up in MySQL (pma and whatever you chose), and finally, set $cfg['Servers'][$i]['auth_type'] to http.

Your phpMyAdmin installation should now be ready to go. Point your web browser at the phpMyAdmin directory on your web server, such as http://127.0.0.1/~jray/phpMyAdmin/ for the installation in my local Sites directory. You will be prompted for a username and password supply the account and password you used when you set up MySQL. If you didn't configure a username and password, the default is root with an empty password.

The default phpMyAdmin page, shown in Figure 19.3, provides access to serverwide configuration, including a privileges section for managing user accounts. You can reach this screen at any time by clicking the Home icon.

Figure 19.3. The phpMyAdmin Home page provides serverwide configuration options.

For most database-specific operations, the phpMyAdmin interface is divided into three primary areas, as shown in Figure 19.4.

Figure 19.4. The phpMyAdmin interface is divided into sections for databases, tables, and overall Information.

Choosing a database in the Databases area will refresh the browser to display the tables within that database. Choosing a table will, in turn, refresh to display information about the database on the right side of the screen. Use the tabs across the top of the information area to browse and edit the table contents, search the data, insert records, and more. A complete guide to using phpMyAdmin can be displayed by pointing your browser at the Documentation directory within the phpMyAdmin distribution.